python脚本-批量操作sql

又在一个阳光明媚的下午，数据库管理员小王正盯着一堆杂乱无章的表结构，陷入了深深的思考：

“为什么每次都要手动添加字段？难道我的时间不值钱吗？难道我的手指不累吗？难道我的大脑不想休息吗？”

于是，小王灵光一闪，决定用 Python 写一个“数据库魔法师”，简称 “SQL 批量操作神器”。这个工具不仅能帮他快速批量新增字段和设置字段值，还能让他的工作效率翻倍，甚至让他有时间去喝杯咖啡，享受生活。

数据库操作，一定要谨慎，先备份啊！！！

批量新增字段

import pymysql
import pymysql.cursors

# 数据库连接配置
connection = pymysql.connect(host='127.0.0.1',
                             user='root',
                             password='123456',
                             database='coolbase1',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # 获取所有表名
        cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='coolbase1'")
        tables = cursor.fetchall()

        # 为每个表添加 'admin' 字段
        for table in tables:
            try:
                sql = f"ALTER TABLE `{table['table_name']}` ADD COLUMN `admin` varchar(255) NULL"
                cursor.execute(sql)
                print(f"Added 'admin' column to {table['table_name']}")
            except pymysql.MySQLError as e:
                print(f"Error occurred when adding 'admin' to {table['table_name']}: {e}")

        connection.commit()
finally:
    connection.close()

批量设置指定字段的值

import pymysql
import pymysql.cursors

# 数据库连接配置
connection = pymysql.connect(host='127.0.0.1',
                             user='root',
                             password='123456',
                             database='coolbase1',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # 获取所有表名
        cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='coolbase1'")
        tables = cursor.fetchall()

        # 为每个表添加 'admin' 字段
        for table in tables:
            try:
                # 设置 'admin' 字段的值为相同的值
                sql_update_value = f"UPDATE `{table['table_name']}` SET `admin` = '176****2222'"
                cursor.execute(sql_update_value)
                print(f"Set 'admin' column value for {table['table_name']}")

            except pymysql.MySQLError as e:
                print(f"Error occurred for {table['table_name']}: {e}")

        connection.commit()
finally:
    connection.close()

批量倍增数据

import pymysql

# 数据库连接信息
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'coolbase1',
    'charset': 'utf8mb4'
}

# 需要修改的字段名和新值
field_to_modify = 'admin'
new_value = '17681831400'

# 连接到数据库
connection = pymysql.connect(**db_config)
cursor = connection.cursor()

# 获取所有表名
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()

# 创建一个文件用于保存生成的SQL语句
with open('insert_statements.sql', 'w', encoding='utf-8') as f:
    for table in tables:
        table_name = table[0]
        print(f"Processing table: {table_name}")

        # 获取表的所有字段名
        cursor.execute(f"DESCRIBE {table_name}")
        columns = [column[0] for column in cursor.fetchall()]

        # 查询所有数据
        cursor.execute(f"SELECT * FROM {table_name}")
        rows = cursor.fetchall()

        # 遍历每条数据，生成INSERT语句
        for row in rows:
            values = []
            for i, value in enumerate(row):
                if columns[i] == 'id':
                    values.append('NULL')
                elif columns[i] == field_to_modify:
                    values.append(f"'{new_value}'")
                else:
                    values.append(f"'{value}'" if isinstance(value, str) else str(value))
            # 将字段名称加上反引号
            quoted_columns = [f"`{col}`" for col in columns]
            insert_statement = f"INSERT INTO {table_name} ({', '.join(quoted_columns)}) VALUES ({', '.join(values)});\n"
            print(f"Added sql: {insert_statement}")
            f.write(insert_statement)

# 关闭数据库连接
cursor.close()
connection.close()