SQLAlchemy

SQLAlchemy

1.介绍

  • SQLAlchemy是Python SQL工具包和对象关系映射器，是python中最著名的ORM(Object Relationship Mapping)框架，它简化了应用程序开发人员在原生SQL上的操作，使开发人员将主要精力都放在程序逻辑上，从而提高开发效率。它提供了一整套著名的企业级持久性模式，设计用于高效和高性能的数据库访问。
  • 使用ORM操作数据库：

　　　　优势 ：代码易读，隐藏底层原生SQL语句，提高了开发效率。

　　　　劣势 ：执行效率相对低一些 ，将方法转换为原生SQL后再去查询。

2.安装

  • 在终端安装

    pip install SQLAlchemy

  • 在工具中下载SQLAlchemy

3.普通表操作

3.1 创建数据表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
# 实例化模型，Base 就是 ORM 模型
Base = declarative_base()
# 当前的这个Object继承了Base也就是代表了Object继承了ORM的模型
class User(Base):
    # 为table创建名称
    __tablename__ = "user"
    # 格式Column(数据类型,索引,主键,外键,等等)
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), index=True, nullable=False)
    # Column定义数据列，每一个字段都是数据列
    # primary_key=True  主键
    # autoincrement=True 自增字段
    # index=True  索引
    # nullable=False  能否为空，默认能。False不能
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
                    #  数据库 数据库驱动 user pwd  host       dbname   编码
# Base 自动检索所有继承Base的ORM 对象 并且创建所有的数据表
Base.metadata.create_all(engine)
View Code

3.2 添加数据

# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
from createTable import User
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
# 4.写入SQL语句
user = User(name = "xiaoqi")
# 放入查询窗口
db_session.add(user)
# 5.提交sql语句
db_session.commit()
# 6.关闭查询窗口
db_session.close()
​
# 多条数据一起提交
user_list = [
     User(name="xiaoqi1"),
     User(name="xiaoqi2"),
     User(name="xiaoqi3")
]
db_session.add_all(user_list)
db_session.commit()
db_session.close()
View Code

3.3 删除数据

# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
from createTable import User
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
​
# 将user表中id=1的数据删除
res = db_session.query(User).filter(User.id ==1).delete()
db_session.commit()
db_session.close()
View Code

3.4 修改数据

# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
from createTable import User
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
​
# 将user表中id=1的数据的名字改成"shazi"
res = db_session.query(User).filter(User.id ==1).update({"name":"shazi"})
db_session.commit()
db_session.close()
View Code

3.5 查询数据

简单无条件查询

from my_create_table import User
from sqlalchemy.orm import sessionmaker
# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
select_db = sessionmaker(engine)
db_session = select_db()
​
# 1. select * from user 查询user表中的所有数据
# 语法是这样的 使用 db_session 会话 执行User表 query(User) 取出全部数据 all()
user_all_list = db_session.query(User).all()
print(user_all_list)  # [<my_create_table.User object at 0x0000016D7C4BCDD8>]
# 通过循环查看user_all_list其中的数据
for i in user_all_list:
    print(i.id, i.name)  # ORM对象 直接使用调用属性的方法 拿出对应字段的值
db_session.close()
​
# 只取出一条
user = db_session.query(User).first()
print(user.id, user.name)
db_session.close()
View Code

简单条件查询

from my_create_table import User
from sqlalchemy.orm import sessionmaker
# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
select_db = sessionmaker(engine)
db_session = select_db()
​
"""1. select * from user where id>5; 查询user表中id>5的数据 """
# 语法是这样的 使用 db_session 会话 执行User表 query(User) 筛选内容User.id >5 的数据全部取出 all()
user_all_list = db_session.query(User).filter(User.id>5).all()
print(user_all_list)  
for i in user_all_list:  # 通过循环查看user_all_list其中的数据
    print(i.id, i.name)  # ORM对象 直接使用调用属性的方法 拿出对应字段的值
db_session.close()
​
"""2.除了取出全部还可以只取出一条"""
user = db_session.query(User).filter(User.id>5).first()
print(user.id, user.name)
db_session.close()
​
user = db_session.query(User).filter(User.id==5).first()  # 筛选id=5的数据,要双等于
print(user.id, user.name)
db_session.close()
​
user = db_session.query(User).filter_by(id=5).first()  # 筛选id=5的数据,要双等于
print(user.id, user.name)
db_session.close()
View Code

复杂一点的查询

----------models.py-------------
​
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
# 实例化模型，Base 就是 ORM 模型
Base = declarative_base()
# 当前的这个Object继承了Base也就是代表了Object继承了ORM的模型
class User(Base):
    # 为table创建名称
    __tablename__ = "user"
    # 格式Column(数据类型,索引,主键,外键,等等)
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), index=True, nullable=False)
    
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# Base 自动检索所有继承Base的ORM 对象 并且创建所有的数据表
Base.metadata.create_all(engine)
​
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
from al.models import User,db_session
​
"""1.合并条件查询，不能使用and，使用逗号隔开，表示两个条件与的关系"""
user_all_list = db_session.query(User).filter(User.id>2,User.name=='xiaoqi3').all()
for i in user_all_list: 
    print(i.id, i.name)  
db_session.close()
​
"""2.指定查询数据列 加入别名"""
r2 = db_session.query(User.name.label('username'), User.id).first()
r2.username  # 要使用别名
​
"""3.表达式筛选"""
r3 = db_session.query(User).filter(User.name == "xiaoqi").all()
​
"""4.sql筛选"""
r4 = db_session.query(User).filter_by(name='xiaoqi').all()
​
"""5.筛选查询列"""
r5 = db_session.query(User.name).all()
​
"""6.字符串匹配方式筛选条件"""
# 复杂查询
from sqlalchemy.sql import text
user_list = db_session.query(User).filter(text("id<:value and name=:name")).params(value=3,name="xiaoqi1")
# 查询语句
from sqlalchemy.sql import text
user_list = db_session.query(User).filter(text("select * from User id<:value and name=:name")).params(value=3,name="xiaoqi1")
​
"""7.与and_()、或or_() """
from sqlalchemy.sql import and_ , or_
ret = db_session.query(User).filter(or_(User.id>2, User.name == 'xiaoqi1')).all()  # 查询出id>2或name == 'xiaoqi1'的数据
for i in ret:
    print(i.id,i.name)
ret = db_session.query(User).filter(and_(User.id>2, User.name == 'xiaoqi1')).all() # 查询出id>2并且name == 'xiaoqi1'的数据
​
"""8.排序order_by()"""
user_list = db_session.query(User).order_by(User.id).all()  # 默认升序排列
user_list = db_session.query(User).order_by(User.id.desc()).all()  # desc()降序排列
​
"""9.between() 大于1小于3的"""
ret = session.query(User).filter(User.id.between(1, 3), User.name == 'xiaoqi2').all()
​
"""10.in_() 只查询范围内的"""
ret = db_session.query(User).filter(User.id.in_([1,3,4])).all()
​
"""11.子查询"""
ret = db_session.query(User).filter(User.id.in_(db_session.query(User.id).filter_by(name='xiaoqi1'))).all()
​
"""12.通配符"""
ret = db_session.query(User).filter(User.name.like('%1')).all()
​
"""13.限制"""
ret = db_session.query(User)[1:3]  # 实际就是索引取值
​
"""14.分组"""
ret = db_session.query(User).group_by(User.extra).all()
​
from sqlalchemy.sql import func  # 聚合函数在func这个对象中
ret = db_session.query(func.max(User.id),func.sum(User.id),func.min(User.id)).group_by(User.name).all()
View Code

4.一对多的操作

4.1 创建数据表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import relationship
​
# 实例化模型，Base 就是 ORM 模型
Base = declarative_base()
​
class Class(Base):
    __tablename__ = "class"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
​
class Student(Base):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    class_id = Column(Integer, ForeignKey("class"))  # 创建外键，数据库层面的所以使用数据库的表名
    # 关系映射，为了能够互相找到
    class_stu = relationship("Class",backref="class_stu")
# 创建数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
                    #  数据库 数据库驱动 user pwd  host       dbname   编码
# Base 自动检索所有继承Base的ORM 对象 并且创建所有的数据表
Base.metadata.create_all(engine)
View Code

4.2 添加数据

# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
from createTable import Class,Student
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
​
"""1.简单增加数据"""
# 添加两个班级:
db_session.add_all([
    Class(name="one"),
    Class(name="two")
])
db_session.commit()
# 添加一个学生 xiaoqi 班级是 one
# 查询要添加到的班级
class_obj = db_session.query(Class).filter(Class.name == "one").first()
# 创建学生
stu = Student(name="xiaoqi",class_id = class_obj.id)
db_session.add(stu)
db_session.commit()
db_session.close()
​
"""2.relationship正向添加"""
stu = Student(name="xiaoqi",class_stu=Class(name="one"))
db_session.add(stu)
db_session.commit()
db_session.close()
​
"""3.relationship反向添加"""
cla = Class(name="one")
cla.class_stu = [
    Student(name="xiaoqi"),
    Student(name="dada")
]
db_session.add(cla)
db_session.commit()
db_session.close()
View Code

4.3 删除数据

# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
from createTable import Class,Student
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
​
# 单表删除
class_info = db_session.query(Class).filter(Class.name=="one").first()
db_session.query(Student).filter(Student.class_id == class_info.id).delete()
db_session.commit()
db_session.close()
​
# 注意：如果外键被引用着是删除不了的。要先把关联解除。
View Code

4.4 修改数据

# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
from createTable import Class,Student
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
​
# 单表修改
res = db_session.query(Class).filter(Class.id ==1).update({"name":"three"})
db_session.commit()
db_session.close()
View Code

4.5 查询数据

from my_create_table import Class,Student
from sqlalchemy.orm import sessionmaker
# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
select_db = sessionmaker(engine)
db_session = select_db()
​
"""1.正向查询"""
student_list = db_session.query(Student).all() # 查询出所有学生
for stu in student_list:  # 获取每个学生对象
    # stu.class_stu.name 通过Student对象中的关系字段relationship class_stu 获取关联 Class中的name
    print(stu.name,stu.class_stu.name,stu.class_id)
    
"""2.反向查询"""
class_list = db_session.query(Class).all() # 查询出所有学生
for cla in class_list:  # 获取每个班级对象
    # cla.class_stu取到的是一个列表里面是student的对象
    for stu in cla.class_stu：# 通过 backref 中的class_stu反向关联到 Student 表
        print(cla.name,stu.name)
View Code

5.多对多的操作

5.1 创建数据表

# sqlalchemy中第三张表需要自己创建
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import relationship
​
# 实例化模型，Base 就是 ORM 模型
Base = declarative_base()
​
# 创建数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
                    #  数据库 数据库驱动 user pwd  host       dbname   编码
​
class Boy(Base):
    __tablename__ = "boy"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    
class Girl(Base):
    __tablename__ = "gril"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    boys = relationship("Boy",secondary="hotel",backref="girl_boy")  # 关系不能放在第三张表中。
    
class Hotel(Base):
    __tablename__ = "hotel"
    id = Column(Integer,primary_key=True)
    girl_id = Column(Integer,ForeignKey("girl.id"))
    boy_id = Column(Integer,ForeignKey("boy.id"))
    
Base.metadata.create_all(engine)
View Code

5.2 添加数据

# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
from createTable import Boy,Girl
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
​
"""1.正向添加"""
g = Girl(name="a",boys=[Boy(name="b"),Boy(name="c")])
db_session.add(g)
db_session.commit()
db_session.close()
​
"""2.反向添加"""
b = Boy(name="x")
b.girl_boy = [
    Girl(name="y"),
    Girl(name="z")
]
db_session.add(b)
db_session.commit()
db_session.close()
View Code

5.3 查询数据

# 1.选择数据库
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mydb?charset=utf8")
# 2.选择表,从创建表的文件导入
from createTable import Boy,Girl
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口
​
"""1.正向查询"""
ret_lis = db_session.query(Girl).all()
for g in ret_lis:
    for b in g.boys:
        print(g.name,b.name)
    
"""2.反向查询"""
res_lis = db_session.query(Boy).all()
for b in res_lis:
    for g in b.girl_boy:
        print(b.name,g.name)
View Code

 

参考：

https://www.cnblogs.com/DragonFire/p/10166527.html

https://www.lagou.com/lgeduarticle/36763.html

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/liuweida/p/11697290.html