python同步Sqlserver表结构
python脚本
import decimal import logging import pymssql def sync_db_structure(sou_conn, tar_conn): """ 同步数据库表结构 :param source: 源数据库 :param target: 目标数据库 :param database: 要同步的库 :return: """ sou_cur = sou_conn.cursor() tar_cur = tar_conn.cursor() """查询出源库中的表及其对应的架构""" sql1 = 'select name,schema_id from %s.sys.tables' % database sou_tables = fetch_data(sou_cur, sql1) tar_tables = fetch_data(tar_cur, sql1) for table_info in sou_tables: if table_info in tar_tables: # print(table_info) """查询出列名""" sql2 = "select name from syscolumns where id = (select object_id from %s.sys.tables where name='%s' and schema_id=%s) " % ( database, table_info[0], table_info[1]) # print(sql2) sou_columns = fetch_data(sou_cur, sql2) tar_columns = fetch_data(tar_cur, sql2) for column in sou_columns: """查询出源表中有，但是目标表中没有的列,排除大小写""" if column[0].upper() not in [x[0].upper() for x in tar_columns]: # print(table_info[1], '.', column[0]) # print(type(column)) """查询字段信息，column_info对应 数据类型，长度，是否为空，默认值""" sql3 = ('select st.name, sc.length, sc.isnullable, SM.TEXT AS "default" \n' 'from syscolumns sc left join systypes st on sc.xtype = st.xtype \n' 'LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id \n' f'where sc.id=(select object_id from {database}.sys.tables \n' f' where name=\'{table_info[0]}\' and schema_id={table_info[1]})' f'and sc.name=\'{column[0]}\'\n') # print("sql3:", sql3) column_info = fetch_data(sou_cur, sql3) # print(column_info) "nvarchar长度是length的一半" length = column_info[0][1] / 2 if column_info[0][0] == 'nvarchar' else column_info[0][1] is_null = 'NULL' if column_info[0][2] else 'NOT NULL' """查询数据库架构名""" sql4 = ('select sys.schemas.name as [架构名称] \n' 'from sys.objects,sys.schemas \n' 'where sys.objects.type=\'U\'\n' 'and sys.objects.schema_id=sys.schemas.schema_id \n' f"and sys.schemas.schema_id={table_info[1]}") # print("sql4:", sql4) arc = fetch_data(sou_cur, sql4) # print("arc:", arc[0][0]) default_value = column_info[0][3] if column_info[0][3] else 'NULL' '''不同的数据类型对应不同的长度，有些还不用写''' if column_info[0][0] in ['char', 'varchar', 'nchar', 'binary', 'varbinary']: length = '(' + str(column_info[0][1]) + ')' elif column_info[0][0] == 'nvarchar': print(column_info[0][1]) length = '(' + str(column_info[0][1] // 2) + ')' else: length = '' '''NOT NULL和timestamp不需要默认值''' if column_info[0][0] == 'timestamp' or (is_null == 'NOT NULL' and default_value == 'NULL'): default_value = '' else: default_value = 'DEFAULT ' + default_value """把缺失的列加到目标表中""" sql5 = f"ALTER TABLE [{arc[0][0]}].{table_info[0]} ADD {column[0]} {column_info[0][0]}{length} {is_null} {default_value}" print("执行SQL:", sql5) # try: tar_cur.execute(sql5) tar_conn.commit() # except Exception: # print("SQL执行失败： "+sql5) else: "目标库表有该字段则不处理" pass else: "建表及列" pass sou_cur.close() tar_cur.close() def fetch_data(cur, sql): """根据SQL查询数据库数据，返回查询结果tuple类型的数据""" try: cur.execute(sql) results = cur.fetchall() # print(results) # print('SQL执行成功:' + sql) return results except Exception: print('数据库查询失败') if __name__ == '__main__': source_ip = input("请输入源数据库IP：") source_user = input("请输入源数据库用户名(默认sa)：") if input("请输入源数据库用户名(默认值)：") else 'sa' source_pwd = input("请输入源数据库密码(默认fyw554193)：") if input("请输入源数据库密码(默认密码)：") else 'fyw554193' target_ip = input("请输入目标数据库IP：") target_user = input("请输入目标数据库用户名(默认sa)：") if input("请输入目标数据库用户名(默认值)：") else 'sa' target_pwd = input("请输入目标数据库密码(默认fyw554193)：") if input("请输入目标数据库密码(默认密码)：") else 'fyw554193' database = input("请输入要同步的数据库(Loan/PostLoan/Sys)：") print("源数据库IP：%s" % source_ip) print("源数据库用户名：%s" % source_user) print("源数据库密码：%s" % source_pwd) print("目标数据库IP：%s" % target_ip) print("目标数据库用户名：%s" % target_user) print("目标数据库密码：%s" % target_pwd) print("要同步的数据库：%s" % database) confirm = input("输入Y继续，N退出：") if confirm in ['Y', 'y']: sou_conn = pymssql.connect(host=source_ip, user=source_user, password=source_pwd, database=database, charset='UTF-8') tar_conn = pymssql.connect(host=target_ip, user=target_user, password=target_pwd, database=database, charset='UTF-8') sync_db_structure(sou_conn, tar_conn) print("\n%s 所有数据库表同步成功" % target_ip) sou_conn.close() tar_conn.close() else: print("\n请重新打开程序！") pass
然后用 pyInstaller -F sync_DB.py 命令就可以打包成exe了
pyInstaller 参考：http://c.biancheng.net/view/2690.html
相应的sql
--根据表名和架构查包含的列-- select name,id from syscolumns where id = (select object_id from DB.sys.tables where name='DeleteBillLog' and schema_id=19) --查询字段信息，包括 数据类型，长度，是否为空，默认值 select st.name, sc.length, sc.isnullable, SM.TEXT AS "default" from syscolumns sc left join systypes st on sc.xtype = st.xtype LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id where sc.id=(select object_id from PostLoan.sys.tables where name='表名' and schema_id=1)and sc.name='列名' --查表对应的架构-- select sys.schemas.name as [架构名称] from sys.objects,sys.schemas where sys.objects.type='U' and sys.objects.schema_id=sys.schemas.schema_id and sys.schemas.schema_id=1 --新增TIMESTAMP列-- ALTER TABLE 架构名.表名 ADD TIMESTAMP timestamp NOT NULL
