想过很多总方法,这种方法最实在,有兴趣的可以看下,思路:先读取csv,在去数据库数据库读取,存在就直接写入数据,不存在着直接复制原来的数据。还有一个没有时间去告,读取csv文件为null,写入新的csv文件默认为“nan”值。
def mysqlread(ISBN):
# print(tables)
# 打开数据库连接
db = pymysql.connect("xxx.xxx.xx.xxx", "**", "**", "**")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 查询
sql = sql = "select replace(ISBN,'-','') as 'ISBN','' as '供应商产品ID',dj as '定价',BookName as '书名','' as '最新印次' ,cbd as '出版地',Publishing_house as '出版者',nr as '内容提要','' as '读者范围',ztffl as '中图分类','' as '字数',bc as '版次','' as '责任编辑',author as '著者','' as '译者','' as '编者','' as '定价单位','' as '版权登记号','' as '教材征订号',zdfs as '装帧','' as '成书高度','' as '成书高度单位','' as '成书宽度','' as '成书宽度单位','' as '成书厚度','' as '成书厚度单位','' as '单个重量','' as '单个重量单位','' as '附件类型','' as '附件数量',CBookName as '丛套书题名','' as '丛套书顺序号','' as '附属丛套书题名', '' as '并列丛套书题名','' as '丛套书作者','' as '副书名','' as '并列书名','' as '引进版图书原题名','' as '作者(提供者)简介',zwyz as '语种','' as '正文页数','' as '图幅数','' as '关键词','' as '营销分类','' as '目录信息','' as '合作出版者','' as '原书出版者','' as '原出版地',case WHEN LENGTH(cbsj)=7 THEN CONCAT(REPLACE(cbsj,'.',''),'01') WHEN LENGTH(cbsj)=6 THEN CONCAT(REPLACE(cbsj,'.','0'),'01') WHEN LENGTH(cbsj)=5 THEN REPLACE(cbsj,'.','0101') ELSE cbsj END as '出版时间','' as '商品介质'from cipInfo_JX where replace(ISBN,'-','')='{}' ORDER BY cip DESC"
sql=sql.format(ISBN)
try:
# 执行sql语句
cursor.execute(sql.encode('utf-8'))
# result = cursor.fetchall()## 获取所有查询数据
result = cursor.fetchone()# 获取单条查询数据
except:
# 如果发生错误则回滚
db.rollback()
finally:
# 关闭数据库连接
db.close()
return result
def MianStart(filename):
with open(filename,mode='a+',newline='',encoding='utf_8_sig') as f:
write = csv.writer(f,dialect='excel')
write.writerow(['ISBN','供应商产品ID','定价','书名','最新印次','出版地','出版者','内容提要','读者范围','中图分类','字数','版次','责任编辑','著者','译者','编者 ','定价单位','版权登记号','教材征订号','装帧','成书高度','成书高度单位','成书宽度','成书宽度单位','成书厚度','成书厚度单位','单个重量','单个重量单位','附件类型','附件数量','丛套书题名','丛套书顺序号','附属丛套书题名','并列丛套书题名','丛套书作者','副书名','并列书名','引进版图书原题名','作者(提供者)简介','语种','正文页数','图幅数','关键词','营销分类','目录信息','合作出版者','原书出版者','原出版地','出版时间','商品介质'])
pData = pd.read_csv(r"./wys.csv", header=0, encoding='utf-8')
for i in range(0,len(pData)):
data=mysqlread(pData.at[i,'ISBN'])
print(pData.at[i,'ISBN'])
if data!=None:
write.writerow(data)
else:
write.writerow(pData.loc[i])
# for item in data:
# write.writerow(item)
if __name__ == "__main__":
MianStart(os.path.abspath(os.curdir)+"\\xxxx"+time.strftime("%Y%m%d%H%M%S", time.localtime())+'.csv')#生成到当前目录下面