Connecting to SQL Server 2012 using sqlalchemy and pyodbc

The file-based DSN string is being interpreted by SQLAlchemy as server name = c, database name = users.

I prefer connecting without using DSNs, it's one less configuration task to deal with during code migrations.

This syntax works using Windows Authentication:

engine = sa.create_engine('mssql+pyodbc://server/database')

Or with SQL Authentication:

engine = sa.create_engine('mssql+pyodbc://user:password@server/database')

SQLAlchemy has a thorough explanation of the different connection string options here.

I have an update info about the connection to MSSQL Server without using DSNs and using Windows Authentication. In my example I have next options: My local server name is "(localdb)\ProjectsV12". Local server name I see from database properties (I am using Windows 10 / Visual Studio 2015). My db name is "MainTest1"

engine = create_engine('mssql+pyodbc://(localdb)\ProjectsV12/MainTest1?driver=SQL+Server+Native+Client+11.0', echo=True)

It is needed to specify driver in connection. You may find your client version in:

control panel>Systems and Security>Administrative Tools.>ODBC Data Sources>System DSN tab>Add

Look on SQL Native client version from the list.

In Python 3 you can use function quote_plus from module urllib.parse to create parameters for connection:

import urllib
params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};"
                                 "SERVER=dagger;"
                                 "DATABASE=test;"
                                 "UID=user;"
                                 "PWD=password")

engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))

In order to use Windows Authentication, you want to use Trusted_Connection as parameter:

params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};"
                                 "SERVER=dagger;"
                                 "DATABASE=test;"
                                 "Trusted_Connection=yes")

In Python 2 you should use function quote_plus from library urllib instead:

params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};"
                           "SERVER=dagger;"
                           "DATABASE=test;"
                           "UID=user;"
                           "PWD=password")

Just want to add some latest information here: If you are connecting using DSN connections:

engine = create_engine("mssql+pyodbc://USERNAME:PASSWORD@SOME_DSN")

If you are connecting using Hostname connections:

engine = create_engine("mssql+pyodbc://USERNAME:PASSWORD@HOST_IP:PORT/DATABASENAME?driver=SQL+Server+Native+Client+11.0")

For more details, please refer to the "Official Document"