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]

Re: help with mysql cursor.execute( )

William Gill wrote:[color=blue]
> 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?[/color]

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 parameterizatio n 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@A cm.Org

[William Gill]

Re: help with mysql cursor.execute( )

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

Scott David Daniels wrote:[color=blue]
> William Gill wrote:
>[color=green]
>> 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?[/color]
>
>
> 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 parameterizatio n 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@A cm.Org[/color]

[Steve Holden]

Re: help with mysql cursor.execute( )

William Gill wrote:[color=blue]
> I have been testing and it seems that:
>
> 1- Cursor.execute does not like '?' as a placeholder in sql
>[/color]
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".
[color=blue]
> 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.
>[/color]
That's correct, as Scott has pointed out (with a good explanation of why).
[color=blue]
> 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.
>[/color]
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.
[color=blue]
> 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.[/color]

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
[color=blue]
>
> Thanks.
>
> Bill
>
> Scott David Daniels wrote:
>[color=green]
>>William Gill wrote:
>>
>>[color=darkred]
>>>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?[/color]
>>
>>
>>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 parameterizatio n 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 @Acm.Org[/color][/color]


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

[William Gill]

Re: help with mysql cursor.execute( )



Steve Holden wrote:[color=blue]
> William Gill wrote:
>[color=green]
>> I have been testing and it seems that:
>>
>> 1- Cursor.execute does not like '?' as a placeholder in sql
>>[/color]
> 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".
>[color=green]
>> 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.
>>[/color]
> That's correct, as Scott has pointed out (with a good explanation of why).
>[color=green]
>> 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.
>>[/color]
> 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.
>[color=green]
>> 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.[/color]
>
>
> 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.[/color]

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
[color=blue]
>
> regards
> Steve
>[color=green]
>>
>> Thanks.
>>
>> Bill
>>
>> Scott David Daniels wrote:
>>[color=darkred]
>>> 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 parameterizatio n 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@A cm.Org[/color][/color]
>
>
>[/color]

[Dennis Lee Bieber]

Re: help with mysql cursor.execute( )

On Sun, 14 Aug 2005 19:28:04 GMT, William Gill <noreply@gcgrou p.net>
declaimed the following in comp.lang.pytho n:
[color=blue]
> 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 ))
>[/color]
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.
--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color]

[William Gill]

Re: help with mysql cursor.execute( )


Dennis Lee Bieber wrote:[color=blue]
> On Sun, 14 Aug 2005 19:28:04 GMT, William Gill <noreply@gcgrou p.net>
> declaimed the following in comp.lang.pytho n:
>
>[color=green]
>>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 ))
>>[/color]
>
> 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.
>[/color]

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[color=blue]
> 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.[/color]

[Dennis Lee Bieber]

Re: help with mysql cursor.execute( )

On Mon, 15 Aug 2005 22:03:05 GMT, Dennis Lee Bieber
<wlfraed@ix.net com.com> declaimed the following in comp.lang.pytho n:
[color=blue]
> On Sun, 14 Aug 2005 19:28:04 GMT, William Gill <noreply@gcgrou p.net>
> declaimed the following in comp.lang.pytho n:
>[/color]
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...
--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color]