两个数据库关联更新，分析一个数据库相对另一个数据库要更更新的部分，通过pyhton导出更新语句

最新推荐文章于 2024-07-22 15:27:09 发布

加油牛牛

最新推荐文章于 2024-07-22 15:27:09 发布

阅读量439

点赞数

文章标签：数据库 sql mysql python

本文链接：https://blog.csdn.net/a15845561305_/article/details/123405257

版权

该博客介绍了一个Python脚本，用于比较应用程序新旧版本的数据库结构，并自动生成SQL更新语句。脚本首先连接到两个数据库，然后通过查询information_schema获取表结构信息，找出数据类型不一致的字段、缺失的字段以及新版本中存在的表。接着，它拼接SQL语句来修改字段类型、添加缺失字段和创建新表。最后，所有更新的SQL语句被写入到一个文件中，方便执行数据库的同步操作。

摘要由CSDN通过智能技术生成

业务场景：应用程序新版本和旧版本的兼容，这里新版本的数据库是db1，旧版本数据库db2

将查询出的结果对更新语句进行拼串，最后输出一个sql文件，文件内容就是更新db2中表结构的sql语句

#!/usr/bin/python3
# -*- coding:UTF-8 -*-
# 打开数据库连接
import MySQLdb


# 写入内容到文件的方法
def saveTextToFile(text, path, encoding="utf-8"):
    with open(path, "w", encoding=encoding) as fp:
        fp.write(text)


# 进行对比的数据库
db1 = "ceshidb1"
# 要更新的数据库
db2 = "ceshidb2"
conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db=db1, charset='utf8')
# 获取游标
cur = conn.cursor()
# 两个数据库相同的表要更新字段的数据类型（db1与db2相同的表和字段，字段的数据类型不同）
sql = f"""select distinct * from (SELECT DISTINCT
    t1.TABLE_NAME,t2.column_name,t2.column_comment,t2.column_type
FROM
    information_schema.TABLES t1,information_schema.columns t2
WHERE
    table_type = 'BASE TABLE'
AND t1.table_schema = '{db1}' and t2.table_schema = '{db1}' and t1.table_name=t2.table_name) t1
left join (
SELECT DISTINCT
    t1.TABLE_NAME,t2.column_name,t2.column_comment,t2.column_type
FROM
    information_schema.TABLES t1,information_schema.columns t2
WHERE
    table_type = 'BASE TABLE'
AND t1.table_schema = '{db2}' and t2.table_schema = '{db2}' and t1.table_name=t2.table_name) t2
on t1.table_name = t2.table_name and t1.column_name=t2.column_name where t1.column_type!=t2.column_type"""
cur.execute(sql)
l1 = []
while 1:
    res = cur.fetchone()
    if res is None:
        # 表示已经取完结果集s
        break
    l1.append(res)
print(len(l1))
# 两个数据库相同表，需要更新的数据库要添加的表字段（db1中的表，db2中存在但是缺少字段）
sql2 = f"""select DISTINCT * from (select distinct t1.table_name,t1.column_name,t1.column_type,t1.column_comment from (SELECT DISTINCT
    t1.TABLE_NAME,t2.column_name,t2.column_comment,t2.column_type
FROM
    information_schema.TABLES t1,information_schema.columns t2
WHERE
    table_type = 'BASE TABLE'
AND t1.table_schema = '{db1}' and t2.table_schema = '{db1}' and t1.table_name=t2.table_name) t1
left join (
SELECT DISTINCT
    t1.TABLE_NAME,t2.column_name,t2.column_comment,t2.column_type
FROM
    information_schema.TABLES t1,information_schema.columns t2
WHERE
    table_type = 'BASE TABLE'
AND t1.table_schema = '{db2}' and t2.table_schema = '{db2}' and t1.table_name=t2.table_name) 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
    t1.TABLE_NAME,t2.column_name,t2.column_comment,t2.column_type
FROM
    information_schema.TABLES t1,information_schema.columns t2
WHERE
    table_type = 'BASE TABLE'
AND t1.table_schema = '{db1}' and t2.table_schema = '{db1}' and t1.table_name=t2.table_name) t1
left join (
SELECT DISTINCT
    t1.TABLE_NAME,t2.column_name,t2.column_comment,t2.column_type
FROM
    information_schema.TABLES t1,information_schema.columns t2
WHERE
    table_type = 'BASE TABLE'
AND t1.table_schema = '{db2}' and t2.table_schema = '{db2}' and t1.table_name=t2.table_name) 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 = []
while 1:
    res = cur.fetchone()
    if res is None:
        # 表示已经取完结果集s
        break
    l2.append(res)
print(len(l2))
# 需要更新的数据库中需要新建的表（db1中有的表,db2中没有的）
sql3 = f"""select distinct t3.table_name from (
SELECT DISTINCT
    t1.TABLE_NAME,t2.column_name,t2.column_comment,t2.column_type
FROM
    information_schema.TABLES t1,information_schema.columns t2
WHERE
    table_type = 'BASE TABLE'
AND t1.table_schema = '{db1}' and t2.table_schema = '{db1}' and t1.table_name=t2.table_name) t3
left join (
SELECT DISTINCT
    t1.TABLE_NAME,t2.column_name,t2.column_comment,t2.column_type
FROM
    information_schema.TABLES t1,information_schema.columns t2
WHERE
    table_type = 'BASE TABLE'
AND t1.table_schema = '{db2}' and t2.table_schema = '{db2}' and t1.table_name=t2.table_name) t4
on t3.table_name = t4.table_name where t4.table_name is null
"""
cur.execute(sql3)
l3 = []
while 1:
    res = cur.fetchone()
    if res is None:
        # 表示已经取完结果集s
        break
    l3.append(res)
print(len(l3))

text = ""
# 对更新数据类型的sql语句进行拼接
for i in l1:
    biao1 = i[0]
    ziduan1 = i[1]
    ziduanleixing1 = i[3]
    biao2 = i[4]
    ziduan2 = i[5]
    ziduanleixing2 = i[7]
    sql = f"alter table {biao2} modify column {ziduan2} {ziduanleixing1};"
    print(sql)
    text = text + sql + "\n"
# 对需要新增字段的sql语句进行拼接
for i in l2:
    table_name = i[0]
    column_name = i[1]
    column_type = i[2]
    column_comment = i[3]
    sql = f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_type} DEFAULT NULL COMMENT '{column_comment}';"
    print(sql)
    text = text + sql + "\n"
# 获取新建表的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:
            # 表示已经取完结果集s
            break
        l4.append(res)
    # print(len(l4))
    print(l4[0][1] + ";")
    text = text + l4[0][1] + ";" + "\n"
print(text)
# 调用方法写入同目录文件中
saveTextToFile(text, "gengxin.sql")
cur.close()
conn.commit()
conn.close()
print('sql执行成功')