«

I've written a database (Access mdb) front-end using Python/wxpython/and
ADO. However, the scope of the project has changed and I need to access
the same data on an MSSQL server. Also, the front-end needs to be cross-
platform (Windows and Linux).

Does anyone have any suggestions on what database connectivity I should
use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
the best way to go.

BTW, although I would love to move away from the MSSQL server, that's
not going to change.

SectorUnknown wrote:
> I've written a database (Access mdb) front-end using Python/wxpython/and
> ADO. However, the scope of the project has changed and I need to access
> the same data on an MSSQL server. Also, the front-end needs to be cross-
> platform (Windows and Linux).
>
> Does anyone have any suggestions on what database connectivity I should
> use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
> the best way to go. [...]

AFAIC you have two options:

- Use mxODBC and save yourself/your company/your employer a considerable
amount of time.

- Program to the Python DB-API v2.0 and work around the differences
between the various DB-API modules you'll need yourself. You'll probably
end up the n-th abstraction layer on top of the DB-API.

Another possibility is to see if an OR-thingie like PDO/SQLObject/...
actually helps for database abstraction. I suppose they'll get really
"fun" to use once you need advanced queries, though. Does anybody have
any real-life experience with any of these Python OR mappers?

-- Gerhard

SectorUnknown wrote:
> I've written a database (Access mdb) front-end using Python/wxpython/and
> ADO. However, the scope of the project has changed and I need to access
> the same data on an MSSQL server. Also, the front-end needs to be cross-
> platform (Windows and Linux).
>
> Does anyone have any suggestions on what database connectivity I should
> use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
> the best way to go.
>
> BTW, although I would love to move away from the MSSQL server, that's
> not going to change.

mxODBC works on Windows, Linux and quite a few other platforms.
Apart from mxODBC you will need an ODBC driver that allows
you to connect to the database (whereever it is running).

On Windows this is a no-brainer since all decent databases
come with a Windows ODBC driver.

On other platforms, the commercial
ODBC driver vendors are usually the best choice, but there are
also a couple of alternatives such as the FreeTDS ODBC which allows
connecting to MS SQL running on Windows, but whether these are
suitable for your needs depends on what you plan to do with the
database -- FreeTDS is not exactly high performance, nor very
reliable. However, it is quite usable for simple queries.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source (#1, Nov 24 2003)
>>> Python/Zope Products & Consulting ... 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 ! ::::

> Another possibility is to see if an OR-thingie like PDO/SQLObject/...
> actually helps for database abstraction. I suppose they'll get really
> "fun" to use once you need advanced queries, though. Does anybody have
> any real-life experience with any of these Python OR mappers?

I have lots of experience with PDO - but it is not an OR mapper.
It's an abstraction layer on top of the DB-API that adds functionality
and attempts to make it easier to write your application to a single
API. I guess you could say its the n-1th abstraction layer.

PDO might be useful for SectorUnknown's needs.

~Jon Franz
NeuroKode Labs, LLC

SectorUnknown wrote:
> I've written a database (Access mdb) front-end using Python/wxpython/and
> ADO. However, the scope of the project has changed and I need to access
> the same data on an MSSQL server. Also, the front-end needs to be cross-
> platform (Windows and Linux).
>
> Does anyone have any suggestions on what database connectivity I should
> use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
> the best way to go.
>
> BTW, although I would love to move away from the MSSQL server, that's
> not going to change.

Besides mxODBC, I know of two others:

Windows only:
http://adodbapi.sourceforge.net/

Cross-platform:
http://sourceforge.net/projects/pymssql/

Peace,
Joe

I've been looking through the documentation for PDO, but it sounds like
you still need mxODBC. Is this correct?

See: http://sourceforge.net/docman/displa...0024&group_id=
86244#supported

In article <mailman.1029.1069699121.702.python->,
says...
> > Another possibility is to see if an OR-thingie like PDO/SQLObject/...
> > actually helps for database abstraction. I suppose they'll get really
> > "fun" to use once you need advanced queries, though. Does anybody have
> > any real-life experience with any of these Python OR mappers?
>
> I have lots of experience with PDO - but it is not an OR mapper.
> It's an abstraction layer on top of the DB-API that adds functionality
> and attempts to make it easier to write your application to a single
> API. I guess you could say its the n-1th abstraction layer.
>
> PDO might be useful for SectorUnknown's needs.
>
> ~Jon Franz
> NeuroKode Labs, LLC
>
>
>

Yes, an underlying DBAPI driver is required, and mxODBC works...
mostly (see below).
I might recommend the adodbapi driver for use on the windows platform,
but that still leaves mxODBC for linux.

I may be wrong, but I think mxODBC module still doesn't provide all
the values for the DBAPI .description fields - I know the field name
is provided, but I don't know if the size info will be correct, or if a
non-None value will be available for the other attributes.
Thus, some of the Field object's member variables may be meaningless
when used with mxODBC on your project.

Does anyone know offhand when mxODBC will add this info? I think
its the last (or one of the few) things holding it back from true DBAPI
2.0 compliance.

~Jon Franz
NeuroKode Labs, LLC


----- Original Message -----
From: "SectorUnknown" <>
To: <python->
Sent: Monday, November 24, 2003 3:47 PM
Subject: Re: Database connect / PDO


> I've been looking through the documentation for PDO, but it sounds like
> you still need mxODBC. Is this correct?
>
> See: http://sourceforge.net/docman/displa...0024&group_id=
> 86244#supported
>
> In article <mailman.1029.1069699121.702.python->,
> says...
> > > Another possibility is to see if an OR-thingie like PDO/SQLObject/...
> > > actually helps for database abstraction. I suppose they'll get really
> > > "fun" to use once you need advanced queries, though. Does anybody have
> > > any real-life experience with any of these Python OR mappers?
> >
> > I have lots of experience with PDO - but it is not an OR mapper.
> > It's an abstraction layer on top of the DB-API that adds functionality
> > and attempts to make it easier to write your application to a single
> > API. I guess you could say its the n-1th abstraction layer.
> >
> > PDO might be useful for SectorUnknown's needs.
> >
> > ~Jon Franz
> > NeuroKode Labs, LLC
> >
> >
> >
>
>

Jon Franz wrote:
> Yes, an underlying DBAPI driver is required, and mxODBC works...
> mostly (see below).
> I might recommend the adodbapi driver for use on the windows platform,
> but that still leaves mxODBC for linux.
>
> I may be wrong, but I think mxODBC module still doesn't provide all
> the values for the DBAPI .description fields - I know the field name
> is provided, but I don't know if the size info will be correct, or if a
> non-None value will be available for the other attributes.

mxODBC provides all .description values except display_size and
internal_size (and this is allowed by the DB API standard). These
two values are rarely of importance and if you absolutely need them
they can also be queried using the catalog methods the mxODBC exposes.

You should note however, that some ODBC database drivers try
to be smart and "optimize" the return values that you see
in .description (the MyODBC driver is a prominent example).
While this is allowed by the ODBC standard, it is certainly
not good practice.

As a result, the only true source of the schema information
are the catalog methods, e.g. .columns() available in mxODBC.
These also provide much more information than is available in
..description.

> Thus, some of the Field object's member variables may be meaningless
> when used with mxODBC on your project.
>
> Does anyone know offhand when mxODBC will add this info? I think
> its the last (or one of the few) things holding it back from true DBAPI
> 2.0 compliance.

mxODBC 2.0.x is 100% DB API 2.0 compliant.

The only omissions are .nextset() and .callproc() which will be
available in mxODBC 2.1.0. Both are optional in the DB API 2.0
specification.

> ~Jon Franz
> NeuroKode Labs, LLC
>
>
> ----- Original Message -----
> From: "SectorUnknown" <>
> To: <python->
> Sent: Monday, November 24, 2003 3:47 PM
> Subject: Re: Database connect / PDO
>
>
>
>>I've been looking through the documentation for PDO, but it sounds like
>>you still need mxODBC. Is this correct?
>>
>>See: http://sourceforge.net/docman/displa...0024&group_id=
>>86244#supported
>>
>>In article <mailman.1029.1069699121.702.python->,
>> says...
>>
>>>>Another possibility is to see if an OR-thingie like PDO/SQLObject/...
>>>>actually helps for database abstraction. I suppose they'll get really
>>>>"fun" to use once you need advanced queries, though. Does anybody have
>>>>any real-life experience with any of these Python OR mappers?
>>>
>>>I have lots of experience with PDO - but it is not an OR mapper.
>>>It's an abstraction layer on top of the DB-API that adds functionality
>>>and attempts to make it easier to write your application to a single
>>>API. I guess you could say its the n-1th abstraction layer.
>>>
>>>PDO might be useful for SectorUnknown's needs.
>>>
>>>~Jon Franz
>>>NeuroKode Labs, LLC
>>>
>>>
>>>
>>
>>
>
>

--
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source (#1, Nov 24 2003)
>>> Python/Zope Products & Consulting ... 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 ! ::::

Thank you for your help.

What I'm hearing though, is that there isn't an open source way to write an application for
both Linux and Windows and have it access an MS-SQL server. Is this true?

mxODBC requires a license and PDO appears to require mxODBC to run. (I work for a company and
the program would be developed for internal use.)

Is there another opportunity I'm missing?


In article <>, says...
> I've written a database (Access mdb) front-end using Python/wxpython/and
> ADO. However, the scope of the project has changed and I need to access
> the same data on an MSSQL server. Also, the front-end needs to be cross-
> platform (Windows and Linux).
>
> Does anyone have any suggestions on what database connectivity I should
> use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
> the best way to go.
>
> BTW, although I would love to move away from the MSSQL server, that's
> not going to change.
>

> mxODBC provides all .description values except display_size and
> internal_size (and this is allowed by the DB API standard). These
> two values are rarely of importance and if you absolutely need them
> they can also be queried using the catalog methods the mxODBC exposes.

Sorry, but I disagree - these two values can be very important.

> You should note however, that some ODBC database drivers try
> to be smart and "optimize" the return values that you see
> in .description (the MyODBC driver is a prominent example).
> While this is allowed by the ODBC standard, it is certainly
> not good practice.
>
> As a result, the only true source of the schema information
> are the catalog methods, e.g. .columns() available in mxODBC.
> These also provide much more information than is available in
> .description.

I can understand where you are coming from in that the drivers
themselves may make it impossible to provide full/accurate column
data from a query. I'd wager you can't even automate calls to
..column() because mxODBC doesn't necessarily know what table
a column came from when results are fetched.

I can only speak for myself, but it is quite frustrating to not get
the information I need when I perform a query. Please realize
that my message was not intended as a defacement or argument
against mxODBC - I was simply warning the user of the pitfalls they
may experience when using it with PDO.

> mxODBC 2.0.x is 100% DB API 2.0 compliant.

Then you should change your documentation
"The mxODBC package provides a nearly 100% Python Database API 2.0 compliant
interface "
>From http://www.egenix.com/files/python/mxODBC.html

> The only omissions are .nextset() and .callproc() which will be
> available in mxODBC 2.1.0. Both are optional in the DB API 2.0
> specification.

If this is why the documentation says nearly, then your interpretation
of what 100% would mean is different from mine. 100% compliant
would, in my mind, be supporting all required interfaces. I wouldn't
think optional interfaces are needed for compliance, and supporting
them, although good, wouldn't come into the percentage... unless you
wanted to say you were 105% compliant .Just my two cents.

~Jon Franz
NeuroKode Labs, LLC