from turtle import title
from util_db import Base, Session
from sqlalchemy import Column, Integer, String, Text, ForeignKey
# 关联表(将主表的某一个数据注入到子表中)
from sqlalchemy.orm import relationship
# 创建表t_user
class User(Base):
__tablename__ = 't_user'
id = Column(Integer, primary_key = True, autoincrement = True)
uname = Column(String(50), nullable = False, name = 'name')
# 返回时,直接给出具体内容
def __repr__(self):
return f"<User: id:{self.id}, uname:{self.uname}>"
#创建表t_news
# 外键放在多的一方
class News(Base):
__tablename__ = 't_news'
id = Column(Integer, primary_key = True, autoincrement = True)
title = Column(String(50), nullable = False)
content = Column(Text, nullable = False)
# 外键(关联哪个表的哪个属性)
uid = Column(Integer, ForeignKey('t_user.id'))
# 将主表中的数据,注入到子表中,这样才能不用手写关联信息
# 第一个参数时用于子表查主表内容(需要给出子表的类名),第二个子表查询主表(给出名就好)
user = relationship('User',backref = 'news')
# 返回时,直接给出具体内容
def __repr__(self):
return f"<News: id:{self.id}, title:{self.title}, content:{self.content}, uid:{self.uid}>"
# 添加数据
class create_data():
user = User(uname = 'sxt')
news1 = News(title = 'python',content = 'flask', uid = 1)
news2 = News(title = 'MySQL', content = 'SQL', uid = 1)
# 先添加user的,再添加news。否则无法关联,容易报错
with Session() as ses:
ses.add(user)
ses.commit()
with Session() as ses:
ses.add(news1)
ses.add(news2)
ses.commit()
# 测试1:常规方法获取数据
def query_data():
with Session() as ses:
# 获取News表中的第一条信息
# new1 = ses.query(News).first()
# print(new1)
# 获取News表中的第一条数据
# news1 = ses.query(News).first()
# 传递news1数据中的uid
# uid = news1.uid
# 获取User表中的第一条数据
user = ses.query(User).first()
print(user)
# 因为建立了外键,尝试使用传递子表数据,去查询主表信息(没有relationship)
# 子表查找主表中的内容
def query_data2():
with Session() as ses:
# 获取子表数据
news1 = ses.query(News).first()
# user信息存在于主表
print(news1.user)
#AttributeError: 'News' object has no attribute 'user'
# 获取失败
# 主表中查询子表的内容
def query_data3():
with Session() as ses:
user1 = ses.query(User).first()
print(user1.news)
if __name__ == "__main__":
# Base.metadata.create_all()
# create_data()
# query_data()
# query_data2()
query_data3()