当前位置: 首页 > 工具软件 > Drizzle ORM > 使用案例 >

Python3-sqlalchemy-orm 创建关联表带外键并查询数据

马欣德
2023-12-01
#-*-coding:utf-8-*-
#__author__ = "logan.xu"


import sqlalchemy
from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey,DATE
from sqlalchemy.orm import sessionmaker,relationship


engine=create_engine("mysql+pymysql://root:12345678@localhost/news",encoding='utf-8',echo=True)
Base=declarative_base()

class Student(Base):
    __tablename__='student'
    id=Column(Integer,primary_key=True)
    name=Column(String(32),nullable=False)
    register_date=Column(DATE,nullable=False)

    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

class StudyRecord(Base):
     __tablename__ = 'study_record'
     id = Column(Integer, primary_key=True)
     day = Column(Integer,nullable=False)
     status = Column(String(32),nullable=False)
     stu_id = Column(Integer,ForeignKey("student.id"))

     student=relationship("Student",backref="my_study_record")
     #
    #def __repr__(self):
        #return "<%s name:%s>" % (self.id,self.day)
   def __repr__(self):
   return "<%s day:%s status:%s>" % (self.id,self.day,self.status)
#---------------1.创建表-----------------# 

Base.metadata.create_all(engine)
#---------------------------------------#
Session_class = sessionmaker(bind=engine) Session = Session_class()

#---------------2.写入数据-----------------#

s1=Student(name='Alex',register_date="2019-01-09")
s2=Student(name='Jack',register_date="2019-01-09")
s3=Student(name='Rain',register_date="2019-01-10")
s4=Student(name='Eric',register_date="2019-01-11")
s5=Student(name='John',register_date="2019-01-12")

#

study_obj1=StudyRecord(day=1,status="Yes",stu_id=1)
study_obj2=StudyRecord(day=2,status="NO",stu_id=1)
study_obj3=StudyRecord(day=3,status="Yes",stu_id=1)
study_obj4=StudyRecord(day=1,status="NO",stu_id=2)
study_obj5=StudyRecord(day=1,status="NO",stu_id=2)

# ## 写入数据 并调用 study_record字段插入数据 为 Student数据

Session.add_all([s1,s2,s3,s4,s5,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5])

#---------------------------------------#

#---------------3.查询数据-----------------#

# 取出student内的 内存对象
stu_obj = Session.query(Student).filter(Student.name=="Alex").first()

print(stu_obj.my_study_record)

#---------------------------------------#

# 执行事务 Session.commit()
/Users/drizzle/PycharmProjects/2018-12-03/venv/bin/python /Users/drizzle/PycharmProjects/2018-12-03/orm_fk.py
2019-01-08 18:30:00,061 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-01-08 18:30:00,061 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:30:00,063 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-01-08 18:30:00,063 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:30:00,065 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-01-08 18:30:00,065 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:30:00,066 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019-01-08 18:30:00,066 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:30:00,069 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-01-08 18:30:00,069 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:30:00,069 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-01-08 18:30:00,069 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:30:00,070 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) 
COLLATE utf8mb4_bin AS anon_1 2019-01-08 18:30:00,070 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 18:30:00,071 INFO sqlalchemy.engine.base.Engine DESCRIBE `student` 2019-01-08 18:30:00,071 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 18:30:00,073 INFO sqlalchemy.engine.base.Engine DESCRIBE `study_record` 2019-01-08 18:30:00,073 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 18:30:00,080 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-01-08 18:30:00,081 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:30:00,081 INFO sqlalchemy.engine.base.Engine {'name': 'Alex', 'register_date': '2019-01-09'} 2019-01-08 18:30:00,082 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:30:00,082 INFO sqlalchemy.engine.base.Engine {'name': 'Jack', 'register_date': '2019-01-09'} 2019-01-08 18:30:00,082 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:30:00,082 INFO sqlalchemy.engine.base.Engine {'name': 'Rain', 'register_date': '2019-01-10'} 2019-01-08 18:30:00,083 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:30:00,083 INFO sqlalchemy.engine.base.Engine {'name': 'Eric', 'register_date': '2019-01-11'} 2019-01-08 18:30:00,083 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:30:00,083 INFO sqlalchemy.engine.base.Engine {'name': 'John', 'register_date': '2019-01-12'} 2019-01-08 18:30:00,084 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:30:00,084 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'Yes', 'stu_id': 1} 2019-01-08 18:30:00,085 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:30:00,085 INFO sqlalchemy.engine.base.Engine {'day': 2, 'status': 'NO', 'stu_id': 1} 2019-01-08 18:30:00,085 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:30:00,085 INFO sqlalchemy.engine.base.Engine {'day': 3, 'status': 'Yes', 'stu_id': 1} 2019-01-08 18:30:00,086 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:30:00,086 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'NO', 'stu_id': 2} 2019-01-08 18:30:00,086 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:30:00,086 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'NO', 'stu_id': 2} 2019-01-08 18:30:00,088 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.register_date
AS student_register_date FROM student WHERE student.name = %(name_1)s LIMIT %(param_1)s 2019-01-08 18:30:00,088 INFO sqlalchemy.engine.base.Engine {'name_1': 'Alex', 'param_1': 1} 2019-01-08 18:30:00,090 INFO sqlalchemy.engine.base.Engine SELECT study_record.id AS study_record_id, study_record.day AS study_record_day,
study_record.status AS study_record_status, study_record.stu_id AS study_record_stu_id FROM study_record WHERE %(param_1)s = study_record.stu_id 2019-01-08 18:30:00,090 INFO sqlalchemy.engine.base.Engine {'param_1': 1} [<2 day:1 status:Yes>, <3 day:2 status:NO>, <4 day:3 status:Yes>, <7 day:1 status:Yes>, <8 day:2 status:NO>, <9 day:3 status:Yes>,
<12 day:1 status:Yes>, <13 day:2 status:NO>, <14 day:3 status:Yes>] 2019-01-08 18:30:00,091 INFO sqlalchemy.engine.base.Engine COMMIT Process finished with exit code 0

 

可以在study_record 反查student表信息

#-*-coding:utf-8-*-
#__author__ = "logan.xu"


import sqlalchemy
from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey,DATE
from sqlalchemy.orm import sessionmaker,relationship


engine=create_engine("mysql+pymysql://root:12345678@localhost/news",encoding='utf-8',echo=True)
Base=declarative_base()

class Student(Base):
    __tablename__='student'
    id=Column(Integer,primary_key=True)
    name=Column(String(32),nullable=False)
    register_date=Column(DATE,nullable=False)

    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

class StudyRecord(Base):
     __tablename__ = 'study_record'
     id = Column(Integer, primary_key=True)
     day = Column(Integer,nullable=False)
     status = Column(String(32),nullable=False)
     stu_id = Column(Integer,ForeignKey("student.id"))

     student=relationship("Student",backref="my_study_record")
     #
     def __repr__(self):
        return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)

#---------------1.创建表-----------------#

Base.metadata.create_all(engine)
#---------------------------------------#

Session_class = sessionmaker(bind=engine)
Session = Session_class()


#---------------2.写入数据-----------------#

s1=Student(name='Alex',register_date="2019-01-09")
s2=Student(name='Jack',register_date="2019-01-09")
s3=Student(name='Rain',register_date="2019-01-10")
s4=Student(name='Eric',register_date="2019-01-11")
s5=Student(name='John',register_date="2019-01-12")
#
study_obj1=StudyRecord(day=1,status="Yes",stu_id=1)
study_obj2=StudyRecord(day=2,status="NO",stu_id=1)
study_obj3=StudyRecord(day=3,status="Yes",stu_id=1)
study_obj4=StudyRecord(day=1,status="NO",stu_id=2)
study_obj5=StudyRecord(day=1,status="NO",stu_id=2)
#

## 写入数据 并调用 study_record字段插入数据 为 Student数据
Session.add_all([s1,s2,s3,s4,s5,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5])

#---------------------------------------#

#---------------3.查询数据-----------------#

# 取出student内的 内存对象
stu_obj = Session.query(Student).filter(Student.name=="Alex").first()
print(stu_obj.my_study_record)

#---------------------------------------#

# 执行事务
Session.commit()
/Users/drizzle/PycharmProjects/2018-12-03/venv/bin/python /Users/drizzle/PycharmProjects/2018-12-03/orm_fk.py
2019-01-08 18:35:46,521 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-01-08 18:35:46,522 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:35:46,523 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-01-08 18:35:46,523 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:35:46,525 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-01-08 18:35:46,525 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:35:46,526 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019-01-08 18:35:46,526 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:35:46,529 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-01-08 18:35:46,529 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:35:46,529 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-01-08 18:35:46,530 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:35:46,530 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) 
COLLATE utf8mb4_bin AS anon_1 2019-01-08 18:35:46,530 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 18:35:46,531 INFO sqlalchemy.engine.base.Engine DESCRIBE `student` 2019-01-08 18:35:46,531 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 18:35:46,533 INFO sqlalchemy.engine.base.Engine DESCRIBE `study_record` 2019-01-08 18:35:46,533 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 18:35:46,541 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-01-08 18:35:46,542 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:35:46,542 INFO sqlalchemy.engine.base.Engine {'name': 'Alex', 'register_date': '2019-01-09'} 2019-01-08 18:35:46,543 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:35:46,543 INFO sqlalchemy.engine.base.Engine {'name': 'Jack', 'register_date': '2019-01-09'} 2019-01-08 18:35:46,544 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:35:46,544 INFO sqlalchemy.engine.base.Engine {'name': 'Rain', 'register_date': '2019-01-10'} 2019-01-08 18:35:46,544 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:35:46,544 INFO sqlalchemy.engine.base.Engine {'name': 'Eric', 'register_date': '2019-01-11'} 2019-01-08 18:35:46,545 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s) 2019-01-08 18:35:46,545 INFO sqlalchemy.engine.base.Engine {'name': 'John', 'register_date': '2019-01-12'} 2019-01-08 18:35:46,546 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:35:46,546 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'Yes', 'stu_id': 1} 2019-01-08 18:35:46,547 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:35:46,547 INFO sqlalchemy.engine.base.Engine {'day': 2, 'status': 'NO', 'stu_id': 1} 2019-01-08 18:35:46,547 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:35:46,547 INFO sqlalchemy.engine.base.Engine {'day': 3, 'status': 'Yes', 'stu_id': 1} 2019-01-08 18:35:46,548 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:35:46,548 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'NO', 'stu_id': 2} 2019-01-08 18:35:46,548 INFO sqlalchemy.engine.base.Engine INSERT INTO study_record (day, status, stu_id) VALUES (%(day)s, %(status)s, %(stu_id)s) 2019-01-08 18:35:46,548 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'NO', 'stu_id': 2} 2019-01-08 18:35:46,550 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.register_date AS student_register_date FROM student WHERE student.name = %(name_1)s LIMIT %(param_1)s 2019-01-08 18:35:46,550 INFO sqlalchemy.engine.base.Engine {'name_1': 'Alex', 'param_1': 1} 2019-01-08 18:35:46,552 INFO sqlalchemy.engine.base.Engine SELECT study_record.id AS study_record_id, study_record.day AS study_record_day,
study_record.status AS study_record_status, study_record.stu_id AS study_record_stu_id FROM study_record WHERE %(param_1)s = study_record.stu_id 2019-01-08 18:35:46,552 INFO sqlalchemy.engine.base.Engine {'param_1': 1} [<Alex day:1 status:Yes>, <Alex day:2 status:NO>, <Alex day:3 status:Yes>, <Alex day:1 status:Yes>, <Alex day:2 status:NO>,
<Alex day:3 status:Yes>, <Alex day:1 status:Yes>, <Alex day:2 status:NO>, <Alex day:3 status:Yes>, <Alex day:1 status:Yes>,
<Alex day:2 status:NO>, <Alex day:3 status:Yes>] 2019-01-08 18:35:46,553 INFO sqlalchemy.engine.base.Engine COMMIT Process finished with exit code 0

 

转载于:https://www.cnblogs.com/drizzle-xu/p/10240721.html

 类似资料: