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

?

1 2	pip3 install sqlalchemy pip3 install pymysql

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

　　2.2 连接数据库

　　配置信息

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

?

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

　　

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

　　建议将这些配置信息放到一个配置文件中，如config.py然后倒入配置文件

　　config.py

?

1 2 3 4 5 6 7 8 9 10

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需要提前创建好

?

1 2 3 4 5 6 7 8 9

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模型并映射到数据库中

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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查看建表语句

?

1 2 3 4 5 6 7 8 9 10 11

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

?

1	address = Column(String(100))

　　再执行不会修改表，只有删除表再执行才会重新创建表并且新增这个字段

　　即执行Base.metadata.create_all()是如果数据库里面没有表则创建表，如果有表则不进行操作

　　新增数据

　　在添加数据之前可以把以下代码注释，因为表已经创建好了，无需执行

?

1	Base.metadata.create_all()

　　

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

?

1 2 3 4 5 6 7

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

　　添加数据以后查看

?

1 2 3 4 5 6

mysql> select * from student; +----+------+------+------+ | id | name | age  | sex  | +----+------+------+------+ |  1 | Tony |   18 | male | +----+------+------+------+

　　也可以批量添加数据

?

1 2 3 4 5 6 7 8

# 批量添加数据 # 使用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条数据了

?

1 2 3 4 5 6 7

mysql> select * from student; +----+------+------+--------+ | id | name | age  | sex    | +----+------+------+--------+ |  1 | Tony |   18 | male   | |  2 | Jane |   16 | female | |  3 | Ben  |   20 | male   |

　　查询数据

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

　　获取所有数据

?

1 2 3 4 5 6 7 8 9 10 11 12

# 查询数据 # 获取所有数据 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对象的列表

　　指定列查询

?

1 2 3 4 5 6 7 8

# 指定列查询 # 指定查询name列 # 类似于执行sql语句select name from student; item_list = session.query(Student.name).all() # 查询的数据组成一个list，数据为元组为查询的结果 # 可以指定多个列以,分隔,获取的结果的元组也以,分隔 print(item_list) # [('Tony',), ('Jane',), ('Ben',)]

　　获取返回数据的第一行

?

1 2 3 4

# 获取返回数据的第一行 item = session.query(Student.name).first() print(item) # ('Tony',)

　　使用filter()方法过滤

?

1 2 3 4 5

# 使用filter方法过滤 item_list = session.query(Student.name).filter(Student.age >= 18).all() print(item_list)   # [('Tony',), ('Ben',)]

　　使用order_by()进行排序

?

1 2 3 4 5

# 使用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)]

　　多条件查询

?

1 2 3

# 多条件查询条件以,分隔默认为and item_list = session.query(Student.name, Student.age, Student.sex).filter(Student.age >= 10, Student.sex == 'female').all() print(item_list)

　　以上查询语句类似于sql语句

?

1 2 3 4 5 6 7

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_连接多个条件

?

1 2 3 4 5 6

# 以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是出现是否包含传递参数列表类元素

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

# 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计算个数

?

1 2 3 4

# count计算个数 count = session.query(Student).count() print(count) # 3

　　切片

?

1 2 3 4

# 切片，和Python切片方法一样 item_list = session.query(Student.name).all()[:2] print(item_list)  # [('Tony',), ('Jane',)]

　　修改数据

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

?

1 2 3 4

# 修改数据 # 把Tony的age修改为22 # 先查询对象，filter设置过滤条件，update修改数据 session.query(Student).filter(Student.name=='Tony').update({'age':22})<br>session.commit()

　　删除数据

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

?

1 2 3

# 删除数据 session.query(Student).filter(Student.name=='Tony').delete() session.commit()

　　注意：增删改查除了查询不设计修改操作，其他增删改都需要使用commit()方法提交事务