[python-sybase] Order of named parameters

Marcos Sánchez Provencio msanchez at grupoburke.com
Fri, 02 Jan 2004 11:00:38 +0100


In my company (medium sized, public administration oriented), we use it 
to access MS SQL Server from Linux. But we don't use parameters when 
using this modules.

Peter Hansack wrote:
> Sybase ASE uses parameters positional (in the order they are defined in
> ct_param) - even named parameters.
> 
> An example:
> 
> ----------------------
> import Sybase
> 
> db = Sybase.connect('SYBASE','sa','',database='data',auto_commit=1)
> 
> db.execute('''create table test (
>    key1    varchar(10)     not null,
>    value1  varchar(10)     null,
>    value2  varchar(10)     null,
>    value3  varchar(10)     null,
>    value4  varchar(10)     null,
>    value5  varchar(10)     null  )
> 	   ''')
> 
> # 1 #
> v = { '@key1'   : 'key1',   '@value1' : 'value1', '@value2' : 'value2',
>       '@value3' : 'value3', '@value4' : 'value4', '@value5' : 'value5'}
> print "v=", v
> 
> c = db.cursor()
> 
> # 2 #
> c.execute('insert into test values
> (@key1,@value1,@value2,@value3,@value4,@value5)',v)
> 
> # 3 #
> c.execute('select * from test')
> x = c.fetchone()
> print "x=", x
> 
> # 4 #
> c.execute('''select * from test where key1=@key1 and value1=@value1
>              and value2=@value2 and value3=@value3 and value4=@value4
> 	     and value5=@value5''',v)
> 
> y = c.fetchone()
> print "y=", y
> 
> c.close()
> db.close()
> ----------------------
> 
> 
> Running the example gives the following output:
> 
> v= {'@key1': 'key1', '@value4': 'value4', '@value5': 'value5', '@value2':
> 'value2', '@value3': 'value3', '@value1': 'value1'}
> x= ('key1', 'value4', 'value5', 'value2', 'value3', 'value1')
> y= ('key1', 'value4', 'value5', 'value2', 'value3', 'value1')
> 
> 
> Conclusion:
> # 3 # shows, that in insert the parameters are applied not in the order of #
> 2 #, but in the order
> shown in # 1 # (i.e in the incidental internal order of the dictionary) and
> # 4 # shows, that this is
> true also for where-clauses.
> 
> 
> Consequence: a patch (for 0.36)
> 
> # diff Sybase.py.original Sybase.py
> 246a247,249
> 
>>import re
>>re_params = re.compile(r'@\w+')
>>
> 
> 681c684,685
> <             for name, value in params.items():
> ---
> 
>>            for name in re_params.findall(sql):
>>                value = params[name]
> 
> 
> This helps only in cursor.execute(); the rest of the module should be
> checked also.
> 
> 
> Question: Is anybody using this module?
> 
> 
> Regards,
>