sqlalchemy_sqlalchemy selectinload aliased-CSDN博客

本文链接：https://blog.csdn.net/qq_44647926/article/details/122541825
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker

# 连接
engine = create_engine('sqlite:///1.db', echo=False)

# 基类
Base = declarative_base()

# class User(Base):
#     __tablename__ = 'users'
#
#     id = Column(Integer, primary_key=True)
#     name = Column(String)
#     fullname = Column(String)
#     nickname = Column(String)
#
#     def __repr__(self):
#         return "<User(name='%s', fullname='%s', nickname='%s')>" % (self.name, self.fullname, self.nickname)

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    addresses = relationship("Address", back_populates='user', cascade='all, delete, delete-orphan')

    def __repr__(self):
        return "<User(name='%s', fullname='%s', nickname='%s')>" % (self.name, self.fullname, self.nickname)



Session = sessionmaker(bind=engine)
session = Session()

Base.metadata.create_all(engine)


# 添加一条数据
# session.add(User(name='ed', fullname='Ed Jones', nickname='eddie'))
# 添加多条数据
# session.add_all([
#     User(name='wendy', fullname='Wendy Williams', nickname='windy'),
#     User(name='mary', fullname='Mary Contrary', nickname='mary'),
#     User(name='fred', fullname='Fred Flintstone', nickname='freddy'),
# ])
# session.commit()

# ################################# 查询 #########################################
# 迭代获取对象
# for instance in session.query(User).order_by(User.id):
#     print(instance)

# 迭代获取指定列
# for name, fullname in session.query(User.id, User.fullname).all():
#     print(name, fullname)

# 迭代获取的列绑定到row具名元组实例
# for row in session.query(User, User.name).all():
#     print(row.User, row.name)

# 迭代获取的列绑定到row实例（列名映射到自定义属性名）
# for row in session.query(User, User.name.label('label_name')).all():
#     print(row.label_name)

# 实体别名
# from sqlalchemy.orm import aliased
# user_alias = aliased(User, name='user_alias')
# for row in session.query(user_alias, user_alias.name).all():
#     print(row.user_alias, row.name)

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

# 条件过滤 filter 和 filter_by
# for name, in session.query(User.name).filter_by(fullname='Ed Jones'):
#     print(name)
# for name, in session.query(User.name).filter(User.fullname=='Ed Jones'):
#     print(name)

# Query对象可多次filter
# for name, in session.query(User.name).filter_by(name='ed').filter_by(fullname='Ed Jones'):
#     print(name)
#
# for name, in session.query(User.name).filter(User.name=='ed').filter(User.fullname=='Ed Jones'):
#     print(name)

# 1.常用筛选器运算符
# from sqlalchemy import and_, or_
# __eq__
# print(session.query(User).filter(User.name.__eq__('ed')).all())

# __ne__
# print(session.query(User).filter(User.name.__ne__('ed')).all())

# like
# print(session.query(User).filter(User.name.like('%eD%')).all())

# ilike
# print(session.query(User).filter(User.name.ilike('%eD%')).all())

# in_
# print(session.query(User).filter(User.name.in_(['ed', 'wendy', 'jack'])).all())

# not_in
# print(session.query(User).filter(User.name.not_in(['ed', 'wendy', 'jack'])).all())

# is
# print(session.query(User).filter(User.name.is_(None)).all())

# is_not
# print(session.query(User).filter(User.name.is_not(None)).all())

# AND
# print(session.query(User).filter(and_(User.name=='ed',User.fullname=='Ed Jones')).all())
# print(session.query(User).filter(User.name=='ed',User.fullname=='Ed Jones').all())
# print(session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones').all())

# OR
# print(session.query(User).filter(or_(User.name=='ed',User.name=='wendy')).all())

# match
# print(session.query(User).filter(User.name.match('wendy')).all())


# 2.返回列表和标量
# query = session.query(User).filter(User.name.like('%fred')).order_by(User.id)
# print(query.all())
# print(query.first())
# print(query.one()) # 获取一行记录，没有记录会报错，多行记录会报错。
# print(query.one_or_none())
# print(query.scalar())

# 3.使用文本SQL
# from sqlalchemy import text

# 3.1 过滤排序使用文本SQL
# for user in session.query(User).filter(text('id<24')).order_by(text('id')).all():
#     print(user.name)

# 3.2 文本SQL绑定参数
# print(session.query(User).filter(text('id<:value and name=:name')).params(value=224,name='fred').order_by(User.id).one())

# 3.3 文本SQL语句
# print(session.query(User).from_statement(text('select * from users where name = :name')).params(name='ed').all())

# 3.4 文本SQL列映射
# stmt = text('select name, id, fullname, nickname from users where name = :name')
# stmt = stmt.columns(User.name, User.id, User.fullname, User.nickname)
# print(session.query(User).from_statement(stmt).params(name='ed').all())

# 3.5 文本SQL返回指定列
# stmt = text('select name, id from users where name = :name')
# stmt = stmt.columns(User.name, User.id)
# print(session.query(User.id, User.name).from_statement(stmt).params(name='ed').all())


# 4.计数
# 4.1 count
# print(session.query(User).filter(User.name.like('%ed')).count())

# 4.2 func.count
# from sqlalchemy import func
# print(session.query(func.count(User.id), User.name).group_by(User.name).all())
# print(session.query(func.count('*')).select_from(User).scalar())
# print(session.query(func.count(User.id)).scalar())

# ################################# 建立关系 #########################################
from sqlalchemy.orm import relationship
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 = relationship('User', back_populates='addresses')

    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

Base.metadata.create_all(engine)


# 创建数据
# jack = User(name='jack', fullname='Jack Bean', nickname='gjffdd')
#
# print(jack.addresses)
# jack.addresses = [
#     Address(email_address='jack@google.com'),
#     Address(email_address='j25@yahoo.com'),
# ]
# session.add(jack)
# session.commit()

# ################################# 使用相关对象 #########################################
# user = session.query(User).filter_by(name='jack').one()
# print(user.addresses)
# print(user.addresses[0].user)

# ################################# 使用联接查询 #########################################
# for u,a in session.query(User, Address).filter(User.id == Address.user_id).filter(Address.email_address=='jack@google.com').all():
#     print(u, a)

# print(session.query(User).join(Address).filter(Address.email_address.like('jack@google.com')).all())

# 内连接 join
# print(session.query(User).join(Address,Address.user_id == User.id).all())
# print(session.query(User).join(User.addresses).all())
# print(session.query(User).join(Address, User.addresses).all())
# print(session.query(User).join(User.addresses.and_(Address.email_address != 'foo')))

# 外连接 outerjoin
# print(session.query(User).outerjoin(User.addresses).all())

# 如果省略了ON子句，或者ON子句是纯SQL表达式。要控制联接列表中的第一个实体，请使用 Query.select_from() 方法
# print(session.query(User, Address).select_from(Address).join(User).all())

# 1.使用别名
# from sqlalchemy.orm import aliased
#
# adalias1 = aliased(Address)
# adalias2 = aliased(Address)
#
# for username, email1, email2 in session.query(User.name, adalias1.email_address, adalias2.email_address)\
#         .join(User.addresses.of_type(adalias1))\
#         .join(User.addresses.of_type(adalias2))\
#         .filter(adalias1.email_address=='jack@google.com')\
#         .filter(adalias2.email_address=='j25@yahoo.com'):
#     print(username, email1, email2)

# query.join(adalias1, User.addresses) equivalent to query.join(User.addresses.of_type(adalias1))
# for username, email1, email2 in session.query(User.name, adalias1.email_address, adalias2.email_address)\
#         .join(adalias1, User.addresses)\
#         .join(adalias2, User.addresses)\
#         .filter(adalias1.email_address=='jack@google.com')\
#         .filter(adalias2.email_address=='j25@yahoo.com'):
#     print(username, email1, email2)

# 2.使用子查询
# from sqlalchemy.sql import func
# stmt = session.query(Address.user_id, func.count("*").label('address_count')).group_by(Address.user_id).subquery()
#
# for u,count in session.query(User, stmt.c.address_count)\
#     .outerjoin(stmt, User.id == stmt.c.user_id)\
#     .order_by(User.id):
#     print(u, count)

# 3.从子查询中选择实体
# from sqlalchemy.orm import aliased
# stmt = session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery()
# addr_alias = aliased(Address ,stmt)
#
# for user, address in session.query(User, addr_alias).join(addr_alias, User.addresses):
#     print(user,address)

# 4.使用存在性
# from sqlalchemy.sql import exists

# exists
# stmt = exists().where(Address.user_id==User.id)
# for name, in session.query(User.name).filter(stmt):
#     print(name)

# any
# for name, in session.query(User.name).filter(User.addresses.any()):
#     print(name)

# any中添加限制条件
# for name, in session.query(User.name).filter(User.addresses.any(Address.email_address.like('%google%'))):
#     print(name)

# has
# print(session.query(Address).filter(~Address.user.has(User.name=='jack')).all())

# 5.公共关系运算符

# eq
# some_user = session.query(User).filter(User.name=='jack').one()
# print(session.query(Address).filter(Address.user == some_user).all())
# print(session.query(Address).filter(Address.user == None).all())

# ne
# some_user = session.query(User).filter(User.name=='jack').one()
# print(session.query(Address).filter(Address.user != some_user).all())

# contains
# some_address = session.query(Address).filter(Address.email_address=='jack@google.com').one()
# print(session.query(User).filter(User.addresses.contains(some_address)).all())

# any
# print(session.query(User).filter(User.addresses.any(Address.email_address=='bar')).all())
# print(session.query(User).filter(User.addresses.any(email_address='bar')).all())

# has
# print(session.query(Address).filter(Address.user.has(name='jack')).all())

# with_parent
# some_user = session.query(User).filter(User.name == 'jack').one()
# print(session.query(Address).with_parent(some_user, 'addresses').all())

# ################################# 急加载 #########################################
# 1.选择负荷
# from sqlalchemy.orm import selectinload
# jack = session.query(User).options(selectinload(User.addresses)).filter(User.name=='jack').one()
# print(jack)
# print(jack.addresses)

# 2.连接荷载
# from sqlalchemy.orm import joinedload
# jack = session.query(User).options(joinedload(User.addresses)).filter(User.name=='jack').one()
# print(jack)
# print(jack.addresses)

# 3.显示连接+热切加载
# from sqlalchemy.orm import contains_eager
# jacks_addresses = session.query(Address).join(Address.user).options(contains_eager(Address.user)).filter(User.name=='jack').all()
# print(jacks_addresses)
# print(jacks_addresses[0].user)

# ################################# 删除 #########################################
# jack = session.query(User).filter(User.name=='jack').one()
# session.delete(jack)
# session.commit()
# print(session.query(User).filter_by(name='jack').count())

# print(session.close())

# jack = session.query(User).get(5)
# print(jack.addresses)
# del jack.addresses[1]
# print(session.query(Address).filter(Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])).count())
#
# session.delete(jack)
# print(session.query(Address).filter(Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])).count())
# session.commit()

# ################################# 建立多对多关系 #########################################
from sqlalchemy import Table, Text

post_keywords = Table(
    'post_keywords',
    Base.metadata,
    Column('post_id', ForeignKey('posts.id'), primary_key=True),
    Column('keywords_id', ForeignKey('keywords.id'), primary_key=True)
)

class BlogPost(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    headline = Column(String(255), nullable=False)
    body = Column(Text)

    keywords = relationship('KeyWord',secondary=post_keywords,back_populates='posts')

    def __init__(self, headline, body, author):
        self.headline = headline
        self.body = body
        self.author = author

    def __str__(self):
        return "BlogPost(%s, %s, %s)"%(self.author, self.headline, self.body)



class KeyWord(Base):

    __tablename__ = 'keywords'
    id = Column(Integer, primary_key=True)
    keyword = Column(String(50), nullable=False, unique=True)
    posts = relationship('BlogPost', secondary=post_keywords, back_populates='keywords')

    def __init__(self, keyword):
        self.keyword = keyword


BlogPost.author = relationship(User, back_populates='posts')

User.posts = relationship(BlogPost, back_populates='author', lazy='dynamic')

Base.metadata.create_all(engine)

wendy = session.query(User).filter(User.name=='wendy').one()

post = BlogPost("Wendy's Blog Post", "This is a test",wendy)
session.add(post)

post.keywords.append(KeyWord('wendy'))
post.keywords.append(KeyWord('firstpost'))

print(session.query(BlogPost).filter(BlogPost.keywords.any(KeyWord.keyword=='firstpost')).all())
print(session.query(BlogPost).filter(BlogPost.author==wendy).filter(BlogPost.keywords.any(KeyWord.keyword=='firstpost')).all())

print(session.query(BlogPost).filter(BlogPost.keywords.any(keyword='firstpost')).all())
print(wendy.posts.filter(BlogPost.keywords.any(keyword='firstpost')).all())