help with mysql cursor.execute()

[William Gill]

I have been testing and it seems that:

1- Cursor.execute does not like '?' as a placeholder in sql

2- Cursor.execute accepts '%s' but puts the quotation mark around the 
substitution.

   sql = 'select * from %s where cusid = ? ' % name
   Cursor.execute(sql, (recID,))

still fails, but:

   sql = 'select * from basedata where cusid = %s '
   Cursor.execute(sql, (recID,))

works regardless of recID being a string or an int.  Obviously this 
stems from trying to parameterize the table name.

If I use:

   sql = 'select * from %s where cusid = %%s ' % name
   Cursor.execute(sql, (recID,))

It makes 1 substitution in the first line, and another in the execute()

   sql = 'select * from %s where cusid = %%s ' % name
   # sql now == 'select * from basedata where cusid = %s '
   Cursor.execute(sql, (recID,))

and it works.

Between your comments re: column names and table names , and the notes 
in cursor.py, I was able to figure it out.

FYI I wanted to create a tableHandler class that could be extended for 
individual tables.  That's why the query needs to accept variables for 
tablename.

Thanks.

Bill

Scott David Daniels wrote:
> William Gill wrote:
> 
>> I have been trying to pass parameters as indicated in the api.
>> when I use:
>>   sql= 'select * from %s where cusid = %s ' % name,recID)
>>   Cursor.execute(sql)
>> it works fine, but when I try :
>>   sql= 'select * from %s where cusid like %s '
>>   Cursor.execute(sql,(name,recID))
>> or
>>   sql= 'select * from ? where cusid like ? '
>>   Cursor.execute(sql,(name,recID))
>> it fails.
>> Can someone help me with the semantics of using parameterized queries?
> 
> 
> Neither column names nor table names can be parameters to
> fixed SQL.  Values are what you fiddle with.  This squares with
> the DBMS being allowed to plan the query before looking at the
> parameters (deciding which indices to use, which tables to join
> first, ...), then reusing the plan for identical queries with
> different values.  MySQL may not take advantage of this two-step
> approach, but the DB interface is designed to allow it, so
> the parameterization is constrained.
> 
> See if something like this works:
> 
>     sql = 'select * from %s where cusid like ? ' % name
>     Cursor.execute(sql, (recID,))
> 
> --Scott David Daniels
> Scott.Daniels at Acm.Org