python-mysqldb - Python insert MySQL _mysql_exceptions.ProgrammingError: (1064,

Question

数据库结构
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=DBHOST,user=DBUSER,passwd=DBPASS,db=DBNAME)
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

发生的错误
mysqlexceptions.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")

Answer 1

오류:

으아악

정확함:

으아악

@displaynone의 단순화된 버전을 참조하세요. Python에는 많은 구문 설탕이 있습니다

으아악

Answer 2

문자열이 제대로 할당되지 않아서 간략한 버전으로 포스팅하겠습니다.

으아악

간단한 버전(왜 귀찮게 %s을 사용하나요? 하하):

으아악