Syntax error using INSERT INTO statement with MySQL-Python

Question

I'm trying to populate a table in a MySQL database with the values for each row coming from a text file, using this SQL syntax:

INSERT INTO Table SET
  fieldname1 = "value1",
  fieldname2 = "value2",
  fieldname3 = "value3";

I used a nested for loop to print a string (named SQL3) that uses the same field names for every line of the text file, trying to take care of the syntax in the right way. Seems like I'm getting close but it still throws a SQL syntax error at me, and I seem blind for what might be going on. There might be still more issues hidden underneath as it's the first time trying to connect to a db through Python.

Hopefully someone can help find the issue(s). Is some important info missing?

This is the code:

import MySQLdb 

fieldNames = """Org_rowNr_countsheet
Orig_row_10604
pt3
pt3_ensembl_id
status
pt2_meth
pt3_meth
pt2_kegg_id
pt2_uniprotID
pt3_kegg_hit
pt3_uniprot
pt2_contig
pt2_start
pt2_stop
pt2_strand
pt3_contig
pt3_start
pt3_stop  
pt3_strand
"""
fieldnames = fieldnames.strip().split("\n")

myconnection = MySQLdb.connect(host = "localhost", user = 'root', passwd = "mock", db="pt3_annot")
mycursor = myconnection.cursor()

infilename = "/home/oaklander114/winshare/mysql_pt3/pt3_annot_ids_reduced.csv"
infile =  open(infilename, 'r')
linenumber = 0 

for line in infile:
    if linenumber > 0:
    line =  line.strip("\n\r")
    fields = line.split(',')
    print 'INSERT INTO ids SET '
    for i, fieldname in enumerate(fieldnames):
        query = []
        if i < 18:
            SQL1 = """
             %s = '%s',
             """ % (fieldname, fields[i])
            query.append(SQL1)
        else:
            SQL2 = """
             %s = '%s';
             """ % (FieldName, Fields[i])
            query.append(SQL2)
        SQL3 = " ".join(query)
        print SQL3
        mycursor.execute(SQL3)

    linenumber += 1
infile.close()
mycursor.close()
myconnection.commit()
myconnection.close()

This is the error message:

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 'Org_rowNr_countsheet = '10578',' at line 1")

I thought it would be useful to show the print out of the syntax at the point of the error as the script seems to be generating it (looks right to me):

INSERT INTO ids SET
  Org_rowNr_countsheet = '10579',
  Orig_row_10604 = '',
  pt3 = '300002',

etc... But the error relates to the first line. Here is a more detailed error report:

%run /home/oaklander114/winshare/mysql_pt3/insert_rows_ids.py
INSERT INTO ids SET
Org_rowNr_countsheet='10578',
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
/home/oaklander114/Canopy/appdata/canopy-1.4.1.1975.rh5-x86_64/lib/python2.7/site-   packages/IPython/utils/py3compat.pyc in execfile(fname, *where)
202             else:
203                 filename = fname
--> 204             __builtin__.execfile(filename, *where)

/home/oaklander114/winshare/mysql_pt3/insert_rows_ids.py in <module>()
 52             SQL3 = " ".join(query)
 53             print SQL3
---> 54             MyCursor.execute(SQL3)
 55 
 56     LineNumber += 1

/home/oaklander114/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/MySQL_python-1.2.5-    py2.7-linux-x86_64.egg/MySQLdb/cursors.pyc in execute(self, query, args)
203             del tb
204             self.messages.append((exc, value))
--> 205             self.errorhandler(self, exc, value)
206         self._executed = query
207         if not self._defer_warnings: self._warning_check()

/home/oaklander114/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/MySQL_python-1.2.5-py2.7-linux-x86_64.egg/MySQLdb/connections.pyc in defaulterrorhandler(***failed resolving arguments***)
 34     del cursor
 35     del connection
---> 36     raise errorclass, errorvalue
 37 
 38 re_numeric_part = re.compile(r"^(\d+)")

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 'Org_rowNr_countsheet='10578',' at line 1") 

These are the columns of the ids table:

mysql>  SHOW COLUMNS FROM ids;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| Org_rowNr_countsheet | varchar(255) | NO   | PRI | NULL    |       |
| Org_row_10604        | varchar(255) | YES  |     | NULL    |       |
| pt3                  | varchar(255) | YES  |     | NULL    |       |
| pt3_ensembl_id       | varchar(255) | YES  |     | NULL    |       |
| status               | varchar(255) | YES  |     | NULL    |       |
| pt2_meth             | varchar(255) | YES  |     | NULL    |       |
| pt3_meth             | varchar(255) | YES  |     | NULL    |       |
| pt2_kegg_id          | varchar(255) | YES  |     | NULL    |       |
| pt2_uniprotID        | varchar(255) | YES  |     | NULL    |       |
| pt3_kegg_hit         | varchar(255) | YES  |     | NULL    |       |
| pt3_uniprot          | varchar(255) | YES  |     | NULL    |       |
| pt2_contig           | varchar(255) | YES  |     | NULL    |       |
| pt2_start            | varchar(255) | YES  |     | NULL    |       |
| pt2_stop             | varchar(255) | YES  |     | NULL    |       |
| pt2_strand           | varchar(255) | YES  |     | NULL    |       |
| pt3_contig           | varchar(255) | YES  |     | NULL    |       |
| pt3_start            | varchar(255) | YES  |     | NULL    |       |
| pt3_stop             | varchar(255) | YES  |     | NULL    |       |
| pt3_strand           | varchar(255) | YES  |     | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+
19 rows in set (0.00 sec)

Answer 1

Your code has a number of issues, both functional and stylistic. For the style issues, I recommend that you read PEP 8 -- Style Guide for Python Code. Specifically, reserving CamelCase for packages, modules and classes will result in more appropriate syntax highlighting here on StackOverflow.

You say in comments that you found the spelling error in your FieldNames string, but the INSERT statement was still silently failing until you switched to the VALUES syntax. I don't see the INSERT syntax variation with SET used very often, but it is correct and it does work using MySQL-Python:

>>> curs.execute('create temporary table tbl (col1 varchar(10), col2 varchar(10))')
0L
>>> statement = r"""
... INSERT INTO tbl SET
...   col1 = "value1",
...   col2 = "value2";
... """
>>> curs.execute(statement)
1L
>>> curs.execute("select * from tbl")
1L
>>> curs.fetchall()
(('value1', 'value2'),)

So the problem is somewhere else, either in your code or in your expectations. Unless you give a clear description of this new problem (and most likely that would involve rewriting much of your question), I can't say exactly what's going wrong. However, I can point out some of the problems with your approach; fixing these issues may lead you to your solution.

You waste an awful lot of space building your query from a jumble of strings, when you should be writing parameterized queries instead. Forget all that back and forth from strings to lists and figuring out when you need a comma or a semicolon; just write your query with %s as the placeholder for each value and provide those values as the second argument to the execute method, as shown in the User's Guide.

It's also a waste to parse the csv file yourself instead of simply using a standard library module for parsing csv files and you will write much better code if you familiarize yourself with the with statement for use with file objects (among other things). Here's a less error-prone way to do what you're trying to do:

import csv
import MySQLdb 

INSERT_STATEMENT = """
  INSERT INTO ids
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
            %s, %s, %s, %s, %s, %s, %s, %s, %s);
"""

INPUT_PATH = '/home/oaklander114/winshare/mysql_pt3/pt3_annot_ids_reduced.csv'

connection = MySQLdb.connect(host='localhost', user='root', passwd='mock',
                             db='pt3_annot')

with open(INPUT_PATH, 'r') as input_file, connection as cursor:
    reader = csv.reader(input_file)
    next(reader, None)  # skip the header row
    for row in reader:
        cursor.execute(INSERT_STATEMENT, row)

connection.close()

Notes on function:

• The with keyword and csv module are essentials to learn if you're serious about Python. Parsing a csv file is the sort of basic task that you'll be asked to demonstrate in job interviews, where you won't have the luxury of taking your time and writing a hundred lines of code.
• Make sure you understand code before you use it, and if you're having trouble, use code you do understand in the meantime. For example, next(reader, None) doesn't know there's a header row; I do, from reading your code. If you use this exact code with a file that has no header, it'll be buggy.
• Creating MySQLdb cursors this way, using the with keyword, automatically commits a transaction at the end of the with block. If you want to do separate transactions, do them in separate with blocks.
• On the other hand, the cursor object in this code isn't closed until the program ends – but that's not really an issue given the way MySQLdb implements cursors. If you want to read more, my answer to When to close cursors using MySQLdb addresses the subject in some depth.
• You could explicitly list the column names for the INSERT_STATEMENT as a tuple between ids and VALUES. I left them out to save space in the answer but that does leave the order of the columns implicit in both the SQL statement and the input file. If you want to reuse this code, it might be safer to make the column list explicit.

Notes on style:

• Separating the SQL statement from the triple-quote delimiters in this way makes it easier to distinguish visually from your Python code. It also makes selecting the statement easier if you like to copy-and-paste between an SQL client application for testing purposes.
• Splitting the values list where I did makes it slightly easier to count the number of placeholders – you only have to manually count the first row of 10 to see that there are 19 in total.
• It might be more readable and maintainable to construct your MySQL statement in a way that explicitly identifies the number of placeholders without having to count them manually (e.g., 'INSERT INTO ids VALUES ({});'.format(','.join(['%s'] * 19)). Note that this approach would still rely on the cursor's execute method to properly handle the values to be inserted, which is very important.
• This example doesn't follow every guideline in PEP 8, mostly to keep the length down, but it follows most of them (and so should you).

Answer 2

2 things. I would take the if block out of your loop and be sure to prepend proper SQL.

SQL3 = 'UPDATE table SET ' + " ".join(query) + ';'  #don't forget WHERE clause as well...

or if you're inserting...your valid INSERT statement

are you encountering a blank line in your file? maybe try....

instead of this(which is not formatted properly in your question fyi)

for line in InFile:
    if LineNumber > 0:
    line =  line.strip("\n\r")
    #print line
    Fields = line.split(',')

try this:

for line in InFile:
    line =  line.strip("\n\r")
    if LineNumber > 0 and line != '':
        #print line
        Fields = line.split(',')
        #....rest of your code...