# https://download.lfd.uci.edu/pythonlibs/archived/cp36/pymssql-2.1.5-cp36-cp36m-win_amd64.whl
# pip install D:\dsWork\baseServiceJava\AutoCodeGenerate\pymssql-2.1.5-cp36-cp36m-win_amd64.whl
import pymssql

server = "10.10.14.108"
user = "sa"
password = "dsideal"
FinalDataBase = "Temp2020"
database = "eduData2020DB_xt"
conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor()

# 1、生成数据
sql = "select 'select * into " + FinalDataBase + ".dbo.'+table_name+' from '+table_name as sql,table_name from information_schema.tables"
cursor.execute(sql)
list = cursor.fetchall()
for x in list:
    if x[1][0] in ['G', 'Z', 'J']:
        print("正在执行" + x[1] + "数据迁移！")
        cursor.execute(x[0])
        print("成功执行"+x[1]+"数据迁移！")

# 2、添加字段
sql = "select 'alter table' +table_name+' ADD recordyear INT;' as sql from " + FinalDataBase + "information_schema.tables"
list = cursor.fetchall()
print(list)

# 关闭数据库
conn.commit()
conn.close()