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)
            # print(sou_columns)
            tar_columns = fetch_data(tar_cur, sql2)
            # print(tar_columns)
            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)
                    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‘, ‘nvarchar‘, ‘binary‘, ‘varbinary‘]:
                        sql5 = f"ALTER TABLE {arc[0][0]}.{table_info[0]} ADD {column[0]} {column_info[0][0]}({column_info[0][1]}) {is_null} DEFAULT {default_value}"
                    elif column_info[0][0] == ‘timestamp‘:
                        sql5 = f"ALTER TABLE {arc[0][0]}.{table_info[0]} ADD {column[0]} {column_info[0][0]} {is_null}"
                    else:
                        sql5 = f"ALTER TABLE {arc[0][0]}.{table_info[0]} ADD {column[0]} {column_info[0][0]} {is_null} DEFAULT {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("请输入源数据库用户名(默认sa)：") else ‘sa‘
    source_pwd = input("请输入源数据库密码(默认)：") if input("请输入源数据库密码(默认)：") else ‘默认值‘
    target_ip = input("请输入目标数据库IP：")
    target_user = input("请输入目标数据库用户名(默认sa)：") if input("请输入目标数据库用户名(默认sa)：") else ‘sa‘
    target_pwd = input("请输入目标数据库密码(默认)：") if input("请输入目标数据库密码(默认)：") else ‘默认值‘
    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

--根据表名和架构查包含的列--
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