handle DBAPI ImportErrors properly

[Mike Bayer]

right now all the database modules do some combination of module-level imports, silently catching errors and setting dbapis to None, etc., this is so that all database modules are importable on all systems and dialects are creatable, even if the DBAPI is not installed in the environment. this functionality is required for testing as well as the usage of database dialects to generate SQL independently of their target platform.

a consistent module-importing scheme should be developed which pushes all imports into a standard place and raises errors, but those errors are only reported when the DBAPI is required, i.e. when you want to create an engine with the DBAPI.

so requirements:

• all dialects should include a "module" keyword argument, where a module can be passed to the dialect that serves as its DBAPI.
• all database modules need some standard "module importing" method which raises ImportError as appropriate.
• engine/strategies.py DefaultEngineStrategy needes to report these ImportErrors where it currently says "Cant get DBAPI module for dialect '%s'".
• engine/default.py _figure_paramstyle needs to optionally load the DBAPI from the dialect. no exception should be raised. this implies that a dialect needs to attempt to load the DBAPI modules, fail, not report the error, but do report the error for a create_engine() call.
• the DBAPI modules should probably be local to the Dialect, and not even be module-wide, so that the create_engine() call can repeatedly be called and will repeatedly raise ImportErrors if modules arent loadable.
• MS-SQL is going to need some tinkering here, since it has functionality to load three different modules now. it might be useful (or maybe not) for use_adodbapi(), use_pymssql(), etc. functions return some kind of strategy object that is used by the dialect for the functions it provides (i.e. connect, do_commit, make_connect_string) so that these functions are local.

i think this ticket is pretty high priority since the first issue new users are going to hit is that their DBAPI is not importable and this is a point of confusion.

[Former user]

(original author: ram) Some random thoughts on the above items:

Not sure how much sense it makes to pass a DB-module argument to a dialect -- Most if not all dialects are going to expect/require a specific DB-module, and not work with anything else. How is a database module supposed to figure out that it was given a "wrong" module? An optional "load your module now" call makes more sense.

Agree with tying module to dialect, esp. for those database modules such as MS-SQL that support mulitple DB-API modules.

[paj]

Yes, reporting appropriate errors for these problems would definitely help.

What I really need for MSSQL is a string argument to specify the module to use. I will be specifying this is a TurboGears configuration file, where it's not possible to specify a module. Also, I think it makes sense for the dialect to do the actual importing.

I have prepared a patch for MSSQL that refactors it a bit to improve importing. You can now have multiple engines using different MSSQL connectors, at the same time. Pass "module" to create_engine, set to a string - adodbapi/pymssql/pyodbc. The error reporting is improved, although you still can't create a dialect without at least one connector present.

It also removes the "pyodbc is experimental" warning. I'll attach the patch when Trac is working again!

[Mike Bayer]

assuming the permissions were off on the attachments directory (didnt see an error in the logs). try your attachment again.

also, the "module" argument is expected across all dialects to be an actual python module instance. if you want to pass a string, lets call it "module_name" or something.

[paj]

Patch to improve MSSQL db module selection

[paj]

Ok, I've changed it to use module_name instead of module. Attachments are working fine now, so patch is attached.

[Former user]

(original author: ram) I'm not sure this was fully cooked, but it's high priority and is a far sight better than the use_* stuff that was there before. I've cleaned up a couple of small issues and it now seems to work fine.

Committed in rev 2427, thanks once again Paul!

-- leaving the ticket open as it applies to other DB modules as well, and I'm not sure this is the end of the story anyway.

[Mike Bayer]

OK, this patch is improved but is still not correct. dialects must be able to run with any module passed in, or no module at all. most of a dialect's functioning doesnt really need the dbapi module (such as for generating SQL strings).

these are the two use cases:

   dialect = MSSQLDialect()

   dialect = MSSQLDialect(module=some_other_module)

some_other_module can be a mock dbapi module for testing, for example.

so in changeset:2429 I made a change to allow the unit tests in test/sql/ to pass. I know you want ImportError in there but thats the bug; none of the DBAPI's can properly raise ImportErrors right now until this is worked out.

Its the "early importing" behavior of all the dialects that has to change; so that the error is only raised when they actually try to establish a connection. which is why I said that for ms-sql, a system of strategies would work better than straight subclassing; at module-import time (which will be much later than dialect-creation time), thats when the dialect picks its underlying strategy as well (which would be PymssqlStrategy, PyODBCStrategy, ADODBAPIStrategy). you encapsulate everything about the different modules in those strategies, which the dialect then calls upon as needed.

establishing the pattern for this import scheme is something im going to get around to, i might want to put out 0.3.6 first and then dig in to an across-the-board change in all the db dialects so that all module loading is deferred until the latest point, where it can then raise ImportErrors at the point at which theres no choice.

[Mike Bayer]

i have this mostly completed in the branch:

source:sqlalchemy/branches/execcontext

have not yet actually tested ms-sql but it should retain all the behaviors you guys had already. all the imports occur in a module-level dbapi() function now which will be called when create_engine() is called. When creating a standalone dialect, the dbapi() function is not called, and the dbapi member of the dialect is None.

going to be merging this RSN so please try it out !

[Mike Bayer]

ive tested on ms-sql and oracle and it works, so committed in changeset:2487.