"Python"--pymsql foreign key

Pymsql FOREIGN Key

This film is a supplement to the pymsql operation MySQL, mainly demonstrates the use of PYMYSQL foreign key operation

1. Foreign Key Association

1.

2. Pymysql Foreign Key Association instance

From SQLAlchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy Import Column, Integer, String, DATE, foreignkeyfrom sqlalchemy.orm import relationship, Sessionmakerconnect = Create_engine (" Mysql+pymysql://root:[email protected]:3306/test ", encoding=" Utf-8 ", EC Ho=false) # Connect to the database, Echo=true = print out all the information. Base = Declarative_base () # Generate ORM base class Student (Base): # Student Table __tablenam e__ = "Student" id = column (Integer, primary_key=true) name = Column (String (+), nullable=false) Register_date = Column (DATE, Nullable=false) def __repr__ (self): return "<{0} name:{1}>". Format (self.id, Self.name) class S Tudentrecord (Base): # Student learning record Table __tablename__ = "Study_record" id = Column (Integer, primary_key=true) Day = Colum N (integer, nullable=false) status = Column (String (+), nullable=false) stu_id = Column (Integer, ForeignKey ("Student"). ID ") # association foreign Key #关联student表, and then I need toStudy_record through student This field, you can go to the student class inside All the fields, # in turn using backref= "My_study_record" in the My_study_record, In the Student table, my_study_record This field to check all fields inside the Study_record class, Student = Relationship ("Student", backref= "My_study_record" ) def __repr__ (self): return ' <name:{0} day:{1} stu_id:{2}> '. Format (self. Student.name, Self.day, self.stu_id) Base.metadata.create_all (Connect) # Create student table and Student record table, comment session_class after first creation = Sessionmaker (Connect) # Creates a session with the database sessions class, which is returned to the session is a class, not an instance session = Session_class () # Generate session Instance # Insert data in two tables STU1 = Student (name= "test", register_date= "2017-05-30") Stu2 = Student (name= "Test2", register_date= " 2017-06-30 ") Record1 = Studentrecord (Day=1, status=" y ", stu_id=1) Record2 = Studentrecord (day=2, status=" y ", stu_id=1) Session.add_all ([Record1, Record2]) Session.commit ()

3, according to the relevant foreign key, check the corresponding table information

Stu_obj = Session.query (Student). Filter_by (name= "test"). First () print (Stu_obj.my_study_record)  # Check the student's corresponding study record in the student's Table record_obj = Session.query (Studentrecord). First () print (record_obj. student.name)  # Check the student record for the student's name # output [<name:test day:1 stu_id:1>, <name:test day:2 stu_id:1>]test

　　

"Python"--pymsql foreign key