[大数据]Python 数据库建表（from mssql2mysql）

读取SQL server数据库表，在MySQL数据库创建对应表。

python代码（未测试所有可能情况）：

# -*- coding: utf-8 -*-

import pymssql
import pymysql
import pandas as pd

#构建数据库连接
conn_1 = pymssql.connect(host = '121.xxx.xx.xx',
                         port = 1433,
                         user = 'cdcreader',
                         password = '123456',
                         database = 'abc_car')
    
conn_2 = pymysql.connect(host = '192.168.xx.xxx',
                         port = 3306,
                         user = 'test',
                         password = '123456',
                         database = 'ods_1')

#数据类型映射（mssql-mmysql）
mssql_mysql = {
'bigint':'bigint',
'int':'int',
'tinyint':'tinyint',
'smallint':'smallint',
'bit':'boolean',
'decimal':'decimal',
'money':'decimal',
'smallmoney':'decimal',
'numeric':'numeric',
'float':'float',
'real':'float',
'date':'date',
'datetimeoffset':'timestamp',
'datetime2':'datetime(3)',
'datetime':'datetime(3)',
'smalldatetime':'timestamp',
'time':'time',
'char':'char',
'nchar':'varchar',
'varchar':'varchar',
'nvarchar':'varchar',
'text':'text',
'ntext':'text',
'xml':'text',
'binary':'binary',
'varbinary':'varbinary'
}

schema = "'dbo'"
sql_1 = """select object_id,name from sys.objects where type ='U' and schema_id=""" + \
    '(select schema_id from sys.schemas where name=' + schema + ')'
    
tables = pd.DataFrame(pd.read_sql(sql_1,conn_1))

for index, tb_row in tables.iterrows():
    
    object_id = tb_row.object_id
    tb_name = tb_row['name']
    
    if tb_name[:3] != 'pit':
        continue
    
    sql_2 = """select
    a.name as col,
    b.name as datatype,
    a.max_length,
    a.precision,
    a.scale,
    a.collation_name
    from sys.columns a join sys.types b on b.user_type_id=a.user_type_id
    where a.object_id=""" + str(object_id)
    
    tb_info = pd.DataFrame(pd.read_sql(sql_2,conn_1))
    
    sql_3 = """select
    c.name as kel_col
    from sys.indexes a
    JOIN sys.index_columns b on b.object_id=a.object_id and b.index_id=a.index_id
    JOIN sys.columns c on c.object_id=a.object_id and c.column_id=b.column_id
    WHERE a.is_primary_key=1 and a.object_id=""" + str(object_id)
    
    tb_keys = pd.read_sql(sql_3,conn_1).values.ravel()
    
    db_cols = []
    
    for index, i_row in tb_info.iterrows():
        
        datatype = mssql_mysql[(i_row.datatype)]
        
        if i_row.max_length > 2000:
            datatype = 'text'
        elif i_row.max_length == -1:
            datatype = 'text'
            i_row.max_length = 8000
            
        col = '`' + i_row.col + '`'
        
        if i_row.collation_name != None and (i_row.collation_name)[:7].lower() == 'chinese' \
        and (i_row.datatype)[0] == 'n':
            i_row.max_length = int(i_row.max_length/2)
            
        if i_row.precision == 0 and i_row.scale == 0:
            db_col = col + ' ' + datatype + '(' + str(i_row.max_length) + ')'
        elif datatype in ['decimal','numeric','money','smallmoney']:
            db_col = col + ' ' + datatype + '(' + str(i_row.precision) + ',' + str(i_row.scale) + ')'
        else:
            db_col = col + ' ' + datatype
            
    
            
        db_cols.append(db_col)
        
    if tb_keys == []:
        key_conf = ''
    else:
        key_conf = '\n,primary key (' + ','.join(tb_keys) + ')'
        
    ddl = 'create table if not exists ' + tb_name + '(' + '\n,'.join(db_cols) + key_conf + ')'
    
    print(ddl) 
    cur = conn_2.cursor()
    conn_2.ping(reconnect=True)
    #cur.execute(ddl)
        
conn_1.close()
conn_2.close()