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 >>> [EMAIL PROTECTED] > > > -- http://mail.python.org/mailman/listinfo/python-list
