MySQLdb question... using table name as arg

[Sean Berry]

I have four tables that all have the same column names (50 in each.)

I have created an admin program to edit, delete and add records to the
tables and 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. Usually I would do something
like this by having 1 table with special column to categorize the records as
I am doing with each table, but this specific application requires that I do
it with 4 tables instead.

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, ...))

But, not I want to do this with a variable tableName. If I add it to the
tuple of parameters in the second arg before val1 and replace tableName with
%s, then the tableName will be quoted in the query, causing an error.

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.


Thanks for any help.

 

[Carsten Haese]

I have four tables that all have the same column names (50 in each.)

I have created an admin program to edit, delete and add records to the
tables and 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. Usually I would do something
like this by having 1 table with special column to categorize the records as
I am doing with each table, but this specific application requires that I do
it with 4 tables instead.

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, ...))

But, not I want to do this with a variable tableName. If I add it to the
tuple of parameters in the second arg before val1 and replace tableName with
%s, then the tableName will be quoted in the query, causing an error.

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.
You have to build the query string for the appropriate table, then hand
it to execute for filling in the actual parameters:

queryString = "update "+tableName+" set col1=%s, col2=%s,..."
c.execute(queryString, (val1, val2,...))

HTH,

Carsten.

 

[Sean Berry]

As you have discovered, the table name is not allowed to be a parameter.
You have to build the query string for the appropriate table, then hand
it to execute for filling in the actual parameters:

queryString = "update "+tableName+" set col1=%s, col2=%s,..."
c.execute(queryString, (val1, val2,...))

HTH,

Thanks... that is kind of what I figured.

 

[Scott David Daniels]

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
(e-mail address removed)

 

[Jim Segrave]

I have four tables that all have the same column names (50 in each.)

I have created an admin program to edit, delete and add records to the
tables and 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. Usually I would do something
like this by having 1 table with special column to categorize the records as
I am doing with each table, but this specific application requires that I do
it with 4 tables instead.

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, ...))

But, not I want to do this with a variable tableName. If I add it to the
tuple of parameters in the second arg before val1 and replace tableName with
%s, then the tableName will be quoted in the query, causing an error.

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.

How about interpolating the table name into the string:

c.execute("update %s set col1 = %%s, col2 = %%s, col3=%%s" % (sometable), \
['the', 'cat', 'in the hat'])

Note the need to double the %'s for the parameters to be bound.