python(19) : sqlserver数据克隆

只克隆数据, 需要声明表名和表字段

注意: 会清除目标表的数据

代码

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

# ====> 从源数据库克隆到目标数据库 <==== #

import pyodbc

driver = '{' + pyodbc.drivers()[0] + "}"

source_conn = pyodbc.connect(
    f'DRIVER={driver};SERVER=127.0.0.1,1433;DATABASE=test;UID=root;PWD=123456')
source_cursor = source_conn.cursor()

target_conn = pyodbc.connect(
    f'DRIVER={driver};SERVER=127.0.0.1,1433;DATABASE=prod;UID=root;123456')
target_cursor = target_conn.cursor()

tables = [
    ['t1', 'id,create_time,modify_time,name,age'],
    ['t2', 'id,create_time,modify_time,name2,age2'],
]

# 清空目标表
for t in tables:
    target_cursor.execute(f'truncate table {t[0]}')
target_conn.commit()

# 克隆数据
for t in tables:
    target_cursor.execute(f'set identity_insert {t[0]} on')
    source_cursor.execute(f'SELECT {t[1]} FROM {t[0]}')
    rows = source_cursor.fetchall()
    print(f'[{t[0]}]-{len(rows)}')
    data = []
    f_count = len(t[1].split(','))
    wh = ",".join(["?"] * f_count)
    for row in rows:
        d = ()
        for r in row:
            d = d + (r,)
        data.append(d)
    target_cursor.executemany(f'INSERT INTO {t[0]} ({t[1]}) VALUES ({wh})', data)
    target_cursor.execute(f'set identity_insert {t[0]} off')

# 提交并关闭连接
target_conn.commit()
target_cursor.close()
target_conn.close()

source_cursor.close()
source_conn.close()

依赖

pip3 install pyodbc -i https://mirrors.aliyun.com/pypi/simple/ requests