Python insert MySQL _mysql_exceptions.ProgrammingError: (1064,
| 数据库结构 | |||||
|---|---|---|---|---|---|
| Field | Type | Null | Key | Default | Extra |
| +---------------+-------------+------+-----+---------+-------+ | |||||
| Device_Name | varchar(24) | YES | NULL | ||
| IP_Address | varchar(24) | YES | NULL | ||
| DRAC | varchar(24) | YES | NULL | ||
| Support_Group | varchar(24) | YES | NULL | ||
| Domain | varchar(24) | YES | NULL | ||
| OU | varchar(24) | YES | NULL | ||
| IEM | varchar(24) | YES | NULL | ||
| OS | varchar(24) | YES | NULL | ||
| OS_Version | varchar(24) | YES | NULL | ||
| Environment | varchar(24) | YES | NULL | ||
| Risk | varchar(24) | YES | NULL | ||
| Function | varchar(24) | YES | NULL | ||
| App | varchar(24) | YES | NULL | ||
| Owner | varchar(24) | YES | NULL | ||
| Owner_mail | varchar(24) | YES | NULL | ||
| Country | varchar(24) | YES | NULL | ||
| City | varchar(24) | YES | NULL | ||
| Site | varchar(24) | YES | NULL | ||
| Location | varchar(24) | YES | NULL | ||
| Category | varchar(24) | YES | NULL | ||
| Manufacturer | varchar(24) | YES | NULL | ||
| Model | varchar(24) | YES | NULL | ||
| Rack | varchar(24) | YES | NULL | ||
| Power | varchar(24) | YES | NULL | ||
| Serial | varchar(24) | YES | NULL | ||
| Warranty | varchar(24) | YES | NULL | ||
| Decomm_Comm | varchar(24) | YES | NULL | ||
| Project | varchar(24) | YES | NULL | ||
| Manager | varchar(24) | YES | NULL | ||
| Comm_data | varchar(24) | YES | NULL | ||
代码
!/bin/python
import os
import xlrd
import MySQLdb
import re
DB_HOST='127.0.0.1'
DB_USER='root'
DB_PASS='Jun06jun'
DB_NAME='list'
db=MySQLdb.connect(host=DB
HOST,user=DB
USER,passwd=DB
PASS,db=DB
NAME)
cursor=db.cursor()
a=[]
os.chdir('/home/ran/Desktop')
data=xlrd.open_workbook('list.xlsx')
table=data.sheets()[0]
nrows=table.nrows
for i in range(nrows):
c = table.row_values(i)
print len(c)
for i_row in range(len(c)):
if c[i_row] == '': c[i_row] = "NULL"
sql="insert into JLL values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) % (c[0],c[1],c[2],c[3],c[4],c[5],c[6],c[7],c[8],c[9],c[10],c[11],c[12],c[13],c[14],c[15],c[16],c[17],c[18],c[19],c[20],c[21],c[22],c[23],c[24],c[25],c[26],c[27],c[28],c[29])"
cursor.execute(sql)
Excel 中某一列数据
AUCFD317-Joey
x.x.x.x
VMWare
Joey
Linux
N/A
N/A
Linux
Linux (MLOS)- RHEL 5 64bit Patch 3
Production
NULL
ePO Server
McAfee GTI Proxy Appliance 2.0.0
x
x
x
x
x
Server Room
Server-Virtual
VMware, Inc.
VMware Virtual Platform
NULL
NULL
Vmware
N/A - VM
NULL
NULL
NULL
NULL
发生的错误
mysql
exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s' at line 1")
python-xlrd python python-mysqldb
蓬萊山輝月
9 years, 10 months ago
Answers
错误:
sql="insert into JLL values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) % (c[0],c[1],c[2],c[3],c[4],c[5],c[6],c[7],c[8],c[9],c[10],c[11],c[12],c[13],c[14],c[15],c[16],c[17],c[18],c[19],c[20],c[21],c[22],c[23],c[24],c[25],c[26],c[27],c[28],c[29])"
正确:
sql="insert into JLL values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (c[0],c[1],c[2],c[3],c[4],c[5],c[6],c[7],c[8],c[9],c[10],c[11],c[12],c[13],c[14],c[15],c[16],c[17],c[18],c[19],c[20],c[21],c[22],c[23],c[24],c[25],c[26],c[27],c[28],c[29])
参考 @displaynone 的来个简化版，python的语法糖真多
sql="insert into JIL values(%s)" % (','.join('"' + str(item) + '"' for item in c))
活到老宅到老
answered 9 years, 10 months ago