SQLAlchemy介绍
[TOC]
前言
玩Python的人很有必要非常熟悉SQLAlchemy。因为它是python中最主流的orm。
我之前只在flask中用过。发现用的还不是很顺畅,不能手到拿来。故又整理了这篇文章。
查询sql有几个点:
- 直接原始语句查询。
- 对象关系映射,即orm
- 其它关联的是数据库链接池和事务。
下面一一道来。
直接使用原生语句查询
pymysql
SQLAlchemy依赖于各种驱动,底层其实还是用的pymysql之类的。相当于一个适配器类,把其它的操作都抽象化了。
查询示例
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.56.11',port=3306,user='root',passwd='root',db='oldboydb')
# 创建游标 socket上建立的实例
cursor=conn.cursor()
# 执行SQL,并返回行数,用游标执行,
effect_row = cursor.execute("select * from student")
# print(cursor.fetchone())
# print(cursor.fetchone())
print(cursor.fetchall())
插入示例
import pymysql
#创建连接
conn = pymysql.connect(host='192.168.56.11',port=3306,user='root',passwd='root',db='oldboydb')
#创建游标 socket上建立的实例
cursor=conn.cursor()
data = [
("N1","2015-05-22",'M'),
("N2","2015-05-22",'M'),
("N3","2015-05-22",'M'),
("N4","2015-05-22",'M'),
]
cursor.executemany("insert into student (name,register_date,gender) values(%s,%s,%s)",data )
conn.commit()
使用SQLAlchemy
这个是SQLAlchemy Core中的。
查询
from sqlalchemy import create_engine
e = create_engine('mysql://user:pass@host/db')
for row in e.execute('select * from table where id < %s', 2):
print(dict(row))
这是最简单的,只用到了engine对象。
还可以用字典参数,如下
from sqlalchemy import text
result = e.execute(text('select * from table where id < :id'), {'id': 2})
相对于上面pymysql中只能用索引或字典。
这里可以用索引、key、属性。
row[0] == row['id'] == row.id
事务支持
conn = e.connect()
try:
conn.begin()
#dosomething
conn.commit()
except:
conn.rollback()
python版本的sql表达式
这个比写原生sql语句好,因为可以跨数据库引擎写。我就不知道sqlserver,但工作中有这种类型的库。
我用python就比较开心了。不用每次都上网查。
这个就是我需要用的,临时任务写orm太复杂了, 偏偏又不熟悉sqlserver。
查询
from sqlalchemy import Table, MetaData
meta = MetaData(bind=e, reflect=True)
users = meta.tables['user]
list(e.execute(users.select(table.c.id < 2)))
除了上面的直接在engine上查询,也可以在connection上查询
con = engine.connect()
con.execute(users.insert(), name='admin', email='admin@loclhost')
甚至还可以直接在table上调用excute()方法
users.select(users.c.id == 1).execute().first()
ORM
先看一个示例,半手动
from sqlalchemy import orm
class Table(object):
pass
meta = MetaData(bind=e, reflect=True)
orm.Mapper(Table, meta.tables['table'])
s = orm.Session(bind=e)
s.query(Table).filter(Table.id < 2).first().info
注意到几个对象:
engine, 数据库连接的. 可以直接在engine上查询。
connection, 这个是和事务相关的。 当用connection时,查询是在connection上。
MetaData, 对应数据库表的,可以手写Table, 也可以自动反射出来。
Mapper对象就是用来把object和table 做映射的。
session 这里其实已经把事务加了进来, 查询是在session上。原始的sql和python表达式都是在engin对象上。
在flask-sqlalchemy中,查询是直接在对象上的。
声明的方式
上面用的是半手动模式,object是手动的,table是自动反射的。
这里介绍声明的方式
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
# 创建实例,并连接test库
engine = create_engine("mysql+pymysql://root:123456@localhost/test",
encoding='utf-8', echo=True)
# echo=True 显示信息
Base = declarative_base() # 生成orm基类
class User(Base):
__tablename__ = 'user' # 表名,这里就关联上Table了
id = Column(Integer, primary_key=True)
name = Column(String(32))
password = Column(String(64))
Base.metadata.create_all(engine) #创建表结构 (这里是父类调子类)
sqlalchemy 就是把Base子类转变为数据库表,定义好User类后,会生成Table和mapper(),分别通过User.__table__和User.__mapper__返回这两个对象
手动模式
from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper
metadata = MetaData()
user = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('fullname', String(50)),
Column('password', String(12)))
class User(object):
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
mapper(User, user)
另一种半手动
上面的方式user表是手动写的。还可以用autoload自动配置。
user = Table('user', metadata, autoload=True)
使用reflect性能会比较低,他会一次性把整个库的所有表都看下结构,然后自动配置。
autoload只会自动反射当前表。
性能最佳的当然还是手动模式。
查询
Session_class = sessionmaker(bind=engine) # 实例和engine绑定
Session = Session_class() # 生成session实例,相当于游标
my_user = Session.query(User).filter_by(name="fgf").first() # 查询
print(my_user)
插入
# 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session_class = sessionmaker(bind=engine) # 实例和engine绑定
Session = Session_class() # 生成session实例,相当于游标
user_obj = User(id=27,name="fgf",password="123456") # 生成你要创建的数据对象
print(user_obj.name,user_obj.id) # 此时还没创建对象呢,不信你打印一下id发现还是None
Session.add(user_obj) # 把要创建的数据对象添加到这个session里, 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建
Session.commit() # 到此才统一提交,创建数据
遇到的问题
连接池的问题
现在开始流行serverless了,这种模式下建议是关闭连接池。
关闭的方法是在创建engin时指定连接池类为NullPoll类.
e = create_engine('mssql+pymssql://yourserver',
poolclass=pool.NullPool)
orm类型找不到问题
参考
https://stackoverflow.com/questions/34894170/difficulty-serializing-geography-column-type-using-sqlalchemy-marshmallow
分表问题
通过type类自动生成对应的类。
参考文章:http://www.cnblogs.com/carlo/p/4630943.html
文章中有错误,直接返回ModelClass即可。而不是再调用一次。
其它经验
sqlacodegen
sqlacodegen是orm中model的自动生成工具。有了这个工具,你就不用手写model对象了。
参考
http://www.mapfish.org/doc/tutorials/sqlalchemy.html
http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html
http://blog.csdn.net/tantexian/article/details/39230459
http://docs.sqlalchemy.org/en/latest/