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()
