2022-02-27 21:32:14

SQLAlchemy介绍

SQLAlchemy介绍
[TOC]

前言

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

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/

本文链接:http://blog.go2live.cn/post/SQLAlchemy.html

-- EOF --