sqlalchemy外键关联

# -*- 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)