SQLAlchemy

今天来聊一聊 Python 的 ORM 框架 SQLAlchemy

我在这里官宣辟谣一下啊, Models 紧紧只是配置和使用比较简单(这特么就是废话),因为他是Django自带的ORM框架,也正是因为是Django原生的,所以兼容性远远不如SQLAlchemy

真正算得上全面的ORM框架必然是我们的SQLAlchemy ORM框架,它可以在任何使用SQL查询时使用

当然了,无论是使用什么ORM框架,都是为了方便不熟练数据库的同学使用的,我个人还是比较推崇原生 SQL ,也建议每位同学攻克 SQL 的难关

废话不多说,我们来看一下 SQLAlchemy 如何使用:

单表操作:

1.创建

创建基类和第一张数据表

# 创建一张数据表
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

# 创建基类,相当于Django中的 models.Model,被各个数据表类所继承
Base = declarative_base()


# ##################### 单表示例 #########################
# 创建一张数据表
class Users(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)

    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'extra'),
    )


# 创建另一张数据表


class School(Base):
    __tablename__ = "school"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), unique=True)


# 创建数据库链接
engine = create_engine(
    "mysql+pymysql://root:666@localhost:3306/SQLAlchemy?charset=utf8",  # SQLAlchemy 数据库名字
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

# 通过Base.metadata找到所有继承 Base 的数据表class
Base.metadata.create_all(engine)

# SQLAlchemy数据表进行修改后,无法直接进行更新,只能删除表后进行操作,重新进行操作

2.增删改查相关操作:

from s1_create import Users, School

# 1. 创建一个用户添加到数据库
# 创建连接
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:666@localhost:3306/SQLAlchemy?charset=utf8", )

# 创建数据表操作对象 sessionmaker
from sqlalchemy.orm import sessionmaker

DB_session = sessionmaker(engine)
db_session = DB_session()

# 1.增加 操作数据表
# 单行操作
user = Users(name="chaoge")  # 通过 Users数据表类创建一条数据
db_session.add(user)  # 将创建好的数据添加到 数据表操作对象的内存中,此时数据库还并不知道要添加数据
db_session.commit()  # 将数据表操作对象内存中的所有数据及操作提交到数据库中

# 多行操作
db_session.add_all([
    Users(name="chaoge1"),
    Users(name="chaoge2"),
])
db_session.commit()  # 提交

# 2.查询 数据表操作
user_list = db_session.query(Users) # 查询所有数据 ，不写.all(),输出的是sql语句，sql语句也可以循环
user_list = db_session.query(Users).all() # 查询所有数据
print(user_list)

for row in user_list:
    print(row.id,row.name)

user_one = db_session.query(Users).first()  # 查询第一条数据
print(user_one)
print(user_one.id, user_one.name)  # 1 chaoge

user_list = db_session.query(Users).filter(Users.id >=2) # 查询带有条件的数据 表达式 返回sql语句,循环依然可以获取到数据
print(user_list,type(user_list))
for row in user_list:
    print(row.id,row.name)

user_list = db_session.query(Users).filter(Users.id >=2).all() # 查询带有条件的数据 表达式 返回数据列表
print(user_list)
for row in user_list:
    print(row.id,row.name)

# 3.删除数据 数据表操作
db_session.query(Users).filter(Users.id == 1).delete() # 删除带有条件的数据
db_session.commit()

# 4.修改
db_session.query(Users).filter(Users.id == 3).update({"name":"chaoge666"}) # 更新id=3的数据
db_session.commit()

# 关闭连接
db_session.close()

3.高级版查询，更新操作

from s1_create import Users, School

# 1. 创建一个用户添加到数据库
# 创建连接
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:666@localhost:3306/SQLAlchemy?charset=utf8")

# 创建数据表操作对象 sessionmaker
from sqlalchemy.orm import sessionmaker

DB_session = sessionmaker(engine)
db_session = DB_session()

# 1.增加 操作数据表

# 2.查询 数据表操作
# 查询数据表操作
"""
r1 = session.query(Users).all()
r2 = session.query(Users.name.label('xx'), Users.age).all()
r3 = session.query(Users).filter(Users.name == "chaoge2").all()
r4 = session.query(Users).filter_by(name='chaoge2').all()
r5 = session.query(Users).filter_by(name='chaoge2').first()
r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()
r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()
"""
# 筛选查询列
user_list = db_session.query(Users.name).all()
print(user_list)  # [('chaoge1',), ('chaoge666',)] 虽然看到的是元祖,但是依然可以通过对象打点儿调用属性
for row in user_list:
    print(row.name)

# 别名映射  name as nick
"""
select name as nick  from users
"""
user_list = db_session.query(Users.name.label("nick")).all()
print(user_list)  # [('chaoge1',), ('chaoge666',)] 虽然看到的是元祖,但是依然可以通过对象打点儿调用属性
for row in user_list:
    print(row.nick)  # 这里要写别名了

# 筛选条件格式
user_list = db_session.query(Users).filter(Users.name == "chaoge666").all()  # .all()出来的东西是个列表，循环可以取出来
user_list = db_session.query(Users).filter(Users.name == "chaoge666").first()
user_list = db_session.query(Users).filter_by(name="chaoge666").first()  # where name="chaoge666"
print(user_list, type(user_list))
print(user_list.name)
for row in user_list:
    print(row.name)

# 复杂查询
from sqlalchemy.sql import text

user_list = db_session.query(Users).filter(text("id<:id and name=:name")).params(id=4, name="chaoge666").all()
print(user_list)

# 查询语句  # 有问题
from sqlalchemy.sql import text

user_list = db_session.query(Users).filter(text("select *  from users where id<:id and name=:name")).params(id=4,
                                                                                                            name='chaoge666').all()
print(user_list)

# 排序 :
user_list = db_session.query(Users).order_by(Users.id.asc()).all()
user_list = db_session.query(Users).order_by(Users.id.desc()).all()
for row in user_list:
    print(row.name, row.id)

# 其他查询条件
"""
ret = session.query(Users).filter_by(name='chaoge666').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() # between 大于1小于3的
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() 子查询
from sqlalchemy import and_, or_
ret = session.query(Users).filter(
    and_(Users.id > 3, Users.name == 'eric')
).all()
ret = session.query(Users).filter(
    or_(Users.id < 2, Users.name == 'eric')
).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()
# select * from users where id<2 or (name="eric" and id>3) or extra != "" 

 or or (name="eric" and id>3)

or_(id < 2 ,extra != "" ,and_(name="eric" , id>3) )

"""

# 2修改 ：
"""
db_session.query(Users).filter(Users.id > 0).update({"name" : "999"})
db_session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "999"}, synchronize_session=False)
db_session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")
db_session.commit()
"""
db_session.query(Users).filter(Users.name == "chaoge1").update({Users.name: Users.name + "999"},
                                                               synchronize_session=False)
db_session.query(Users).filter(Users.name == "chaoge1999").update({"name": "chaoge"}, synchronize_session="evaluate")
db_session.commit()

# 关闭连接
db_session.close()

4.扩展内容

排序分组选取通配符

 

# 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

一对多的操作 : ForeignKey

1.创建表:

创建多表并建关系

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

# 创建基类,相当于Django中的 models.Model,被各个数据表类所继承
Base = declarative_base()


# ##################### 多表示例 #########################
# 创建一张数据表
class Publisher(Base):
    __tablename__ = 'publisher'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)


#  创建另一张数据表
class Book(Base):
    __tablename__ = 'book'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)

    publisher_id = Column(Integer, ForeignKey("publisher.id"))

    # relationship 不会单独生成字段 ,只用于增加查询操作
    book2publisher = relationship("Publisher", backref="publisher2book")  # backref 反向查找的名字


# 创建数据库链接
engine = create_engine(
    "mysql+pymysql://root:666@localhost:3306/SQLAlchemy?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

# 通过Base.metadata找到所有继承 Base 的数据表class
Base.metadata.create_all(engine)

2.增删改查

 

from s4_one_many import Publisher,Book

# 1. 创建一个用户添加到数据库
# 创建连接
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine( "mysql+pymysql://root:666@localhost:3306/SQLAlchemy?charset=utf8")

# 创建数据表操作对象 sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()

# 1.增加 操作数据表
# 添加出版社:
db_session.add_all([
    Publisher(name="西二旗出版社"),
    Publisher(name="人民出版社")
])
db_session.commit()

# 添加一本书 jinpingmei 出版社是西二旗
publisher = db_session.query(Publisher).filter(Publisher.name == "西二旗出版社").first()
db_session.add(Book(name="jinpingmei",publisher_id=publisher.id))
db_session.commit()

# 2.查询所有数据,并显示出版社名称,连表查询
book_list = db_session.query(Book).all()
for row in book_list:
    print(row.name,row.publisher_id)

book_list = db_session.query(Book.name.label("bname"),Publisher.name.label ("pname")).join(Publisher,Book.publisher_id == Publisher.id,isouter=True).all()
print(book_list)
for row in book_list:
    print(row.pname,row.bname)

# 查询之relationship 快速连表
# 创建表的时候加入 relationship
#普通版添加
obj = Publisher(name="望京出版社")
db_session.add(obj)
db_session.commit()
print(obj.id,obj.name)

obj_book = Book(name="jinpingmei",publisher_id=obj.id)
db_session.add(obj_book)
db_session.commit()
obj = Publisher(name="望京出版社")

# relationship版 添加
bobj = Book(name="jinpingmei",book2pulisher=Publisher(name="望京出版社"))
db_session.add(bobj)
db_session.commit()

# 查询之relationship 快速连表
book_list = db_session.query(Book).all()
for row in book_list:
    print(row.name, row.book2publisher.name,row.book2publisher.id ,row.publisher_id)

# 查询作者的所有书籍
obj = db_session.query(Publisher).filter(Publisher.name=="望京出版社").first()
print(obj.name)
for row in obj.publisher2book:
    print(row.name)

# 反向字段添加
publisher_obj = Publisher(name="人民出版社")
publisher_obj.publisher2book = [Book(name="超哥历险记"),Book(name="超哥洗白记")]
db_session.add(publisher_obj)
db_session.commit()

# 关闭连接
db_session.close()

多对多 : ManyToMany