python mssql bulk_Python批量插入SQL Server数据库

importpymssqlimportrandom__author__ = 'sryan'

classGenerateData:def __init__(self):print('init')

self.conn=None

self.cur=Nonedefconnect(self, host, user, password, database):try:

self.conn= pymssql.connect(host=host, user=user, password=password, database=database)

self.cur=self.conn.cursor()exceptException, e:printe.messagedefdisconnect(self):try:if self.conn is notNone:

self.cur.close()

self.conn.close()exceptException, e:printe.messagedef insert_script(self, str_insert, times=1):for i inrange(times):

self.cur.execute(str_insert)

@staticmethoddefbcustomer_mzn(cst_id, cur):

instr= "insert into BCUSTOMER_MZN (CST_ID, CST_CTT_ID, CST_GND_ID,"\+ "CST_NAME, CST_MEMBERSHIPNUM, CST_MOBILE, CST_DELETED_FLAG,CST_CREATION_DT,"\+ "CST_CREATIONUID, CST_UPDATE_DT, CST_UPDATEUID, CST_MNGT_CNT_ID) values ("\+ "'" + str(cst_id) + "'" + ","\+ str("1") + ","\+ str("1") + ","\+ "'" + "sryan_" + str(cur) + "'" + ","\+ "'" + "100" + str(cst_id) + "'" + ","\+ "'" + " " + "'" + ","\+ str("0") + ","\+ "SYSDATETIME()" + ","\+ "'" + "sryan" + "'" + ","\+ "SYSDATETIME()" + ","\+ "'" + "sryan" + "'" + ","\+ str("1")\+ ")"

returninstr

@staticmethoddefxcustomer_mzn(cst_id):

instr= "insert into XCUSTOMER_MZN (XCS_CST_ID,XCS_ADT_ID,XCS_MRT_ID,XCS_RVT_ID,XCS_IDCARDNUM) values ("\+ "'" + str(cst_id) + "'" + ","\+ str("1") + ","\+ str("2") + ","\+ str("1") + ","\+ "'" + "32060219800618" + str(random.randint(1111, 9999)) + "'"\+ ")"

returninstr

@staticmethoddefbcustomer_reward(cst_id):

instr= "insert into BCUSTOMERREWARD values ("\+ "'" + "100" + str(cst_id) + "'" + ","\+ str("1") + ","\+ str("2") + ","\+ "null" + ","\+ "null" + ","\+ "null" + ","\+ "null" + ","\+ "null"\+ ")"

returninstr

@staticmethoddefbmembership_mzn(cst_id):

instr= "insert into BMEMBERSHIP_MZN (MMB_ID,MMB_CST_ID,MMB_MEMBERSHIPNUM,"\+ "MMB_ISSUE_DT,MMB_START_DT,MMB_CREATION_DT,MMB_UPDATE_DT) values ("\+ "'" + str(cst_id) + "'" + ","\+ "'" + str(cst_id) + "'" + ","\+ "'" + "100" + str(cst_id) + "'" + ","\+ "SYSDATETIME()" + ","\+ "SYSDATETIME()" + ","\+ "SYSDATETIME()" + ","\+ "SYSDATETIME()"\+ ")"

returninstr

@staticmethoddefxmembership_mzn(cst_id):

instr= "insert into XMEMBERSHIP_MZN (XMB_MMB_ID,XMB_CREATION_DT,"\+ "XMB_CREATIONUID,XMB_UPDATE_DT,XMB_UPDATEUID) values("\+ "'" + str(cst_id) + "'" + ","\+ "SYSDATETIME()" + ","\+ "'" + "sryan" + "'" + ","\+ "SYSDATETIME()" + ","\+ "'" + "sryan" + "'"\+ ")"

returninstr#测试代码

d =GenerateData()

d.connect('xx.xxx.xx.xxx', 'xxx', 'xxxx', 'xxxx')

i=0for i in range(1, 100000):

cstid= 2000000 +i

insert=GenerateData.bcustomer_mzn(cstid, i)

d.cur.execute(insert)

insert=GenerateData.xcustomer_mzn(cstid)

d.cur.execute(insert)

insert=GenerateData.bcustomer_reward(cstid)

d.cur.execute(insert)

insert=GenerateData.bmembership_mzn(cstid)

d.cur.execute(insert)

insert=GenerateData.xmembership_mzn(cstid)

d.cur.execute(insert)

d.cur.execute("select * from XMEMBERSHIP_MZN where XMB_MMB_ID = 2000505")printd.cur.fetchall()

d.conn.commit()