SqlAlchemy操作（一）
博客转载于 http://www.cnblogs.com/haiyan123/p/8270520.html
一. 介绍
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
pip install sqlalchemy -i ![]()
http://pypi.douban.com/simple --trusted-host ![]()
pypi.douban.com
组成部分：
- Engine，框架的引擎
- Connection Pooling ，数据库连接池
- Dialect，选择连接数据库的DB API种类
- Schema/Types，架构和类型
- SQL Exprression Language，SQL表达式语言
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
MySQL-Python
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
MySQL-Connector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html
二. 使用
1. 执行原生SQL语句
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",
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()
1 #!/usr/bin/env python
2 # -*- coding:utf-8 -*-
3 import time
4 import threading
5 import sqlalchemy
6 from sqlalchemy import create_engine
7 from sqlalchemy.engine.base import Engine
8
9 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5)
10
11
12 def task(arg):
13 conn = engine.contextual_connect()
14 with conn:
15 cur = conn.execute(
16 "select * from t1"
17 )
18 result = cur.fetchall()
19 print(result)
20
21
22 for i in range(20):
23 t = threading.Thread(target=task, args=(i,))
24 t.start()
1 #!/usr/bin/env python
2 # -*- coding:utf-8 -*-
3 import time
4 import threading
5 import sqlalchemy
6 from sqlalchemy import create_engine
7 from sqlalchemy.engine.base import Engine
8 from sqlalchemy.engine.result import ResultProxy
9 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5)
10
11
12 def task(arg):
13 cur = engine.execute("select * from t1")
14 result = cur.fetchall()
15 cur.close()
16 print(result)
17
18
19 for i in range(20):
20 t = threading.Thread(target=task, args=(i,))
21 t.start()
注意: 查看连接 show status like 'Threads%';
2. ORM
a. 创建数据库表
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
8 Base = declarative_base()
9
10
11 class Users(Base):
12 __tablename__ = 'users'
13
14 id = Column(Integer, primary_key=True)
15 name = Column(String(32), index=True, nullable=False)
16 # email = Column(String(32), unique=True)
17 # ctime = Column(DateTime, default=datetime.datetime.now)
18 # extra = Column(Text, nullable=True)
19
20 __table_args__ = (
21 # UniqueConstraint('id', 'name', name='uix_id_name'),
22 # Index('ix_id_name', 'name', 'email'),
23 )
24
25
26 def init_db():
27 """
28 根据类创建数据库表
29 :return:
30 """
31 engine = create_engine(
32 "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
33 max_overflow=0, # 超过连接池大小外最多创建的连接
34 pool_size=5, # 连接池大小
35 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
36 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
37 )
38
39 Base.metadata.create_all(engine)
40
41
42 def drop_db():
43 """
44 根据类删除数据库表
45 :return:
46 """
47 engine = create_engine(
48 "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
49 max_overflow=0, # 超过连接池大小外最多创建的连接
50 pool_size=5, # 连接池大小
51 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
52 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
53 )
54
55 Base.metadata.drop_all(engine)
56
57
58 if __name__ == '__main__':
59 drop_db()
60 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()
b. 操作数据库表
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Users
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
##############方式一########################
Session = sessionmaker(bind=engine)
# 每次执行数据库操作时，都需要创建一个session
session = Session()
# ############# 执行ORM操作 #############
obj1 = Users(name="alex1")
session.add(obj1)
# 提交事务
session.commit()
# 关闭session
session.close()
######################方式二####################
# 方式二：支持线程安全，为每个线程创建一个session
# - threading.Local
# - 唯一标识
# ScopedSession对象
# self.registry(), 加括号 创建session
# self.registry(), 加括号 创建session
# self.registry(), 加括号 创建session
from greenlet import getcurrent as get_ident
Session = sessionmaker(bind=engine)
session = scoped_session(Session,get_ident)
# session.add
# 操作
session.remove()
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()
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()
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()
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()
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()
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()
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()
基本增删改查补充
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import time
import threading
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from db import Users, Hosts
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# ################ 添加 ################
"""
obj1 = Users(name="wupeiqi")
session.add(obj1)
#批量添加
session.add_all([
Users(name="wupeiqi"),
Users(name="alex"),
Hosts(name="c1.com"),
])
session.commit()
"""
# ################ 删除 ################
"""
session.query(Users).filter(Users.id > 2).delete()
session.commit()
"""
# ################ 修改 ################
"""
session.query(Users).filter(Users.id > 0).update({"name" : "099"})
session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False) #字符串
session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate") #数字
session.commit()
"""
# ################ 查询 ################
"""
r1 = session.query(Users).all()
r2 = session.query(Users.name.label('xx'), Users.age).all() #lable #as xx
r3 = session.query(Users).filter(Users.name == "alex").all()
r4 = session.query(Users).filter_by(name='alex').all() #如果里面写条件就用filter_by,和上面filter查询是一回事，只是一种不同的方式
r5 = session.query(Users).filter_by(name='alex').first()
r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all() #查询里面如果有动态传参的时候，吧它包在text里面，:value,:name这样的语法后面用.params来进行格式化
r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all() #上面的这个方式也可以用这一种 ，直接可以进行SQL语句的查询
"""
session.close()
