[Tutor] MySQLdb error - PLEASE SAVE ME!

[Danny Yoo]

On Sat, 17 Sep 2005, Ed Hotchkiss wrote:

> Ok. I am trying to read a csv file with three strings separated by
> commas. I am trying to insert them into a MySQL DB online. MySQLdb is
> installed, no problems.
>
> I think that I am having some kind of error with my csv going into the
> fields and being broken apart correctly.

Hi Ed,

Can you show us what error you're seeing?  Maybe it is related to the csv
splitting... Then again, maybe it isn't.  *grin*

If you have an error message, then it'll be good to show that to us on the
list, because then we can try to help you interpret the error message.
That way, next time you seem a similar error, you might have a better idea
what the system's trying to tell you.


Let's look at some of the code.

######
arr=[]
inp = open ("sites1.txt","r")
#read line into array
for line in inp.readlines():
    links = map(str, line.split(","))
    arr.append(links)
    cursor.execute ("""
        INSERT INTO links (Name, URL, category)
       VALUES (%s, %s, %s)""" % tuple(links[0:3])
    )
######

Ok, I see it.  I'll focus on the cursor execute() bug, but there's
possibly another one: if the sites1.txt file has lines that don't contain
two commas, then it won't be valid to try to process the data on an
incomplete line.  You may want to do a check to warn and skip such broken
lines.


Try to avoid doing direct string formatting when you're working with
databases: let the database do it for you.  cursor.execute() can take in
an additional tuple argument of values, which it'll use to fill in the
'%s' placeholders in the SQL.

So rather than:

    cursor.execute ("""
        INSERT INTO links (Name, URL, category)
       VALUES (%s, %s, %s)""" % tuple(links[0:3])
    )

Do this instead:

    cursor.execute ("""
        INSERT INTO links (Name, URL, category)
       VALUES (%s, %s, %s)""", tuple(links[0:3])
    )

The reason this is important is because SQL uses a different set of rules
for string quotation than Python, and it's really easy to mess it up.

This is one of those things that everyone gets mixed up about.  See the
thread on 'escape-quoting strings' here:

    http://mail.python.org/pipermail/tutor/2004-November/032943.html

and read that thread for more details on using cursor.execute() with that
second argument.


Best of wishes to you!