Python连接Sqlserver操作

Zss 发表于:

需要持续的插入数据来测试数据库同步的效率怎么样

使用四个线程,分别来做源端插入,源端查询,临时表查询,目的端查询来看数据的增长速度

#coding:utf-8
import pymssql
import time,uuid
from threading import Thread

def src_insert():
    server = "192.168.3.221"
    user = "sa"
    password = "Anmit_2005"
    conn = pymssql.connect(server, user, password, "test")
    cur = conn.cursor()

    for i in range(1,100000):
        index = '1'
        index = index + str('%07d'%i)
        sql = '''INSERT INTO test.dbo.bdc_fwsx(幢ID,不动产单元号,房屋编码,要素代码,自然幢号,层号,房屋坐落,面积单位,实际层数,户号,室号部位,户型,户型结构,房屋用途1,房屋用途2,房屋用途3,建筑面积,套内建筑面积,分摊建筑面积,地下部分建筑面积,其它建筑面积,分摊系数,共有土地面积,分摊土地面积,独用土地面积,房屋类型,房屋性质,状态,栋号,单元号,单元名称,总层数,物理层,名义层,房屋用途,房屋结构,幢编码,宗地代码,测绘状态,分割合并状态,区县,街道,街坊,街路巷,门牌号,东至,南至,西至,北至,备注,房屋来源,房屋所有者,房屋总价,户编号,房屋编号,房屋所有权人,权利人类型,证件种类,证件号码,电话,住址,邮政编码,产权来源,产别,共有情况,规划用途,专有建筑面积,附加说明,调查意见,总套数,竣工日期,项目名称,BMFHOUSEID,土地用途,权利性质,土地使用期限,土地使用权起始时间,土地使用权结束时间,ROOMID,不动产编号,分户图比例尺,分摊占地面积,分摊宗地面积,ISXMNDZ,项目号,业务办理情况,拆迁限制,XZDJ,预测建筑面积,预测套内建筑面积,预测分摊建筑面积,测量号,FH,ISUNIONHOUSE,整合单位,BOUNDBDCDYH,推送日期,XZXZQSSJ,XZXZZZSJ,XZXZSM,XZQXXZSM,数据推送情况,msrepl_tran_version) VALUES (11397,'410602302001GB00001F00010110','%sGB0000100010529019','','430602302001GB00001F0001','','岳阳市南湖新区求索街道南湖游路212号天鹅湖三期5栋2902','1','0.00','2901','2901','','1','','','','124.65','98.45','26.20','0.00',7.46,0.266155,7.040,7.040,0.000,1,0,0,003,1,'一单元',29,29,29,11,3,'02302001GB00001000105','430602302001GB00001',1,0,NULL,NULL,NULL,'','','共有墙','共有墙','共有墙','共有墙','',1,'',0.0000,1,NULL,'岳阳市恒瑞置业有限公司',2,6,'67559251-8',NULL,NULL,414000,6,'','',11,0.00,'','',113,NULL,'天鹅湖三期5栋',481244020003,071,102,'','2015-07-01','2085-06-30',NULL,'','',0.000,0.000,NULL,NULL,'SYQZT-0103-201709110239711-1,',NULL,NULL,NULL,NULL,NULL,481244020003,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s')'''%(index,uuid.uuid1())
        cur.execute(sql)
        conn.commit()

def src_select():
    server = "192.168.3.221"
    user = "sa"
    password = "Anmit_2005"
    conn = pymssql.connect(server, user, password, "test")
    cur = conn.cursor()
    while 1:
        #cur.fetchall()获取所有未获取的记录,cursor.fetchone()获取第一条记录,cursor.fetchmany(i)获取前i条记录
        cur.execute('SELECT COUNT(房屋编码) FROM [test].[dbo].[bdc_fwsx]')
        number1 = int(cur.fetchall()[0][0])
        time.sleep(1)
        cur.execute('SELECT COUNT(房屋编码) FROM [test].[dbo].[bdc_fwsx]')
        number2 = int(cur.fetchall()[0][0])
        print '源端: 数量:%d条  每秒增长数:%d'%(number2,number2-number1)

def src_ds_select():
    server = "192.168.3.221"
    user = "sa"
    password = "Anmit_2005"
    conn = pymssql.connect(server, user, password, "test")
    cur = conn.cursor()
    while 1:
        #cur.fetchall()获取所有未获取的记录,cursor.fetchone()获取第一条记录,cursor.fetchmany(i)获取前i条记录
        cur.execute('SELECT COUNT(DS_ACTIONE) FROM [test].[dbo].[DS_bdc_fwsx]')
        number1 = int(cur.fetchall()[0][0])
        time.sleep(1)
        cur.execute('SELECT COUNT(DS_ACTIONE) FROM [test].[dbo].[DS_bdc_fwsx]')
        number2 = int(cur.fetchall()[0][0])
        print '临时表: 数量:%d条  每秒增长数:%d'%(number2,number2-number1)

def dst_select():
    server = "192.168.3.221"
    user = "sa"
    password = "Anmit_2005"
    conn = pymssql.connect(server, user, password, "zss")
    cur = conn.cursor()
    while 1:
        #cur.fetchall()获取所有未获取的记录,cursor.fetchone()获取第一条记录,cursor.fetchmany(i)获取前i条记录
        cur.execute('SELECT COUNT(房屋编码) FROM [zss].[dbo].[bdc_fwsx]')
        number1 = int(cur.fetchall()[0][0])
        time.sleep(1)
        cur.execute('SELECT COUNT(房屋编码) FROM [zss].[dbo].[bdc_fwsx]')
        number2 = int(cur.fetchall()[0][0])
        print '目的端: 数量:%d条  每秒增长数:%d'%(number2,number2-number1)

if __name__ == '__main__':
    T1 = Thread(target=src_insert)
    T1.start()
    T2 = Thread(target=src_select)
    T2.start()
    T3 = Thread(target=src_ds_select)
    T3.start()
    T4 = Thread(target=dst_select)
    T4.start()