sqlalchemy foreign key Association

1, Create two tables and associate foreign keys

Import ForenginKey module

# -*- 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", )  # Connect to the database, echo=True =>Print out all the information

Base = declarative_base()  # generate orm Base class

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)

# Create table
Base.metadata.create_all(engine)
Create table
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)
Table structure

 

2, Insert data

# Establish session Conversation
Session_class = sessionmaker(bind=engine)
# generate session Example
session = Session_class()

# Create data
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()
insert data
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)
Data content

 

3, relationship

3.1 generated object calls

Foreign key association is a real foreign key in mysql database, while relationship is an association between classes and a call between two classes.

Import relationship module

Modify the code of a Score class and add a 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")
    # This relationship allows the use of studnet in the score table to display everything in the table studnet
    # Use my "score" in table student to display all contents in the score table
    # This relationship is orm's own thing, not related to mysql. It is a call between classes
    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)

Query usage:

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()


# output
[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]


# You can see that their results are the same
# But the first stu obj calls my score through studnet, which implements the content of calling the score table

  

3.2 call between classes

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")
    # This relationship allows the use of studnet in the score table to display everything in the table studnet
    # Use my "score" in table student to display all contents in the score table
    # This relationship is orm's own thing, not related to mysql. It is a call between classes
    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)


# Call self.student.register_date directly in the Score class.

  

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()


#output
[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]

Tags: Python Session MySQL Database encoding

Posted on Sat, 02 May 2020 16:05:45 -0700 by Kisses