# 56 ORM層外鍵和一對多關系 mysql級別的外鍵，還不夠ORM，必須拿到一個表中的外鍵，然后再去通過這個外鍵再去另外一張表查找 ```text # 表之間的關系 article = session.query(Article).first() uid = article.uid print(article) # get方法只匹配主鍵字段 user = session.query(User).get(uid) print(user) print(uid) ``` 可這樣太麻煩了。SQLAlchemy提供了一個"relationship"，這個類可以定義屬性，以后再訪問相關聯的表的時候就可以直接通過屬性訪問的方式就可以訪問得到了。 ```text class User(Base): .... articles = relationship('Article') def __repr__(self): return "<User(username:%s)>" % self.username class Article(Base): .... # 映射到User模型 # backref:反向引用,為user添加一個屬性 author = relationship("User") def __repr__(self): return "<Article(title:%s),content:%s>" % (self.title,self.content) article = session.query(Article).first() print(article.author.username) user = session.query(User).first() print(user.articles) ``` 另外，可以通過"backref"來指定反向訪問的屬性名稱。articles是有多個，它們之間的關系是一對多 ```text class Article(Base): ...... # 映射到User模型 # backref:反向引用,為user添加一個屬性 author = relationship("User",backref="articles") ``` ```text from sqlalchemy import create_engine, Column, Integer, Text, String, DateTime, String, Float, func,and_,or_,ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker,relationship,backref import random HOSTNAME = '127.0.0.1' PORT = "3306" USERNAME = "root" PASSWORD = "123456" DATABASE = "xt_flask" DB_URI = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE) from datetime import datetime engine = create_engine(DB_URI) Base = declarative_base(engine) session = sessionmaker(engine)() # 父表/從表 # user/article class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) username = Column(String(50)) # articles = relationship('Article') def __repr__(self): return "<User(username:%s)>" % self.username class Article(Base): __tablename__ = "article" id = Column(Integer,primary_key=True,autoincrement=True) title = Column(String(50),nullable=False) content = Column(Text,nullable=False) uid = Column(Integer, ForeignKey("user.id", ondelete="SET NULL")) # 映射到User模型 # backref:反向引用,為user添加一個屬性 author = relationship("User",backref="articles") def __repr__(self): return "<Article(title:%s),content:%s>" % (self.title,self.content) # Base.metadata.drop_all() # Base.metadata.create_all() # # user = User(username = 'angle') # session.add(user) # session.commit() # # article = Article(content='abc',title='123',uid=1) # session.add(article) # session.commit() # # 表之間的關系 # article = session.query(Article).first() # uid = article.uid # print(article) # # get方法只匹配主鍵字段 # user = session.query(User).get(uid) # print(user) # print(uid) article = session.query(Article).first() print(article.author.username) user = session.query(User).first() print(user.articles) ```