Using unlisted dialect to connect with sqlanydb

[leh...@cnm.de]

Hello,

the Python DB-API 2.0 driver 'sqlanydb' for SQL Anywhere works fine and I'd like to use the SQLAlchemy connection pool within Pyramid. Unfortunately, SQLAlchemy doesn't let me use sqlanydb.

Actually, I don't need any ORM stuff for the start, but if its required, the mssql dialect should be pretty close to SQL Anywhere. But using create_engine() with special connect_args for sqlanydb doesn't work and using the creator=<callback> parameter doesn't work either. I always get "No module named pyodbc" because the dialects mssql and sybase seem to default to pyodbc.

Why isn't it possible to use just any DB-API compliant database driver?

I also read a post which claimed, that until SQLAlchemy 0.5 there was a driver for sqlanydb included, but I couldn't find one in that release. It would be very bad if I had to resign from using SQLAlchemy, but I have no choice in the database driver. I'm also not that much into SQLAlchemy to write an own driver and dialect, but I'm happy to run a test suite on a current SQL Anywhere database if someone else does.

Kind regards
Marten

[Michael Bayer]

On Sep 18, 2012, at 1:14 PM, leh...@cnm.de wrote:

Hello,

the Python DB-API 2.0 driver 'sqlanydb' for SQL Anywhere works fine and I'd like to use the SQLAlchemy connection pool within Pyramid. Unfortunately, SQLAlchemy doesn't let me use sqlanydb.

if you want pooling of a DBAPI and nothing else, create_engine() is not required.   use pool.manage:

http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html?highlight=manage#pooling-plain-db-api-connections

Actually, I don't need any ORM stuff for the start, but if its required, the mssql dialect should be pretty close to SQL Anywhere.

in fact it's closer to Sybase, and we used to have a "sybase" dialect someone provided which was actually SQL Anywhere, though it was in disrepair.   At some point we were tasked with creating a real Sybase dialect, which replaced the SQL Anywhere dialect.  

But using create_engine() with special connect_args for sqlanydb doesn't work and using the creator=<callback> parameter doesn't work either. I always get "No module named pyodbc" because the dialects mssql and sybase seem to default to pyodbc.

The "pyodbc" usage there is a default, which you can actually change by passing in the "sqlanydb" module as an argument to create_engine(), called "dbapi":

engine = create_engine("sybase+pyodbc://...", dbapi=sqlanydb)

However this can still have issues as DBAPIs all have non-standard behaviors (see below).    if you wanted to adapt the sybase or mssql dialects to SQL Anywhere fully, you can create a sqlanydb dialect of your own with a dozen lines of code or so.   The dialect can then be installed using a setuptools entrypoint.    Short DBAPI "stubs" like this don't really require "programming" as much as a little bit of cut and paste.   Examples of super-short DBAPI stubs include dialects/informix/informix.py and dialects/sybase/pysybase.py.     

Why isn't it possible to use just any DB-API compliant database driver?

within the create_engine()/dialect system, all DBAPIs have tons of idiosyncrasies that make them all effectively incompatible.   Right from dbapi.connect(), the format of arguments accepted by connect() is entirely unspecified and changes dramatically with all DBAPIs.   From there, there are dozens of other areas where non-standard or inconsistent behaviors must be normalized.   This is why you see in the dialect system not just a Python module for every database we support, but beyond that an extra module for every possible DBAPI running against that target DB.

I also read a post which claimed, that until SQLAlchemy 0.5 there was a driver for sqlanydb included, but I couldn't find one in that release. It would be very bad if I had to resign from using SQLAlchemy, but I have no choice in the database driver. I'm also not that much into SQLAlchemy to write an own driver and dialect, but I'm happy to run a test suite on a current SQL Anywhere database if someone else does.

Kind regards
Marten

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/19fdkEbEB_AJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.