• 前言
• 直接使用原生语句查询
  • pymysql
    • 查询示例
    • 插入示例
  • 使用SQLAlchemy
    • 查询
  • 事务支持
• python版本的sql表达式
  • 查询
• ORM
  • 先看一个示例,半手动
  • 声明的方式
  • 手动模式
  • 另一种半手动
  • 查询
  • 插入
• 遇到的问题
  • 连接池的问题
  • orm类型找不到问题
  • 分表问题
• 其它经验
  • sqlacodegen
• 参考

前言

玩Python的人很有必要非常熟悉SQLAlchemy。因为它是python中最主流的orm。
我之前只在flask中用过。发现用的还不是很顺畅，不能手到拿来。故又整理了这篇文章。

查询sql有几个点:

1. 直接原始语句查询。
2. 对象关系映射，即orm
3. 其它关联的是数据库链接池和事务。

下面一一道来。

直接使用原生语句查询

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) #创建表结构 （这里是父类调子类）

手动模式

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类自动生成对应的类。

其它经验

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/