python数据库多字段插入

# -*- co;ding: utf-8 -*-
#企业详细信息写入数据库+征信得分
import pymysql
from impala.dbapi import connect

conn = pymysql.connect(host='10.1.2.90', port= 8066,user='root',passwd='123456',db='mq_sale_disc',charset="utf8")
cur = conn.cursor()

#查询被推荐企业
sqlid="select a.ENTERPRISE_ID from tm_enterprise_info a"
cur.execute(sqlid)
idlist=cur.fetchall()
#print(idlist)
companyid=[-1]
for comid in idlist:
companyid.append(comid[0])
#print(companyid)

sql1=("select distinct tr_customer_recommend.ENTERPRISE_ID from tr_customer_recommend where tr_customer_recommend.ENTERPRISE_ID not in " + (str(companyid)).replace('[','(').replace(']',')'))
#print(sql1)
cur.execute(sql1)
#cur.close()
#conn.close()

#得到企业ID
IDrows=cur.fetchall()
#print(IDrows)

#不重复的ID企业数据写进库

for idrow in IDrows:
print(idrow)
#cong 数据库取出该数据
#####################链接mysql找到idrow匹配的公司名称
conn = pymysql.connect(host='10.1.2.90', port= 8066 ,user='root',passwd='123456',db='mq_sale_disc',charset="utf8")
cur = conn.cursor()
# print(idrow[0])
sql1="SELECT t.ENTERPRISE_NAME from tmp_enterprise t where t.ENTERPRISE_ID = " + str(idrow[0])
cur.execute(sql1)
results1 = cur.fetchall()
print("********************",results1)
for row in results1:
name=row[0]
# cur.close()
# conn.close()
# print("*****************!!!!!!!",name)
conn1 = connect(host="10.1.2.72", port=10000,database="moreqinrdb")
cursor = conn1.cursor()
sqlx = "SELECT COMPANY_NAME, UNIFIED_SOCIAL_ID, COMPANY_TYPE, BUILD_DATE,ISSUE_DATE, REGISTRATION_AUTHORITY, ADDRESS, BUSINESS_SCOPE from COMPANY_BUSINESS_INFO where COMPANY_NAME=\'"+name+"\'"
# print(sqlx)
cursor.execute(sqlx)
#把数据放进数据库
rows=cursor.fetchall()
print(rows)
# VALUteS=[]
data1=[]
data2=[]
data3=[]
data4=[]
data5=[]
data6=[]
data7=[]
data8=[]
for row in rows:
if row[0] == '-':
data1= ''
else :
data1=row[0]
if row[1] == '-':
data2 = 0
else:
data2 = row[1]
if row[2]=='-' :
data3= ''
else:
data3 = row[2]

if row[3]=='' or row[3]== None :
data4 = ''
else:
data4 = row[3]

if row[4]== '' or row[4]==None:
data4 = ''
else :

data5 = row[4]
if row[5]=='':
data6 =''
else:
data6 = row[5]
if row[6]=='-':
data7=''
else:
data7 = row[6]
if row[7] == '-' :
data8 = ''
else:
data8 = row[7]
data=''

VALUetS = (idrow[0],data1,data2,data3,data4,data5,data6,data ,data7,data8)

cur.execute("INSERT INTO tm_enterprise_info(ENTERPRISE_ID,ENTERPRISE_NAME,UNIFORM_CODE,ENTERPRISE_TYPE,ESTABLISH_DATE,RELEASE_DATE,REGISTER_DEPT,TEL,ADDRESS,BUSINESS_SCOPE,CREATE_TIME,UPDATE_TIME) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW(),NOW())",VALUetS)

######################################################################################################改字段
#把征信放进数据库
cur.execute("select tm_enterprise_credit.CREDIT_SCORE from tm_enterprise_credit where tm_enterprise_credit.ENTERPRISE_ID =" +str(idrow[0]))
rows=cur.fetchall()
print(rows)
for row in rows:
value2=rows[0]
value4 = value2[0]
value3=idrow[0]
print(value4)
print(value3)
cur.execute("update tm_enterprise_info SET CREDIT_SCORE= %s WHERE ENTERPRISE_ID= %s",(value4,value3))
cursor.close()
conn1.close()
conn.commit()
cur.close()
conn.close()

 

转载于:https://www.cnblogs.com/wyj690/p/5386991.html