通过表关系查数据能更简洁的查询到需要的内容
user, user1, article, user_article(为中间表user_article关联article和user)四个表
from datetime import datetime from sqlalchemy import Column,Integer,String,DateTime from connect import Base from sqlalchemy.orm import relationship # 把数据库的表映射成python的类(继承Base类) 用python中的类(继承Base类)来表示mysql的表 class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) username = Column(String(20),nullable=False) password = Column(String(50)) creatime = Column(DateTime,default=datetime.now()) def __repr__(self): # 重写repr方法 return "<User(id=%s,username=%s,password=%s,createtime=%s)>" %( self.id, self.username, self.password, self.creatime ) from sqlalchemy import ForeignKey class User1(Base): __tablename__ = 'new1' id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(10),nullable=False) password = Column(String(50)) user_id = Column(Integer,ForeignKey('user.id')) # relationship必须有外键才能使用 user1 = relationship('User',backref='new1',uselist=False,cascade='all') # 表明一对一的表关系 def __repr__(self): # 重写repr方法 return "<User1(id=%s,name=%s,password=%s)>" %( self.id, self.name, self.password ) from sqlalchemy import Table user_article = Table( # 中间表 'user_article',Base.metadata, Column('user_id',Integer,ForeignKey('user.id'),primary_key=True), Column('article_id',Integer,ForeignKey('article.id'),primary_key=True) # 复合主键 ) class Article(Base): __tablename__ = 'article' id = Column(Integer,primary_key=True,autoincrement=True) content = Column(String(100),nullable=True) create_time = Column(DateTime,default=datetime.now) article_user = relationship('User',backref='article',secondary=user_article) def __repr__(self): return '<Article(id=%s, content=%s, create_time=%s)>' %( self.id, self.content, self.create_time ) if __name__ == '__main__': Base.metadata.create_all()
一对一表关系 user 和 user1
# 表关系 一对一 # 在类创建表(必须有外键) 类的后面 写上relationship 需导入 from sqlalchemy.orm import relationship # User类 new1:给User的每条数据对象增加的新属性(外键表的数据) # user1 = relationship('User', backref='new1', uselist=False, cascade='all') 'all' 所有操作关联到关联对象去 # uselist=False:限制表为一对一关系 cascade:删除数据的限制 row = session.query(User).all() print(row, dir(row[0])) # row[0] tuple print(row[0].new1) # # 反向取值.new1 从User取User1的数据 row =session.query(User1).first() print(row.user1) # 正向取值 .user1 从User1取User的数据 >>> [<User(id=1,username=budong,password=qwe123,createtime=2018-03-07 16:57:09)>, <User(id=2,username=tj,password=123qwe1,createtime=2018-03-07 16:54:09)>,
<User(id=3,username=tj1,password=123qwe2,createtime=2018-03-07 16:58:09)>, <User(id=4,username=tj2,password=123qwe3,createtime=2018-03-07 16:07:09)>,
<User(id=5,username=budong,password=qweq,createtime=2018-03-08 05:10:38)>]
['__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__le__',
'__lt__', '__mapper__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__',
'__table__', '__tablename__', '__weakref__', '_decl_class_registry', '_sa_class_manager', '_sa_instance_state', 'creatime', 'id', 'metadata', 'new1',
'password', 'username'] >>> [<User1(id=1,name=ha,password=1)>] >>> <User(id=1,username=budong,password=qwe123,createtime=2018-03-07 16:57:09)>
一对多:uselist=True, 时为一对多关系,因为relationship默认为一对多关系 所以不传入uselist也为一对多关系
# user1 = relationship('User', backref='new1', uselist=True, cascade='all') 为 '多' 的需要外键关联为 ‘一’ 的,唯一的表用主键限制
多对多 user 和 user_article中间表 及article
# 多对多 relationship 需要通过建立两表关联id的中间关联表--通过Table建立(复合主键,外键) # 不传入uselist or uselist=True 则表示一对多的表关系 # article_user = relationship('User', backref='article', secondary=user_article) row = session.query(User).first() print(row, dir(row)) print(row.new1) print(row.article) # 反向取值 通过relationship添加的article属性取到对应的数据 row = session.query(Article).first() print(row) print(row.article_user) # 正向取值 通过article_user取article对应的user数据 >>> <User(id=1,username=budong,password=qwe123,createtime=2018-03-07 16:57:09)> ['__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__',
'__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__le__', '__lt__', '__mapper__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__',
'__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__table__', '__tablename__', '__weakref__', '_decl_class_registry', '_sa_class_manager',
'_sa_instance_state', 'article', 'creatime', 'id', 'metadata', 'new1', 'password', 'username']
>>> [<User1(id=1,name=ha,password=1)>] >>> [<Article(id=2, content=content2, create_time=2018-03-02 02:51:54)>] >>> <Article(id=1, content=content1, create_time=2018-03-10 01:55:39)> >>> [<User(id=3,username=tj1,password=123qwe2,createtime=2018-03-07 16:58:09)>]