The following works:
import pyodbc pyodbc.connect('DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;')
The following fails:
import sqlalchemy sqlalchemy.create_engine("mssql://myuser:[email protected]:1433/mydb?driver=FreeTDS& odbc_options='TDS_Version=8.0'").connect()
The error message for above is:
DBAPIError: (Error) ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnectW)') None None
Can someone please point me in the right direction? Is there a way I can simply tell sqlalchemy to pass a specific connect string through to pyodbc?
Please Note: I want to keep this DSN-less.
The example by @Singletoned would not work for me with SQLAlchemy 0.7.2. From the SQLAlchemy docs for connecting to SQL Server:
If you require a connection string that is outside the options presented above, use the odbc_connect keyword to pass in a urlencoded connection string. What gets passed in will be urldecoded and passed directly.
So to make it work I used:
import urllib quoted = urllib.quote_plus('DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;') sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
This should apply to Sybase as well.
NOTE: In python 3 the urllib module has been split into parts and renamed. Thus, this line in python 2.7:
quoted = urllib.quote_plus
has to be changed to this line in python3:
quoted = urllib.parse.quote_plus
I'm still interested in a way to do this in one line within the sqlalchemy create_engine
statement, but I found the following workaround detailed here:
import pyodbc, sqlalchemy def connect(): pyodbc.connect('DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;') sqlalchemy.create_engine('mssql://', creator=connect)
UPDATE: Addresses a concern I raised in my own comment about not being able to pass arguments to the connect string. The following is a general solution if you need to dynamically connect to different databases at runtime. I only pass the database name as a parameter, but additional parameters could easily be used as needed:
import pyodbc import os class Creator: def __init__(self, db_name='MyDB'): """Initialization procedure to receive the database name""" self.db_name = db_name def __call__(self): """Defines a custom creator to be passed to sqlalchemy.create_engine http://stackoverflow.com/questions/111234/what-is-a-callable-in-python#111255""" if os.name == 'posix': return pyodbc.connect('DRIVER={FreeTDS};' 'Server=my.db.server;' 'Database=%s;' 'UID=myuser;' 'PWD=mypassword;' 'TDS_Version=8.0;' 'Port=1433;' % self.db_name) elif os.name == 'nt': # use development environment return pyodbc.connect('DRIVER={SQL Server};' 'Server=127.0.0.1;' 'Database=%s_Dev;' 'UID=user;' 'PWD=;' 'Trusted_Connection=Yes;' 'Port=1433;' % self.db_name) def en(db_name): """Returns a sql_alchemy engine""" return sqlalchemy.create_engine('mssql://', creator=Creator(db_name))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With