help with mysql cursor.execute()

[William Gill]

Steve Holden wrote:
> William Gill wrote:
> 
>> I have been testing and it seems that:
>>
>> 1- Cursor.execute does not like '?' as a placeholder in sql
>>
> The particular format required by each DBI-compatible module should be 
> available as the module's "paramstyle" variable. mxODBC, for example, 
> uses the "qmark" style, but MySQLdb uses "format".
> 
>> 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.
>>
> That's correct, as Scott has pointed out (with a good explanation of why).
> 
>> 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.
>>
> That's right: you are now building a table-dependent query (i.e. the 
> table name is hard-wired in the SQL string) parameterized to the 
> required value for cusid.
> 
>> 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.
> 
> 
> You might want to take a look at how some existing object-mappers 
> achieve this - Ian Bicking's sqlobject module might be a good place to 
> start.

I also might want to take a tutorial on searching.  This looks like it 
could have saved me lots of wheel re-inventing.

Thanks,

Bill

> 
> regards
>  Steve
> 
>>
>> 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
> 
> 
>