数据库版本更新后，有新增的表，新增的字段
通过对比两个库的差异，然后生成语句补充差异
import MySQLdb
import datetime
# 进行对比的数据库（新库）
db1 = ""
# 要更新的数据库（旧库）
db2 = ""
sql_host = '127.0.0.1'
sql_port = 3306
sql_user = 'root'
sql_pwd = '='
sql_charset = 'utf8'
# 数据需要更新的表
table_list = []
# table_list = []
conn = MySQLdb.connect(host=sql_host, port=sql_port, user=sql_user, passwd=sql_pwd, db=db1, charset=sql_charset)
# 获取游标
cur = conn.cursor()
text = ""
# 两个数据库相同的表要更新字段的数据类型（db1与db2相同的表和字段，字段的数据类型不同）
sql = f"""
select distinct * from (SELECT DISTINCT
table_name ,column_name, column_comment, column_type, is_nullable, column_default, extra
FROM
information_schema.columns
WHERE
table_schema = '{db1}') t1
left join (
SELECT DISTINCT
table_name ,column_name ,column_comment ,column_type, is_nullable, column_default, extra
FROM
information_schema.columns
WHERE
table_schema = '{db2}') t2
on t1.table_name = t2.table_name and t1.column_name=t2.column_name
where t1.column_type!=t2.column_type OR t1.is_nullable!=t2.is_nullable or t1.column_default!=t2.column_default or t1.extra!=t2.extra
"""
cur.execute(sql)
l1 = []
while 1:
res = cur.fetchone()
if res is None:
break # 表示已经取完结果集s
l1.append(res)
# print('l1')
# print(l1)
# 对更新数据类型的sql语句进行拼接
for i in l1:
table_name = i[0] # 表名
column_name = i[1] # 字段名
column_comment = i[2] # 备注
column_type = i[3] # 字段类型
is_nullable = i[4] # 字段是否为空
column_default = i[5] # 默认值
extra = i[6] # 自动递增，更新时间等
# 默认值
default_ = 'DEFAULT NULL' if column_default is None else f'DEFAULT {column_default}'
# 字段是否为空
if is_nullable == 'YES':
is_nullable_ = 'NULL'
else:
is_nullable_ = 'NOT NULL'
# 当该字段为非空时，判断默认值是否为空，如果是则清除默认值
default_ = '' if column_default is None else default_
# 备注
column_comment_ = f" COMMENT '{column_comment}'" if column_comment else ''
sql = f"ALTER TABLE `{table_name}` MODIFY COLUMN `{column_name}` {column_type} {is_nullable_} {default_} {extra}{column_comment_};"
# sql = f"ALTER TABLE {table_name} MODIFY COLUMN {column_name} {column_type};"
# print(sql)
text = text + sql + "\n"
# 两个数据库相同表，需要更新的数据库要添加的表字段（db1中的表，db2中存在但是缺少字段）
sql2 = f"""
SELECT DISTINCT * from (SELECT DISTINCT
t1.table_name,t1.column_name,t1.column_type,t1.column_comment, t1.is_nullable, t1.column_key,
t1.column_default, t1.extra, t1.character_set_name, t1.collation_name
from (SELECT DISTINCT
table_name,column_name,column_comment,column_type, is_nullable, column_key,
column_default, extra, character_set_name, collation_name
FROM
information_schema.columns
WHERE
table_schema = '{db1}') t1
left join (SELECT DISTINCT
table_name,column_name,column_comment,column_type
FROM
information_schema.columns
WHERE
table_schema = '{db2}') t2
on t1.table_name = t2.table_name and t1.column_name=t2.column_name where t2.table_name is null) t3 left join
(select distinct t1.table_name from (SELECT DISTINCT
table_name,column_name,column_comment,column_type
FROM
information_schema.columns
WHERE
table_schema = '{db1}') t1
left join (SELECT DISTINCT
table_name,column_name,column_comment,column_type
FROM
information_schema.columns
WHERE
table_schema = '{db2}') t2
on t1.table_name = t2.table_name where t2.table_name is null) t4 on t3.table_name=t4.table_name where t4.table_name is null
"""
cur.execute(sql2)
l2 = []
l2_ = []
while 1:
res = cur.fetchone()
if res is None:
break # 表示已经取完结果集s
l2.append(res)
l2_.append((res[0], res[1]))
# print('l2')
# print(l2_)
# 对需要新增字段的sql语句进行拼接.
for i in l2:
table_name = i[0] # 表名
column_name = i[1] # 字段名
column_type = i[2] # 字段类型
column_comment = i[3] # 备注
is_nullable = i[4] # 字段是否为空
column_key = i[5] # 字段的主键(PRI为主键)
column_default = i[6] # 默认值
extra = i[7] # 自动递增，更新时间等
character_set_name = i[8] # 字符集
collation_name = i[9] # 排序规则
# 默认值
default_ = 'DEFAULT NULL' if column_default is None else f'DEFAULT {column_default}'
# 字段是否为空
if is_nullable == 'YES':
is_nullable_ = 'NULL'
else:
is_nullable_ = 'NOT NULL'
# 当该字段为非空时，判断默认值是否为空，如果是则清除默认值
default_ = '' if column_default is None else default_
# varchar类型的编码
character_ = f' CHARACTER SET {character_set_name} COLLATE {collation_name}' if character_set_name else ''
# 备注
column_comment_ = f" COMMENT '{column_comment}'" if column_comment else ''
sql = f"ALTER TABLE `{table_name}` ADD COLUMN `{column_name}` {column_type}{character_} {is_nullable_} {default_} {extra}{column_comment_};"
# print(sql)
text = text + sql + "\n"
# 需要更新的数据库中需要新建的表（db1中有的表,db2中没有的）
sql3 = f"""
select table_name from information_schema.tables where table_schema='{db1}' and table_name not in (
select table_name from information_schema.tables where table_schema='{db2}')
"""
cur.execute(sql3)
l3 = []
while 1:
res = cur.fetchone()
if res is None:
break # 表示已经取完结果集s
l3.append(res)
# print('l3')
# print(l3)
# 获取新建表的sql建表语句
for i in l3:
table_name = i[0]
cur.execute(f"show create table {table_name}")
l4 = []
while 1:
res = cur.fetchone()
if res is None:
break # 表示已经取完结果集s
l4.append(res)
# print(len(l4))
# print(l4[0][1] + ";")
text += f"""
-- ----------------------------
-- Table structure for {l4[0][0]}
-- ----------------------------
"""
text += l4[0][1] + ";" + "\n"
# 添加新的外键
sql5 = f"""
SELECT * FROM ( SELECT DISTINCT
table_name,column_name,referenced_table_name,referenced_column_name,position_in_unique_constraint
FROM
information_schema.key_column_usage
WHERE
table_schema = '{db1}' and position_in_unique_constraint = 1
) t1 LEFT JOIN ( SELECT DISTINCT
table_name,column_name,referenced_table_name,referenced_column_name,position_in_unique_constraint
FROM
information_schema.key_column_usage
WHERE
table_schema = '{db2}' and position_in_unique_constraint = 1
) t2 ON t1.table_name = t2.table_name AND t1.column_name = t2.column_name
WHERE t2.column_name is null and t1.table_name not in (
select table_name from information_schema.tables where table_schema='{db1}' and table_name not in (
select table_name from information_schema.tables where table_schema='{db2}'));
"""
cur.execute(sql5)
l5 = []
while 1:
res = cur.fetchone()
if res is None:
break # 表示已经取完结果集s
l5.append(res)
# print('l5')
# print(l5)
# 需要更新的数据库中需要补充的外键
text += f'''\nSET foreign_key_checks = 0;\n'''
for i in l5:
table_name = i[0] # 表名
column_name = i[1] # 字段名
referenced_table_name = i[2] # 关联的表名
referenced_column_name = i[3] # 关联的字段名
position_in_unique_constraint = i[4] # 关联的字段名
if position_in_unique_constraint == 1:
text += f'''ALTER TABLE `{table_name}`ADD FOREIGN KEY(`{column_name}`) REFERENCES `{referenced_table_name}`(`{referenced_column_name}`) ON DELETE CASCADE ON UPDATE CASCADE;\n'''
text += '''SET foreign_key_checks = 1;\n''' # 添加外键约束
# 更新规则，解析器等固定数据表的数据
text += '''\n-- 覆盖插入数据，先清楚外键约束，等数据更新完成之后再添加外键约束\nSET foreign_key_checks = 0;\n''' # 删除外键约束
for table_name in table_list:
text += f'''
-- ----------------------------
-- Records of {table_name}
-- ----------------------------
truncate table {table_name};
'''
sql = f'''SELECT * FROM {table_name}'''
cur.execute(sql)
field_name_list = '('
for i, field_name in enumerate(cur.description):
if i == len(cur.description) - 1:
field_name_list += f'{table_name}.{field_name[0]}'
else:
field_name_list += f'{table_name}.{field_name[0]}, '
field_name_list += ')'
while 1:
res = cur.fetchone()
if res is None:
# 表示已经取完结果集s
break
new_res = []
for i in res:
if type(i) == datetime.datetime:
new_res.append(i.strftime("%Y-%m-%d %H:%M:%S"))
else:
new_res.append(i)
text += f'''INSERT INTO `{table_name}` {field_name_list} VALUES {tuple(new_res)};\n'''.replace(', None',
', Null')
text += '''SET foreign_key_checks = 1;\n''' # 添加外键约束
# 调用方法写入同目录文件中
with open("gengxin.sql", "w", encoding="utf-8") as fp:
fp.write(text)
cur.close()
conn.commit()
conn.close()
print('sql生成成功')
# 执行更新的sql语句
conn2 = MySQLdb.connect(host=sql_host, port=sql_port, user=sql_user, passwd=sql_pwd, db=db2, charset=sql_charset)
cur2 = conn2.cursor()
cur2.execute(text)
cur2.close()
conn2.commit()
conn2.close()
print('sql执行成功')
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
