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

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

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

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


# 执行事务
Session.commit()
/Users/drizzle/PycharmProjects/2018-12-03/venv/bin/python /Users/drizzle/PycharmProjects/2018-12-03/orm_fk.py
2019-01-08 18:08:40,496 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-01-08 18:08:40,496 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,498 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-01-08 18:08:40,498 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,500 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-01-08 18:08:40,500 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,501 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019-01-08 18:08:40,501 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,504 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-01-08 18:08:40,504 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,504 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-01-08 18:08:40,504 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,505 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:08:40,505 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,506 INFO sqlalchemy.engine.base.Engine DESCRIBE `student`
2019-01-08 18:08:40,506 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,507 INFO sqlalchemy.engine.base.Engine DESCRIBE `study_record`
2019-01-08 18:08:40,507 INFO sqlalchemy.engine.base.Engine {}
2019-01-08 18:08:40,511 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-01-08 18:08:40,512 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s)
2019-01-08 18:08:40,512 INFO sqlalchemy.engine.base.Engine {'name': 'Alex', 'register_date': '2019-01-09'}
2019-01-08 18:08:40,513 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s)
2019-01-08 18:08:40,513 INFO sqlalchemy.engine.base.Engine {'name': 'Jack', 'register_date': '2019-01-09'}
2019-01-08 18:08:40,513 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s)
2019-01-08 18:08:40,514 INFO sqlalchemy.engine.base.Engine {'name': 'Rain', 'register_date': '2019-01-10'}
2019-01-08 18:08:40,514 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s)
2019-01-08 18:08:40,514 INFO sqlalchemy.engine.base.Engine {'name': 'Eric', 'register_date': '2019-01-11'}
2019-01-08 18:08:40,515 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, register_date) VALUES (%(name)s, %(register_date)s)
2019-01-08 18:08:40,515 INFO sqlalchemy.engine.base.Engine {'name': 'John', 'register_date': '2019-01-12'}
2019-01-08 18:08:40,516 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:08:40,516 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'Yes', 'stu_id': 1}
2019-01-08 18:08:40,517 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:08:40,517 INFO sqlalchemy.engine.base.Engine {'day': 2, 'status': 'NO', 'stu_id': 1}
2019-01-08 18:08:40,517 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:08:40,517 INFO sqlalchemy.engine.base.Engine {'day': 3, 'status': 'Yes', 'stu_id': 1}
2019-01-08 18:08:40,518 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:08:40,518 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'NO', 'stu_id': 2}
2019-01-08 18:08:40,518 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:08:40,518 INFO sqlalchemy.engine.base.Engine {'day': 1, 'status': 'NO', 'stu_id': 2}
2019-01-08 18:08:40,519 INFO sqlalchemy.engine.base.Engine COMMIT

Process finished with exit code 0
mysql> show tables;
+----------------+
| Tables_in_news |
+----------------+
| address        |
| customer       |
| student        |
| study_record   |
| user           |
+----------------+
5 rows in set (0.01 sec)

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    |                |
+---------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc study_record;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| day    | int(11)     | NO   |     | NULL    |                |
| status | varchar(32) | NO   |     | NULL    |                |
| stu_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> select * from stuy_record;
ERROR 1146 (42S02): Table 'news.stuy_record' doesn't exist
mysql>
mysql> select * from study_record;
Empty set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_news |
+----------------+
| address        |
| customer       |
| student        |
| study_record   |
| user           |
+----------------+
5 rows in set (0.00 sec)

mysql> show create table student;
+---------+-------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                             |
+---------+-------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `register_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table study_record;
+--------------+-------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                 |
+--------------+-------------------------------------------------------------------------+
| study_record | CREATE TABLE `study_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` int(11) NOT NULL,
  `status` varchar(32) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stu_id` (`stu_id`),
  CONSTRAINT `study_record_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from study_record;
+----+-----+--------+--------+
| id | day | status | stu_id |
+----+-----+--------+--------+
|  2 |   1 | Yes    |      1 |
|  3 |   2 | NO     |      1 |
|  4 |   3 | Yes    |      1 |
|  5 |   1 | NO     |      2 |
|  6 |   1 | NO     |      2 |
+----+-----+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from student;
+----+------+---------------+
| id | name | register_date |
+----+------+---------------+
|  1 | Alex | 2019-01-09    |
|  2 | Jack | 2019-01-09    |
|  3 | Rain | 2019-01-10    |
|  4 | Eric | 2019-01-11    |
|  5 | John | 2019-01-12    |
+----+------+---------------+
5 rows in set (0.00 sec)

mysql>

 

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

 类似资料：

相关阅读

插入创建新表在JavaScript关联数组中动态创建键SQLAlchemy create_all（）不创建表创建外键SQLColdFusion：如何在一个插入中插入带有静态外键的列表？

相关文章

Cassandra创建表MariaDB创建表SQLite 创建表Oracle创建表Access创建表

相关问答

通过带外键的Liquibase插入新记录MySql和创建外键在数据插入期间绕过外键表中的序列号作为其他表的外键。插入数据python - Python sqlalchemy 插入数据失败 ？

相关文档

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