一:介绍
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上,使用关系对象映射进行数据库操作,简言之便是:将类和对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
安装:pip3 install sqlalchemy
组成部分:
- Engine,框架的引擎
- Connection Pooling ,数据库连接池
- Dialect,选择连接数据库的DB API种类
- Schema/Types,架构和类型
- SQL Exprression Language,SQL表达式语言
SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
1 MySQL-Python 2 mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> 3 4 pymysql 5 mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] 6 7 MySQL-Connector 8 mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> 9 10 cx_Oracle 11 oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 12 13 更多:http://docs.sqlalchemy.org/en/latest/dialects/index.html
二:使用
1.执行原生SQL语句
1 import time 2 import threading 3 import sqlalchemy 4 from sqlalchemy import create_engine 5 from sqlalchemy.engine.base import Engine 6 7 engine = create_engine( 8 "mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8", 9 max_overflow=0, # 超过连接池大小外最多创建的连接 10 pool_size=5, # 连接池大小 11 pool_timeout=30, # 池中没有线程最多等待的时间,否则报错 12 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) 13 ) 14 15 16 def task(arg): 17 conn = engine.raw_connection() 18 cursor = conn.cursor() 19 cursor.execute( 20 "select * from t1" 21 ) 22 result = cursor.fetchall() 23 cursor.close() 24 conn.close() 25 26 27 for i in range(20): 28 t = threading.Thread(target=task, args=(i,)) 29 t.start()
1 import time 2 import threading 3 import sqlalchemy 4 from sqlalchemy import create_engine 5 from sqlalchemy.engine.base import Engine 6 7 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) 8 9 10 def task(arg): 11 conn = engine.contextual_connect() 12 with conn: 13 cur = conn.execute( 14 "select * from t1" 15 ) 16 result = cur.fetchall() 17 print(result) 18 19 20 for i in range(20): 21 t = threading.Thread(target=task, args=(i,)) 22 t.start()
1 import time 2 import threading 3 import sqlalchemy 4 from sqlalchemy import create_engine 5 from sqlalchemy.engine.base import Engine 6 from sqlalchemy.engine.result import ResultProxy 7 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) 8 9 10 def task(arg): 11 cur = engine.execute("select * from t1") 12 result = cur.fetchall() 13 cur.close() 14 print(result) 15 16 17 for i in range(20): 18 t = threading.Thread(target=task, args=(i,)) 19 t.start()
注意: 查看连接 show status like 'Threads%';
2.orm
a. 创建数据库表
1 import datetime 2 from sqlalchemy import create_engine 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index 5 6 Base = declarative_base() 7 8 9 class Users(Base): 10 __tablename__ = 'users' 11 12 id = Column(Integer, primary_key=True) 13 name = Column(String(32), index=True, nullable=False) 14 # email = Column(String(32), unique=True) 15 # ctime = Column(DateTime, default=datetime.datetime.now) 16 # extra = Column(Text, nullable=True) 17 18 __table_args__ = ( 19 # UniqueConstraint('id', 'name', name='uix_id_name'), 20 # Index('ix_id_name', 'name', 'email'), 21 ) 22 23 24 def init_db(): 25 """ 26 根据类创建数据库表 27 :return: 28 """ 29 engine = create_engine( 30 "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", 31 max_overflow=0, # 超过连接池大小外最多创建的连接 32 pool_size=5, # 连接池大小 33 pool_timeout=30, # 池中没有线程最多等待的时间,否则报错 34 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) 35 ) 36 37 Base.metadata.create_all(engine) 38 39 40 def drop_db(): 41 """ 42 根据类删除数据库表 43 :return: 44 """ 45 engine = create_engine( 46 "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", 47 max_overflow=0, # 超过连接池大小外最多创建的连接 48 pool_size=5, # 连接池大小 49 pool_timeout=30, # 池中没有线程最多等待的时间,否则报错 50 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) 51 ) 52 53 Base.metadata.drop_all(engine) 54 55 56 if __name__ == '__main__': 57 drop_db() 58 init_db()
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import datetime 4 from sqlalchemy import create_engine 5 from sqlalchemy.ext.declarative import declarative_base 6 from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index 7 from sqlalchemy.orm import relationship 8 9 Base = declarative_base() 10 11 12 # ##################### 单表示例 ######################### 13 class Users(Base): 14 __tablename__ = 'users' 15 16 id = Column(Integer, primary_key=True) 17 name = Column(String(32), index=True) 18 age = Column(Integer, default=18) 19 email = Column(String(32), unique=True) 20 ctime = Column(DateTime, default=datetime.datetime.now) 21 extra = Column(Text, nullable=True) 22 23 __table_args__ = ( 24 # UniqueConstraint('id', 'name', name='uix_id_name'), 25 # Index('ix_id_name', 'name', 'extra'), 26 ) 27 28 29 class Hosts(Base): 30 __tablename__ = 'hosts' 31 32 id = Column(Integer, primary_key=True) 33 name = Column(String(32), index=True) 34 ctime = Column(DateTime, default=datetime.datetime.now) 35 36 37 # ##################### 一对多示例 ######################### 38 class Hobby(Base): 39 __tablename__ = 'hobby' 40 id = Column(Integer, primary_key=True) 41 caption = Column(String(50), default='篮球') 42 43 44 class Person(Base): 45 __tablename__ = 'person' 46 nid = Column(Integer, primary_key=True) 47 name = Column(String(32), index=True, nullable=True) 48 hobby_id = Column(Integer, ForeignKey("hobby.id")) 49 50 # 与生成表结构无关,仅用于查询方便 51 hobby = relationship("Hobby", backref='pers') 52 53 54 # ##################### 多对多示例 ######################### 55 56 class Server2Group(Base): 57 __tablename__ = 'server2group' 58 id = Column(Integer, primary_key=True, autoincrement=True) 59 server_id = Column(Integer, ForeignKey('server.id')) 60 group_id = Column(Integer, ForeignKey('group.id')) 61 62 63 class Group(Base): 64 __tablename__ = 'group' 65 id = Column(Integer, primary_key=True) 66 name = Column(String(64), unique=True, nullable=False) 67 68 # 与生成表结构无关,仅用于查询方便 69 servers = relationship('Server', secondary='server2group', backref='groups') 70 71 72 class Server(Base): 73 __tablename__ = 'server' 74 75 id = Column(Integer, primary_key=True, autoincrement=True) 76 hostname = Column(String(64), unique=True, nullable=False) 77 78 79 def init_db(): 80 """ 81 根据类创建数据库表 82 :return: 83 """ 84 engine = create_engine( 85 "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", 86 max_overflow=0, # 超过连接池大小外最多创建的连接 87 pool_size=5, # 连接池大小 88 pool_timeout=30, # 池中没有线程最多等待的时间,否则报错 89 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) 90 ) 91 92 Base.metadata.create_all(engine) 93 94 95 def drop_db(): 96 """ 97 根据类删除数据库表 98 :return: 99 """ 100 engine = create_engine( 101 "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", 102 max_overflow=0, # 超过连接池大小外最多创建的连接 103 pool_size=5, # 连接池大小 104 pool_timeout=30, # 池中没有线程最多等待的时间,否则报错 105 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) 106 ) 107 108 Base.metadata.drop_all(engine) 109 110 111 if __name__ == '__main__': 112 drop_db() 113 init_db() 114 115 创建多个表并包含Fk、M2M关系
指定关联列:hobby = relationship("Hobby", backref='pers',foreign_keys="Person.hobby_id")
b. 操作数据库表
1 from sqlalchemy.orm import sessionmaker 2 from sqlalchemy import create_engine 3 from models import Users 4 5 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) 6 Session = sessionmaker(bind=engine) 7 8 # 每次执行数据库操作时,都需要创建一个session 9 session = Session() 10 11 # ############# 执行ORM操作 ############# 12 obj1 = Users(name="alex1") 13 session.add(obj1) 14 15 # 提交事务 16 session.commit() 17 # 关闭session 18 session.close()
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.orm import sessionmaker 4 from sqlalchemy import create_engine 5 from sqlalchemy.orm import scoped_session 6 from models import Users 7 8 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) 9 Session = sessionmaker(bind=engine) 10 11 """ 12 # 线程安全,基于本地线程实现每个线程用同一个session 13 # 特殊的:scoped_session中有原来方法的Session中的一下方法: 14 15 public_methods = ( 16 '__contains__', '__iter__', 'add', 'add_all', 'begin', 'begin_nested', 17 'close', 'commit', 'connection', 'delete', 'execute', 'expire', 18 'expire_all', 'expunge', 'expunge_all', 'flush', 'get_bind', 19 'is_modified', 'bulk_save_objects', 'bulk_insert_mappings', 20 'bulk_update_mappings', 21 'merge', 'query', 'refresh', 'rollback', 22 'scalar' 23 ) 24 """ 25 session = scoped_session(Session) 26 27 28 # ############# 执行ORM操作 ############# 29 obj1 = Users(name="alex1") 30 session.add(obj1) 31 32 33 34 # 提交事务 35 session.commit() 36 # 关闭session 37 session.close() 38 39 基于scoped_session实现线程安全
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import time 4 import threading 5 6 from sqlalchemy.ext.declarative import declarative_base 7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 8 from sqlalchemy.orm import sessionmaker, relationship 9 from sqlalchemy import create_engine 10 from db import Users 11 12 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) 13 Session = sessionmaker(bind=engine) 14 15 16 def task(arg): 17 session = Session() 18 19 obj1 = Users(name="alex1") 20 session.add(obj1) 21 22 session.commit() 23 24 25 for i in range(10): 26 t = threading.Thread(target=task, args=(i,)) 27 t.start() 28 29 多线程执行示例
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import time 4 import threading 5 6 from sqlalchemy.ext.declarative import declarative_base 7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 8 from sqlalchemy.orm import sessionmaker, relationship 9 from sqlalchemy import create_engine 10 from sqlalchemy.sql import text 11 12 from db import Users, Hosts 13 14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) 15 Session = sessionmaker(bind=engine) 16 17 session = Session() 18 19 # ################ 添加 ################ 20 """ 21 obj1 = Users(name="wupeiqi") 22 session.add(obj1) 23 24 session.add_all([ 25 Users(name="wupeiqi"), 26 Users(name="alex"), 27 Hosts(name="c1.com"), 28 ]) 29 session.commit() 30 """ 31 32 # ################ 删除 ################ 33 """ 34 session.query(Users).filter(Users.id > 2).delete() 35 session.commit() 36 """ 37 # ################ 修改 ################ 38 """ 39 session.query(Users).filter(Users.id > 0).update({"name" : "099"}) 40 session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False) 41 session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate") 42 session.commit() 43 """ 44 # ################ 查询 ################ 45 """ 46 r1 = session.query(Users).all() 47 r2 = session.query(Users.name.label('xx'), Users.age).all() 48 r3 = session.query(Users).filter(Users.name == "alex").all() 49 r4 = session.query(Users).filter_by(name='alex').all() 50 r5 = session.query(Users).filter_by(name='alex').first() 51 r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all() 52 r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all() 53 """ 54 55 56 session.close() 57 58 基本增删改查示例
1 # 条件 2 ret = session.query(Users).filter_by(name='alex').all() 3 ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() 4 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() 5 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() 6 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() 7 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() 8 from sqlalchemy import and_, or_ 9 ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() 10 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() 11 ret = session.query(Users).filter( 12 or_( 13 Users.id < 2, 14 and_(Users.name == 'eric', Users.id > 3), 15 Users.extra != "" 16 )).all() 17 18 19 # 通配符 20 ret = session.query(Users).filter(Users.name.like('e%')).all() 21 ret = session.query(Users).filter(~Users.name.like('e%')).all() 22 23 # 限制 24 ret = session.query(Users)[1:2] 25 26 # 排序 27 ret = session.query(Users).order_by(Users.name.desc()).all() 28 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() 29 30 # 分组 31 from sqlalchemy.sql import func 32 33 ret = session.query(Users).group_by(Users.extra).all() 34 ret = session.query( 35 func.max(Users.id), 36 func.sum(Users.id), 37 func.min(Users.id)).group_by(Users.name).all() 38 39 ret = session.query( 40 func.max(Users.id), 41 func.sum(Users.id), 42 func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() 43 44 # 连表 45 46 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() 47 48 ret = session.query(Person).join(Favor).all() 49 50 ret = session.query(Person).join(Favor, isouter=True).all() 51 52 53 # 组合 54 q1 = session.query(Users.name).filter(Users.id > 2) 55 q2 = session.query(Favor.caption).filter(Favor.nid < 2) 56 ret = q1.union(q2).all() 57 58 q1 = session.query(Users.name).filter(Users.id > 2) 59 q2 = session.query(Favor.caption).filter(Favor.nid < 2) 60 ret = q1.union_all(q2).all() 61 62 常用操作
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import time 4 import threading 5 6 from sqlalchemy.ext.declarative import declarative_base 7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 8 from sqlalchemy.orm import sessionmaker, relationship 9 from sqlalchemy import create_engine 10 from sqlalchemy.sql import text 11 from sqlalchemy.engine.result import ResultProxy 12 from db import Users, Hosts 13 14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) 15 Session = sessionmaker(bind=engine) 16 17 session = Session() 18 19 # 查询 20 # cursor = session.execute('select * from users') 21 # result = cursor.fetchall() 22 23 # 添加 24 cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'}) 25 session.commit() 26 print(cursor.lastrowid) 27 28 session.close() 29 30 原生SQL语句
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import time 4 import threading 5 6 from sqlalchemy.ext.declarative import declarative_base 7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 8 from sqlalchemy.orm import sessionmaker, relationship 9 from sqlalchemy import create_engine 10 from sqlalchemy.sql import text 11 from sqlalchemy.engine.result import ResultProxy 12 from db import Users, Hosts, Hobby, Person 13 14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5) 15 Session = sessionmaker(bind=engine) 16 session = Session() 17 # 添加 18 """ 19 session.add_all([ 20 Hobby(caption='乒乓球'), 21 Hobby(caption='羽毛球'), 22 Person(name='张三', hobby_id=3), 23 Person(name='李四', hobby_id=4), 24 ]) 25 26 person = Person(name='张九', hobby=Hobby(caption='姑娘')) 27 session.add(person) 28 29 hb = Hobby(caption='人妖') 30 hb.pers = [Person(name='文飞'), Person(name='博雅')] 31 session.add(hb) 32 33 session.commit() 34 """ 35 36 # 使用relationship正向查询 37 """ 38 v = session.query(Person).first() 39 print(v.name) 40 print(v.hobby.caption) 41 """ 42 43 # 使用relationship反向查询 44 """ 45 v = session.query(Hobby).first() 46 print(v.caption) 47 print(v.pers) 48 """ 49 50 session.close() 51 52 基于relationship操作ForeignKey
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import time 4 import threading 5 6 from sqlalchemy.ext.declarative import declarative_base 7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 8 from sqlalchemy.orm import sessionmaker, relationship 9 from sqlalchemy import create_engine 10 from sqlalchemy.sql import text 11 from sqlalchemy.engine.result import ResultProxy 12 from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group 13 14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5) 15 Session = sessionmaker(bind=engine) 16 session = Session() 17 # 添加 18 """ 19 session.add_all([ 20 Server(hostname='c1.com'), 21 Server(hostname='c2.com'), 22 Group(name='A组'), 23 Group(name='B组'), 24 ]) 25 session.commit() 26 27 s2g = Server2Group(server_id=1, group_id=1) 28 session.add(s2g) 29 session.commit() 30 31 32 gp = Group(name='C组') 33 gp.servers = [Server(hostname='c3.com'),Server(hostname='c4.com')] 34 session.add(gp) 35 session.commit() 36 37 38 ser = Server(hostname='c6.com') 39 ser.groups = [Group(name='F组'),Group(name='G组')] 40 session.add(ser) 41 session.commit() 42 """ 43 44 45 # 使用relationship正向查询 46 """ 47 v = session.query(Group).first() 48 print(v.name) 49 print(v.servers) 50 """ 51 52 # 使用relationship反向查询 53 """ 54 v = session.query(Server).first() 55 print(v.hostname) 56 print(v.groups) 57 """ 58 59 60 session.close() 61 62 基于relationship操作m2m
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import time 4 import threading 5 6 from sqlalchemy.ext.declarative import declarative_base 7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 8 from sqlalchemy.orm import sessionmaker, relationship 9 from sqlalchemy import create_engine 10 from sqlalchemy.sql import text, func 11 from sqlalchemy.engine.result import ResultProxy 12 from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group 13 14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5) 15 Session = sessionmaker(bind=engine) 16 session = Session() 17 18 # 关联子查询 19 subqry = session.query(func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(Group).as_scalar() 20 result = session.query(Group.name, subqry) 21 """ 22 SELECT `group`.name AS group_name, (SELECT count(server.id) AS sid 23 FROM server 24 WHERE server.id = `group`.id) AS anon_1 25 FROM `group` 26 """ 27 28 29 # 原生SQL 30 """ 31 # 查询 32 cursor = session.execute('select * from users') 33 result = cursor.fetchall() 34 35 # 添加 36 cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'}) 37 session.commit() 38 print(cursor.lastrowid) 39 """ 40 41 session.close() 42 43 其他
补充:
1 1.SQLAlchemy链接的两种方式 2 session = Session()与session = scoped_session(Session) 3 当一个py文件里面有__all__,在一个文件里面只能导出scoped_session 4 scoped_session本质是执行do函数,可以为每一个线程创建一个对象分配内存 5 6 2.增删改查 7 r2 = session.query(Users.name.label('xx'), Users.age).all()# label取别名 8 relationship与生成表结构无关,仅用于查询方便,backref='pers'反向查询 9 10 3.Flask-SQLAlchemy 11 db = SQLAlchemy() 12 ----包含配置 13 ----包含ORM基类 14 ----包含链接 15 ----包含create_all 16 ----包含engine 17 18 4.pipreqs 19 pip3 install pipreqs 20 执行:pipreqs ./ 21 22 5.flask-script 23 from flask-script import manager 24 manage = Mananger(app) 25 26 6. flask-migrate 27 from flask-migrate import Migrate, MigrateCommand 28 app = create_all() 29 manage = Manage(app) 30 migrate = Migrate(app,db) 31 32 manager.add_command('db',MigrateCommand) 33 数据库迁移: 34 python manage.py db init 35 python manage.py db migrate 36 python manage.py db upgrate