登录
当前位置： 首页 > 工具软件 > 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

 类似资料：

相关阅读

SQL Server 2000-查询表的外键关系SQLAlchemy联接子查询问题在JavaScript关联数组中动态创建键SQLAlchemy create_all（）不创建表创建外键SQL

相关文章

MyBatis一对一关联查询MySQL查询数据表SQL关联查询详解，SQL JOIN详解MySQL创建数据表JSF数据表(ui:repeat)创建表

相关问答

javascript - typeorm关联查询如何筛选关联数据？无法在Laravel中创建主键外键关系无法使用 JPA 查询交叉联接创建查询使用Hibernate使用外键（其中）查询数据库表MySql和创建外键

相关文档

Sequelize ORM 实践SQLAlchemy 中文文档深入 Python3一步步搭建物联网系统好用的中文速查表
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.