15

I am working in an environment where I am given an ODBC connection, which has been created using credentials to which I don't have access (for security reasons). However I would like to access the underlying database using SQLAlchemy - so my question is, can I pass this ODBC connection to something like create_engine, or alternatively, wrap it in such a way that it looks like a SQLAlchemy connection?

As a supplementary question (and working on the optimistic assumption that the first part can be satisfied) is there a way that I can tell SQLA what dialect to use for the underlying RDBMS?

Thanks

Andy
  • 523
  • 6
  • 20

2 Answers2

14

yes you can:

from sqlalchemy import create_engine
from sqlalchemy.pool import StaticPool
eng = create_engine("mssql+pyodbc://", poolclass=StaticPool, creator=lambda: my_odbc_connection)

however, if you truly have only one connection already created, as opposed to a callable that creates them, you must only use this engine in a single thread, one operation at a time. It is not threadsafe for use in a multithreaded application.

If OTOH you can actually get at a Python function that creates new connections whenever called, this is much more appopriate:

from sqlalchemy import create_engine
eng = create_engine("mssql+pyodbc://", creator=my_odbc_connection_function)

the above engine will pool connections normally and can be used freely as a source of connectivity.

zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • 1
    Thanks for your helpful response :-) I'm actually getting the ODBC connection from a factory method, so I could write a simple (very simple) wrapper that generates connections on-demand. – Andy May 19 '14 at 18:25
  • Any thoughts on my second point (specifying the dialect)? I'm sure I saw some way of doing this in the SQLA docs, but can;t find it now (there's *so* much of the stuff :-)) – Andy May 19 '14 at 18:26
  • Aha - I think I've got it - I just change the first part of the url - the bit before the plus sign - yes? Now reading more of the docs on create_engine, including pool options :-) – Andy May 19 '14 at 18:33
  • the only dialect that works well with odbc is the MSSQL one. Other options include MySQL or Sybase, not sure how well those work as they are very rarely used. we don't have ODBC support for other kinds of databases (as Pyodbc doesn't). – zzzeek May 19 '14 at 21:51
  • That'll be fun then - I'm actually using Netezza and had been hoping I could 'get by' with the Postgres dialect - I guess I'll have to make do with MSSQL and work around the exceptions. – Andy May 20 '14 at 08:43
  • The problem with this is that it doesn't continue a transaction and can't reference anything you've previously written. (at least for psycopg2) – user48956 Feb 15 '19 at 00:28
0

found this old post (14 years ago)

import sqlalchemy

sqlalchemy.create_engine('mssql://?dsn=mydsn')

my user case is for using jupyter SQL magics in new notebooks to be distributed to users which already have the DSN configured on their systems

for jupyter, after pip install ipython-sql:

import sqlalchemy

%load_ext sql

%sql mssql://?dsn=mydsn

tested on SQL Server 2019 with Windows authentication, as all info I found point that DSNs don't hold passwords for MSSQL

there's no reason why it shouldn't work on older versions (I'll use it on SQL Server 2005 too, of course with another DSN)

lexc
  • 61
  • 3