Python and Sybase

[David Rysdam]

What module is most recommended for accessing Sybase from Python? This
one: http://www.object-craft.com.au/projects/sybase/sybase/ ?

Also, is there any module that provides a generic DB API and can be
hooked to both Sybase and postgresql? This
(http://www.python.org/peps/pep-0249.html) looks pretty old.

[Skip Montanaro]

David> What module is most recommended for accessing Sybase from Python?
David> This one: http://www.object-craft.com.au/projects/sybase/sybase/
David> ?

If I'm not mistaken, I think that's the only one, barring some sort of ODBC
stuff.

David> Also, is there any module that provides a generic DB API and can
David> be hooked to both Sybase and postgresql? This
David> (http://www.python.org/peps/pep-0249.html) looks pretty old.

There hasn't been that much change to how relational databases are accessed
in the time since that PEP was finalized. Do you think something's missing
or was done wrong? If so, I think the db****@python.org list is still alive
and well.

Skip

[Josh Close]

On Thu, 23 Sep 2004 14:50:04 -0400, David Rysdam <dr*****@ll.mit.edu> wrote:

What module is most recommended for accessing Sybase from Python? This
one: http://www.object-craft.com.au/projects/sybase/sybase/ ?
Object-Craft's is the best one I've found so far.

Also, is there any module that provides a generic DB API and can be
hooked to both Sybase and postgresql? This
(http://www.python.org/peps/pep-0249.html) looks pretty old.

Why would you need that? Python has a DB API, so all you have to do to
change from db to db is the connection string.

conn = Sybase.connection(.....)
conn = otherModules.connection(....)

Everthing else should work the same way.

-Josh

[John J. Lee]

David Rysdam <dr*****@ll.mit.edu> writes:

What module is most recommended for accessing Sybase from Python?
This one: http://www.object-craft.com.au/projects/sybase/sybase/ ?
As far as I know, that's the only one.

Also, is there any module that provides a generic DB API and can be
hooked to both Sybase and postgresql? This
(http://www.python.org/peps/pep-0249.html) looks pretty old.

Not a single module -- why would you want one?

The DB API (version 2) is the standard. The idea of the DB API is to
make it possible to write DB API code that works across multiple
database backends. No need at all for a single module, just a single
standard. Unfortunately, making this work is not entirely trivial:
There are some stupid issues that are the fault of the DB API -- SQL
argument substitution syntax in particular -- and some trickier ones
-- particularly the variations in the capabilities of DBMSes. If you
want your code to work across multiple DBMS backends, you should test
it with those DBMSes from the start. Doing it later is possible but
may be hard work (made less difficult if you keep your DB code
localised).
John

[David Rysdam]

John J. Lee wrote:

David Rysdam <dr*****@ll.mit.edu> writes:

Also, is there any module that provides a generic DB API and can be
hooked to both Sybase and postgresql? This
(http://www.python.org/peps/pep-0249.html) looks pretty old.

Not a single module -- why would you want one?

The DB API (version 2) is the standard. The idea of the DB API is to
make it possible to write DB API code that works across multiple
database backends. No need at all for a single module, just a single
standard. Unfortunately, making this work is not entirely trivial:
There are some stupid issues that are the fault of the DB API -- SQL
argument substitution syntax in particular -- and some trickier ones
-- particularly the variations in the capabilities of DBMSes. If you
want your code to work across multiple DBMS backends, you should test
it with those DBMSes from the start. Doing it later is possible but
may be hard work (made less difficult if you keep your DB code
localised).

Yes, I posted this question before I understood what the DB API was.
Looks good.

[Nelson Minar]

David Rysdam <dr*****@ll.mit.edu> writes:

What module is most recommended for accessing Sybase from Python?
This one: http://www.object-craft.com.au/projects/sybase/sybase/ ?

While we're here, is there a free module for accessing SQLServer from
Python? Last I checked this Sybase driver was the only option.

[Josh Close]

On Fri, 24 Sep 2004 15:45:21 GMT, Nelson Minar <ne****@monkey.org> wrote:

David Rysdam <dr*****@ll.mit.edu> writes:

What module is most recommended for accessing Sybase from Python?
This one: http://www.object-craft.com.au/projects/sybase/sybase/ ?

While we're here, is there a free module for accessing SQLServer from
Python? Last I checked this Sybase driver was the only option.

Pymssql is, but I don't think it's DB API-2.0 compliant.

-Josh

[Moof]

Nelson Minar wrote:

While we're here, is there a free module for accessing SQLServer from
Python? Last I checked this Sybase driver was the only option.

Pywin32 (formerly win32all) has an odbc module which is DB-API 1.0 compliant.

There's also adodbapi, which is DB-API 2.0 compliant.

I haven't used the latter enough to work out if it's reasonably useful. I
don't get on with the former very well, though.

Oh, and non-free there's mxODBC, which also works under unix.

Also from unix someone mentioned a module based on ODBTP, which hasn't yet
surfaced out in the wild.

Moof

[Alan Kennedy]

[David Rysdam]

What module is most recommended for accessing Sybase from Python?
This one: http://www.object-craft.com.au/projects/sybase/sybase/?

[Nelson Minar] While we're here, is there a free module for accessing SQLServer
from Python? Last I checked this Sybase driver was the only option.
[Josh Close] Pymssql is, but I don't think it's DB API-2.0 compliant.
[Moof] Pywin32 (formerly win32all) has an odbc module which is DB-API 1.0
compliant.

There's also adodbapi, which is DB-API 2.0 compliant.

I haven't used the latter enough to work out if it's reasonably
useful. Idon't get on with the former very well, though.

Oh, and non-free there's mxODBC, which also works under unix.

Also from unix someone mentioned a module based on ODBTP, which
hasn't yet surfaced out in the wild.

Thanks to Josh and Moof for pointing out the other alternatives for
connecting to Sybase and MS-SQLServer.

When looking at the DB-SIG web page, although I can see a list of
database modules there that support the DB-API in its different versions
and levels, I can see no easy way for that list to be updated with the
above sort of invaluable information.

Might it be a good idea to make at least part of the DB-SIG web page a
wiki/moin? A moin works well in the web framework/templating arena, so
perhaps it is also suitable for database modules?

Regards,

Alan.

[Skip Montanaro]

Alan> When looking at the DB-SIG web page, although I can see a list of
Alan> database modules there that support the DB-API in its different
Alan> versions and levels, I can see no easy way for that list to be
Alan> updated with the above sort of invaluable information.

Alan> Might it be a good idea to make at least part of the DB-SIG web
Alan> page a wiki/moin? A moin works well in the web
Alan> framework/templating arena, so perhaps it is also suitable for
Alan> database modules?

Go ahead and update the Python wiki:

http://www.python.org/moin

culling whatever dynamic information you think approrpriate from the
existing db-sig pages. Note that there are already several pages with
the word "database" in their titles. Search at the bottom of any page to
find them. It's likely one of them will be a better starting place than a
clean sheet of WikiPaper.

Once you have something, let me know and I'll update the topics/data/modules
page to refer to it. It would be extra helpful if you could update a copy
of this page:

http://www.python.org/topics/database/modules.ht

(note the lack of "ml" at the end) and shoot it to me. If so, that will
simplify my job.

Skip

[Alex Martelli]

Josh Close <na****@gmail.com> wrote:

Also, is there any module that provides a generic DB API and can be
hooked to both Sybase and postgresql? This
(http://www.python.org/peps/pep-0249.html) looks pretty old.

Why would you need that? Python has a DB API, so all you have to do to
change from db to db is the connection string.

conn = Sybase.connection(.....)
conn = otherModules.connection(....)

Everthing else should work the same way.

....except that you may need to use different placeholders in SQL strings
for parameters to insert (sigh)...
Alex

[John J. Lee]

al*****@yahoo.com (Alex Martelli) writes:

Josh Close <na****@gmail.com> wrote:

Also, is there any module that provides a generic DB API and can be
hooked to both Sybase and postgresql? This
(http://www.python.org/peps/pep-0249.html) looks pretty old.

Why would you need that? Python has a DB API, so all you have to do to
change from db to db is the connection string.

conn = Sybase.connection(.....)
conn = otherModules.connection(....)

Everthing else should work the same way.

...except that you may need to use different placeholders in SQL strings
for parameters to insert (sigh)...

....and there are of course variations in SQL from DBMS to DBMS...

IIRC, Steve Holden suggests in his book [1] the use of
internationalization (i18n) tools (eg. gettext) to make DB API code
portable. Seems unfortunately apt.
[1] "Python Web Programming", New Riders (2002)
John

[Alan Kennedy]

[Alan Kennedy]

When looking at the DB-SIG web page, although I can see a list of
database modules there that support the DB-API in its different
versions and levels, I can see no easy way for that list to be
updated with the above sort of invaluable information.

Might it be a good idea to make at least part of the DB-SIG web
page a wiki/moin? A moin works well in the web
framework/templating arena, so perhaps it is also suitable for
database modules?

[Skip Montanaro] Go ahead and update the Python wiki:

http://www.python.org/moin

culling whatever dynamic information you think approrpriate from the
existing db-sig pages. Note that there are already several pages
with the word "database" in their titles. Search at the bottom of any
page to find them. It's likely one of them will be a better starting
place than a clean sheet of WikiPaper.

Once you have something, let me know and I'll update the
topics/data/modules page to refer to it. It would be extra helpful
if you could update a copy of this page:

http://www.python.org/topics/database/modules.ht

(note the lack of "ml" at the end) and shoot it to me. If so, that
will simplify my job.

Thanks for all your work on the python.org website Skip.

When I first posed my question, the sort of thing that I had in mind was
a comparison matrix, something like I've done for HTTP proxies:-

http://www.xhaus.com/alan/python/proxies.html

On browsing around the existing python moin, I see that exactly what I
suggested is already there, namely

http://www.python.org/moin/DbApiModuleComparison

and

http://www.python.org/moin/ChoosingDatabase

Although the DbApiModuleComparison seems less than 100% complete, I
think that may be because a lot of people don't know that it's there, so
they don't update it.

Maybe a good solution would be to simply link the above pages from the
DB-SIG main page, so that they can be more easily found?

I would make two small changes to the structure of the Module comparison
page:-

1. Change the "ThreadSupport" column to instead refer to the much more
tightly defined "threadsafety" variable from the DB-API spec, i.e.
"Integer constant stating the level of thread safety the interface
supports."

2. Add a "Pure python" column to differentiate between modules that are
pure python (and thus potentially portable between pythons), and modules
that require a native layer in C or Java, etc.

regards,

--
alan kennedy
------------------------------------------------------
email alan: http://xhaus.com/contact/alan

[Josh Close]

On Sat, 25 Sep 2004 01:09:05 +0200, Alex Martelli <al*****@yahoo.com> wrote:

...except that you may need to use different placeholders in SQL strings
for parameters to insert (sigh)...

Forgot about that.... I use sybase for mssql connections, and freetds
doesn't have support for params, so I forgot about that.

-Josh

[Alex Martelli]

John J. Lee <jj*@pobox.com> wrote:
...

...except that you may need to use different placeholders in SQL strings
for parameters to insert (sigh)...
...and there are of course variations in SQL from DBMS to DBMS...

Yeah, _those_ I'm resigned to. No language can paper over them. But a
language shouldn't ADD difficulties such as placeholder differences.

IIRC, Steve Holden suggests in his book [1] the use of
internationalization (i18n) tools (eg. gettext) to make DB API code
portable. Seems unfortunately apt.

[1] "Python Web Programming", New Riders (2002)

Superb book, though unfortunately dated "pythonwise". I was a technical
reviewer for it and loved the experience -- and when I wanted to give
Anna, at the time my long-lost and recently-reconnected friend, a
birthday gift consonant to her nascent interest in Python, that's the
book I had Amazon deliver to her door. Since she's now my wife, and a
Python enthusiast, it seems to have worked. It's particularly great in
terms of the lightweight but useful coverage it gives on so many things,
from E/R design to HTTP and other protocols.

But on that one detail I beg to differ from my good friend Steve.
Dropping down to string-substitution is not really the best way to solve
SQL discrepancies among RDBMS's. Unfortunately, what I developed for
the purpose was a "work for hire", and to this day remains proprietary
to the firm I developed it for (think3 inc, if you must know) -- when I
resigned, one part of our verbal agreements was that they'd opensource
certain parts of their SW (mostly infrastructure aspects I had
developed) and I'd freely take them over and maintain them on
sourceforge or the like... they never followed through and I got tired
of pressuring them. Next time I should need that kind of thing from an
employer or client, I'm gonna get it in writing:-).

Anyway, the solution was born back in the day where we had to add
Informix to Ingres (or was it vice versa?) in the set of RDBMSs
supported by our ancillary applications (main applications were in the
CAD field for mechanical engineering, but RDBMS access was needed by
helper applications doing Bills of Materials and the like). Rather than
kludging it up as I was requested to do, I veered off on my own and made
up a more general solution -- part of my motivation was that I owned a
license of Watcom SQL and wanted to be able to develop at home using
that, even though supporting it was of no interest to the firm. To make
a long story short, it worked -- it basically reimplemented the original
Relational model, quite independent of SQL _as a language_, and
generated SQL on the fly (well, not quite on-the-fly, it was done
offline) in the required dialect. I remember later ports to such
'alien' RDBMSs as Oracle, Jet (aka [incorrectly] 'Access'), and SQL
Server, taking 1/4 the time management reasonably expected them to
(that's how I got to goof off on Usenet for 2/4ths of that time, and
still deliver in 3/4 of the time and look good;-).

But it's all captive, closed-source, proprietary stuff now. Sigh....
Alex

[Alex Martelli]

Josh Close <na****@gmail.com> wrote:

On Sat, 25 Sep 2004 01:09:05 +0200, Alex Martelli <al*****@yahoo.com> wrote:

...except that you may need to use different placeholders in SQL strings
for parameters to insert (sigh)...

Forgot about that.... I use sybase for mssql connections, and freetds
doesn't have support for params, so I forgot about that.

No support for parameters?! Talk about junk...!-(
Alex

[Josh Close]

On Sat, 25 Sep 2004 19:47:24 +0200, Alex Martelli <al*****@yahoo.com> wrote:

No support for parameters?! Talk about junk...!-(

Alex

Well put!

-Josh

[Steve Holden]

Moof wrote:

Nelson Minar wrote:

While we're here, is there a free module for accessing SQLServer from
Python? Last I checked this Sybase driver was the only option.

Pywin32 (formerly win32all) has an odbc module which is DB-API 1.0 compliant.

I believe Mark Hammond doesn't make exaggerated claims for this module,
and I certainly wouldn't recommend it.
There's also adodbapi, which is DB-API 2.0 compliant.
For what it's worth, I actually managed to use adodbapi as a drop-in
replacement for mx.ODBC.Windows accessing a SQL Server database - there
were resource-utilization problems, which we eventually traced to IIS
(lest anyone should interpret this as criticism of mx.ODBC, an excellent
piece of software).
I haven't used the latter enough to work out if it's reasonably useful. I
don't get on with the former very well, though.

Oh, and non-free there's mxODBC, which also works under unix.
Also, remember, mx.ODBC's licensing allow use on non-commercial projects
without charge.
Also from unix someone mentioned a module based on ODBTP, which hasn't yet
surfaced out in the wild.

Moof

regards
Steve

[Benji York]

Moof <us****@metamoof.net> wrote in message news:<2r*************@uni-berlin.de>...

While we're here, is there a free module for accessing SQLServer from
Python? Last I checked this Sybase driver was the only option.

Also from unix someone mentioned a module based on ODBTP, which hasn't yet
surfaced out in the wild.

Consider it surfaced.

It's still alpha, and I'm improving the packaging, but it's not hard
to install: http://benjiyork.com/odbtp.html.

I've been using it to access SQL Server, and other databases that
have Windows-only ODBC drivers from Linux. So far it works
wonderfully.
--
Benji York
http://benjiyork.com