创建连接
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
# 创建引擎
engine = create_engine('mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset={charset}')
session = sessionmaker(engine)()
创建模型
# 基类模型
Base = declarative_base(engine)
# 创建模型
class Student(Base):
"""功能：学生映射模型类"""
__tablename__ = 'Student'
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键ID")
name = Column(String(50), index=True, nullable=True, comment="学生名称")
age = Column(Integer, comment="学生年龄")
sex = Column(String(10), comment="学生性别")
新增数据
新增数据到数据库，需要执行session.commit()提交事务
单个添加数据
# 创建一个学生对象
student = Student(name='Tony', age=18, sex='male')
# 添加到连接
session.add(student)
# 刷新连接，给对象添加自增ID
session.flush()
# 获取对象的ID
last_id = student.id
# 提交到数据库
session.commit()
批量添加数据
"""
方式1：add_all(objects)，刷新可获取自增ID列表
"""
# 创建多个学生对象
students = [
Student(name='Jack', age=19, sex='male'),
Student(name='Rose', age=20, sex='female'),
]
# 添加到连接
session.add_all(students)
# 刷新连接，给对象添加自增ID
session.flush()
insert_ids = [student.id for student in students]
# 提交到数据库
session.commit()
"""
方式2：bulk_save_objects(objects)，无法获取自增ID列表
"""
# 创建多个学生对象
students = [
Student(name='Jack', age=19, sex='male'),
Student(name='Rose', age=20, sex='female'),
]
# 添加到连接
session.bulk_save_objects(students)
# 刷新连接，给对象添加自增ID
session.flush()
insert_ids = [student.id for student in students]
# 提交到数据库
session.commit()
"""
方式3：bulk_insert_mapping(class, list(dict1, dict2...))，无法获取自增ID列表
"""
# 创建多个学生对象
mappings = [
dict(name='Jack10', age=19, sex='male'),
dict(name='Jack20', age=19, sex='male'),
]
# 添加到连接
session.bulk_insert_mappings(Student, mappings)
# 刷新连接，给对象添加自增ID
session.flush()
insert_ids = [student.id for student in students]
# 提交到数据库
session.commit()
查询数据
1.查询全部字段全部数据
item_list = session.query(Student).all()
print(item_list)
for item in item_list:
print(item.id, item.name, item.age, item.sex)
[<mymodel.Student object at 0x000002A0E6A38088>, <mymodel.Student object at 0x000002A0E6A38208>...]
Tony 18
Jack 16
...
2.查询指定字段全部数据
item_list1 = session.query(Student.name).all()
item_list2 = session.query(Student.name, Student.sex).all()
print(item_list1)
print(item_list2)
[('Tony',), ('Jack',)...]
[('Tony', 'male'), ('Jack', 'male')...]
3.获取返回结果的第一行
first_student_object = session.query(Student).first()
first_student_data = session.query(Student.name).first()
4.指定过滤条件筛选数据
# 单个过滤条件筛选
item_list = session.query(Student.name).filter(Student.age >= 18).all()
print(item_list)
# 多个过滤条件筛选（and）
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.age >= 10, Student.sex == 'female').all()
print(item_list)
# 多个过滤条件筛选（or）
item_list = session.query(Student.name, Student.age, Student.sex).filter(
or_(Student.age >= 20, Student.sex == 'female')
).all()
print(item_list)
5.与或非查询(and, or, not)
# 默认逗号分隔条件即为与
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.age >= 20, Student.sex == 'female'
).all()
print(item_list)
# 使用and_()方法，即为或
item_list = session.query(Student.name, Student.age, Student.sex).filter(
and_(Student.age >= 20, Student.sex == 'female')
).all()
print(item_list)
# 使用or_()方法，即为或
item_list = session.query(Student.name, Student.age, Student.sex).filter(
or_(Student.age >= 20, Student.sex == 'female')
).all()
print(item_list)
# 使用not_()方法，即为或
item_list = session.query(Student.name, Student.age, Student.sex).filter(
not_(Student.age >= 20, Student.sex == 'female')
).all()
print(item_list)
# 使用与或非组合筛选（and_(), or_(), not_()）
item_list = session.query(Student).filter(
or_(
Student.id < 2,
and_(Student.name == 'Tony', Student.id > 3),
Student.sex != ""
)).all()
print(item_list)
6.查询数据并进行排序(asc, desc)
# 默认正序排列
item_list = session.query(Student.name, Student.age).order_by(Student.age).all()
# asc()函数正序排列
item_list = session.query(Student.name, Student.age).order_by(Student.age.asc()).all()
# desc()表示倒序
item_list = session.query(Student.name, Student.age).order_by(Student.age.desc()).all()
7.模糊查询(like, contains)
# 使用like()，即为like
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.name.like('%To%')
).all()
print(item_list)
# 使用contains()，即为like
item_list = session.query(Student.name, Student.age, Student.sex).filter(
Student.name.contains('To')
).all()
print(item_list)
8.包含不包含查询(in, not in)
# 使用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')]
# 使用~和in_()组合，为不包含
item_list = session.query(Student.name, Student.age, Student.sex).filter(
~Student.age.in_([16, 20])
).all()
print(item_list)
9.区间查询（between...and...）
session.query(Student).filter(Student.id.between(1, 3), Student.name == 'Jack').all()
10.子查询
session.query(Student).filter(Student.id.in_(session.query(Student.id).filter(Student.name=='Rose'))).all()
11.分组查询(group by)
from sqlalchemy.sql import func
ret = session.query(
Users.depart_id,
func.count(Users.id),
).group_by(Users.depart_id).all()
for item in ret:
print(item)
ret = session.query(
Users.depart_id,
func.count(Users.id),
).group_by(Users.depart_id).having(func.count(Users.id) >= 2).all()
for item in ret:
print(item)
12.查询数据数量
count = session.query(Student).count()
print(count)
13.查询数据切片
item_list = session.query(Student.name).all()
print(item_list)
14.查询结果拼接
# 自动过滤重复数据
q1 = session.query(Student.name).filter(Student.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()
# 不自动过滤重复数据
q1 = session.query(Student.name).filter(Student.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()
删除数据
删除数据使用delete()方法，同样也需要执行session.commit()提交事务
session.query(Student).filter(Student.name == 'Jack').delete()
session.commit()
item_list = session.query(Student.name, Student.age).all()
print(item_list)
修改数据
修改数据可以使用update()方法，update完成后记得执行session.commit()
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)
