Python SQLAlchemy入门教程(基本用法)

本文将以Mysql举例，介绍sqlalchemy的基本用法。其中，Python版本为2.7，sqlalchemy版本为1.1.6。

一. 介绍

SQLAlchemy是Python中最有名的ORM工具。

关于ORM：

全称Object Relational Mapping（对象关系映射）。

特点是操纵Python对象而不是SQL查询，也就是在代码层面考虑的是对象，而不是SQL，体现的是一种程序化思维，这样使得Python程序更加简洁易读。

具体的实现方式是将数据库表转换为Python类，其中数据列作为属性，数据库操作作为方法。

优点：

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

为什么要用sqlalchemy?

虽然性能稍稍不及原生SQL，但是操作数据库真的很方便！

二. 使用

概念和数据类型

概念

概念	对应数据库	说明
Engine	连接	驱动引擎
Session	连接池，事务	由此开始查询
Model	表	类定义
Column	列
Query	若干行	可以链式添加多个条件

常见数据类型

数据类型	数据库数据类型	python数据类型	说明
Integer	int	int	整形，32位
String	varchar	string	字符串
Text	text	string	长字符串
Float	float	float	浮点型
Boolean	tinyint	bool	True / False
Date	date	datetime.date	存储时间年月日
DateTime	datetime	datetime.datetime	存储年月日时分秒毫秒等
Time	time	datetime.datetime	存储时分秒

创建数据库表

1.安装

pip install SQLalchemy

2. 创建连接

from sqlalchemy import create_engine
 
engine = create_engine("mysql://user:password@hostname/dbname?charset=uft8")

这行代码初始化创建了Engine，Engine内部维护了一个Pool（连接池）和Dialect（方言），方言来识别具体连接数据库种类。

创建好了Engine的同时，Pool和Dialect也已经创建好了，但是此时并没有真正与数据库连接，等到执行具体的语句.connect()等时才会连接到数据库。

create_engine还有其它可选的参数，比如：

engine = create_engine("mysql://user:password@hostname/dbname?charset=uft8",
            echo=True,
            pool_size=8,
            pool_recycle=60*30
            )

• echo: 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用
• pool_size: 连接池的大小，默认为5个，设置为0时表示连接无限制
• pool_recycle: 设置时间以限制数据库多久没连接自动断开

3. 创建数据库表类（模型）

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

from sqlalchemy.ext.declarative import declarative_base
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)

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

操作数据

表创建好了就是操作数据了，常见的操作增删改查，我们一一介绍。

session

sqlalchemy中使用session用于创建程序和数据库之间的会话，所有对象的载入和保存都需要通过session对象 。

通过sessionmaker调用创建一个工厂，并关联Engine以确保每个session都可以使用该Engine连接资源：

from sqlalchemy.orm import sessionmaker

# 创建session
DbSession = sessionmaker(bind=engine)
session = DbSession()

session的常见操作方法包括：

• flush：预提交，提交到数据库文件，还未写入数据库文件中
• commit：提交了一个事务
• rollback：回滚
• close：关闭

增

举个最简单的例子：

add_user = Users("test", "test123@qq.com")
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()

查

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

users = session.query(Users).filter_by(id=1).all()
for item in users:
  print(item.name)

通常我们通过以上查询模式获取数据，需要注意的是，通过session.query()我们查询返回了一个Query对象，此时还没有去具体的数据库中查询，只有当执行具体的.all()，.first()等函数时才会真的去操作数据库。

其中，query有filter和filter_by两个过滤方法，上述例子也可写为：

users = session.query(Users).filter_by(Users.id == 1).all()

通常这两个方法都会用到的，所以一定要掌握它们的区别：

filter filter_by支持所有比较运算符，相等比较用比较用==只能使用"="，"!="和"><"过滤用类名.属性名过滤用属性名不支持组合查询，只能连续调用filter变相实现参数是**kwargs，支持组合查询支持and，or和in等

改

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

filter	filter_by
支持所有比较运算符，相等比较用比较用==	只能使用"="，"!="和"><"
过滤用类名.属性名	过滤用属性名
过滤用类名.属性名	参数是**kwargs，支持组合查询
支持and，or和in等

改

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

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

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

users = session.query(Users).filter_by(name="Jack").first()
users.name = "test"
session.add(users)

这两种方式呢，一般批量更新的话我会选前者，而要对查询获取对象属性之后再更新的场景就需要使用后者。

删

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

delete_users = session.query(Users).filter(Users.name == "test").first()
if delete_users:
  session.delete(delete_users)
  session.commit()

第二种：

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

批量删除时推荐使用第二种。

以上，就是Python sqlalchemy的基本用法。

代码可参照：my github

---

1.基本用法

1.安装

安装sqlalchemy

pip3 install sqlalchemy
pip3 install pymysql

本文使用MySQL作为数据库，使用pymysql作为驱动，因此需要安装pymysql

2.连接数据库

1.配置信息

在连接数据库前，需要使用到一些配置信息，然后把它们组合成满足以下条件的字符串：

dialect+driver://username:password@host:port/database

• dialect：数据库，如：sqlite、mysql、oracle等
• driver：数据库驱动，用于连接数据库的，本文使用pymysql
• username：用户名
• password：密码
• host：IP地址
• port：端口
• database：数据库

HOST = 'localhost'
PORT = 3306
USERNAME = 'root'
PASSWORD = '123456'
DB = 'myclass'

# dialect + driver://username:passwor@host:port/database
DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'

建议将配置信息放到你的配置文件中，如config.py

2.创建引擎并连接数据库

from sqlalchemy import create_engine
from config import DB_URI


engine = create_engine(DB_URI)  # 创建引擎
conn = engine.connect()  # 连接
result = conn.execute('SELECT 1')  # 执行SQL
print(result.fetchone())  
conn.close()  # 关闭连接

3.创建ORM模型并映射到数据库中

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from config import DB_URI

engine = create_engine(DB_URI)
Base = declarative_base(engine)  # SQLORM基类
session = sessionmaker(engine)()  # 构建session对象


class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    sex = Column(String(10))


Base.metadata.create_all()  # 将模型映射到数据库中

执行上面代码，将会在数据库中生成对应的映射表student。

4.新增数据

创建表后，接下来我们要添加数据，代码如下：

student = Student(name='Tony', age=18, sex='male')  # 创建一个student对象
session.add(student)  # 添加到session
session.commit()  # 提交到数据库

也可以批量添加数据：

session.add_all([
    Student(name='Jane', age=16, sex='female'),
    Student(name='Ben', age=20, sex='male')
])
session.commit()

4.查询数据

sqlalchemy提供了query()方法来查询数据

获取所有数据

item_list = session.query(Student).all()
print(item_list)
for item in item_list:
    print(item.name, item.age)

执行结果如下

[<mymodel.Student object at 0x000002A0E6A38088>, <mymodel.Student object at 0x000002A0E6A38208>, <mymodel.Student object at 0x000002A0E6A38288>]
Tony 18
Jane 16
Ben 20

查询得到的item_list是一个包含多个Student对象的列表

指定查询列

item_list = session.query(Student).all()
print(item_list)
for item in item_list:
    print(item.name, item.age)

执行结果如下

[<mymodel.Student object at 0x000002A0E6A38088>, <mymodel.Student object at 0x000002A0E6A38208>, <mymodel.Student object at 0x000002A0E6A38288>]
Tony 18
Jane 16
Ben 20

查询得到的item_list是一个包含多个Student对象的列表

指定查询列

item_list = session.query(Student.name).all()
print(item_list)

# [('Tony',), ('Jane',), ('Ben',)]

获取返回数据的第一行

item = session.query(Student.name).first()
print(item)  

# ('Tony',)

使用filter()方法进行筛选过滤

item_list = session.query(Student.name).filter(Student.age >= 18).all()
print(item_list)

# [('Tony',), ('Ben',)]

使用order_by()进行排序

item_list = session.query(Student.name, Student.age).order_by(Student.age.desc()).all() # desc()表示倒序
print(item_list)

# [('Ben', 20), ('Tony', 18), ('Jane', 16)]

多个查询条件（and和or）

# 默认为and, 在filter()中用,分隔多个条件表示and
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age >= 10, Student.sex == 'female'
).all()
print(item_list)  # [('Jane', 16, 'female')]



from sqlalchemy import or_

# 使用or_连接多个条件
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    or_(Student.age >= 20, Student.sex == 'female')
).all()
print(item_list)  # [('Jane', 16, 'female'), ('Ben', 20, 'male')]

equal/like/in

# 等于
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age == 18
).all()
print(item_list)  # [('Tony', 18, 'male')]

# 不等于
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age != 18
).all()
print(item_list)  # [('Jane', 16, 'female'), ('Ben', 20, 'male')]

# like
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.name.like('%To%')
).all()
print(item_list)  # [('Tony', 18, 'male')]

# in
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age.in_([16, 20])
).all()
print(item_list) # [('Jane', 16, 'female'), ('Ben', 20, 'male')]

count计算个数

count = session.query(Student).count()
print(count)  # 3

切片

item_list = session.query(Student.name).all()[:2]
print(item_list)  # [('Tony',), ('Jane',)]

4.修改数据

修改数据可以使用update()方法，update完成后记得执行session.commit()

# 修改Tony的age为22
session.query(Student).filter(Student.name == 'Tony').update({'age': 22})
session.commit()

item = session.query(Student.name, Student.age).filter(Student.name == 'Tony').first()
print(item) 

执行结果如下

('Tony', 22)

4.删除数据

删除数据使用delete()方法，同样也需要执行session.commit()提交事务

# 删除名称为Ben的数据
session.query(Student).filter(Student.name == 'Ben').delete()
session.commit()

item_list = session.query(Student.name, Student.age).all()
print(item_list)

执行结果如下

[('Tony', 22), ('Jane', 16)]