Database connectivity

[SectorUnknown]

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.

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

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

 

[M.-A. Lemburg]

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)________________________________________________________________________

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

 

[Jon Franz]

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

 

[Joe Francia]

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

 

[SectorUnknown]

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/display_doc.php?docid=20024&group_id=
86244#supported

 

[Jon Franz]

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" <[email protected]>
To: <[email protected]>
Sent: Monday, November 24, 2003 3:47 PM
Subject: Re: Database connect / PDO

 

[M.-A. Lemburg]

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" <[email protected]>
To: <[email protected]>
Sent: Monday, November 24, 2003 3:47 PM
Subject: Re: Database connect / PDO

--
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source (#1, Nov 24 2003)________________________________________________________________________

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

 

[SectorUnknown]

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?

 

[Jon Franz]

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

 

[M.-A. Lemburg]

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

Can you present a use case ? display_size is predefined statically in
ODBC:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappdpr_28.asp

I can't think of any use case for internal size...

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.

Well, the display_size could be hard-coded, but I don't
see much a use... internal_size would be hard to figure out
and is not worth the performance it costs. APIs like .gettypeinf()
and .getinfo() can help you here, if you need more low-level
information.

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.


Then you should change your documentation
"The mxODBC package provides a nearly 100% Python Database API 2.0 compliant
interface "

Good catch I'll fix that. It was true for mxODBC 1.x.

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.

Hmm, I am the editor of the DB API 2.0 spec...

A database package can be 100% compliant without implementing
all optional features. The DB API spec was designed to allow
this since otherwise some modules would never be able to
call themselves compatible.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source (#1, Nov 24 2003)________________________________________________________________________

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

 

[Jon Franz]

Can you present a use case ? display_size is predefined statically in

ODBC:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappdpr_28.asp

I can't think of any use case for internal size...

Variable length character (or binary) fields...
I've written many a piece of code over the years that has had to
create dynamic edit forms for a database. Variable length
character fields are common place, and I've always found that
enforcing the limit at edit time, rather than letting an error be
raised or data be silently truncated, is a good practice.

Good catch I'll fix that. It was true for mxODBC 1.x.

No worries.

Hmm, I am the editor of the DB API 2.0 spec...

A database package can be 100% compliant without implementing
all optional features. The DB API spec was designed to allow
this since otherwise some modules would never be able to
call themselves compatible.

That's exactly what I thought, and as my statement said, I was only trying
to figure out why 'nearly' was used, and then argue against the use if
all of the required features were already present. A typo/slip-up makes
much more sense anyway.

cheers.

~Jon Franz
NeuroKode Labs, LLC

 

[M.-A. Lemburg]

Variable length character (or binary) fields...
I've written many a piece of code over the years that has had to
create dynamic edit forms for a database. Variable length
character fields are common place, and I've always found that
enforcing the limit at edit time, rather than letting an error be
raised or data be silently truncated, is a good practice.

Ah, now I understand: for VARCHAR columns, that information is
available in the precision field of .descpription. It may sound
like the wrong entry, but that's where ODBC put's this
information.

OTOH, what the optional description entries really mean is not
defined anywhere in the DB API. Perhaps we should fix that (on
the db-sig mailing list where these discussions usually happen) ?!

--
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source (#1, Nov 26 2003)________________________________________________________________________

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

 

[Jon Franz]

Ah, now I understand: for VARCHAR columns, that information is
available in the precision field of .descpription. It may sound
like the wrong entry, but that's where ODBC put's this
information.

Doh, it looks like you found my source of confusion though.

OTOH, what the optional description entries really mean is not
defined anywhere in the DB API. Perhaps we should fix that (on
the db-sig mailing list where these discussions usually happen) ?!

I was ready to mention this today, I'll move this to the db-sig list.
Good catch

~Jon Franz
NeuroKode Labs, LLC