SQLAchemy - 什么是ORM
ORM简介
ORM简介：
对象关系映射(Object Relational Mapping，简称ORM)是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术。
ORM的方法论基于三个核心原则：
· 简单：以最基本的形式建模数据。
· 传达性：数据库结构被任何人都能理解的语言文档化。
· 精确性：基于数据模型创建正确标准化了的结构。
ORM技术特点：
1. 提高了开发效率。由于ORM可以自动对Entity对象与数据库中的Table进行字段与属性的映射，
所以我们实际可能已经不需要一个专用的、庞大的数据访问层。
2. ORM提供了对数据库的映射，不用sql直接编码，能够像操作对象一样从数据库获取数据。
ORM的缺点：
1. 会牺牲程序的执行效率和会固定思维模式。
2. 从系统结构上来看,采用ORM的系统一般都是多层系统，系统的层次多了，效率就会降低。
ORM是一种完全的面向对象的做法，而面向对象的做法也会对性能产生一定的影响。
当有了对应关系之后，不再需要编写SQL语句，取而代之的是操作：类、对象。
SQLAchemy - 介绍
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，
简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
安装
架构与流程
- Engine：框架的引擎
- Connection Pooling ：数据库连接池
- Dialect ：选择连接数据库的DB API种类
- Schema/Types ：架构和类型
- SQL Exprression Language ：SQL表达式语言
流程分析
1 使用者通过ORM对象提交命令
2 将命令交给SQLAlchemy Core（Schema/Types SQL Expression Language）转换成SQL
3 使用 Engine/ConnectionPooling/Dialect 进行数据库操作
3.1 匹配使用者事先配置好的egine
3.2 egine从连接池中取出一个链接
3.3 基于该链接通过Dialect调用DB API，将SQL转交给它去执行
##### 上述流程分析，可以大致分为两个阶段 #####
第一个阶段（流程1-2）：将SQLAlchemy的对象换成可执行的sql语句
第二个阶段（流程3）：将sql语句交给数据库执行
DB API
# SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，
根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
#1. MySQL-Python
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
#2. pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
#3. MySQL-Connector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
#4. cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
#5. SQLAlchemy官方文档
基于SQLALchemy写原生SQL：因为内部提供了连接池
- SQLAclchemy连接池
import time
import threading
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine
engine = create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8",# 链接MySQL
max_overflow=0, # 超过连接池大小后最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
def task(arg):
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
"select * from t1"
)
result = cursor.fetchall()
cursor.close()
conn.close()
for i in range(20):
t = threading.Thread(target=task, args=(i,))
t.start()
- DBUtils+pymysql 做连接池
SQLAlchemy - 引用
以下所有代码片段都使用了统一的引用，该引用如下：(如有其它引用会提前声明)
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Table
from sqlalchemy.orm import relationship, sessionmaker
SQLAlchemy - 创建表
ORM中：
类 ===> 表
对象 ==> 表中的一行记录
--- models.py
import datetime
Base = declarative_base() # 创建基类
class Student(Base):
__tablename__ = 'students' # 指定表名
__table_args__ = {
# "mysql_engine":"MyISAM", # 指定存储引擎
"mysql_charset":"utf8" # 指定建表字符集
}
id = Column(Integer, primary_key=True, autoincrement=True) # autoincrement：自增长ID
name = Column(String(30), nullable=False, unique=True) # nullable：是否为空，unique：唯一索引
gender = Column(Integer)
std_id = Column(String(10))
teacher = Column(String(30))
ctime = Column(Datetime, default=datetime.datetime.now ) # 注意now没有括号！
- 以下为示例说明不在表结构中：
## math = Column(Integer) ##
# __table_args__ = UniqueConstraint("student_id","class_id", name="uix_student_id_class_id") # 联合唯一索引
# Index("ix_id_name", "name","ctime") # 普通的联合索引
# show create table students 可以查看建表语句;默认是Innodb,lating-1.如果想显示中文需要修改指定建表的类型,同时,engine也要指定编码格式
- end
def __str__(self):
return self.name
engine = create_engine("mysql+pymysql://root:root@localhost/student?charset=utf8", echo=True) # 连接数据库，显示SQL语句
# 创建所有继承自 Base 的类对应的表
Base.metadata.create_all(engine)
# 删除所有继承自 Base 的类对应的表
Base.metadata.drop_all(engine)
sqlslchemy在数据库中创建表之前，会先检查该表是否存在，如果不存在，才会去创建新表。
如果新加了一个 math 字段，但是由于数据库中已有students表，所以不会去创建新表，match 字段也就不存在
SQLAlchemy单表操作 - 插入数据
--- demo1.py
Session = sessionmaker(bind=engine) # 获取 Session类
ses = Session() # 获取session对象
- 实例化数据对象
zhangsan = Student(name="zhangsan", gender=1, std_id='XS331', teacher='hanmeimei', math=90)
lisi = Student(name="lisi", gender=1, std_id='XS332', teacher='hanmeimei', math=10)
wangwu = Student(name="wangwu", gender=0, std_id='XS333', teacher='lilei', math=60)
zhaoliu = Student(name="zhaoliu", gender=0, std_id='XS337', teacher='lilei', math=80)
- 增加
ses.add(zhangsan) # 单条添加
ses.add_all([lisi, wangwu, zhaoliu]) # 批量添加
ses.commit() # 注意一定要提交
SQLAlchemy单表操作 - 修改数据
# 直接修改数据
zhangsan = ses.query(Student).filter_by(name='zhangsan').first()
zhangsan.math = 91
ses.commit()
# 创建数据后修改
liqi = Student(name="liqi", gender=1, std_id='XS335', teacher='peiqi', math=100)
ses.add(liqi)
liqi = ses.query(Student).filter_by(name='liqi').first()
liqi.gender = 0
ses.commit()
通过update进行修改
# sess.quert(student).filter(Student.id > 10).update({Student.name:Student.name + "007"}, synchronize_sess=False)
# synchronize_sess： False/evaluate 对字符串的拼接/对数字进行计算，
# sess.query(Student).filter_by(name='liming').update({'gender':0})
# sess.commit()
SQLAlchemy单表操作 - 删除数据
# filter 后直接 delete
# result = sess.query(Student).filter(Student.id == 1 ).delete()
# sess.commit()
# 对象.delete( 数据对象)
# result = sess.query(Student).filter(Student.id == 2 ).first()
# sess.delete(result)
# sess.commit()
SQLAlchemy单表操作 - 查询数据
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/student?charset=utf8")
XXX = sessionmaker(bind=engine)
sess = XXX()
# =操作符：注意 filter 与 filter_by 的区别
# result1 = sess.query(student).all()
# result2 = sess.query(student).filter(student.name == "zhangsan").all()
# result3 = sess.query(student).filter_by(name = "zhangsan").all()
# for i in result1:
# print(i.name)
# = 操作符
# students_99 = sess.query(Student).filter_by(name ='zhangsan').all()
# students_100 = sess.query(Student).filter(Student.name == 'zhangsan').all()
# for i in students_100:
# print(i.name)
# ！= 操作符
# student_101 = sess.query(Student).filter_by(name != 'liming').all() # error
# student_98 = sess.query(Student).filter(Student.name != 'liming').all()
# for i in student_98:
# print(i.name)
# > ,<, <=, >=, 操作符
# student_60 = sess.query(Student).filter(Student.math > 10).all()
# student_61= sess.query(Student).filter_by(math>10).all() # error
# student_62 = sess.query(Student).filter(Student.math < 100).all()
# student_63 = sess.query(Student).filter(Student.math <= 90).all()
# student_64 = sess.query(Student).filter(Student.math >= 90).all()
# student_64 = sess.query(Student).filter(Student.math <> 90).all() # error
# for i in student_60:
# print(i.math)
# for i in student_62:
# print(i.math)
# for i in student_63:
# print(i.math)
# for i in student_64:
# print(i.math)
# 根据主键获取对象
# students_1 = sess.query(Student).get(1)
# print(students_1
# value指定要获取的字段 返回的是生成器
# students_3 = sess.query(Student).value(Student.name)
# print(students_3)
# values 指定多个字段 返回的是生成器
# students_3_2 = sess.query(Student).values(Student.id,Student.name)
# print(students_3_2)
# for i in students_3_2:
# print(i)
# 一次获取多个字段值
# students_4 = sess.query(Student.name,Student.gender).all()
# print(students_4)
# for i in students_4:
# print(i)
# 后续添加的方式选择要得到的字段结果
# students_5 = sess.query(Student.name).add_columns(Student.gender).all()
# print(students_5)
# student_6 = sess.query(Student).filter_by(name="王大麻子").one()
# print(student_6)
# 字段别名
# student_7 = sess.query(Student.name.label('std_name')).all()
# print(student_7)
# for i in student_7:
# print(i.std_name)
# print(i[0]) # 第二种取值方式
# student_8 = sess.query(Student.name).all()
# print(student_8)
# for i in student_8:
# print(i.name)
# print(i[0])
# 分页
# student_10 = sess.query(Student).limit(2).offset(1).all()
# student_11 = sess.query(Student).offset(1).limit(2).all()
# student_12 = sess.query(Student)[1:3]
# for i in student_10:
# print(i.id)
# for i in student_11:
# print(i.id)
# for i in student_12:
# print(i.id)
# filter 和 filter_by
# student_13 = sess.query(Student).filter_by(id=1).one_or_none()
# student_14 = sess.query(Student).filter(Student.id == 1).one_or_none()
# print(student_13)
# print(student_14)
# chain 链式调用
# student_15 = sess.query(Student).filter_by(gender=1).filter(Student.math>80).all()
# # student_15_2 = sess.query(Student).filter_by(gender=1).filter_by(math>80).all() # error
# student_16 = sess.query(Student).filter(Student.gender == 1).filter(Student.math>80).all()
# for i in student_15:
# print(i.name)
# for i in student_16:
# print(i.name)
# like 操作符 % 代表任意多个字符 _ 代表一个字符
# student_17 = sess.query(Student).filter(Student.name.like("%i%")).all()
# for i in student_17:
# print(i.name)
# student_17_2 = sess.query(Student).filter(Student.name.like("%i_i")).all()
# for i in student_17_2:
# print(i.name)
# not like
# student_70 = sess.query(Student).filter(~Student.name.like("%i_i")).all()
# student_71 = sess.query(Student).filter(Student.name.notlike("%i_i")).all()
# for i in student_70:
# print(i.name)
# print(student_70 == student_71)
# in 操作符
# student_18 = sess.query(Student).filter(Student.name.in_(['zhangsan','lisi','wangwu'])).all()
# for i in student_18:
# print(i.name)
# not in
# student_19 = sess.query(Student).filter(~Student.name.in_(['lisi'])).all()
# student_19_2 = sess.query(Student).filter(Student.name.notin_(['lisi'])).all()
# for i in student_19:
# print(i.name)
# print(student_19 == student_19_2)
# 是否为null
# student_20 = sess.query(Student).filter(Student.name != None).all()
# student_21 = sess.query(Student).filter(~Student.name.is_(None)).all()
# student_22 = sess.query(Student).filter(Student.name.isnot(None)).all()
# student_21_3 = sess.query(Student).filter(Student.name.is_('zhangsan')).all() # error
# student_21_2 = sess.query(Student).filter(Student.name.is_(1)).all()
# for i in student_20:
# print(i.name)
# print(student_20 == student_21 == student_22)
# student_23 = sess.query(Student).filter(Student.math.is_(90)).all()
# student_24 = sess.query(Student).filter(Student.math == None).all()
# 类别名
# flower = aliased(Student)
# student_9 = sess.query(flower.name).all()
# print(student_9)
# and
# student_25 = sess.query(Student).filter(Student.gender == 1, Student.math > 70).all()
# student_26 = sess.query(Student).filter(and_(Student.gender == 1, Student.math > 70)).all() # 别忘了引入该方法
# student_27 = sess.query(Student).filter(Student.gender == 1).filter(Student.math > 70).all()
# for i in student_25:
# print(i.name)
# print(student_25 == student_26 == student_27)
# or
# student_28 = sess.query(Student).filter(or_(Student.gender == 1, Student.math > 80)).all()
# for i in student_28:
# print(i.name)
# 错误用法
# error_1 = sess.query(Student).filter_by(and_(Student.id == 1, Student.name == 'lisi')).first()
# error_2 = sess.query(Student).filter_by(and_(id=1, name='lisi')).first()
# error_3 = sess.query(Student).filter(and_(id=1, name='lisi')).first()
# one 有且只有一个，否则报错
# student_30 = sess.query(Student).filter(Student.id == 1).one()
# print(student_30)
# 可以只有一个或者没有，不能为多个结果，否则报错
# student_31 = sess.query(Student).filter(Student.id == 10).one_or_none()
# print(student_31)
# text 原生sql条件语句
# student_31 = sess.query(Student).filter(text("name='lisi'")).first() # 注意值为字符串时的写法
# student_32 = sess.query(Student).filter(text("id=1")).first() # 注意值为 数字 的写法
# student_31 = sess.query(Student).filter(text("id>1 and math>10")).all()
# for i in student_31:
# print(i.name)
# order_by 排序
# student_33 = sess.query(Student).filter(text("math >= 10")).order_by(~text("math")).all() # 逆序
# student_33_2 = sess.query(Student).filter(text("math >= 10")).order_by(text("math")).all() # 顺序
# student_33_2_1 = sess.query(Student).filter(text("math >= 10")).order_by(desc(text("math"))).all() # 逆序
# student_33_3 = sess.query(Student).order_by(desc(Student.math)).all() # 逆序
# student_33_4 = sess.query(Student).order_by(Student.math.desc()).all() # 逆序
# student_33_5 = sess.query(Student).order_by(~Student.math).all() # 逆序
# student_33_6 = sess.query(Student).order_by(Student.math).all() # 顺序
# print(student_31)
# print(student_32)
# print("___"* 30)
# for i in student_33:
# print(i.math)
# print("___"* 30)
# for i in student_33_2:
# print(i.math)
# for i in student_33_3:
# print(i.math)
# for i in student_33_4:
# print(i.math)
# for i in student_33_5:
# print(i.math)
# for i in student_33_6:
# print(i.math)
# text 插入变量
# student_34 =sess.query(Student).filter(text("gender=:sex and math>:score")).params(sex=1, score=1).all()
# for i in student_34:
# print(i.name)
# from_statement 原生sql语句
# student_35 = sess.query(Student).from_statement(text("select * from students where id=:id")).params(id=1).one()
# student_36 = sess.query(Student).from_statement("select * from students where math>:score").params(score=10).all()
# student_36_1 = sess.query(Student).from_statement("select * from students where math>10").all()
# print(student_35)
# for i in student_36:
# print(i.name)
# print(student_36 == student_36_1)
# count
# student_38 = sess.query(Student).filter_by(gender=1).count()
# student_38_2 = sess.query(func.count(Student.name),Student.name).group_by(Student.name).all()
# student_38_3 = sess.query(func.count(Student.name),Student.gender).group_by(Student.gender).all()
# print(student_38_2)
# print(student_38_3)
# group_by 分组
# student_39 = sess.query(Student).group_by(Student.gender == 1).count()
# print(student_39)
# having
# student_39_1 = sess.query(Student).group_by(Student.gender == 1).having(Student.math>60).all()
# for i in student_39_1:
# print(i)
# distinct
# student_40 = sess.query(distinct(Student.name)).all()
# for i in student_40:
# print(i)
浙公网安备 33010602011771号