There have been a few comments that automatically detecting SQL parameter
style based on the container that parameters are passed in might not work.
I have discovered that sometimes trying to put something in code sheds a
lot of light on my understanding, so I decided to try it. Indeed, my last
comment to this group "just try to get the key" was completely unworkable.
This is my report.
I added another paramstyle, 'auto', to adodbapi.py
The actual code addition was trivial: two lines altered...
>>>accepted_paramstyles = ('qmark', 'named', 'format', 'auto')
and
>>>...elif self.paramstyle == 'named' or (self.paramstyle == 'auto' and
isinstance(parameters, dict)):
Just to experience working in an autoswitched world, I reset the default:
>>>paramstyle='auto' # the default parameter style
I added some appropriate tests to my unittest suite, and went on my way to
other things.
Several hours later, errors appeared in the tests for the django-mssql
project I was working on. "Ahah", I thought: "I found a place where auto
switch breaks something!"
It was dbapi20.py (running as a sub-test) informing me about an unknown
paramstyle.
Later, I downloaded Daniele Varrazzo's qmarkpg package.
Daniele uses pure autoswitching. His only reference to paramstyle is when
it is defined. I quote:
>>>paramstyle = 'named' # or qmark? whatever.
The heart of his algorithm is as follows (I will cut out details)..
> from collections import Mapping, Sequence
>
> ...clip...
> def convert_params(query, args):
> """
> Convert a qmark query into "format" or a named query into "pyformat".
>
> I'm not sure it is possible to disambiguate the two query styles, I'm not
> even sure anybody has tried showing it is possible or the contrary. So we
> try to infer from the args which type of arguments the query has.
> """
> if args is None:
> # no placeholder here
> return query
>
>
> elif isinstance(args, Mapping):
> ...clip...
> return RE_NAMED.sub(sub_mapping, query)
> elif isinstance(args, Sequence):
>
> ...clip...
> return RE_QMARK.sub(sub_sequence, query)
> else:
> raise TypeError('expected a sequence or mapping argument')
>
> A quick check of the psycopg2 documentation reveals that, indeed, it does
not care whether you hand it 'format' or 'pyformat' queries: it
autoswitches. It has done that for years. Daniele just hands off the
autoswitch to it.
(I admired his use of collections.Mapping, and immediately copied it. Made
my code one line longer for the import.)
My conclusion: Auto switching is easy, practical, and has years of
precedent history. DB-api 3 should use it as the default action.
- - - - - - - - - - - - - - - - - - - - - - - - - -
References:
https://sourceforge.net/projects/adodbapi/files/adodbapi/2.5.0/ (Works
with most databases, but you have to have a Windows machine to act as a
proxy server if you are running Linux. You will have to set .paramstyle =
'auto' -- it defaults to 'qmark')
https://github.com/dvarrazzo/qmarkpg (Requires PostgreSQL and psycopg2.
Works on Linux or Windows.)
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
