数据库实验2-python连接，批量插入

mrqzgmzlb

已于 2023-04-15 21:01:42 修改

阅读量224

点赞数

文章标签： sql 数据库 python

于 2023-03-16 19:40:28 首次发布

本文链接：https://blog.csdn.net/mrqzgmzlb/article/details/129436686

版权

2021011203

学号，课程号，cpno,都改为Int类型

限定性别（设置默认）

int 类型不需要加“”

USE stufuchenyu;
CREATE TABLE studentfcy
(Sno INT(10)PRIMARY KEY,
 Sname Varchar(20)UNIQUE,
 Ssex Char(2) check(Ssex in('男','女')),
 Sage int,
 Sdept varchar(20)
 )ENGINE = InnoDB;
 
 CREATE TABLE coursefcy
(Cno int (4)PRIMARY KEY,
Cname Varchar(40) NOT NULL,
 Cpno int(4),
 Ccredit SMALLINT CHECK(Ccredit=1 or Ccredit=2 or Ccredit=3 or Ccredit=4),
 foreing key(Cpno) references coursefcy(Cno)
)ENGINE = InnoDB;

CREATE table scfcy
(Sno int(10) PRIMARY KEY,
 Cno int(4) PRIMARY KEY,
 Grade int,
 foreing key(Sno) REFERENCES studentfcy(Sno),
 foreing key(Cno) REFERENCES coursefcy(Cno)
)ENGINE = InnoDB;

不需要写ENGINE，才能运行成功；

代码段分次执行才能成功

写ENGINE，报错

ALTER TABLE scfcy ADD 选课时间 DATETIME;

ALTER TABLE scfcy ADD CONSTRAINT 
grade CHECK(grade>=0 and grade<=100)

Create index stusname on studentfcy(sname asc);
Create unique  index coucno on coursefcy(cno);
Create unique index scno on scfcy(sno asc,cno desc)

——————————————————————————————————————————————————————————————————————————————————————

——mysql出现ERROR : (2006, 'MySQL server has gone away') 原因和解决方案_Data_IT_Farmer的博客-CSDN博客

python安装faker包后引用时报错ImportError The “fake-factory“ package is now called “Faker“..._evasnowind的博客-CSDN博客

mysql报错：Duplicate entry ‘xx‘ for key ‘PRIMARY‘ 解决可行方案。已解决_duplicate entry for key primary__陈哈哈的博客-CSDN博客

批量数据生成

1.python faker库随机假数据

2.用random 随机生成，构造代码

import pymysql

import random
from faker import Faker
# 连接到数据库
mydb = pymysql.connect(
    host="localhost",
    user="root",
    password="（密码）",
    database="stufuchenyu"
)

# 创建cursor对象
mycursor = mydb.cursor()
fake = Faker("zh_CN")
name_set = set()
# 执行2000条SQL语句
for i in range(2000):
    while True:
        Sname = fake.name()
        if Sname not in name_set:
            name_set.add(Sname)
            break
        else:
            continue
    Sno = str(2008000000 + i)
    Ssex = random.choice(['女', '男'])
    Sage = fake.random_int(min=18, max=25)
    Sdept=random.choice(['CS','art','MA','IS'])

    # 组装SQL语句
    sql = "INSERT INTO studentfcy (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES (%s, %s, %s, %s, %s)"
    values = (Sno,Sname, Ssex, Sage, Sdept)

    # 执行SQL语句
    mycursor.execute(sql, values)
    
# 提交更改
mydb.commit()

# 关闭连接
mycursor.close()
mydb.close()

import pymysql
import random
# 连接到数据库
mydb = pymysql.connect(
    host="localhost",
    user="root",
    password="（密码）",
    database="stufuchenyu"
)
mycursor = mydb.cursor()

for i in range(2000):
    # 生成随机的课程号
    cno =int(1+i)
    # 生成随机的课程名
    cname='Course '+str({i+1})
    # 生成随机的先修课号
    cpno =random.randint(1, i+1) 
    # 生成随机的学分
    ccredit = random.choice([1, 2, 3, 4])# {i%4+1}
    course_data = (cno, cname, cpno, ccredit)
    sql = "INSERT INTO coursefcy (cno, cname, cpno, ccredit) VALUES (%s, %s, %s, %s)"
    mycursor.execute(sql, course_data)
sql1="UPDATE coursefcy SET cpno = NULL WHERE cno = cpno"
mycursor.execute(sql1)
mydb.commit()
# 关闭连接
mycursor.close()
mydb.close()

import pymysql
import random
from datetime import datetime, timedelta

# 生成随机时间
def random_time():
    start = datetime(2009, 3, 1, 0, 0, 0)
    end = datetime(2009, 3, 16, 0, 0, 0)
    delta = end - start
    int_delta = (delta.days * 24 * 60 * 60) + delta.seconds
    random_second = random.randrange(int_delta)
    return start + timedelta(seconds=random_second)

# 连接数据库
db = pymysql.connect(host="localhost", user="root", password="（密码 ）", database="stufuchenyu")
cursor = db.cursor()

# 插入数据到scfcy表
for i in range(100000):
    grade=random.randint(0,100)
    sno=random.randint(2008000000,2008002000)
    cno=random.randint(1,2001)
    sql= "INSERT INTO scfcy (sno, cno, grade, 选课时间)VALUES(%s, %s, %s,%s)"
    values=(sno, cno, grade,random_time())
    
    cursor.execute(sql,values)
    # 提交数据库操作并关闭连接
db.commit()
db.close()

代码可以更简洁，需要改进。

import pymysql
import random
from datetime import datetime, timedelta

# 生成随机时间
def random_time():
    start = datetime(2009, 3, 1, 0, 0, 0)
    end = datetime(2009, 3, 16, 0, 0, 0)
    delta = end - start
    int_delta = (delta.days * 24 * 60 * 60) + delta.seconds
    random_second = random.randrange(int_delta)
    return start + timedelta(seconds=random_second)

# 连接数据库
db = pymysql.connect(host="localhost", user="root", password="( 密 码 )", database="stufuchenyu")
cursor = db.cursor()



# 插入数据到scfcy表
for i in range(200000):
#前闭后闭
    grade=random.randint(0,100)
    sno=random.randint(2008000000,2008002000)
    cno=random.randint(1,2000)
#    sql= "INSERT INTO scfcy (sno, cno, grade, 选课时间)VALUES(%s, %s, %s,%s)"改一下
#   values=(sno, cno, grade,random_time())

    # 先检查是否存在重复的sno和cno的对应关系
    check_query = "SELECT * FROM scfcy WHERE sno = %s AND cno = %s"
    cursor.execute(check_query, (sno, cno))
    result = cursor.fetchone()
    # 如果不存在则插入数据
    if not result:
        insert_query = "INSERT INTO scfcy (sno, cno,grade, 选课时间) VALUES (%s, %s,%s,%s)"
        cursor.execute(insert_query, (sno, cno, grade,random_time()))
        db.commit()

db.close()

需要调试一下

import random
import datetime

# 连接数据库的代码省略

def insert_data():
    # 循环插入数据，直到插入达到200000条为止
    count = 0
    while count < 200000:
        grade = random.randint(0, 100)
        sno = random.randint(2008000000, 2008002000)
        cno = random.randint(1, 2000)
        check_query = "SELECT * FROM scfcy WHERE sno = %s AND cno = %s"
        cursor.execute(check_query, (sno, cno))
        result = cursor.fetchone()
        if not result:
            insert_query = "INSERT INTO scfcy (sno, cno, grade, 选课时间) VALUES (%s, %s, %s, %s)"
            values = (sno, cno, grade, random_time())
            cursor.execute(insert_query, values)
            count += 1
    # 提交并关闭数据库连接
    db.commit()
    cursor.close()
    db.close()

def random_time():
    start = datetime.datetime(2020, 7, 1)
    end = datetime.datetime(2020, 12, 31)
    delta = end - start
    int_delta = (delta.days * 24 * 60 * 60) + delta.seconds
    random_second = random.randrange(int_delta)
    return start + datetime.timedelta(seconds=random_second)

if __name__ == "__main__":
    insert_data()
```

这个代码会不断循环，尝试插入200000条数据，如果遇到重复的则跳过，直到插入达到目标条数为止。其中`random_time`函数用于生成随机的日期时间。