473,289 Members | 1,842 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Database connectivity

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.
Jul 18 '05 #1
13 3371
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
Jul 18 '05 #2


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 ! ::::
Jul 18 '05 #3
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
Jul 18 '05 #4
> 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
Jul 18 '05 #5
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 <ma*************************************@python.or g>,
jf****@neurokode.com 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

Jul 18 '05 #6
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" <se***@yahoo.com>
To: <py*********@python.org>
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.py*********@python.or g>,
jf****@neurokode.com 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


Jul 18 '05 #7
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" <se***@yahoo.com>
To: <py*********@python.org>
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.py*********@python.or g>,
jf****@neurokode.com 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 ! ::::
Jul 18 '05 #8
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 <MP************************@news2.atlantic.net>, se***@yahoo.com 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.

Jul 18 '05 #9
> 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
Jul 18 '05 #10
Jon Franz wrote:
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.


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

http://msdn.microsoft.com/library/de...dappdpr_28.asp

I can't think of any use case for internal size...
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.


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.
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
Good catch :-) I'll fix that. It was true for mxODBC 1.x.
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.


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)
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 ! ::::
Jul 18 '05 #11
> Can you present a use case ? display_size is predefined statically in
ODBC:

http://msdn.microsoft.com/library/de...dappdpr_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.
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.


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
Jul 18 '05 #12
Jon Franz wrote:
Can you present a use case ? display_size is predefined statically in
ODBC:


http://msdn.microsoft.com/library/de...dappdpr_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.


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)
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 ! ::::
Jul 18 '05 #13
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

Jul 18 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Alex | last post by:
I have developed a VB application that uses Oracle 8i database for backend. The database server is on the local network. From my development machine, the application connects (via ODBC) to the...
1
by: harish | last post by:
I am using java applications to connect to my MS SQL Server database. I am using ODBC for database connectivity. If i am running my java applications in a remote PC and try to connect to database...
3
by: junaid | last post by:
Hi to all, Can it is possible to connect to the databse like postgre or mysql through a C programm? I know the basics of C and I have developed a small program which uses a file for the...
3
by: Niks | last post by:
Hi, I need to connect to SQL server Database using a System DSN. Can anyone tell me how to connect to SQL Server using DSN in ASP.NET (VB.Net). Using a Try Catch block. Does anyone know how to...
8
by: David | last post by:
Hi, Could someone please xplain how to add a field to an existing SQL table in VB.Net I have added the field in the Server Explorer and it shows up when I reload the program but I cannot...
3
by: Martin B | last post by:
Hallo! I'm working with C# .NET 2.0, implementing Client/Server Applications which are connecting via Network to SQL-Server or Oracle Databases. To stay independent from the underlaying Database...
47
by: Ivan Weiss | last post by:
Hi all, I am just getting started on a new app and am a rusty VB6 guy who is looking to create something on the side to serve as a corporate workbench for my company. ADO.net is new to me so...
0
by: dalaimanoj | last post by:
I have a form with buttons 'View', 'Save', 'Delete', 'Edit' and some textboxes to input data.. On clicking each buttons the database is connected and the respective action is done. For example...
4
by: ykhamitkar | last post by:
Hi there, I am trying to create a web based dataentry application in java applet. When I create a standalone application using applet the database connectivity works fine. However when I use same...
0
Dököll
by: Dököll | last post by:
ASP.NET and SQL Server 2005 Database interaction includes: Language: ASP.NET Connectivity: SQL Server 2005 Foreword: This database connectivity is nothing of genius, it is a simple...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.