Forums

I have a CSV file that that I'm reading line by line and inserting the data into a database. Well..I'm trying to insert the data into the database, but I'm getting an error that I cannot figure out. The relevant portion of my code is listed below. Any ideas?

for line in flines:
    line = line.rstrip().split(",")
    if line[0] != "":
        newlist = []
        if len(line) > 7:
            new2 = listaddr(line[1], line[2:-5])
            newlist.extend([str(line[0]).rstrip(), new2.rstrip(), str(line[-5]).rstrip(), str(line[-4]).rstrip(), int(str(line[-3]).rstrip()), int(str(line[-2]).rstrip()), str(line[-1]).rstrip()])
        else:
            newlist.extend([str(line[0]).rstrip(), str(line[1]).rstrip(), str(line[2]).rstrip(), str(line[3]).rstrip(), int(str(line[4]).strip()), int(str(line[5]).rstrip()), str(line[6]).rstrip()])
            if newlist[1] is not None:
                if newlist[1].startswith('"'):
                    newlist[1] = newlist[1][1:].rstrip()
                if newlist[1].endswith('"'):
                    newlist[1] = newlist[1][:-2].rstrip()
        tuplist = tuple(newlist)
        cur.execute("INSERT INTO bom (Part_No, Description, Model, Type, Per_Eng, Cntr_Qty, Location) VALUES(%s, %s, %s, %s, %d, %d, %s)" % tuplist)

The error I'm getting is:

2014-12-02 07:53:19,031 :_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 \'0000, "BODY COMP,OIL JET", FR1B, AC1, 2, 300, J108)\' at line 1')

The first element of the tuple is getting cutoff. It should read "15280RZP 0000", but it looks like MySQL is only picking up the last four charters. Except for that, everything else looks the way it should. I snipped some code where I'm writing each element in the tuple to a text file, and when I check that file, all of the elements in the tuple show up correctly, so I'm not sure what it is I'm doing incorrectly here.

Hmm. One thing I'd recommend you change is how you execute that statement -- right now you're using Python string formatting to interpolate your insertion values into the SQL, which (depending on the data) might be the cause of your problem -- and also runs the risk of leaving you open to SQL injection attacks.

The MySQL Python library makes it easy to avoid that -- just replace your last line with the following, and see it if works:

cur.execute("INSERT INTO bom (Part_No, Description, Model, Type, Per_Eng, Cntr_Qty, Location) VALUES(%s, %s, %s, %s, %d, %d, %s)", tuplist)

So the single parameter with the % interpolation is replaced with two parameters, the string and the tuple.

Well, yes. I know, and I guess that's another question altogether. When I try to execute the query the safe way, I get another error. This one says there's an error "near (%s,%s,%s,%s,%d,%d,%s)". The only way I can get it to do anything different is if I use the string formatting.

Looking at your original code and the error more closely, it was caused by the fact that you were using %s to interpolate the strings into your SQL code. That meant that (for example) the last parameter was being put into the SQL query without quotes, which of course made it invalid SQL.

I think the new problem is due to the Python MySQL library having slightly different interpolation semantics; you don't need to specify types, it handles that all for you -- so you just need to use %s in the string, rather than using %d for numbers. This is a good thing, as your tuplist is a tuple of strings so the penultimate and antepenultimate items in it will be strings rather than integers....

Given that, I think you can get it to work by taking advantage of that and doing this:

cur.execute("INSERT INTO bom (Part_No, Description, Model, Type, Per_Eng, Cntr_Qty, Location) VALUES(%s, %s, %s, %s, %s, %s, %s)", tuplist)

One other thing you might want to take a look at is using the Python csv module which could do a lot of the work you're doing in your code to generate tuplist.

Ok, so I changed things to use all %s and I'm getting the same error. Also, I did look into the csv module and it really did save me from having to do all of the crazy work I was doing. So thanks for that. See below.

2014-12-03 04:44:05,174 :  File "/home/sjk2712/testapp/testapp.py", line 78, in upload_file
2014-12-03 04:44:05,175 :    updateBOM(filename)
2014-12-03 04:44:05,175 :  File "/home/sjk2712/testapp/testapp.py", line 129, in updateBOM
2014-12-03 04:44:05,176 :    cur.execute("INSERT INTO bom (Part_No, Description, Model, Type, Per_Eng, Cntr_Qty,     Location) VALUES (%s, %s, %s, %s, %s, %s, %s)", tuplist)
2014-12-03 04:44:05,176 :  File "/home/sjk2712/.local/lib/python3.3/site-packages/MySQLdb/cursors.py", line 184, in execute
2014-12-03 04:44:05,180 :    self.errorhandler(self, exc, value)
2014-12-03 04:44:05,180 :  File "/home/sjk2712/.local/lib/python3.3/site-packages/MySQLdb/connections.py", line 37, in defaulterrorhandler
2014-12-03 04:44:05,182 :    raise errorvalue
2014-12-03 04:44:05,182 :  File "/home/sjk2712/.local/lib/python3.3/site-packages/MySQLdb/cursors.py", line 171, in execute
2014-12-03 04:44:05,183 :    r = self._query(query)
2014-12-03 04:44:05,183 :  File "/home/sjk2712/.local/lib/python3.3/site-packages/MySQLdb/cursors.py", line 330, in _query
2014-12-03 04:44:05,184 :    rowcount = self._do_query(q)
2014-12-03 04:44:05,184 :  File "/home/sjk2712/.local/lib/python3.3/site-packages/MySQLdb/cursors.py", line 294, in _do_query
2014-12-03 04:44:05,185 :    db.query(q)
2014-12-03 04:44:05,185 :_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)' at line 1")

So, the one thing I didn't mention is that all of this was being done in Python 3.3. When I run it in Python 2.7, it works with no issues whatsoever.

Ah, that's useful information. The old Python MySQL library is pretty broken on Python 3. How did you install it? There may be a better one that you can use.

I installed using the recommended method in the Help section:

pip3.3 install --user https://github.com/davispuh/MySQL-for-Python-3/archive/1.0.tar.gz

As far as I know, everything installed correctly.

OK, that certainly should be OK.

Could you run it again, but just before the

cur.execute("INSERT INTO bom (Part_No, Description, Model, Type, Per_Eng, Cntr_Qty,     Location) VALUES (%s, %s, %s, %s, %s, %s, %s)", tuplist)

...add some code to print tuplist?

The "row" variable is the row from the CSV file. I turn the row into a tuple simply by doing "tuplist=tuple(row)".

row = ['111035A2 A000M1', 'PLATE,PARTITION', 'F5A2', 'CA1', '1', '240', 'K227']
Part_No should equal:
row[0] = '111035A2 A000M1'
Description should equal:
row[1] = 'PLATE,PARTITION'
Model should equal:
row[2] = 'F5A2'
Type should equal:
row[3] = 'CA1'
Per_Eng should equal:
row[4] = '1'
Cntr_Qty should equal:
row[5] = '240'
Location should equal:
row[6] = 'K227'
row = ['111035A2 A000M1', 'PLATE,PARTITION', 'F5A2', 'CA2', '1', '240', 'K227']
Part_No should equal:
row[0] = '111035A2 A000M1'
Description should equal:
row[1] = 'PLATE,PARTITION'
Model should equal:
row[2] = 'F5A2'
Type should equal:
row[3] = 'CA2'
Per_Eng should equal:
row[4] = '1'
Cntr_Qty should equal:
row[5] = '240'
Location should equal:
row[6] = 'K227'

Very perplexing. Could you run it in from a console and share the console with me? There may be some non-obvious context that could help me debug.

Alternatively, if it's running in a web app, can I take a look at your files?

https://www.pythonanywhere.com/user/sjk2712/consoles/bash/957588/

Not entirely sure what all needs to happen to share a console, so let me know if that will suffice. The file is called testbom.py.

What is the table structure of the table you're trying to populate?

Mystery solved! The underlying MySQLdb code uses the Python string.format method and Python 3 uses {} for string formatting, not %s. Change the %ss to {}s and it will work.

glenn, that's the ticket!! Thank you!

giles, thank you, too, for all of your efforts.

I'm not sure which one of you went and fixed that file, but thanks for that also; works beautifully.

Excellent, thanks for confirming! Sorry it took so long to track down, Glenn and I wound up working through the code in the MySQL module (yay for Open Source :-) and he spotted the problem. Basically, they're using the Python .format function on a string when you get deep enough, and that's changed its behaviour from Python 2.7 to Python 3.