"Correct" db adapter

[king kikapu]

Hi to all,

i have started a month ago to seriously studying Python. I am now
looking at the databases stuff
and i want the opinion of more experienced Python programmers (than
me) at the following :

I see that there are a lot of databases adapters on the net, some
following the DB-API 2.0 and some others do not. I want to use a db-
module that do not tie me down to a specific database and that fully
supports DB-API 2.0
Now i am using Sql Server but who knows about tomorrow.

I started using pyodbc and looking how i can e.x. call stored
procedure with arguments and all that stuff.
This is using ODBC syntac and i found enough info on the net.

Is the approach i took the "correct" one or is there a better db-
module so i can use ?

Thanks in advance

 

[Laurent Rahuel]

Maybe you should take a look at sqlalchemy

 

[Daniel Nogradi]

Maybe you should take a look at sqlalchemy
SQLObject might be of interest to you:
http://sqlobject.org/ it is a DB-API 2.0 compliant ORM.

 

[king kikapu]

Thanks for the replies.

I think i do not need something like ORM, but just a db-module that i
can "work" the database with it.
I just want to know if pyodbc is the "correct" solution to do so or if
it is another db-module that is more
usefull for this job.

 

[Bruno Desthuilliers]

king kikapu a écrit :

Thanks for the replies.

I think i do not need something like ORM, but just a db-module that i
can "work" the database with it.

FWIW, SQLAlchemy is not an ORM, but an higher-level API for SQL
integration. The ORM part is an optional feature built on top of this
API. But I'm not sure SQLAlchemy supports SQL Server anyway !-)

I just want to know if pyodbc is the "correct" solution to do so or if
it is another db-module that is more
usefull for this job.

AFAICT:

* there's an experimental MS SQL Server db-module:
http://www.object-craft.com.au/projects/mssql/

* the Win32 extensions offers support for ADO, but then it's not db-api
compliant

* unless you use adodbapi, but I don't know if it's still supported
(last release is 3+ years old):
http://adodbapi.sourceforge.net/

HTH

 

[Dennis Lee Bieber]

I see that there are a lot of databases adapters on the net, some
following the DB-API 2.0 and some others do not. I want to use a db-
module that do not tie me down to a specific database and that fully
supports DB-API 2.0
Now i am using Sql Server but who knows about tomorrow.

Is the approach i took the "correct" one or is there a better db-
module so i can use ?

There are trade-offs in either scheme.

The DBMS-specific adapters use the same access libraries as the
native clients. Your queries go directly from your program to the DBMS
and back. They may also support DBMS-specific non-standard features.

ODBC throws a middle layer into the mix. While your code may not
(depending upon how much of the SQL becomes pass-through vs hidden
translation) need changes beyond the DSN used on connection, you now
need to define ODBC data sources and have the proper ODBC driver for the
target DBMS. Your queries now go from your program to the ODBC front-end
which passes them to the active back-end driver which then formats them
into the DBMS-specific connection method.

For example, using ODBC to access an SQLite database seems like a
lot of overhead, given that it is a file-server database where the
access code is normally linked as part of the application -- there is no
DBMS engine running as a server. ODBC, in this situation, just feels so
much like running a command line client via subprocess and piping
commands back and forth <G>


I believe mxODBC gets a lot of mention, if one stays within the ODBC
route and desires an alternate to pyodbc.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Diez B. Roggisch]

Thanks for the replies.

I think i do not need something like ORM, but just a db-module that i
can "work" the database with it.
I just want to know if pyodbc is the "correct" solution to do so or if
it is another db-module that is more
usefull for this job.

I think you've got something wrong. There is no such thing as a generic
DB-Adapter, except maybe from the mxODBC which pushes the abstraction to
the ODBC-layer.

If your app is DB-API2.0-compatible, you should be able to more or less just
use your code with different adapters. There are several ways to accomplish
a configurable way, but in the end it boils down to someting like this:

if is_postgres:
import psycopg2 as db
if is_mysql:
import MySQLdb as db

Then db can be used to connect and query the db. A thin layer on top of that
should maybe be written to cover different parameter-styles, but that
shouldn't be too hard.

Diez

 

[king kikapu]

Ok, i see..

Thanks a lot all of you for the help. I know from my Win/.Net/Sql
Server expertise that odbc put a layer in the mix. That's why, for
example, in .Net we have a native SqlClient data provider that talks
to Sql Server directly.

But one of the reasons that i started learning Python, is to NOT to be
tied-up again with ANY company or specific product again (i had enough
MS addiction over these years...). So, based on this direction, i am
using pyodbc, which is DB-API2 compliant and i suppose that the code i
write this way, will have little changes to use another db in the
future.

I dig the net and found that there are several efforts for modules
specific to some databases but some of them are incomplete, faded, or
not db-api2 compliant.

So, i will check mxOdbc and see if there is a reason to use it over
pyodbc.

 

[Peter Decker]

But one of the reasons that i started learning Python, is to NOT to be
tied-up again with ANY company or specific product again (i had enough
MS addiction over these years...). So, based on this direction, i am
using pyodbc, which is DB-API2 compliant and i suppose that the code i
write this way, will have little changes to use another db in the
future.

I don't know if this product will meet your needs, but I've read
recently on the Dabo list that they've added support for Microsoft SQL
Server. I don't work with databases, so I have no idea how this might
compare to what you're looking at, but I do know that in general their
code is solid and the authors are responsive. So you might want to
check out Dabo:
http://dabodev.com

 

[Alejandro Dubrovsky]

king kikapu a écrit :

FWIW, SQLAlchemy is not an ORM, but an higher-level API for SQL
integration. The ORM part is an optional feature built on top of this
API. But I'm not sure SQLAlchemy supports SQL Server anyway !-)


AFAICT:

* there's an experimental MS SQL Server db-module:
http://www.object-craft.com.au/projects/mssql/

* the Win32 extensions offers support for ADO, but then it's not db-api
compliant

* unless you use adodbapi, but I don't know if it's still supported
(last release is 3+ years old):
http://adodbapi.sourceforge.net/

HTH

There's also pymssql <http://pymssql.sourceforge.net/> which works well
enough most of the time.

 

[king kikapu]

Thank you all!