[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,
>