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.

Pone

Answer 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

Automatism

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. – Sniffy

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

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. – Invidious

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

This solution works for Sybase ASE without dns name. – S

Answer 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
           https://mcmap.net/q/55129/-what-is-a-quot-callable-quot#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))

Pone

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 =) – Adam

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

Winding

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. – Pone

It does NOT: sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'DRIVER%3D%7BFreeTDS%7D%3BServer%3Dmy.db.server%3BDatabase%3Dmydb%3BUID%3Dmyuser%3BPWD%3Dmypwd%3BTDS_Version%3D8.0%3BPort%3D1433%3B' – Orchidectomy

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

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

Asia

Answer 5

To pass various parameters to your connect function, it sounds like format string might do what you want:

def connect(server, dbname, user, pass):
  pyodbc.connect('DRIVER={FreeTDS};Server=%s;Database=%s;UID=%s;PWD=%s;TDS_Version=8.0;Port=1433;' % (server, dbname, user, pass))

And you would then call it with something like:

connect('myserver', 'mydatabase', 'myuser', 'mypass')

More info on format strings is here: http://docs.python.org/library/string.html#formatstrings

Strobe

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. – Pone

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. – Strobe

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! – Pone