将CSV数据插入MySQL数据库 - "您的SQL语法有错误;靠近 1 号线

Question

这是我得到的确切的错误消息:

(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 '' at line 1")

这是我的代码。

def mysql_connect():
    conn = MySQLdb.connect(host = "localhost",
                           user = "root",
                           passwd = "********",
                           db = "seomonster")
    cursor = conn.cursor()
    filename = 'tdnam_all_listings.csv'
    filename2 = 'tdnam_all_listings2.csv'
    filename3 = 'tdnam_all_listings3.csv'
    try:
        table_create = """CREATE TABLE sites (domainName CHAR(40),
itemID INT,auctionType CHAR(40),
timeLeft CHAR(40),
price CHAR(20),
bids INT,
domainAge INT,
traffic INT,
valuationPrice CHAR(40))"""
        cursor.execute(table_create)
    except Exception as e:
        print e

    try:
        csv_data = csv.reader(file(filename))
        for row in csv_data:
            cursor.execute("""INSERT INTO sites(domainName,itemID,auctionType,timeLeft,price,bids,domainAge,traffic,valuationPrice)""")
    except Exception as e2:
        print e2
    conn.commit()
    cursor.close()
    conn.close()

我完全不理解错误，虽然我认为它与我通过插入数据使用的SQL语法有关?

@bernie把我的代码改成这样:

cursor.execute("""INSERT INTO sites(domainName,itemID,auctionType,timeLeft,price,bids,domainAge,traffic,valuationPrice) values (%s%s,%s,%s,%s,%s,%s,%s,%s);""")

现在得到这个错误:

(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)' at line 1") 

@VMai我听从了你的建议，把我的代码改成了这样:

for row in csv_data:
    insert = """INSERT INTO sites (domainName,itemID,auctionType,timeLeft,price,bids,domainAge,traffic,valuationPrice) values (%s,%s,%s,%s,%s,%s,%s,%s,%s);"""
    cursor.execute(insert,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]))

我得到一个很长的错误，我认为这与%s的一些列是整数有关，这就是我认为的原因。错误:

Warning (from warnings module):
  File "/var/www/seomonster/ZeroPain.py", line 109
    cursor.execute(insert,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]))
Warning: Incorrect integer value: ' ItemID' for column 'itemID' at row 1
Warning (from warnings module):
  File "/var/www/seomonster/ZeroPain.py", line 109
    cursor.execute(insert,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]))
Warning: Incorrect integer value: ' Bids' for column 'bids' at row 1
Warning (from warnings module):
  File "/var/www/seomonster/ZeroPain.py", line 109
    cursor.execute(insert,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]))
Warning: Incorrect integer value: ' Domain Age' for column 'domainAge' at row 1
Warning (from warnings module):
  File "/var/www/seomonster/ZeroPain.py", line 109
    cursor.execute(insert,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]))
Warning: Incorrect integer value: ' Traffic' for column 'traffic' at row 1
Warning (from warnings module):
  File "/var/www/seomonster/ZeroPain.py", line 109
    cursor.execute(insert,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]))
Warning: Data truncated for column 'domainName' at row 1

Answer 1

我想你在第一个查询参数%s之后只缺少一个逗号，因为你有9个字段要填充，但最后只有8个参数。第一个和第二个%s将被连接，因为它们之间缺少逗号。

第二，正如在注释中已经说过的，将你的参数作为第二个参数传递给这个函数。

试试这个:

cursor.execute("""INSERT INTO sites (domainName,itemID,auctionType,timeLeft,price,bids,domainAge,traffic,valuationPrice) values (%s,%s,%s,%s,%s,%s,%s,%s,%s);""", (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]))