sqlalchemy外键关联
一、创建两张表，并关联外键
导入ForenginKey模块
# -*- coding: UTF-8 -*- from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, DATE, Enum from sqlalchemy import ForeignKey from sqlalchemy.orm import sessionmaker engine = create_engine("mysql+pymysql://bigberg:111111@172.16.200.49:3306/study", encoding="utf-8", ) # 连接数据库，echo=True =>把所有的信息都打印出来 Base = declarative_base() # 生成orm基类 class Student(Base): __tablename__ = "student" id = Column(Integer, primary_key=True) name = Column(String(32), nullable=False) register_date = Column(DATE, nullable=False) gender = Column(Enum('F', 'M'), nullable=False) def __repr__(self): return "id:%s name:%s register_date:%s gender:%s" \ %(self.id,self.name, self.register_date, self.gender) class Score(Base): __tablename__ = "score" id = Column(Integer, primary_key=True) day = Column(Integer, nullable=False) name = Column(String(32), nullable=False) score = Column(Integer, nullable=False) stu_id = Column(Integer, ForeignKey("student.id")) def __repr__(self): return "id:%s day:%s name:%s score:%s stu_id:%s" \ %(self.id, self.day, self.name, self.score, self.stu_id) # 创建表 Base.metadata.create_all(engine)
mysql> desc student; +---------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | NO | | NULL | | | register_date | date | NO | | NULL | | | gender | enum('F','M') | NO | | NULL | | +---------------+---------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> desc score; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | day | int(11) | NO | | NULL | | | name | varchar(32) | NO | | NULL | | | score | int(11) | NO | | NULL | | | stu_id | int(11) | YES | MUL | NULL | | +--------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
二、插入数据
# 创建session会话 Session_class = sessionmaker(bind=engine) # 生成session实例 session = Session_class() # 创建数据 s1 = Student(name="zhangsan", register_date="2018-01-01", gender='M') s2 = Student(name="lisi", register_date="2018-01-02", gender='F') s3 = Student(name="wangwu", register_date="2018-02-04", gender='F') s4 = Student(name="zhaoliu", register_date="2018-03-05", gender='M') score1 = Score(day=1, name='zhangsan', score=90, stu_id=1) score2 = Score(day=2, name='zhangsan', score=70, stu_id=1) score3 = Score(day=3, name='zhangsan', score=84, stu_id=1) score4 = Score(day=1, name='lisi', score=90, stu_id=2) score5 = Score(day=1, name='wangwu', score=87, stu_id=3) session.add_all([s1,s2,s3,s4,score1,score2,score3,score4,score5]) session.commit()
mysql> select * from student; +----+----------+---------------+--------+ | id | name | register_date | gender | +----+----------+---------------+--------+ | 1 | zhangsan | 2018-01-01 | M | | 2 | lisi | 2018-01-02 | F | | 3 | wangwu | 2018-02-04 | F | | 4 | zhaoliu | 2018-03-05 | M | +----+----------+---------------+--------+ 4 rows in set (0.00 sec) mysql> select * from score; +----+-----+----------+-------+--------+ | id | day | name | score | stu_id | +----+-----+----------+-------+--------+ | 1 | 1 | zhangsan | 90 | 1 | | 2 | 2 | zhangsan | 70 | 1 | | 3 | 3 | zhangsan | 84 | 1 | | 4 | 1 | lisi | 90 | 2 | | 5 | 1 | wangwu | 87 | 3 | +----+-----+----------+-------+--------+ 5 rows in set (0.00 sec)
三、relationship
3.1 生成的对象调用
外键关联是mysql数据库中确确实实存在的外键，而relationship是类和类之间的关联，是两个类之间实现相互之间的调用。
导入relationship模块
修改一个Score类的代码，增加一个relationship
class Score(Base):
__tablename__ = "score"
id = Column(Integer, primary_key=True)
day = Column(Integer, nullable=False)
name = Column(String(32), nullable=False)
score = Column(Integer, nullable=False)
stu_id = Column(Integer, ForeignKey("student.id"))
student = relationship("Student", backref="my_score")
# 这个关系允许在score表中使用studnet 来显示 表studnet中所有内容
# 在表student中使用my_score来显示 score表中所有内容
# 这个relationship 是orm自己的东西，和mysql无关，是类之间的调用
def __repr__(self):
return "id:%s day:%s name:%s score:%s stu_id:%s" \
%(self.id, self.day, self.name, self.score, self.stu_id)
查询使用：
stu_obj = session.query(Student).filter(Student.name=='zhangsan').first() print(stu_obj.my_score) stu_obj2 = session.query(Score).filter(Score.name=='zhangsan').all() print(stu_obj2) session.commit() # 输出 [id:1 day:1 name:zhangsan score:90 stu_id:1, id:2 day:2 name:zhangsan score:70 stu_id:1, id:3 day:3 name:zhangsan score:84 stu_id:1] [id:1 day:1 name:zhangsan score:90 stu_id:1, id:2 day:2 name:zhangsan score:70 stu_id:1, id:3 day:3 name:zhangsan score:84 stu_id:1] # 可以看到他们的结果是一样的 # 但是第一个stu_obj通过studnet 调用 my_score实现了调用 score表中的内容
3.2 类中之间调用
class Score(Base):
__tablename__ = "score"
id = Column(Integer, primary_key=True)
day = Column(Integer, nullable=False)
name = Column(String(32), nullable=False)
score = Column(Integer, nullable=False)
stu_id = Column(Integer, ForeignKey("student.id"))
student = relationship("Student", backref="my_score")
# 这个关系允许在score表中使用studnet 来显示 表studnet中所有内容
# 在表student中使用my_score来显示 score表中所有内容
# 这个relationship 是orm自己的东西，和mysql无关，是类之间的调用
def __repr__(self):
return "id:%s day:%s register_date:%s score:%s stu_id:%s" \
%(self.id, self.day, self.student.register_date, self.score, self.stu_id)
# 直接在Score类中调用 self.student.register_date
stu_obj = session.query(Student).filter(Student.name=='zhangsan').first() print(stu_obj.my_score) stu_obj2 = session.query(Score).filter(Score.name=='zhangsan').all() print(stu_obj2) session.commit() #输出 [id:1 day:1 register_date:2018-01-01 score:90 stu_id:1, id:2 day:2 register_date:2018-01-01 score:70 stu_id:1, id:3 day:3 register_date:2018-01-01 score:84 stu_id:1] [id:1 day:1 register_date:2018-01-01 score:90 stu_id:1, id:2 day:2 register_date:2018-01-01 score:70 stu_id:1, id:3 day:3 register_date:2018-01-01 score:84 stu_id:1]
