MS SQL Server/ODBC package for Python

[Graham]

I need a SQL Server or ODBC package for Python. Can anyone help please?
Have search the Python packages under Database and there is no reference to
either SQL Server or ODBC.

Thanks
Graham

[Simon Brunning]

On 4/15/05, Graham <gs****@oxfam.org.uk> wrote:

I need a SQL Server or ODBC package for Python. Can anyone help please?
Have search the Python packages under Database and there is no reference to
either SQL Server or ODBC.

Top of the line is probably mxODBC
(<http://www.egenix.com/files/python/mxODBC.html>). It's the Rolls
Royce of DB-API modules. It's not free for commercial use, but it's
worth every penny/cent/groat/whatever.

For commercial work where I've not been able to justify spending money
(in my bosses opinion at least), adodbapi
(<http://adodbapi.sourceforge.net/>) works a treat.

--
Cheers,
Simon B,
si***@brunningonline.net,
http://www.brunningonline.net/simon/blog/

[Graham]

Thanks Simon
I have just installed mxODBC and tried one of their samples.
Am getting an error on trying to import mx.ODBC

ImportError: No module named mx.ODBC

After the install the mx folder has been place in my C:\Program Files\Plone
2\Python\lib\site-packages folder however when I browse the PythonPath and
look under Standard Python Library I can't see mx.

Any ideas what I mnay have done wrong?

many thanks
Graham
"Simon Brunning" <si************@gmail.com> wrote in message
news:ma**************************************@pyth on.org...
On 4/15/05, Graham <gs****@oxfam.org.uk> wrote:

I need a SQL Server or ODBC package for Python. Can anyone help please?
Have search the Python packages under Database and there is no reference to either SQL Server or ODBC.

Top of the line is probably mxODBC
(<http://www.egenix.com/files/python/mxODBC.html>). It's the Rolls
Royce of DB-API modules. It's not free for commercial use, but it's
worth every penny/cent/groat/whatever.

For commercial work where I've not been able to justify spending money
(in my bosses opinion at least), adodbapi
(<http://adodbapi.sourceforge.net/>) works a treat.

--
Cheers,
Simon B,
si***@brunningonline.net,
http://www.brunningonline.net/simon/blog/

[Simon Brunning]

On 4/15/05, Graham <gs****@oxfam.org.uk> wrote:

Thanks Simon
I have just installed mxODBC and tried one of their samples.
Am getting an error on trying to import mx.ODBC

ImportError: No module named mx.ODBC

After the install the mx folder has been place in my C:\Program Files\Plone
2\Python\lib\site-packages folder however when I browse the PythonPath and
look under Standard Python Library I can't see mx.

Hmmm. Have you got more than one instance of Python installed? Do you
have a 'standard' installation in addition to Plone's? If so, perhaps
mxODBC has been installed for the wrong one.

--
Cheers,
Simon B,
si***@brunningonline.net,
http://www.brunningonline.net/simon/blog/

[Peter Herndon]

Another option is adodbapi, which in my experience is much faster than
mx.ODBC. You can find it at http://adodbapi.sourceforge.net , and it
is Windows-only. There's also http://pymssql.sourceforge.net, which is
cross-platform using FreeTDS and unixodbc on *nix. I haven't any
experience with it, though.

[M.-A. Lemburg]

Peter Herndon wrote:

Another option is adodbapi, which in my experience is much faster than
mx.ODBC.
Much faster ?

See http://www.microsoft.com/technet/pro...t/iischp7.mspx
and scroll down to Table 7.1:

Table 7.1 TPS (transactions per second) Per Number of Threads by MDAC Technology
Threads 1 2 5 10 20 50
ODBC 66.37 146.28 350.46 626.76 900.24 859.91
OLEDB 67.30 141.92 326.19 590.57 794.91 715.78
OLEDB 2.0 61.73 126.93 297.29 506.75 575.35 526.61
ADO 2.0 51.24 108.12 240.91 377.30 361.26 310.34
You can find it at http://adodbapi.sourceforge.net , and it
is Windows-only. There's also http://pymssql.sourceforge.net, which is
cross-platform using FreeTDS and unixodbc on *nix. I haven't any
experience with it, though.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Apr 15 2005)

Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::

[Peter Herndon]

Marc-Andre, I apologize for knocking against something that is part of
your livelihood, I hadn't thought about that aspect before I posted.
In my experience though, adodbapi was much faster. However, I have
done no benchmarks, my situation is likely pathological, and I
wouldn't be able to build objective benchmarks anyway, so take it with
a huge grain of salt.

For what it is worth, my experience is as follows: Using a PIII
550MHz, 256MB RAM, running WinNT 4.0 and Python 2.3.4 and connecting
to a Sybase Adaptive Server Anywhere 8.0 database, mx.ODBC took
approximately 8 wall-clock seconds to connect, while adodbapi took
about 2 seconds to connect. That timing is a guesstimate on my part,
based on me counting. The only thing changed in the script was the
driver import and the connect string. Likewise, a query that would
need to search a table with a full data scan on the columns in the
where clause (vendor doesn't believe in indexes), where the table has
20k - 30k rows, takes 30-60 seconds using mx.ODBC versus 15-30 seconds
using adodbapi.

A different application, connecting to MSSQL2K, also exhibited similar
tendencies. mx.ODBC took a bit longer to connect than adodbapi,
though the differences were *much* smaller. Not measurable by
guesstimation, but a tad longer.

Again, this is just my experience, and my situation is not likely to
match others'. Given other circumstances, I would expect the results
to differ, and I encourage the original poster to test all possible
solutions to find the one that best fits his needs.

---Peter Herndon

On 4/15/05, M.-A. Lemburg <ma*@egenix.com> wrote:

Peter Herndon wrote:

Another option is adodbapi, which in my experience is much faster than
mx.ODBC.

Much faster ?

See http://www.microsoft.com/technet/pro...t/iischp7.mspx
and scroll down to Table 7.1:

Table 7.1 TPS (transactions per second) Per Number of Threads by MDAC Technology
Threads 1 2 5 10 20 50
ODBC 66.37 146.28 350.46 626.76 900.24 859.91
OLEDB 67.30 141.92 326.19 590.57 794.91 715.78
OLEDB 2.0 61.73 126.93 297.29 506.75 575.35 526.61
ADO 2.0 51.24 108.12 240.91 377.30 361.26 310.34

You can find it at http://adodbapi.sourceforge.net , and it
is Windows-only. There's also http://pymssql.sourceforge.net, which is
cross-platform using FreeTDS and unixodbc on *nix. I haven't any
experience with it, though.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Apr 15 2005)

Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::

[Francois Lepoutre]

Peter,

May my I apologize for knocking against your information, as well.

For what it is worth, my experience is as follows: Using a PIII
550MHz, 256MB RAM, running WinNT 4.0 and Python 2.3.4 and connecting
to a Sybase Adaptive Server Anywhere 8.0 database, mx.ODBC took
approximately 8 wall-clock seconds to connect

As a long time user of the ASA range of products, I was surprised
by your connection time to ASA 8. I'm not a regular user of mxODBC
but i have tested it with success here. With no pending time upon
connection whatever the middleware.

The script below ran fast on my machine (an oldish pentium 400)
with ASA 8.0.2 (the engine is local).

When re-cycling ASA connection (a good practice) the script took
0.21 sec. to run and 3.4 sec. when re-building the connection
on every hit (which you should avoid).

For 100 connections, not one! Which confirms my feeling that
something got in the way when you ran your test.

mxODBC is fast and safe (on both linux and win32).

I won't comment on ado since i'm not a user. But the fact that
ASA+mxODBC runs multi-platform may be an additional advantage.

Regards

Francis

from mx import ODBC
import time
mytime=time.time()
print "1 connection and 750 cursors started, used and closed at once..."
dbHandle=ODBC.Windows.Connect("descriptive demo fr","dupont","",0)
for i in range(100):
cHandle=dbHandle.cursor()
cHandle.execute("select 1")
cHandle.fetchall()
cHandle.close()
print time.time() - mytime
print "750 connection fully started, used and closed at once..."
for i in range(100):
dbHandle=ODBC.Windows.Connect("descriptive demo fr","dupont","",0)
cHandle=dbHandle.cursor()
cHandle.execute("select 1")
cHandle.fetchall()
cHandle.close()
dbHandle.close()
print time.time() - mytime

[Dennis Lee Bieber]

On Fri, 15 Apr 2005 18:20:43 -0400, Peter Herndon <tp*******@gmail.com>
declaimed the following in comp.lang.python:

For what it is worth, my experience is as follows: Using a PIII
550MHz, 256MB RAM, running WinNT 4.0 and Python 2.3.4 and connecting
to a Sybase Adaptive Server Anywhere 8.0 database, mx.ODBC took
approximately 8 wall-clock seconds to connect, while adodbapi took
about 2 seconds to connect. That timing is a guesstimate on my part,
If you always ran the ODBC test first, is there any possibility
that the OS is caching some connection information (routing tables?)
such that the second test didn't have to wait for the routing lookup (or
whatever) to take place?

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

[Peter Herndon]

:) Knock away, as my info isn't scientific anyway. In my case, ASA is
*not* local. The db is running on a 500MHz x 2 server with 768MB RAM,
over 100BaseT connection. That same server is also running the MSSQL
instance, and IIS.

Running your benchmark, I ran into a couple of interesting points.
Using mx.ODBC, my times were 0.54 seconds and 6.56 seconds
respectively, while using adodbapi my results are 3.55 seconds and 25.9
seconds respectively. mx.ODBC is faster with the simple query you
provide.

Next I modified the benchmark to reflect my particular circumstances
more accurately (?Maybe? Comments invited). I changed the query to
one of the queries in regular use in my application. This query
selects 26 columns from 3 joined tables, with a where clause "where
f476 = ?", and I provide the missing value as a tuple in the execute
statement. Note that, as I mentioned in my reply to M-A, the f476
field is not indexed, and is a long varchar. Again, the system is
bought, so I have no control over the schema. ;)

The other change I made was to reduce the number of iterations from 100
to 10. Since there are 128000 records in the main table, the wait for
100 iterations was too long for my patience. Under these
circumstances, mx.ODBC's numbers are 188.49 seconds and 377.56 seconds
respectively, and adodbapi's times are 111.15 seconds and 223.55
seconds respectively.

My first wall-clock impressions are obvious exaggerations of reality,
for which I duly apologize to all. However, adodbapi did prove to be
faster in my admittedly very wacky common use case. Slower to connect,
but faster to run a substantial query.

Comments? Questions? Suggestions for improvement?

[Peter Herndon]

I switched around the order, both in the actual application and in my
tests as replied to Francois Lepoutre above. Results were consistent,
after the first run of any given test, which unsurprisingly took a bit
longer.

[Francois Lepoutre]

Hi Peter

Running your benchmark, I ran into a couple of interesting points.
Using mx.ODBC, my times were 0.54 seconds and 6.56 seconds
respectively, while using adodbapi my results are 3.55 seconds and 25.9
seconds respectively. mx.ODBC is faster with the simple query you
provide.
We agree on figures at this stage :)
Next I modified the benchmark to reflect my particular circumstances
more accurately [...] reduce the number of iterations from 100
to 10. Since there are 128000 records in the main table, the wait for
100 iterations was too long for my patience. Under these
circumstances, mx.ODBC's numbers are 188.49 seconds and 377.56 seconds
respectively, and adodbapi's times are 111.15 seconds and 223.55
seconds respectively.
This is an interesting feedback. It looks like both middleware have
their distinct value and distinct set of advantages.

I'll definitely review my judgment on ADO!
My first wall-clock impressions are obvious exaggerations of reality,
for which I duly apologize to all. However, adodbapi did prove to be
faster in my admittedly very wacky common use case. Slower to connect,
but faster to run a substantial query. Comments? Questions? Suggestions for improvement?

Based on your results, my feeling is that mx.ODBC remains a solution
of choice for db-support behing web services "à la mod_python"
where connection time is essential whilst adodbapi would be the
definite winner when it comes to typical db-intensive win32-based
applications (such as wxpython-based ones).

Regards to you

Francois

[M.-A. Lemburg]

Peter Herndon wrote:

:) Knock away, as my info isn't scientific anyway. In my case, ASA is
*not* local. The db is running on a 500MHz x 2 server with 768MB RAM,
over 100BaseT connection. That same server is also running the MSSQL
instance, and IIS.

Running your benchmark, I ran into a couple of interesting points.
Using mx.ODBC, my times were 0.54 seconds and 6.56 seconds
respectively, while using adodbapi my results are 3.55 seconds and 25.9
seconds respectively. mx.ODBC is faster with the simple query you
provide.

Next I modified the benchmark to reflect my particular circumstances
more accurately (?Maybe? Comments invited). I changed the query to
one of the queries in regular use in my application. This query
selects 26 columns from 3 joined tables, with a where clause "where
f476 = ?", and I provide the missing value as a tuple in the execute
statement. Note that, as I mentioned in my reply to M-A, the f476
field is not indexed, and is a long varchar. Again, the system is
bought, so I have no control over the schema. ;)

The other change I made was to reduce the number of iterations from 100
to 10. Since there are 128000 records in the main table, the wait for
100 iterations was too long for my patience. Under these
circumstances, mx.ODBC's numbers are 188.49 seconds and 377.56 seconds
respectively, and adodbapi's times are 111.15 seconds and 223.55
seconds respectively.
Just curious: are you timing just the time it takes to
complete the .execute() or do you also fetch the complete
result or only part of it ?

Note that we have had reports about MS SQL Server being very
slow in determining the number of rows in a result set. It's
possible that ASA has the same problem.

The mxODBC 2.0 release fetches this number after every .execute().
If adodbapi avoids this (which we'll also integrate into mxODBC 2.1),
then this would explain the differences you see.

Another reason could indeed be related to the longvarchar
field: these fields are fetched in multiple chunks if the
ODBC driver doesn't provide proper size information - each
of these chunks will require a network access which slows
down the data fetching.

Since mxODBC supports Unicode, but defaults to returning
8-bit strings, it is also possible that your longvarchar
column is sent as Unicode and has to be converted to
an 8-bit string first. Thus, allowing mxODBC to return Unicode
could make a difference as well (see the docs on how this is
done).
My first wall-clock impressions are obvious exaggerations of reality,
for which I duly apologize to all. However, adodbapi did prove to be
faster in my admittedly very wacky common use case. Slower to connect,
but faster to run a substantial query.

Comments? Questions? Suggestions for improvement?

See some of the hints I mentioned above.

Note that it often also pay off checking the ODBC driver
settings, esp. if you have a networked setup - ODBC drivers
often pre-fetch result sets and changing the defaults they
use for this can make a huge difference in response times.

Unfortunately, mxODBC doesn't have control over these
settings and there's no standard for them, so you'll
have to check the ODBC driver documentation for details
on the best settings can be found and set.

Regards,
--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Apr 18 2005)

Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::

[Peter Herndon]

> > Next I modified the benchmark to reflect my particular circumstances

more accurately (?Maybe? Comments invited). I changed the query to
one of the queries in regular use in my application. This query
selects 26 columns from 3 joined tables, with a where clause "where
f476 = ?", and I provide the missing value as a tuple in the execute
statement. Note that, as I mentioned in my reply to M-A, the f476
field is not indexed, and is a long varchar. Again, the system is
bought, so I have no control over the schema. ;)

The other change I made was to reduce the number of iterations from 100
to 10. Since there are 128000 records in the main table, the wait for
100 iterations was too long for my patience. Under these
circumstances, mx.ODBC's numbers are 188.49 seconds and 377.56 seconds
respectively, and adodbapi's times are 111.15 seconds and 223.55
seconds respectively.
Just curious: are you timing just the time it takes to
complete the .execute() or do you also fetch the complete
result or only part of it ?

I have included the .fetchall() in the loop, yes. The only other
change I made to the benchmark is to use ODBC.Windows.DriverConnect
rather than .Connect, as that's what I'd used before in my
application. Is there a difference between the two, other than the
argument syntax?
The mxODBC 2.0 release fetches this number after every .execute().
If adodbapi avoids this (which we'll also integrate into mxODBC 2.1),
then this would explain the differences you see.

I look forward to retesting with 2.1 -- I'm curious to see what
differences arise.
Another reason could indeed be related to the longvarchar
field: these fields are fetched in multiple chunks if the
ODBC driver doesn't provide proper size information - each
of these chunks will require a network access which slows
down the data fetching.
The datum in the field is uniformly short, 8 characters or less. It's
actually our internal identifier. Again, I'd use a varchar and index
the field if I had control over the schema. However, the small size
of the datum argues against multiple chunks, unless the default chunk
size is really small.

Since mxODBC supports Unicode, but defaults to returning
8-bit strings, it is also possible that your longvarchar
column is sent as Unicode and has to be converted to
an 8-bit string first. Thus, allowing mxODBC to return Unicode
could make a difference as well (see the docs on how this is
done).
I'll take a look into this, as I have a vague memory of adodbapi
returning Unicode by default.
Note that it often also pay off checking the ODBC driver
settings, esp. if you have a networked setup - ODBC drivers
often pre-fetch result sets and changing the defaults they
use for this can make a huge difference in response times.

Unfortunately, mxODBC doesn't have control over these
settings and there's no standard for them, so you'll
have to check the ODBC driver documentation for details
on the best settings can be found and set.

I'll have to dig into this. There don't seem to be too many directly
available settings in ODBC Administrator, but there seem to be some
spots for inserting settings directly into a command line, so I'll
need to dig up the Sybase documentation on what's possible.

Regards,

--
Marc-Andre Lemburg
eGenix.com

Thank you very much for your patience and insight.

Cheers,

---Peter Herndon