Python---Mysql

这是修改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.")