MySQLdb question... using table name as arg

[Scott David Daniels]

Carsten Haese wrote:
> On Fri, 2006-02-03 at 13:24, Sean Berry wrote:
>> I have four tables that all have the same column names (50 in each.)
>> I ...  would like to use the table name as a variable in each query so
>> the code can be used for each of the 4 tables....
>> To ensure that string are quoted properly without any hassle I use the 
>> execute function like so assuming c is my cursor object...
>>
>> c.execute("update tableName set col1 = %s, col2 = %s, col3 = %s, ...", 
>> (val1, val2, val3, ...))
>>
>> What is the best (easiest) way for me to accomplish this?  I know it may be 
>> a stupid question but I just can't figure it out.
> 
> As you have discovered, the table name is not allowed to be a parameter....
>    queryString = "update "+tableName+" set col1=%s, col2=%s,..."
>    c.execute(queryString, (val1, val2,...))


Since you are in control of the query strings, you might try:
choose a string ('<table>' in this example) to represent the table name
that will never appear in your queries to represent the table name,
then do something like:

     for table_name in 'first', 'second', 'third', 'fourth':
         def perform(query, args):
             return cursor.execute(
                       table_name.join(query.split('<table>')), args)
         perform('UPDATE <table> SET col1 = %s, col2 = %s', (val1, val2))
         perform('UPDATE <table> SET col4 = %s, col5 = %s', (val4, val5))

Although, frankly, this doesn't sound like a well-designed database.


--Scott David Daniels
scott.daniels at acm.org