help with mysql cursor.execute()

[William Gill]

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?

Bill

 

[Scott David Daniels]

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
(e-mail address removed)

 

[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

 

[Steve Holden]

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

 

[William Gill]

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".

That's correct, as Scott has pointed out (with a good explanation of why).

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.



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

 

[Dennis Lee Bieber]

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))

Hypothesis: the database TABLE may need to be filled in externally.
..execute() parsing is designed to properly quote arguments for data
fields where needed.

I suspect you are getting quote marks around the table name, which
is not a position they are expected.

You'll likely have to use a two-step process: use string formatting
to fill in table and field names (if you are getting those from user
input, you'll have to validate that there isn't an injection attack --
ie, user didn't enter "name; delete from name" as the table to be
processed); then use .execute() to pass the field values.

If using MySQLdb, you could always read the source files... (Though,
unfortunately, the very bottom is a compiled library and hence
unreadable...


You'll find .execute() invokes an .escape()

Py> escape(...)
Py> escape(obj, dict) -- escape any special characters in object
obj
Py> using mapping dict to provide quoting functions for each
type.
Py> Returns a SQL literal string.

Note the last line: it returns a "literal string" -- in the form
needed to pass /data/. That won't work for field and table names, and
MySQLdb doesn't attempt any semantic parsing to find out is being
substituted -- if just converts (escapes) ALL parameters based on
datatype, THEN does a normal Python string formatting operation.
--

 

[William Gill]

Hypothesis: the database TABLE may need to be filled in externally.
.execute() parsing is designed to properly quote arguments for data
fields where needed.

I suspect you are getting quote marks around the table name, which
is not a position they are expected.

From my testing, your suspicion is correct, as is your suggestion.

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

works,

Bill

 

[Dennis Lee Bieber]

My apologies for the late posting... I forgot to log in to send the
message(s)...

I ordered an upgrade from (unlimited) dial-up to DSL; the DSL
includes 20-hrs of dial-up for roaming. Problem is that I suspect the
20-hrs took effect immediately, but I won't have the DSL hardware and
activated line for another two weeks...
--