[python-win32] How to specify parameters in a query using odbc

[Vernon Cole]

Tim's answer is correct.  A db api connection object supplies a .paramstyle
method which specifies which of _five_ different parameter substitution
methods is used by the underlying query engine.  odbc uses 'qmark', meaning
that you put ? where you want parameters to appear.

You may have something "more up-to-date" available to you that you are not
aware of.  Versions of pywin32 since 2.1.1 have also included adodbapi,
which is fully db api 2.0 compliant.   ADO runs in odbc mode by default, so
you should be able to use all of the same drivers, connection strings, etc.
You could also use ADO providers if you wish, by using other connection
strings. www.connectionstrings.com is a good source.  Adodbapi also uses
'qmark' paramstyle.

 If you were to get one of the latest versions of adodbapi (which will be in
the NEXT release of pywin32) it would also allow you to _change_ the
paramstyle, so that you could specify that you want to use 'format'
paramstyle, which is what you were trying to use.  (This is needed for
django, for example.) Lacking that you are stuck with 'qmark' so use:

myQuery = """SELECT EnteredValue FROM SampleTests INNER JOIN SampleResults

ON (SampleTests.SampleCode = SampleResults.SampleCode AND

SampleTests.AuditFlag = SampleResults.AuditFlag AND

SampleTests.TestPosition = SampleResults.TestPosition)

WHERE (SampleTests.SampleCode=? AND SampleTests.AuditFlag=0 AND
SampleTests.TestCode=? AND

SampleTests.TestStatus<900 AND SampleResults.ComponentName=’IntMethod’)

“””

Paul's impression that parameter substitution only works in INSERT
statements is incorrect.  (The documentation IS awfully lightweight.) You
can also use them in SELECT as in your example.
--
Vernon Cole

On Tue, Feb 1, 2011 at 11:14 AM, Tim Roberts <timr at probo.com> wrote:

> Tom Hawkins wrote:
> >
> >
> >
> > I’m trying to get data out of a MS SQL Server 2005 database from a
> > Python 2.5 script, using the odbc module - I can’t easily get anything
> > more up-to-date installed on this system.
> >
> > ...
> >
> > ...it works OK, but if I try to parameterise the query:Any idea what
> > I’m doing wrong please? Apologies if I’ve missed something stupid. I
> > get the same error if I only specify one of the parameters with %s and
> > leave the other one literal, by the way.
>
> There are, for reasons that have never been clear to me, three different
> parameter substitution styles allowed by the Python dbapi
> specification.  The Win32 odbc module uses ? characters instead of %s.
>
> A simple search-and-replace should solve your problem.
>
> --
> Tim Roberts, timr at probo.com
> Providenza & Boekelheide, Inc.
>
> _______________________________________________
> python-win32 mailing list
> python-win32 at python.org
> http://mail.python.org/mailman/listinfo/python-win32
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110201/f750b748/attachment.html>