from re import match
import mysql.connector
import requests
from bs4 import BeautifulSoup
from mysql.connector import errorcode
headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36'}
conn = mysql.connector.connect(
host="localhost",
user="root",
password="123456",
database="test"
)
cursor = conn.cursor()
def create_tables():
tables = {
'students': (
"CREATE TABLE students ("
"id INT AUTO_INCREMENT PRIMARY KEY,"
"name VARCHAR(50) NOT NULL,"
"sex VARCHAR(50) NOT NULL,"
"age INT"
")"
),
'class': (
"CREATE TABLE class ("
"id INT AUTO_INCREMENT PRIMARY KEY,"
"class_name VARCHAR(50) NOT NULL,"
"class_score INT"
")"
),
'score': (
"CREATE TABLE score ("
"id INT AUTO_INCREMENT PRIMARY KEY,"
"student_id INT NOT NULL,"
"class_id INT NOT NULL,"
"score DECIMAL(5,2) CHECK (score BETWEEN 0 AND 100),"
"FOREIGN KEY (student_id) REFERENCES students(id)"
" ON UPDATE CASCADE ON DELETE RESTRICT," # 注意逗号分隔
"FOREIGN KEY (class_id) REFERENCES class(id)"
" ON UPDATE CASCADE ON DELETE RESTRICT"
")"
)
}
try:
# 按依赖顺序创建表
cursor.execute(tables['students'])
cursor.execute(tables['class'])
cursor.execute(tables['score'])
conn.commit()
print("表创建成功")
except mysql.connector.Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("表已存在")
else:
print(f"创建失败: {err.msg}")
def insert_student(table, data):
try:
placeholders = ', '.join(['%s'] * len(data[0]))
sql = f"INSERT INTO {table} (id, name, sex, age) VALUES ({placeholders})"
cursor.executemany(sql, data)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("none")
else:
print(f"插入失败: {err.msg}")
def insert_score(table, data):
try:
placeholders = ', '.join(['%s'] * len(data[0]))
sql = f"INSERT INTO {table} (student_id, class_id, score) VALUES ({placeholders})"
cursor.executemany(sql, data)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("none")
else:
print(f"插入失败: {err.msg}")
def get_data(url):
re = requests.get(url,
headers=headers).json()
for i in range(2,len(re)):
stu = [[re[i][0], re[i][1], re[i][2], re[i][3]]]
insert_student('students',stu)
if re[i][4] == '语文':
sco = [[re[i][0], 1, re[i][5]]]
insert_score('score',sco)
if re[i][4] == '英语':
sco = [[re[i][0], 2, re[i][5]]]
insert_score('score', sco)
if re[i][4] == '数学':
sco = [[re[i][0], 3, re[i][5]]]
insert_score('score', sco)
# 执行创建
create_tables()
get_data('http://article.xishanyigu.com/index/index/test')
最新发布
本文解析了pymysql.err.InternalError错误，提供了四种常见解决方案，包括数据类型、表引擎、约束名及编码格式的问题排查与修复。
扫一扫
1613
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
