Inserting CSV data into MySQL database - "You have an error in your SQL syntax; near ' ' at line 1

Question

This is the exact error message I am getting:

(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")

And here is my code.

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()

I do not understand the error at all, although I assume it has something to do with the SQL syntax I am using by inserting the data?

@bernie Changed my code to this:

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

Now getting this error:

(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 I followed your advice and changed my code to this:

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]))

I am getting a long error which I assume has something to do with the %s's some of the columns's are integers that is why I think. Error:

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

What values are you inserting? Shouldn't it be 'INSERT INTO ... VALUES ...'? — nullptr, May 10 '14 at 20:50
ya the insert statement is not valid syntax. s/b e.g `insert into sites (col1,col2) values (%s,%s);` — mechanical_meat, May 10 '14 at 20:51
@Coder77 You've got to bind your row to the statement. Maybe http://stackoverflow.com/questions/16506643/inserting-variables-mysql-using-python-not-working can help you. — VMai, May 10 '14 at 21:02
@Coder77: You've got to map your data types. http://stackoverflow.com/questions/11665628/read-data-from-csv-file-and-transform-to-correct-data-type may help. — VMai, May 10 '14 at 21:40
@Coder77: And it seems to me, that your domainName CHAR(40) is a bit undersized for your data. Why don't you use VARCHAR(255) or such? — VMai, May 10 '14 at 21:46
Yeah, I fixed that. Now it is going through the long process of inserting that csv file into the database! — Coder77, May 10 '14 at 21:52
Have you considered using [`LOAD DATA INFILE`](http://dev.mysql.com/doc/en/load-data.html) or [mysqlimport](http://dev.mysql.com/doc/en/mysqlimport.html) for this task? — eggyal, May 13 '14 at 07:41

timo.rieber · Accepted Answer · 2014-05-10T21:19:43.090

1

I suppose your only missing one comma after your first query parameter %s as you have 9 fields to fill but only 8 parameters in the end. First and second %s will be concatenated because of the missing comma between them.

Second, as already stated in the comments, pass your arguments as second parameter to this function.

Try this:

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]))

edited May 10 '14 at 21:19

answered May 10 '14 at 21:05

timo.rieber

3,727
3
32
47

I suppose your file has a headline with column names which you have to skip as inserting these strings to numeric or integer fields is your problem described in your last edit. – timo.rieber May 10 '14 at 21:22
Yes, it does have a headline of all the column names. I will try to remove the first line and see what happens. – Coder77 May 10 '14 at 21:24
I am now getting this error: `Warning (from warnings module): File "/var/www/seomonster/script.py", line 108 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])) Warning: Data truncated for column 'domainName' at row 1` – Coder77 May 10 '14 at 21:29
Check the above comment. – Coder77 May 10 '14 at 21:30
You may provide some sample data in your question. I guess the domain name in your first row is longer than 40 characters. – timo.rieber May 11 '14 at 05:22

Inserting CSV data into MySQL database - "You have an error in your SQL syntax; near ' ' at line 1

1 Answers1