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 >>[EMAIL PROTECTED] -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC http://www.holdenweb.com/ -- http://mail.python.org/mailman/listinfo/python-list
