SQLAlchemy
SQLAlchemy 是一个ORM框架，大量使用了元编程。
安装
$ pip install sqlalchemy
文档
http://docs.sqlalchemy.org/en/latest/
开发
SQLAlchemy内部使用了连接池
创建连接
数据库连接的事情交给引擎
1 import sqlalchemy 2 3 username = "wangjie" 4 password = "wangjie" 5 ip = "127.0.0.1" 6 port = 9999 7 datebase = "schllo" 8 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 9 username, password, ip, port, datebase 10 ) 11 12 # 创建连接 13 engine = sqlalchemy.create_engine(conn_str, echo=True)
Declare a Mapping 创建映射
创建基类
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 4 username = "wangjie" 5 password = "wangjie" 6 ip = "127.0.0.1" 7 port = 9999 8 datebase = "schllo" 9 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 10 username, password, ip, port, datebase 11 ) 12 13 # 创建连接 14 engine = sqlalchemy.create_engine(conn_str, echo=True) 15 # 创建基类， 便于实体类继承 16 Base = declarative_base()
创建实体类
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 5 username = "wangjie" 6 password = "wangjie" 7 ip = "127.0.0.1" 8 port = 9999 9 datebase = "schllo" 10 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 11 username, password, ip, port, datebase 12 ) 13 14 # 创建连接 15 engine = sqlalchemy.create_engine(conn_str, echo=True) 16 # 创建基类， 便于实体类继承 17 Base = declarative_base() 18 19 # 创建实体类 20 class Student(Base): 21 # 指定表名，一定要和数据库的表名一致 22 __tablename__ = "student" 23 24 # 与数据库中表的字段相对应 25 id = Column(Integer, primary_key=True) 26 name = Column(String(64)) 27 age = Column(Integer) 28 29 def __repr__(self): 30 return "{} id={} name={} age={}".format( 31 self.__class__.__name__, self.id, self.name, self.age 32 )
实例化
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 5 username = 'wangjie' 6 password = 'wangjie' 7 ip = '172.16.102.155' 8 port = 3306 9 datebase = 'school' 10 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 11 username, password, ip, port, datebase 12 ) 13 14 # 创建连接 15 engine = sqlalchemy.create_engine(conn_str, echo=True) 16 # 创建基类， 便于实体类继承 17 Base = declarative_base() 18 19 # 创建实体类 20 class Student(Base): 21 # 指定表名，一定要和数据库的表名一致 22 __tablename__ = "student" 23 24 # 与数据库中表的字段相对应 25 id = Column(Integer, primary_key=True) 26 name = Column(String(64), nullable=False) 27 age = Column(Integer) 28 29 def __repr__(self): 30 return "{} id={} name={} age={}".format( 31 self.__class__.__name__, self.id, self.name, self.age 32 ) 33 34 # 实例化 35 s = Student(name="tom") 36 print(s.name) 37 s.age = 79 38 print(s.age)
创建表
可以使用SQLAlchemy来创建、删除表。但是在生产环境中很少这样创建表，都是在系统上线的时候由脚本生成的，更别说删除这样危险的操作了，下面只是知道有这种创建、删除的方式。
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 5 username = 'wangjie' 6 password = 'wangjie' 7 ip = '172.16.102.155' 8 port = 3306 9 datebase = 'school' 10 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 11 username, password, ip, port, datebase 12 ) 13 14 # 创建连接 15 engine = sqlalchemy.create_engine(conn_str, echo=True) 16 # 创建基类， 便于实体类继承 17 Base = declarative_base() 18 19 # 创建实体类 20 class Student(Base): 21 # 指定表名，一定要和数据库的表名一致 22 __tablename__ = "student" 23 24 # 与数据库中表的字段相对应 25 id = Column(Integer, primary_key=True) 26 name = Column(String(64), nullable=False) 27 age = Column(Integer) 28 29 def __repr__(self): 30 return "{} id={} name={} age={}".format( 31 self.__class__.__name__, self.id, self.name, self.age 32 ) 33 34 # 创建表 35 Base.metadata.create_all(engine) 36 # 删除表 37 Base.metadata.drop_all(engine)
创建会话 session
操作数据库需要创建一个会话，会话建立在连接上，连接被引擎管理。
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 from sqlalchemy.orm import sessionmaker 5 6 username = 'wangjie' 7 password = 'wangjie' 8 ip = '172.16.102.155' 9 port = 3306 10 datebase = 'school' 11 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 12 username, password, ip, port, datebase 13 ) 14 15 # 创建连接 16 engine = sqlalchemy.create_engine(conn_str, echo=True) 17 # 创建基类， 便于实体类继承 18 Base = declarative_base() 19 # 创建会话 session 20 Session = sessionmaker(bind=engine) # 返回一个类 21 session = Session() # 类实例化 22 23 # 创建实体类 24 class Student(Base): 25 # 指定表名，一定要和数据库的表名一致 26 __tablename__ = "student" 27 28 # 与数据库中表的字段相对应 29 id = Column(Integer, primary_key=True) 30 name = Column(String(64), nullable=False) 31 age = Column(Integer) 32 33 def __repr__(self): 34 return "{} id={} name={} age={}".format( 35 self.__class__.__name__, self.id, self.name, self.age 36 ) 37
session 对象线程不安全，所以不同线程使用不同的session对象。Session类和engine都是线程安全的，有一个就行了。
ERUD 操作
增
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 from sqlalchemy.orm import sessionmaker 5 6 username = 'wangjie' 7 password = 'wangjie' 8 ip = '172.16.102.155' 9 port = 3306 10 datebase = 'school' 11 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 12 username, password, ip, port, datebase 13 ) 14 15 # 创建连接 16 engine = sqlalchemy.create_engine(conn_str, echo=True) 17 # 创建基类， 便于实体类继承 18 Base = declarative_base() 19 # 创建会话 session 20 Session = sessionmaker(bind=engine) # 返回一个类 21 session = Session() # 类实例化 22 23 # 创建实体类 24 class Student(Base): 25 # 指定表名，一定要和数据库的表名一致 26 __tablename__ = "student" 27 28 # 与数据库中表的字段相对应 29 id = Column(Integer, primary_key=True) 30 name = Column(String(64), nullable=False) 31 age = Column(Integer) 32 33 def __repr__(self): 34 return "{} id={} name={} age={}".format( 35 self.__class__.__name__, self.id, self.name, self.age 36 ) 37 38 # 实例化增加内容 39 s = Student(name="wangjie", age=22) 40 s1 = Student(name="wangjiao", age=23) 41 s2 = Student(name="xuyanbiao", age=22) 42 43 try: 44 session.add(s) 45 session.add_all([s1, s2]) 46 session.commit() 47 except: 48 session.rollback() 49 raise
主键没有值就是新增，主键有值就是找到主键对应的记录进行修改。
简单查询
使用query()方法，返回一个query对象
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 from sqlalchemy.orm import sessionmaker 5 6 username = 'wangjie' 7 password = 'wangjie' 8 ip = '172.16.102.155' 9 port = 3306 10 datebase = 'school' 11 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 12 username, password, ip, port, datebase 13 ) 14 15 # 创建连接 16 engine = sqlalchemy.create_engine(conn_str) 17 # 创建基类， 便于实体类继承 18 Base = declarative_base() 19 # 创建会话 session 20 Session = sessionmaker(bind=engine) # 返回一个类 21 session = Session() # 类实例化 22 23 # 创建实体类 24 class Student(Base): 25 # 指定表名，一定要和数据库的表名一致 26 __tablename__ = "student" 27 28 # 与数据库中表的字段相对应 29 id = Column(Integer, primary_key=True) 30 name = Column(String(64), nullable=False) 31 age = Column(Integer) 32 33 def __repr__(self): 34 return "{} id={} name={} age={}".format( 35 self.__class__.__name__, self.id, self.name, self.age 36 ) 37 38 # 查询全部内容 39 students = session.query(Student) 40 for student in students: 41 print(student) 42 43 # 查询主键为2的内容 44 student = session.query(Student).get(2) 45 print(student)
query() 将实体类传入，返回类的可迭代对象，这时候并不查询。迭代它就执行SQL来查询数据库，封装数据到指定类的实例。get() 使用主键查询，返回一条传入类的一个实例。
改
先查，修改后，再提交更改
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 from sqlalchemy.orm import sessionmaker 5 6 username = 'wangjie' 7 password = 'wangjie' 8 ip = '172.16.102.155' 9 port = 3306 10 datebase = 'school' 11 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 12 username, password, ip, port, datebase 13 ) 14 15 # 创建连接 16 engine = sqlalchemy.create_engine(conn_str) 17 # 创建基类， 便于实体类继承 18 Base = declarative_base() 19 # 创建会话 session 20 Session = sessionmaker(bind=engine) # 返回一个类 21 session = Session() # 类实例化 22 23 # 创建实体类 24 class Student(Base): 25 # 指定表名，一定要和数据库的表名一致 26 __tablename__ = "student" 27 28 # 与数据库中表的字段相对应 29 id = Column(Integer, primary_key=True) 30 name = Column(String(64), nullable=False) 31 age = Column(Integer) 32 33 def __repr__(self): 34 return "{} id={} name={} age={}".format( 35 self.__class__.__name__, self.id, self.name, self.age 36 ) 37 38 # 修改记录为3的内容 39 student = session.query(Student).get(3) 40 print(student) # Student id=3 name=xuyanbiao age=22 41 student.name = "yangziwen" 42 student.age = 23 43 print(student) # Student id=3 name=yangziwen age=23 44 session.add(student) 45 session.commit()
删除
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 from sqlalchemy.orm import sessionmaker 5 6 username = 'wangjie' 7 password = 'wangjie' 8 ip = '172.16.102.155' 9 port = 3306 10 datebase = 'school' 11 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 12 username, password, ip, port, datebase 13 ) 14 15 # 创建连接 16 engine = sqlalchemy.create_engine(conn_str, echo=True) 17 # 创建基类， 便于实体类继承 18 Base = declarative_base() 19 # 创建会话 session 20 Session = sessionmaker(bind=engine) # 返回一个类 21 session = Session() # 类实例化 22 23 # 创建实体类 24 class Student(Base): 25 # 指定表名，一定要和数据库的表名一致 26 __tablename__ = "student" 27 28 # 与数据库中表的字段相对应 29 id = Column(Integer, primary_key=True) 30 name = Column(String(64), nullable=False) 31 age = Column(Integer) 32 33 def __repr__(self): 34 return "{} id={} name={} age={}".format( 35 self.__class__.__name__, self.id, self.name, self.age 36 ) 37 38 try: 39 # 还未提交的数据库的数据删除会抛异常 40 # student = Student(name="xuyanbiao", age=23) 41 # session.delete(student) # # Instance '<Student at 0x15a64175f8>' is not persisted 未持久的异常 42 # session.commit() 43 44 # 查询出来后再进行删除 45 student = session.query(Student).get(3) 46 session.delete(student) 47 session.commit() 48 except Exception as e: 49 session.rollback() 50 print(e)
状态
每一个实体，都有一个状态属性_sa_instance_state，其类型是sqlalchemy.orm.state.InstanceState，可以使用sqlalchemy.inspect(entity)函数查看状态。
| 状态 | 说明 |
|---|---|
| transient | 实体类尚未加入到session中，同时并没有保存到数据库中 |
| pending | transient的实体被add()到session中，状态切换到pending，但它还没有flush到数据库中 |
| persistent | session中的实体对象对应着数据库中的真实记录，pending状态在提交成功后可以变成persistent状态，或者查询成功返回的实体也是persistent状态 |
| deleted | 实体被删除且已经flush但未commit完成，事务提交成功了，实体变成detached，事务失败返回persistent状态 |
| detached | 删除成功的实体进入这个状态 |
新建一个实体，状态是transient的。
一旦add() 后从transient变成pending状态。
成功commit() 后从pending变成persistent状态。
成功查询返回的实体对象，也是persistent状态。persistent状态的实体，修改依然是persistent状态。
persistent状态的实体，删除后，flush后但没有commit，就变成deteled状态，commit 后变成detached状态，commit 失败，还原到persistent状态，flush方法，主动把改变应用到数据库中去。
删除、修改操作，需要对应一个真实的记录，所以要求实体对象是persistent状态。
复杂查询
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy.orm import sessionmaker 4 from sqlalchemy import Column, Integer, String, Enum, Date 5 import enum 6 from sqlalchemy import and_, or_, not_ 7 8 username = "root" 9 password = "python" 10 ip = "192.168.161.129" 11 port = 3306 12 database = "test" 13 14 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 15 username, password, ip, port, database 16 ) 17 18 engine = sqlalchemy.create_engine(conn_str, echo=True) 19 Base = declarative_base() 20 Session = sessionmaker(bind=engine) 21 session = Session() 22 23 24 class MyEnum(enum.Enum): 25 M = "M" 26 F = "F" 27 28 29 class Employee(Base): 30 __tablename__ = "employees" 31 32 emp_no = Column(Integer, primary_key=True) 33 birth_date = Column(Date, nullable=False) 34 first_name = Column(String(14), nullable=False) 35 last_name = Column(String(16), nullable=False) 36 gender = Column(Enum(MyEnum), nullable=False) 37 hire_date = Column(Date, nullable=False) 38 39 def __repr__(self): 40 return "{} emp_no={}, name={} {}, gender={}".format( 41 sessionmaker.__class__.__name__, self.emp_no, self.first_name, self.last_name, self.gender.value 42 ) 43 44 def show(emps): 45 for x in emps: 46 print(x) 47 print("-"*50, end="\n\n") 48 49 50 # 简单条件查询 51 emps = session.query(Employee).filter(Employee.emp_no > 10015) # emp_no大于10015的 52 show(emps) 53 54 # and条件 55 emps = session.query(Employee).filter(Employee.emp_no > 10015).filter(Employee.gender == MyEnum.F) # emp_no大于10015并且性别为女的 56 show(emps) 57 emps = session.query(Employee).filter((Employee.emp_no > 10015) & (Employee.gender == MyEnum.F)) 58 show(emps) 59 emps = session.query(Employee).filter(and_(Employee.emp_no > 10015, Employee.gender == MyEnum.F)) 60 show(emps) 61 62 # or条件 63 emps = session.query(Employee).filter((Employee.emp_no > 10016) | (Employee.emp_no < 10003)) # emp_no大于10016或者小于10003的 64 show(emps) 65 emps = session.query(Employee).filter(or_(Employee.emp_no > 10016, Employee.emp_no < 10003)) 66 show(emps) 67 68 # not条件 69 emps = session.query(Employee).filter(~(Employee.emp_no < 10016)) # emp_no小于10016取反 70 show(emps) 71 emps = session.query(Employee).filter(not_(Employee.emp_no < 10016)) 72 show(emps) 73 74 # in条件 75 emplist = [10010, 10015, 10018] 76 emps = session.query(Employee).filter(Employee.emp_no.in_(emplist)) 77 show(emps) 78 79 # not in条件 80 emplist = [10010, 10015, 10018] 81 emps = session.query(Employee).filter(~Employee.emp_no.in_(emplist)) 82 show(emps) 83 84 # like条件 85 emps = session.query(Employee).filter(Employee.last_name.like("P%")) 86 show(emps) 87 88 # like条件 89 emps = session.query(Employee).filter(Employee.last_name.like("P%")) 90 show(emps) 91 92 # 排序 93 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no) # 升序 94 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.asc()) 95 show(emps) 96 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.desc()) # 降序 97 show(emps) 98 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.last_name).order_by(Employee.emp_no.desc()) # 多列排序 99 show(emps) 100 101 # 分页 102 emps = session.query(Employee).limit(5) 103 show(emps) 104 emps =session.query(Employee).limit(5).offset(16) 105 show(emps) 106 107 # 消费者方法 108 emps = session.query(Employee) 109 print(len(list(emps))) 110 print(emps.count()) # 总行数 111 112 print("-"*50, end="\n\n") 113 114 print(emps.all()) # 取所有数据 115 116 print("-"*50, end="\n\n") 117 118 print(emps.first()) # 取行首 119 120 print("-"*50, end="\n\n") 121 122 # print(emps.one()) # 取一条数据，如果查询结果是多条这个方法抛异常 123 print(emps.limit(1).one())
聚合、分组
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy.orm import sessionmaker 4 from sqlalchemy import Column, Integer, String, Enum, Date 5 import enum 6 from sqlalchemy import func 7 8 9 username = "root" 10 password = "python" 11 ip = "192.168.161.129" 12 port = 3306 13 database = "test" 14 15 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 16 username, password, ip, port, database 17 ) 18 19 engine = sqlalchemy.create_engine(conn_str, echo=True) 20 Base = declarative_base() 21 Session = sessionmaker(bind=engine) 22 session = Session() 23 24 25 class MyEnum(enum.Enum): 26 M = "M" 27 F = "F" 28 29 30 class Employee(Base): 31 __tablename__ = "employees" 32 33 emp_no = Column(Integer, primary_key=True) 34 birth_date = Column(Date, nullable=False) 35 first_name = Column(String(14), nullable=False) 36 last_name = Column(String(16), nullable=False) 37 gender = Column(Enum(MyEnum), nullable=False) 38 hire_date = Column(Date, nullable=False) 39 40 def __repr__(self): 41 return "{} emp_no={}, name={} {}, gender={}".format( 42 sessionmaker.__class__.__name__, self.emp_no, self.first_name, self.last_name, self.gender.value 43 ) 44 45 46 # 聚合 47 query = session.query(func.count(Employee.emp_no)) # count 48 print(query.one()) 49 print(query.scalar()) 50 51 print("-"*50, end="\n\n") 52 53 print(session.query(func.min(Employee.emp_no)).scalar()) # min 54 print(session.query(func.max(Employee.emp_no)).scalar()) # max 55 print(session.query(func.avg(Employee.emp_no)).scalar()) # avg 56 57 print("-"*50, end="\n\n") 58 59 # 分组 60 print(session.query(Employee.gender, func.count(Employee.emp_no)).group_by(Employee.gender).all())
关联查询
有三张表employees、departments、dept_emp这三张表示多对多的关系。
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import Column, Integer, String, Enum, Date, ForeignKey 5 import enum 6 7 8 username = "root" 9 password = "python" 10 ip = "192.168.161.129" 11 port = 3306 12 database = "test" 13 14 conn_str = "mysql+pymysql://{}:{}@{}:{}/{}".format( 15 username, password, ip, port, database 16 ) 17 18 engine = sqlalchemy.create_engine(conn_str, echo=True) 19 Base = declarative_base() 20 Session = sessionmaker(bind=engine) 21 session = Session() 22 23 24 class MyEnum(enum.Enum): 25 M = "M" 26 F = "F" 27 28 29 class Employee(Base): 30 __tablename__ = "employees" 31 32 emp_no = Column(Integer, primary_key=True) 33 birth_date = Column(Date, nullable=False) 34 first_name = Column(String(14), nullable=False) 35 last_name = Column(String(16), nullable=False) 36 gender = Column(Enum(MyEnum), nullable=False) 37 hire_date = Column(Date, nullable=False) 38 39 dept_emps = relationship("Dept_emp") 40 41 def __repr__(self): 42 return "{} emp_no={}, name={} {}, gender={}, dept_no={}".format( 43 sessionmaker.__class__.__name__, self.emp_no, self.first_name, self.last_name, self.gender.value, self.dept_emps 44 ) 45 46 47 class Department(Base): 48 __tablename__ = "departments" 49 50 dept_no = Column(String(4), primary_key=True) 51 dept_name = Column(String(40), nullable=False, unique=True) 52 53 def __repr__(self): 54 return "{} dept_no={} name={}".format( 55 self.__class__.__name__, self.dept_no, self.dept_name 56 ) 57 58 59 class Dept_emp(Base): 60 __tablename__ = "dept_emp" 61 62 emp_no = Column(Integer, ForeignKey("employees.emp_no", ondelete="CASCADE"), primary_key=True) 63 dept_no = Column(Integer, ForeignKey("departments.det_no", ondelete="CASCADE"), primary_key=True) 64 from_date = Column(Date, nullable=False) 65 to_date = Column(Date, nullable=False) 66 67 def __repr__(self): 68 return "{} emo_no={} dep_no={}".format( 69 self.__class__.__name__, self.emp_no, self.dept_no 70 ) 71 72 73 def show(results): 74 for x in results: 75 print(x) 76 print("-"*50, end="\n\n") 77 78 79 # 查询10010员工所在部门的编号 80 results = session.query(Employee, Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() 81 show(results) 82 83 # 使用join 84 results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == 10010).all() 85 show(results) 86 results = session.query(Employee).join(Dept_emp, Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() 87 show(results) 88 # 上面的两种方法都只返回了一条数据，原因在于query(Employee)只能返回一个实体对象中去，为了解决这个问题，需要修改实体类Employee，增加属性来存放部门信息 89 results = session.query(Employee).join(Dept_emp, Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() 90 show(results) 91 results = session.query(Employee).join(Dept_emp, (Employee.emp_no == Dept_emp.emp_no) & (Employee.emp_no == 10010)).all() 92 show(results)
浙公网安备 33010602011771号