python sql server 批量入库_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()