sqlserver 同步数据库表结构（python实现，适用于同步全部表结构）

第一种：
直接上代码，可以直接运行

import decimal
import logging
import pymssql

"""适用于一个库有，一个库没有的,同步表结构"""


class Copy_Mes_Bank():

    def __init__(self, **kwargs):
        self.sou_conn = kwargs['sou_conn']
        self.tar_conn = kwargs['tar_conn']

    """根据SQL查询数据库数据，返回查询结果tuple类型的数据"""

    def fetch_data(self, cur, sql):
        try:
            cur.execute(sql)
            results = cur.fetchall()
            return results
        except Exception:
            print('数据库查询失败')

    """获取库下所有表名"""

    def get_table_name(self):
        sou_cur = self.sou_conn.cursor()
        sql = 'select name,schema_id from %s.sys.tables' % database
        tar_tables = self.fetch_data(sou_cur, sql)
        return tar_tables

    """获取表结构"""

    def get_db_flat(self,table_names):
        sou_cur = self.sou_conn.cursor()
        sql = 'sp_columns {}'.format(table_names)
        tar_flat = self.fetch_data(sou_cur, sql)
        flat_list = []
        for x in tar_flat:
            item = {}
            item['key'] = x[3]
            item['type_name'] = x[5]

            # 判断类型
            if item['type_name'] in ['int', 'int identity', 'datetime','bigint','timestamp','text','image','uniqueidentifier','bigint identity','nvarchar',]:
                item['length'] = 'NOT NULL'
            else:
                item['length'] = x[7]
            flat_list.append(item)

        return flat_list

    # """获取表主键"""
    def get_primary(self,table_names):
        sou_cur = self.sou_conn.cursor()
        sql = "SELECT  TABLE_NAME,COLUMN_NAME  FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE  TABLE_NAME= '{}'".format(table_names)
        tar_primary = self.fetch_data(sou_cur, sql)
        return tar_primary

    def modile_flat(self,table_names):
        """
        (id INT NOT NULL, name VARCHAR(100), salesrep VARCHAR(100), PRIMARY KEY(id))
        """
        flat_list = self.get_db_flat(table_names)
        primsry_name = self.get_primary(table_names)

        new_flat = []
        # 构建单独语句
        for x in flat_list:
            if x['type_name'] in ['int', 'int identity', 'datetime','bigint','timestamp','text','image','uniqueidentifier','bigint identity','nvarchar']:
                sql_s = str(x['key']) + ' ' + str(x['type_name']) + ' ' + str(x['length'])
            else:
                sql_s = str(x['key']) + ' ' + str(x['type_name']) + '(' + str(x['length']) + ')'

            new_flat.append(sql_s)
        # 构建主键语句
        if primsry_name:
            primsry_s = 'PRIMARY' + ' ' + str('KEY({})'.format(primsry_name[0][1]))
            new_flat.append(primsry_s)

        # 转换为str类型，清洗
        str_flat = str(new_flat).replace("'", '').replace("[", '').replace("]", '')
        return str_flat

    """创建表"""

    def creat_table(self, table_names):
        flat_new = self.modile_flat(table_names)
        tar_cur = self.tar_conn.cursor()
        table_name = table_names
        sql = "IF OBJECT_ID('{}', 'U') IS NOT NULL DROP TABLE {} CREATE TABLE {} ({})".format(table_name, table_name,
                                                                                              table_name, flat_new)
        print(sql)
        tar_cur.execute(sql)
        tar_conn.commit()
        print('创建表成功')
        print('********')

    """开始"""

    def run(self):

        table_name_list = self.get_table_name()
        for t_name in table_name_list:
            print('本次创建表名：', t_name[0])
            self.creat_table(table_names=t_name[0])



if __name__ == '__main__':
    """表信息"""

    """源表"""
    source_ip = '192.168.130.xx'
    source_user = 'sa'
    source_pwd = 'xxxx'

    """目标表"""
    target_ip = '192.168.xxx'
    target_user = 'sa'
    target_pwd = 'xxx'

    """目标库"""
    database = 'HYDB'

    """开始"""
    sou_conn = pymssql.connect(host=source_ip, port='1433', user=source_user, password=source_pwd, database=database,
                               charset='utf8')

    tar_conn = pymssql.connect(host=target_ip, port='1434', user=target_user, password=target_pwd, database=database,
                               charset='utf8')

    datas = Copy_Mes_Bank(sou_conn=sou_conn, tar_conn=tar_conn)
    datas.run()
    sou_conn.close()
    tar_conn.close()