How do I use variables in query string?

[Rega]

Hi,

I wish to pass cursor.execute() a query string that is built up from variables, I have a variable for the table name, another for the fields (tuple, multiple values) and another for the data (tuple, multiple values).

How do I go about creating this? I have been tearing my hair out for days, can some one please help

thanks

Regga

[Greg Fortune]

tbl_name = 'the_table'
fields = ('field1, field2')
data = (22, 'a string')

#build query...  The last chunk gives you as many %s=%s
#as needed for the number of fields you've specified
#Replace it with a loop if you like...
my_query = "UPDATE %s SET " + ','.join(["%s=%s" for x in fields])
print my_query #make sure it looks formed correctly...

#Build the list of params
my_params = [tbl_name]
for i in range(len(fields)):
    my_params.append(fields[i])
    my_params.append(data[i])

#Execute query and turn the list of params into a tuple
cursor.execute(my_query, tuple(my_params))

With that example though, I would not recommend passing in the table and field names as params unless they are being specified by the user.  The mysqldb module is going to a little extra work to make sure they are escaped properly if they are passed in as params.  Instead, I would build up my query string and only use %s for the data values from the user.  That makes it a little easier to read as well because you aren't trying to join the two lists...

Good luck :)

Greg Fortune
Fortune Solutions

[Rega]

Thankyou for taking the time to help me its appreciated.

Al the best

Regga

[Andy Dustman]

The main thing to remember is only column values may be passed as arguments to execute(). You can't pass column or table names because they will be quoted as strings.

I think there is an error in Greg's example. It should be more like this:

tbl_name = 'the_table'
fields = ('field1, field2')
data = (22, 'a string')

my_query = "UPDATE %s SET " % tbl_name + ','.join(["%s=%%s" % x for x in fields])
print my_query #make sure it looks formed correctly...

#Execute query
cursor.execute(my_query, data)

Obviously (I hope) your fields and tbl_name need to be properly validated if they coming from an external source.

[Greg Fortune]

<i>I think there is an error in Greg's example. It should be more like this:</i>

Nope, mine works fine... I just take a different approach to building my string.

print query in my example produces

UPDATE %s SET %s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s,%s=%s

Yours just has some of the values already substituted in...

[Andy Dustman]

Here's another example that might be useful:

tbl_name = 'the_table'
data = {'field1': 22, 'field2': 'a string'}
my_query = "UPDATE %s SET" % tbl_name + \     ','.join(["%s=%%(%s)s" % (k,k) for k in data.keys()])
cursor.execute(my_query, data)

It may be a good idea to wrap the %s which hold table or column names with backticks, i.e. `%s`. The backticks in SQL preserve case and allow other special characters. Depends on your application whether this would be useful.

[Andy Dustman]

Yes, but mine are quoted correctly. Your method is passing column names as parameters to execute(), which is wrong. Only column *values* (and not column names or tables names) may be passed by this method.

For that matter, there's an additional error I didn't see at first and copied:

fields = ('field1, field2')

ought to be:

fields = ('field1', 'field2')