SQLAlchemy 入门教程

SQLAlchemy 入门教程

前言

目前，许多主流的语言，都实现了对象关系映射（Object Relational Mapper，简称ORM）的库包。ORM的主要功能是将数据库表中的每条记录映射成一个对象。所有的数据库操作，都转化为对象的操作。这样可以增加代码的可读性和安全性。

ORM优点：

  1. 简洁易读：将数据表抽象为对象（数据模型），更直观易读。
  2. 可移植：封装了多种数据库引擎，面对多个数据库，操作基本一致，代码易维护。
  3. 更安全：有效避免SQL注入。

当然性能上会低于直接执行SQL语句，本文介绍SQLAlchemy的一些基础操作。

1. 建立连接

任何SQLAlchemy应用程序的开始都是一个名为engine . 此对象充当连接到特定数据库的中心源，提供工厂和称为 connection pool对于这些数据库连接。引擎通常是一个只为特定数据库服务器创建一次的全局对象，并使用一个URL字符串进行配置，该字符串将描述如何连接到数据库主机或后端。

# dialect[+driver]://user:password@host/dbname[?key=value..]
engine = create_engine("mysql://scott:tiger@hostname/dbname", encoding='latin1', echo=True)

创建engine的URL格式为dialect[+driver]://user:password@host/dbname[?key=value..]，其中dialect表示数据库类型例如：mysql、oracle、postgresql等，而driver代表使用的数据库API如：psycopg2、pyodbc等。

  • create_engine.echo：设置为True时会打印日志。可以查看调用的具体SQL语句方便调试。

  • create_engine.future ：标志设置为 True 以便我们充分利用 2.0 style ，1.x --> 2.0最主要的API更改是从select()改使用Query对象。

  • create_engine.pool_size：连接池的大小默认为5个，设置为0时表示连接无限制。

  • create_engine.pool_recycle：设置时间以限制数据库多久没连接自动断开。

# python3 下使用pymysql连接mysql。
engine = create_engine(
  "mysql+pymysql://user:pwd@host/dbname", 
  echo=True,
  pool_size=8,
  pool_recycle=3600
)
# 连接SQLite
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)

2. ORM的会话

使用ORM时，基本的事务/数据库交互对象称为Session。

在SQLAlchemy中，这个对象通常传递我们给它的SQL语句，它管理ORM映射对象的持久性操作。

Session的主要目的是建立与数据库的会话，它维护你加载和关联的所有数据库对象。它是数据库查询Query的一个入口。

Session通常在我们需要对数据库进行操作时创建。

一旦一个Session创建成功，我们在这个Session下完成多个事务（transaction）。

究竟何时创建和关闭Session，不能一概而论，但是一个原则是Session不应该在操作事务的方法中创建。

sessionmaker函数是配置Session的工厂，通过它建立的配置参数创建产生新的Session。

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

some_engine = create_engine('mysql+pymysql://username:password@localhost/mydb?charset=utf8')
ession = sessionmaker(bind=some_engine)
session = Session()

Session的常见操作方法包括：

  • flush：预提交，提交到数据库文件，还未写入数据库文件中。

  • commit：提交了一个事务。

  • rollback：回滚。

  • close：关闭会话。

3. 创建模型

前面有提到ORM的重要特点，那么我们操作表的时候就需要通过操作对象来实现，现在我们来创建一个类，以常见的用户表举例：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, SMALLINT, DECIMAL, Enum, TEXT, TIMESTAMP
Base = declarative_base()

class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True)
    email = Column(String(64))

    def __init__(self, name, email):
        self.name = name
        self.email = email

declarative_base()是sqlalchemy内部封装的一个方法，通过其构造一个基类，这个基类和它的子类，可以将Python类和数据库表关联映射起来。

数据库表模型类通过__tablename__和表关联起来，Column表示数据表的列。

4. 生成表

Base.metadata.create_all(engine)

创建表，如果存在则忽略，执行以上代码，就会发现在数据库中创建了users表。

5. 抽象模型

现在我们修改之前的用户表，加入新字段和一张订单表。

使用了一个基类，定义了共同的字段：id和创建更新时间。值得注意的是基类中设置了__abstract__如果不设置将会报错。

order中定义了一个枚举类型，同时order和user使用user_id进行关联并未使用外键。

import enum
from sqlalchemy import Column, String, Integer, SMALLINT, FLOAT, DECIMAL, Enum
from sqlalchemy.sql.functions import current_timestamp, current_time
from orm.base_model import BaseTimestampModel
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class PayType(enum.Enum):

    alipay = 0
    unionpay = 1
    weixin = 2
    balance = 3
    combo = 4
    package = 5
    company = 6
    offline = 7

class BaseModel(Base):
    __abstract__ = True
    id = Column(Integer, primary_key=True, autoincrement=True)
    created_at = Column(TIMESTAMP, nullable=False, default=current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, default=current_timestamp()

class User(BaseModel):

    __tablename__ = 'user'
    mobile = Column(String)
    nickname = Column(String)
    status = Column(SMALLINT, default=1)
    appid = Column(String, nullable=True)

    def __init__(self, nickname, mobile):
        self.nickname = nickname
        self.mobile = mobile                    


class Order(BaseModel):

    __tablename__ = 'order'
    user_id = Column(Integer)                    
    ordersn = Column(String, unique=True)                        
    order_type = Column(SMALLINT, default=2)
    pay_type = Column(Enum(PayType), default=PayType.alipay)
    price = Column(DECIMAL)

6. 新增数据

add_user = User("test", "1351232322")
session.add(add_user)
session.commit()

session.add()将会把Model加入当前session维护的持久空间(可以从session.dirty看到)中，直到commit时提交到数据库。

  • Q1：add之后如何直接返回对象的属性？

可以在add之后执行db.session.flush()，这样便可在session中get到对象的属性。

  • Q2：如何进行批量插入，性能比较？

批量插入共有以下几种方法，对它们的批量做了比较，分别是：

session.add_all() < bulk_save_object()< bulk_insert_mappings() < SQLAlchemy_core()

7. 查询数据

查询是最常用的一个操作了，举个最简单的查询例子：

# 检索id = 1
user = session.query(Users).filter(id == 1).first()
# 检索全部用户
users = session.query(Users).all()

使用Join查询

# 未使用外键
q = session.query(User).join(Order, User.id==Order.user_id)
# 方法2
q = session.query(User).join(Order, Order.user_id)
# 配置了外键
q = session.query(User).join(Order)

下一个例子使用SQL中的函数和Left join，检索当日下单用户昵称和订单信息。

from sqlalchemy.sql.functions import current_date

result = session.query(Order, User.nickname).outerjoin(
            User, User.id == Order.user_id
        ).filter(
            Order.order_type != 2,
            sqlalchemy.func.date(Order.created_at) == current_date
        ).all()

使用 in 、or查询语句。

# 检索id在[1, 2, 3]中的用户。
r = session.query(User).filter(User.id.in_([1,2,3])).all()
# 使用or
query = bus_session.query(User)
filters = [User.id.in_([1, 2, 3]), User.nickname.ilike("ab%")]
r = query.filter(sqlalchemy.or_(*filters)).all()

8. 更新数据

更新数据有两种方法，一种是使用query中的update方法：

session.query(User).filter_by(id=1).update({'name': "Jack"})

另一种是操作对应的表模型：

user = session.query(User).filter_by(name="Jack").first()
user.name = "test"
session.add(users)
session.commit()

9. 删除数据

和更新数据类似，删除数据也有两种方法，第一种：

user = session.query(User).filter(User.name == "test").first()
session.delete(user)
session.commit()

批量删除的时候建议使用第二种：

session.query(User).filter(User.name == "test").delete()
session.commit()

参考资料

  1. SQLAlchemy 1.4 / 2.0 Tutorial

  2. SQLAlchemy ORM Examples

  3. python SQLAlchemy入门教程

  4. sqlalchemy学习

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,636评论 5赞 468
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,890评论 2赞 376
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,680评论 0赞 330
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,766评论 1赞 271
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,665评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,045评论 1赞 276
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,515评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,182评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,334评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,274评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,319评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,002评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,599评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,675评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,917评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,309评论 2赞 345
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,885评论 2赞 341