1

I'm using MySQLdb to upload a lot of data from text files into a MySQL server. This works fine if I manually prepare a string such as 'as', '123', 12, 23, but I can't figure out how to loop through a list to generate this as I need to concatenate strings and ints.

An example of an insert statement that works is as follows:

""" INSERT INTO ACS(ST, CODE, BC001, BC002, BC003) 
VALUES ('AK', '1234567', 20, 30, 40)"""

This is how I have tried to generate this statement from a list:

import MySQLdb

# sample data
table = 'TestTable'
header = ['ST', 'CODE', 'BC001', 'BC002', 'BC003']
values = [['AA', '1234567', 20, 30, 40], ['BB', '1234567', 20, 30, 40], ['CC', '1234567', 20, 30, 40],['DD', '1234567', 20, 30, 40]]


# local SQL server on my computer
db = MySQLdb.connect (host = 'localhost', user = 'root', passwd = '', db = 'test')
# prepare a cursor object using cursor() method
cursor = db.cursor()

# header columns
sql1 = '('
for i in range(len(header)):
    sql1 += header[i] + ','
sql1 = 'INSERT INTO ' + table + sql1[:-1] + ')'

# now loop through data values and combine with header each time
for i in range(len(values)):

    sql2 = ''
    for j in range(len(values[i])):
        sql2 += values[i][j] + ','        #error occurs here

    # structure: sql2 = """ VALUES ('AA', '1234567', 20, 30, 40)"""
    sql2 = 'VALUES ' + table + sql2[:-1] + ')'     
    sql = sql1 + sql2

    try:
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
    except:
       # Rollback in case there is any error
       db.rollback()

# disconnect from server
db.close()

The error message I get is 

TypeError: unsupported operand type(s) for +: 'int' and 'str'

and I understand why that is occurring, but I can't figure out an alternative way of generating the string. Is there a better way of producing these strings?

I'm using Python27 on Win7 64 bit.

Improve this question
2
  • sorry I looked it up on google and there's no such thing as pymsql? could you give a link to homepage pls? Commented Jul 28, 2011 at 21:24
  • Oops, I wrote the wrong package name. It's MySQLdb - I'll update the question; sorry about that. Commented Jul 28, 2011 at 21:35

4 Answers 4

Reset to default
5

MySQLdb can already do this without you having to manually type-cast your variables.

YOU WANT TO DO IT THIS WAY because MySQLdb will automatically quote things and guard you from SQL injection attacks, to some extent. This is really important in any practical setting, and something you should get used to if you plan to ever do any professional database work.

With a MySQLdb cursor object, the execute() command will automatically format your ints, strings, and other variables properly so that they will work in your INSERT statement. They do this using a special kind of format string.

c=db.cursor()
max_price = 5
min_price = 1
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s AND price > %s""", (max_price, min_price))

Note two things: First, all variables, regardless of type, should be represented in these special format strings as %s. It's just the way it works. Second, the second parameter to execute() should be a tuple, so if you only have one variable to use, you need to type it like (max_price,). The comma at the end of the parentheses tells python it's a tuple.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This is definitely a much better way of doing it; I just mentioned above that I was unaware of this approach. I've just tested it by passing a list, and noticed that that MySQL shows the string with quotes 'AA' whereas the other methods did not do this (they just show as AA). Can I stop this from occurring?
What MySQL data type are we talking about here? I was under the impression that all strings had to be surrounded by quotes.
If you want the strings without quotes, use a python old-school format string. Try typing the following into the interpreter: "W%s H%d%d" % ('AA', 0, 0)
But I'm pretty sure that any CHAR or VARCHAR should always have single quotes around it.
Check out this doc for further details: docs.python.org/library/…
1

The other posts correctly identified the error, but didn't answer your question as to a better way to do string formatting.

I'm not sure this formats things exactly as you want, as your code is a bit hard to read. You'll end up with queries like:

INSERT INTO TestTable (ST,CODE,BC001,BC002,BC003) VALUES ('AA','1234567',20,30,40)

Here is the code:

sql = (('INSERT INTO {0} ({1}) VALUES ({2})'.format(table, ','.join(header), 
                         ','.join(("'" + v + "'" if isinstance(v, str) 
                             else str(v)) for v in val))) for val in values)

Then you just do:

for q in sql:
    try:
        # Execute the SQL command
        cursor.execute(q)
        # Commit your changes in the database
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()

It adds quotes to strings, and uses str.join and str.format.

Really, you should be using a parametrized query, which pushes putting the values off into the string to either the database connector or the database itself.

Improve this answer

2 Comments

You probably want to use basestring instead of str here in the isinstance call, just for the sake of increased generality. Also can't hurt to link to the python doc docs.python.org/library/stdtypes.html#str.format
ah! I did not know any better. I just read up on how to make a parametrized query, perhaps I should do that. This is a once off mass-data upload on a personal computer though so security is not an issue.
1

Have you tried simply wrapping values[i][j] in str() like str(values[i][j])?

It looks like you could also do 

sql2 = ','.join([str(x) for x in values[i]]) + ','

if you wanted to simplify it even further. (Not sure if the trailing comma is necessary, but I included it to match your code.)

Example: 

>>> my_list = ['abc', 1, 2,3, 'do','re','mi']
>>> [str(x) for x in my_list]
['abc', '1', '2', '3', 'do', 're', 'mi']
>>> ','.join([str(x) for x in my_list])
'abc,1,2,3,do,re,mi'

On the other hand, if you need to have the objects that were originally strings wrapped in single quotes in the final query, which it looks like you do, you probably need to do something more complex like

sql2 = ''
for x in values[i]:
    if isinstance(x, basestring):
        sql2 += "'"+x+"',"
    else:
        sql2 += str(x) +","
Improve this answer

2 Comments

Thanks for the suggestions. I need to be able to triple quote """ and also to surround variables that are of type string with ', otherwise the database is not updated.
Sorry, I just noticed the second part and edited my response accoridngly. I'm not sure what you mean about needing to triple quote, though.
-1

The error is not related to MySQLdb at all.

You are using '+' operator to merge string with integer. Your values array contains mixed strings and integers, and you are adding them using string append operator which can't work with mixed str and int (you are doing something like mystry = 1+',').

Solution for your problem can be just use str() on items from array. Like:

sql2 += str(values[i][j]) + ','

Improve this answer

1 Comment

But then he's just doing manual piece-by piece format strings. The error may not be related to MySQLdb, but if you took a look at the content of the question you'd realize that he's trying to insert a list of values into a row, which is provided for exactly by the function he's using.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.