SqlAlchemy equivalent of pyodbc connect string using FreeTDS

Question

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.

Solution 1

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

Solution 2

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))

Solution 3

This works:

import sqlalchemy
sqlalchemy.create_engine("DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;").connect()

In that format, SQLAlchemy just ignores the connection string and passes it straight on to pyodbc.

Update:

Sorry, I forgot that the uri has to be url-encoded, therefore, the following works:

import sqlalchemy
sqlalchemy.create_engine("DRIVER%3D%7BFreeTDS%7D%3BServer%3Dmy.db.server%3BDatabase%3Dmydb%3BUID%3Dmyuser%3BPWD%3Dmypwd%3BTDS_Version%3D8.0%3BPort%3D1433%3B").connect()

Solution 4

Internally "my.db.server:1433" is passed as part of a connection string like SERVER=my.db.server:1433;.

Unfortunately unixODBC/FreeTDS won't accept a port in the SERVER bit. Instead it wants SERVER=my.db.server;PORT=1433;

To use the sqlalchemy syntax for a connection string, you must specify the port as a parameter.

sqlalchemy.create_engine("mssql://myuser:[email protected]:1433/mydb?driver=FreeTDS& odbc_options='TDS_Version=8.0'").connect()

becomes:

sqlalchemy.create_engine("mssql://myuser:[email protected]/mydb?driver=FreeTDS&port=1433& odbc_options='TDS_Version=8.0'").connect()

View more solutions

Comments

[mwolfe02]

I realize now that this has the limitation of not allowing an easy way to pass things like the database name as a parameter to the connect function. This may be possible in python but I'm not sure how I would do it (lambda expression?).

[mwolfe02]

Please re-read my question. The pyodbc.connect code is a working sample. My question is how to translate that pyodbc.connect string to a format sqlalchemy can then pass through correctly to pyodbc.

[skermajo]

Yes, this answer was in response to your comment on Dec 20 about not being able to easily pass params to your working connect() example. I probably should have posted in the comments in retrospect, apologies - it's my first time.

[mwolfe02]

No worries. I hope the tone of my comment didn't come across as harsh--I didn't intend it to be. I would not want your first experience at SO to be a bad one. The community here is very friendly overall. I hope you'll stick around!

[mwolfe02]

See UPDATE above for response to my earlier concern.

[mwolfe02]

I'm no longer using SQLAlchemy in my project, so I'll take your word for it that this works. And it is certainly a lot simpler than what I was trying to do. I'm not sure why I didn't think to try that when I was troubleshooting originally.

[sorin]

It does NOT: sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'DRIVER%3D%7BFreeTDS%7D%3BServer%3Dmy.db.server%3BDatabase%3‌​Dmydb%3BUID%3Dmyuser‌​%3BPWD%3Dmypwd%3BTDS‌​_Version%3D8.0%3BPor‌​t%3D1433%3B'

[Singletoned]

You are right. It appears that at some point in the last 1251 days the format has changed.

[Casey]

I spent a few hours messing around with iODBC on OSX Mavericks and this was finally the answer I needed to make pyodbc, iODBC and SQLAlchemy all work together.

[MarkNS]

Of course, 3 slashes! Why would I never have thought of that earlier!</sarcasm> Thank you @jmagnusson.

[Justin]

Awesome! Been having a heck of a time trying to get DB2 & pyodbc w/unixODBC connection pooling working. Using this, with ibm-db-sa-py3, works sooo much better than the ibm drivers. Thanks =)

[jonathanrocher]

This solution works great on Python2.7 but doesn't seem to be working for me on Python3 (MySQL). From the error message, it seems like the connection elements names aren't passed through correctly from pyodbc to create_engine.

[Dnaiel]

@jonathanrocher this worked for me in python3. But I had to change the urlib part of the code to: urllib.parse.quote_plus

[hui chen]

This solution works for Sybase ASE without dns name.