一、介绍
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上,使用关系对象映射进行数据库操作,简言之便是:将类和对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
1
2
|
快速安装
pip3 install sqlalchemy
|
组成部分:
- Engine,框架的引擎
- Connection Pooling ,数据库连接池
- Dialect,选择连接数据库的DB API种类
- Schema/Types,架构和类型
- SQL Exprression Language,SQL表达式语言
SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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 # 多久之后对线程池中的线程进行一次连接的回收(重置)—— -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()
|
#!/usr/bin/env python # -*- coding:utf-8 -*- 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", max_overflow=0, pool_size=5) def task(arg): conn = engine.contextual_connect() with conn: cur = conn.execute( "select * from t1" ) result = cur.fetchall() print(result) for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start()
#!/usr/bin/env python # -*- coding:utf-8 -*- import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine from sqlalchemy.engine.result import ResultProxy engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) def task(arg): cur = engine.execute("select * from t1") result = cur.fetchall() cur.close() print(result) for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start()
注意: 查看连接 show status like 'Threads%';
2. ORM
a. 创建数据库表
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
b. 操作数据库表
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.orm import sessionmaker 4 from sqlalchemy import create_engine 5 from models import Users 6 7 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) 8 Session = sessionmaker(bind=engine) 9 10 # 每次执行数据库操作时,都需要创建一个session 11 session = Session() 12 13 # ############# 执行ORM操作 ############# 14 obj1 = Users(name="alex1") 15 session.add(obj1) 16 17 # 提交事务 18 session.commit() 19 # 关闭session 20 session.close()
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
PS:注意
1. 默认不能修改表的字段,如果修改需要用的到sqlalchemy的一个组件进行修改字段
2. pool_recycle=-1 永远不回收
3. ctime = Column(DateTime,
default
=datetime.datetime.now)
datetime.datetime.now不能加括号,加括号默认值就是第一次插入时间
|
出处:https://www.cnblogs.com/supery007/p/8268577.html