SQLAlchemy常用功能的简单总结(来自于官方文档)

最近在学SQLAlchemy，看了官方文档，记录下最常用的功能，下面的内容都是关于懒加载(lazy loading)的，通俗来讲就是，通过SQLAlchemy改变数据库中的数据，不会立马将数据的改写（包含，创建，删除和更新，不包含查询)提交到数据库进行SQL执行，而是将改变记录下来，当遇到session.commit()时，会将改变一次性的提交到数据库中,在此之前可以使用回滚session.rollback()方法，清空当前Session中的记录的数据库改变。关于急加载(Eager Loading),相关知识，请查阅官方文档，比较短。

一，创建模型以及获取句柄

Create_Add_Date.py

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

engine = create_engine('sqlite:test.db', echo=True)
# engine = create_engine('sqlite:///:memory:', echo=True)

# 创建基类
Base = declarative_base()


# 创建映射
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String)
    gender = Column(String)
    age = Column(Integer)
    # 设置级联关系
    addresses = relationship("Address", bake_queries="user", cascade="all, delete, delete-orphan")
    def __repr__(self):
        return str(self.id)+"|"+self.name + "|" + self.gender + "|" + str(self.age)


class Address(Base):
    __tablename__ = 'addresses'

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    # 设置外键
    user_id = Column(Integer, ForeignKey("users.id"))
    # 连接到User，引用名为addresses
    user = relationship("User", back_populates="addresses")

    def __repr__(self):
        return self.email_address + '|' + str(self.user_id)

# 指定外键，创建数据库
# User.addresses = relationship("Address", order_by=Address.id, back_populates="user")
# Base.metadata.create_all(engine)


# 拿到句柄
Session = sessionmaker()
Session.configure(bind=engine)

if __name__ == '__main__':
    User.addresses = relationship("Address", order_by=Address.id, back_populates="user")

    # 生成实例
    users = [
        User(name="张三", gender="女", age=10, addresses=[
            Address(email_address="admin123@1.com"),
            Address(email_address="12@1.com")
        ]),
        User(name="王五", gender="男",age=12, addresses=[
            Address(email_address="1@1.com"),
            Address(email_address="ww@1.com")
        ]),
        User(name="张柳", gender="女", age=58, addresses=[
            Address(email_address="zl@1.com"),
            Address(email_address="zl.2@1.163")
        ]),
        User(name="德玛西亚", gender="男", age=66,addresses=[
            Address(email_address="admin123@1.163"),
            Address(email_address="12@1.162")
        ]),
        User(name="码流", gender="女", age=66,addresses=[
            Address(email_address="admin123@1.com"),
            Address(email_address="12@1.126")
        ]),
        User(name="王麻子", gender="男", age=66,addresses=[
            Address(email_address="admin123@1.com"),
            Address(email_address="11@1.com")
        ]),
        User(name="张三", gender="女", age=38,addresses=[
            Address(email_address="12345@1.com"),
            Address(email_address="12@1.com")
        ]),
        User(name="德玛西亚", gender="女", age=66,addresses=[
            Address(email_address="ad12313min123@1.com"),
            Address(email_address="12@1.com")
        ]),
    ]
    # user = User(name="张三", gender="女", age=10)
#     user.addresses = [
#         Address(email_address="1@1")
#     ]
    #
    # 创建一个会话
    session = Session()
    #
    # # 添加数据
    # session.add(user)
    session.add_all(users)
# user_resp = session.query(User).filter_by(name="张三").first()
# user_resp.address = "德国"
#
    # 将数据库的更改进行提交
    session.commit()
    # # print(user_resp)

二，按条件查询，以及简单的过滤数据

from SQLAlchemy.Create_Add_Date import Session, User
from sqlalchemy import and_, or_, func


session = Session()

# for instance in session.query(User).order_by(User.id):
#     print(instance.name, instance.address)


# for instance in session.query(User.name, User.gender):
#     print(instance.name, instance.gender)

# for row in session.query(User, User.name).all():
#     print(row.User, row.name)

# 自定义列名
# for row in session.query(User.name.label("name_col")).all():
#     print(row.name_col)

# 切片排序
# for u in session.query(User).order_by(User.id)[1:2]:
#     print(u)

# 过滤
# for u in session.query(User).filter_by(name="德玛西亚"):
#     print(u)

# 条件过滤---------------------------------------
# for u in session.query(User).filter(User.name=='张三'):
#     print(u)

# 不区分大小写
# for u in session.query(User).filter(User.name.comparator().ilike("%三")):
#     print(u)
# # 可能区分大小写
# for u in session.query(User).filter(User.name.like("%三")):
#     print(u)

# in or not in 包含
# for u in session.query(User).filter(User.name.in_(['张三', '李四'])):
#     print(u)
#
# # 多重过滤（and）-----------------------------------------------
# for u in session.query(User).filter(and_(User.name == "张三", User.gender=="女")):
#     print(u)
#
# for u in session.query(User).filter(User.name == "张三", User.gender=="女"):
#     print(u)
#
# # 也可连写多个filter来进行多重过滤
#
# # 多重过滤 or
# for u in session.query(User).filter(or_(User.name == "张三", User.gender=="男")):
#     print(u)

# 计数
# print(session.query(User).filter(or_(User.name == "张三", User.gender=="男")).count())
# print(session.query(func.count(User.id)).scalar())
# 分组并统计计数
print(session.query(func.count(User.id), User.name).group_by(User.name).all())

三，一对多Join查询

from SQLAlchemy.Create_Add_Date import User, Address, Session
from sqlalchemy.orm import relationship

session = Session()
User.addresses = relationship("Address", order_by=Address.id, back_populates="user")

# 普通连接查询
# for u, a in session.query(User, Address)
#         .filter(User.id == Address.user_id)
#         .filter(Address.email_address=="12@1.com"):
#     print(u, a)

# for u in session.query(User).join(Address, User.id==Address.user_id).all():
#     print(u)
for u , a in session.query(User, Address).join(Address, User.addresses).all():
    print(u, a)


# # 特殊的查询
# for u , a in session.query(User, Address).join(User.addresses).all():
#     print(u, a)

级联删除

from SQLAlchemy.Create_Add_Date import Session, User
from sqlalchemy import and_, or_, func


session = Session()
user = session.query(User).get(1)

# 删除user
session.delete(user)

session.commit()