SqlAlchemy的简单使用
1.SQLAlchemy
SQLAlchemy是python的一个通用的ORM框架
1.1 创建数据表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, String, Column
BaseModel = declarative_base()
class User(BaseModel):
# 定义表名
__tablename__ = 'user'
id = Column(Integer, primary_key=True) # 定义主键字段,如果是int型,就会自动自增
name = Column(String(32), nullable=False, unique=True) # 定义字符串非空,唯一字段
# 创建一个引擎连接数据库
from sqlalchemy import create_engine
# 格式:engine = create_engine("postgresql://scott:tiger@localhost/test?charset=utf8")
# "数据库种类+数据库驱动://用户名:密码@IP地址:端口号/数据库名" 端口号不写,使数据库默认端口
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/test')
# 扫描BaseModel下所有的继承它的model,并创建
BaseModel.metadata.create_all(engine)
1.2 增删改查
1.2.1 新增数据
# 导入创建好的model
from models import User
# 创建一个model对象
user = User(name='tom')
# 写入数据库
# 首先要打开数据库会话,相当于创建一个操作数据库的窗口
from sqlalchemy.orm import sessionmaker
# 导入之前创建好的引擎
from models import engine
# 通过数据库引擎创建会话类
Session = sessionmaker(engine)
# 打开会话对象,相当于新建了一个查询窗口
session = Session()
# 在会话中添加一条model数据
session.add(user)
# 提交会话,将会话中的所有数据提交到数据库
session.commit()
# 关闭会话
session.close()
'''
可以一次添加多条数据,然后再提交
session.add(User(name='rose'))
session.add(User(name='jack'))
session.commit()
session.close()
或者使用列表
user_list = [
User(name='rose'),
User(name='jack'),
]
session.add_all(user_list)
session.commit()
session.close()
'''
1.2.2 查询数据
from models import User, engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()
# 查询所有的数据
user_list = session.query(User).all()
for user in user_list:
print(user.name)
# 根据条件查询
user_list = session.query(User).filter(User.id > 1).all()
for user in user_list:
print(user.id, user.name)
#对于等于的条件要用双等号
user = sessiom.query(User).filter(User.id==3).first()
#或使用filter_by
user = session.query(User).filter_by(User.id=3).first()
# 也可以取出一个
user = session.query(User).filter(User.id > 1).first()
print(user.id, user.name)
# 通过all()和first()取出数据,如果不取出数据返回的是原生的sql
sql = session.query(User).filter(User.id > 1)
print(sql)
'''
SELECT user.id AS user_id, user.name AS user_name
FROM user
WHERE user.id > %(id_1)s
'''
session.close()
1.2.3 修改数据
from models import User, engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()
# 要修改数据首先要查询到数据,然后再对其进行修改
res = session.query(User).filter(User.id == 1).update({'name': 'tree'})
print(res) # 返回修改的条数
session.commit() # 增删改都要提交后,才会对数据库数据进行修改
session.close()
1.2.4 删除数据
from models import User, engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()
# 要删除数据首先要查询到数据,然后再对其进行修改
res = session.query(User).filter(User.id == 1).delete()
print(res) # 返回删除的条数
session.commit() # 增删改都要提交后,才会对数据库数据进行修改
session.close()
1.3 高级查询
from models import User, engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()
# 根据多条件查询
from sqlalchemy.sql import and_, or_, text
Users = session.query(User).filter(and_(User.id >= 1, User.name == 'rose')).all()
print(Users)
Users = session.query(User).filter(or_(User.id >= 1, User.name == 'rose')).all()
print(Users)
# 指定查询数据列,和别名
Users = session.query(User.name.label('user_name')).all()
print(Users) # [('jack',), ('rose',)]
for user in Users:
print(user.user_name) # 需要用别名来取
# 表达式筛选条件
Users = session.query(User).filter(User.id == 2).all()
print(Users)
# 原生sql筛选条件
Users = session.query(User).filter_by(id=2).all()
print(Users)
# 字符串方式方式筛选条件,并使用 order_by进行排序
Users = session.query(User).filter(text("id<:value and name=:name")).params(value=7, name='aaa').order_by(
User.id).all()
print(Users)
1.4 一对多创建
from sqlalchemy.ext import declarative
from sqlalchemy.engine import create_engine
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship
BaseModel = declarative.declarative_base()
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test?charset=utf8")
class School(BaseModel):
__tablename__ = "school"
id = Column(Integer, primary_key=True)
name = Column(String(32), unique=True)
class Student(BaseModel):
__tablename__ = "student"
id = Column(Integer, primary_key=True)
name = Column(String(32), unique=True)
sch_id = Column(Integer, ForeignKey("school.id"))
school = relationship("School", backref="student")
BaseModel.metadata.create_all(engine)
1.5 一对多的CURD
#增加数据
#方式一(和ORM无关系):先建立一个学校,再查询这个学校的id,根据这个id去创建学生并添加sch_id
#方式二:通过relationship 正向添加
stu = Student(name='张三', school=School(name="清华大学"))
session.add(stu)
session.commit()
session.close()
#方式三:通过relationship反向添加
sch = School(name="北京大学")
sch.student = [
Student(name="李四"),
Student(name="王五")
]
session.add(sch)
session.commit()
session.close()
#删除数据,和单表删除一样,但若是学校被引用则无法删除
#查询数据
#正向查询
stu_list = session.query(Student).all()
for stu in stu_list:
print(stu.name, stu.school.name)
#反向查询
school_list = session.query(School).all()
for school in school_list:
#print(school.name,len(school.student)) 查询每个学校的学生个数
for stu in school.student:
print(school.name, stu.name)
1.6 多对多创建
from sqlalchemy.ext import declarative
from sqlalchemy.engine import create_engine
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship
BaseModel = declarative.declarative_base()
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test?charset=utf8")
class Girl(BaseModel):
__tablename__ = "girl"
id = Column(Integer, primary_key=True)
name = Column(String(32), unique=True)
boy = relationship("Boy", backref="girl", secondary="hotel") # secondary指定第三张表
class Boy(BaseModel):
__tablename__ = "boy"
id = Column(Integer, primary_key=True)
name = Column(String(32), unique=True)
class Hotel(BaseModel):
__tablename__ = 'hotel'
id = Column(Integer, primary_key=True)
gid = Column(Integer, ForeignKey("girl.id"))
bid = Column(Integer, ForeignKey("boy.id"))
BaseModel.metadata.create_all(engine)
1.7 多对多的CRUD
from sqlalchemy.orm import sessionmaker
from createM2M import engine,Girl,Boy
Session = sessionmaker(engine)
session = Session()
# 添加数据
# 正向添加
girl = Girl(name='小红', boy=[Boy(name="小明"), Boy(name="小王")])
session.add(girl)
session.commit()
session.close()
#反向添加数据
boy = Boy(name='小李')
boy.girl = [
Girl(name="小丽"),
Girl(name="小花")
]
session.add(boy)
session.commit()
session.close()
#查询
#正向查询
res = session.query(Girl).all()
for i in res:
print(i.name, len(i.boy))
#反向查询
res = session.query(Boy).all()
for i in res:
print(i.name, len(i.girl))