这是修改mysql内写入有空白的字段值.py文件
import pymysql
connection = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
database='ol',
charset='utf8mb4'
)
try:
with connection.cursor() as cursor:
# 执行UPDATE语句
sql = "UPDATE gaokao SET is_2022 = 489.00 WHERE is_2022 = ''"
cursor.execute(sql)
# 提交事务
connection.commit()
# 输出受影响的行数
print(f"{cursor.rowcount} rows updated")
except Exception as e:
# 发生异常时回滚事务
connection.rollback()
print(f"Error: {e}")
finally:
# 关闭数据库连接
connection.close()
下一个是,就是固定分数,随机添加专业的分数.py文件
import pymysql
import random
connection = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
database='ol',
charset='utf8mb4'
)
cursor = connection.cursor()
# 查询is_2022字段的所有数据
sql = "SELECT is_2022 FROM gaokao"
cursor.execute(sql)
result = cursor.fetchall()
# 遍历每一行数据,并随机生成一个浮动分数,将其加上is_2022分数得到最终分数,将最终分数插入major字段
majors = ['Philosophy', 'Economics', 'Law', 'Education', 'Literature', 'History', 'Science', 'Engineering', 'Agriculture', 'Medicine', 'Management', 'Art']
idd = 1
for row in result:
is_2022 = row[0]
if is_2022 is not None:
score_2022 = float(is_2022)
for major in majors:
score_float = random.randint(10, 20) # 设置浮动范围为10-20
final_score = score_2022 + score_float
sql_update = "UPDATE gaokao SET " + major + " = %s WHERE id = %s"
cursor.execute(sql_update, (final_score, idd))
idd += 1
# 提交并关闭数据库连接
connection.commit()
cursor.close()
connection.close()
下面是弄4000到6000增加id字段值的代码
import pymysql
connection = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
database='ol',
charset='utf8mb4'
)
mycursor = connection.cursor()
for i in range(4000, 6001):
sql = f"INSERT INTO `gaokao` (`id`, `diqu`, `xuexiao`, `is211`, `is_2020`, `is_2021`, `is_2022`, `is_985`, `Philosophy`, `Economics`, `Law`, `Education`, `Literature`, `History`, `Science`, `Engineering`, `Agriculture`, `Medicine`, `Management`, `Art`, `province`) VALUES ('{i}', '地区{i}', '学校{i}', '否', '1.00', '1.00', '1.00', '否', '1.00', '1.00', '1.00', '1.00', '1.00', '1.00', '1.00', '1.00', '1.00', '1.00', '1.00', '1.00', '省份{i}');"
mycursor.execute(sql)
connection.commit()
print(mycursor.rowcount, "record(s) inserted.")