我试着生成一些代码来将excel数据插入mysql数据库,但是到目前为止我遇到了一些麻烦。上面说在/
(1064,“您的SQL语法有错误;请检查对应于您的MySQL服务器版本的手册,以获得在“=”附近使用的正确语法('Barcelona-Sants','Good',2.1331,41.3788,'NA','NA','NA','0h','Good',84,'NA','at line 1”)
有人能帮我吗?
'''
sql_insert = 'INSERT INTO {} (' .format(db_sheet)
for i in range(0,sheet.ncols):
sql_insert += 'item{},'.format(i)
sql_insert = sql_insert[:-1]
sql_insert += ') '
excel_data = list()
for r in range(1, sheet.nrows):
row_content = []
for j in range(0,sheet.ncols):
ctype = sheet.cell(r,j).ctype # å¤æåå
æ ¼æ°æ®ç±»å
cell = sheet.cell(r,j).value
if ctype==1: #å¦ææ¯å符串ï¼
cell = cell
elif ctype==2 and cell % 1==0: #å¦ææ¯æ´å½¢
cell = int(cell)
elif ctype==3: # å¦ææ¯æ¥æå
dt = xlrd.xldate_as_datetime(cell, book.datemode)
cell = dt.strftime('%Y/%M/%D %H:%M:%S')
elif ctype==4:
cell = True if cell==1 else False
row_content.append(cell)
values = tuple(row_content)
excel_data.append(row_content)
cursor.execute(sql_insert, values)
'''
print(sql_insert%values)
# ====>outputs
INSERT INTO air_quality_Nov2017 (item0,item1,item2,item3,item4,item5,item6,item7,item8,item9,item10,item11,item12,item13,item14) values = (Barcelona - Observ Fabra,Good,2.1239,41.4183,22h,Good,64,22h,Good,21,22h,Good,12,30/11/2018 23:00,1543615502)
while excuting in mysql via command lines, it gives the same error info.