sqlalchemy的基本用法
参考原文：https://www.cnblogs.com/blueberry-mint/p/14277882.html
1 简介
SQLAlchemy是用Python编程语言开发的一个开源项目。它提供了SQL工具包和ORM（对象关系映射）工具，使用MIT许可证发行。
SQLAlchemy最初在2006年2月发行，发行后便很快的成为Python社区中最广泛使用的ORM工具之一，丝毫不亚于Django自带的ORM框架。
SQLAlchemy采用简单的Python语言，提供高效和高性能的数据库访问，实现了完整的企业级持久模型。它的理念是，SQL数据库的量级和性能比对象集合重要，而对象集合的抽象又重要于表和行。
2 基本用法
2.1 安装
安装sqlachemy
pip3 install sqlalchemy pip3 install pymysql
本文使用MySQL作为数据库，使用pymysql作为驱动，因此需要安装pymysql
2.2 连接数据库
配置信息
在连接数据库前，需要使用到一些配置信息，然后把它们组合成满足以下条件的字符串：
dialect+driver://username:password@host:port/database
- dialect：数据库，如：sqlite、mysql、oracle等
- driver：数据库驱动，用于连接数据库的，本文使用pymysql
- username：用户名
- password：密码
- host：IP地址
- port：端口
- database：数据库
建议将这些配置信息放到一个配置文件中，如config.py然后倒入配置文件
config.py
HOST = '192.168.1.180'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'password'
DB = 'myclass'
# f代表可以使用{}引用变量
DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'
# 以下语句和上面语句作用一致
# DB_URI = 'mysql+pymysql://%s:%s@%s:%s/%s'%(USERNAME,PASSWORD,HOST,PORT,DB)
创建引擎并连接数据库
注意：本次操作的数据库名为myclass需要提前创建好
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() # 关闭连接
这里执行是SQL语句可以参考 https://www.cnblogs.com/minseo/p/15291725.html 是一致的
创建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
# print(DB_URI)
# mysql+pymysql://root:password@192.168.1.180:3306/myclass
# 创建引擎
engine = create_engine(DB_URI)
#sconn = engine.connect()
# 创建SQLORM基类，注意这里要加参数，参数为上一步创建的引擎
Base = declarative_base(engine)
# 构建session对象
session = sessionmaker(engine)()
# 创建student表
# 该表有4列分别为自增id name age sex
# 分别代表id 姓名 年龄 性别其中id age为整数 name和sex为字符串
class Student(Base):
__tablename__ = 'student' # 表名
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50))
age = Column(Integer)
sex = Column(String(10))
# 将模型映射到数据库中
# 即如果数据库中没有student表则创建映射表student
Base.metadata.create_all()
执行上面代码，将会在数据库中生成对应的映射表student
可以登录MySQL查看建表语句
mysql> show create table student; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
注意：如果表结构有修改，例如增加一个字段address
address = Column(String(100))
再执行不会修改表，只有删除表再执行才会重新创建表并且新增这个字段
即执行Base.metadata.create_all()是如果数据库里面没有表则创建表，如果有表则不进行操作
新增数据
在添加数据之前可以把以下代码注释，因为表已经创建好了，无需执行
Base.metadata.create_all()
创建好表以后，接下来我们要添加数据，代码如下
# 新增数据 # 创建一个Student对象 student = Student(name='Tony',age=18,sex='male') # 添加到session session.add(student) # 提交到数据库 session.commit()
添加数据以后查看
mysql> select * from student; +----+------+------+------+ | id | name | age | sex | +----+------+------+------+ | 1 | Tony | 18 | male | +----+------+------+------+
也可以批量添加数据
# 批量添加数据
# 使用add_all()方法传递的参数为list
# list对象为Student对象
session.add_all([
Student(name='Jane', age=16, sex='female'),
Student(name='Ben', age=20, sex='male')
])
session.commit()
查询数据库现在有3条数据了
mysql> select * from student; +----+------+------+--------+ | id | name | age | sex | +----+------+------+--------+ | 1 | Tony | 18 | male | | 2 | Jane | 16 | female | | 3 | Ben | 20 | male |
查询数据
sqlalchemy提供了query()方法来查询数据
获取所有数据
# 查询数据
# 获取所有数据
item_list = session.query(Student).all()
# 获取的是Student对象
print(item_list)
# [<__main__.Student object at 0x000002748DEC2BE0>, <__main__.Student object at 0x000002748DEC2C50>, <__main__.Student object at 0x000002748DEC2CC0>]
# 遍历通过对象的属性打印对应的数据
for item in item_list:
print(item.name, item.age)
# Tony 18
# Jane 16
# Ben 20
查询得到的item_list是一个包含多个Student对象的列表
指定列查询
# 指定列查询
# 指定查询name列
# 类似于执行sql语句select name from student;
item_list = session.query(Student.name).all()
# 查询的数据组成一个list，数据为元组为查询的结果
# 可以指定多个列以,分隔,获取的结果的元组也以,分隔
print(item_list)
# [('Tony',), ('Jane',), ('Ben',)]
获取返回数据的第一行
# 获取返回数据的第一行
item = session.query(Student.name).first()
print(item)
# ('Tony',)
使用filter()方法过滤
# 使用filter方法过滤
item_list = session.query(Student.name).filter(Student.age >= 18).all()
print(item_list)
# [('Tony',), ('Ben',)]
使用order_by()进行排序
# 使用order_by()进行排序
# 使用列name和age查询并且使用age进行倒序
item_list = session.query(Student.name,Student.age).order_by(Student.age.desc()).all()
print(item_list)
# [('Ben', 20), ('Tony', 18), ('Jane', 16)]
多条件查询
# 多条件查询条件以,分隔默认为and item_list = session.query(Student.name, Student.age, Student.sex).filter(Student.age >= 10, Student.sex == 'female').all() print(item_list)
以上查询语句类似于sql语句
mysql> select name,age,sex from student where age<=16 and sex='female'; +------+------+--------+ | name | age | sex | +------+------+--------+ | Jane | 16 | female | +------+------+--------+ 1 row in set (0.00 sec)
使用or_连接多个条件
# 以or条件查询
# 需要导入or_
from sqlalchemy import 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
下面示例查询等于，不等于，like是模糊查询，in是出现是否包含传递参数列表类元素
# equal/like/in
# 等于 查询age等于18的对象
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
# 查询名字有有To字符的对象
item_list = session.query(Student.name,Student.age,Student.sex).filter(Student.name.like('%To%')).all()
print(item_list)
# [('Tony', 18, 'male')]
# in
# 查询age为16或者20的对象，不是16到20的区间
# 注意这里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计算个数 count = session.query(Student).count() print(count) # 3
切片
# 切片，和Python切片方法一样
item_list = session.query(Student.name).all()[:2]
print(item_list)
# [('Tony',), ('Jane',)]
修改数据
修改数据可以使用update()方法，update完成后记得执行session.commit()
# 修改数据
# 把Tony的age修改为22
# 先查询对象，filter设置过滤条件，update修改数据
session.query(Student).filter(Student.name=='Tony').update({'age':22})
session.commit()
删除数据
删除数据使用delete()方法，同样也需要执行session.commit()提交事务
# 删除数据 session.query(Student).filter(Student.name=='Tony').delete() session.commit()
注意：增删改查除了查询不设计修改操作，其他增删改都需要使用commit()方法提交事务
