从JSON动态创建表，并在表生成后对列执行操作

import json
import mysql.connector

# 解析包含表结构信息的JSON数据
json_data = '''
{
  "table_name": "users",
  "columns": [
    {"name": "id", "type": "INT", "primary_key": true},
    {"name": "name", "type": "VARCHAR(50)", "nullable": false},
    {"name": "email", "type": "VARCHAR(100)", "nullable": false}
  ]
}
'''
table_info = json.loads(json_data)

# 连接到MySQL数据库
cnx = mysql.connector.connect(user='your_username', password='your_password', host='your_host', database='your_database')
cursor = cnx.cursor()

# 创建表
create_table_query = f"CREATE TABLE {table_info['table_name']} ("
for column in table_info['columns']:
    column_name = column['name']
    column_type = column['type']
    create_table_query += f"{column_name} {column_type}, "
create_table_query = create_table_query.rstrip(', ') + ")"
cursor.execute(create_table_query)

# 执行列操作（示例：添加新列）
alter_table_query = f"ALTER TABLE {table_info['table_name']} ADD COLUMN age INT"
cursor.execute(alter_table_query)

# 提交更改并关闭连接
cnx.commit()
cursor.close()
cnx.close()