help with mysql cursor.execute()

[Steve Holden]

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.

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


-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC             http://www.holdenweb.com/