Connect to SQL Server 2012 using sqlalchemy and pyodbc

Question

I am trying to connect to a SQL Server 2012 database using SQLAlchemy (with pyobbc) on Python 3.3 (Windows 7-64-bit). I can connect using direct pyobbc, but failed to connect using SQLAlchemy. I have a dsn file setup to access a database.

I successfully connect using direct pyodbc as follows:

con = pyodbc.connect('FILEDSN=c:\\users\\me\\mydbserver.dsn') 

For sqlalchemy, I tried:

 import sqlalchemy as sa engine = sa.create_engine('mssql+pyodbc://c/users/me/mydbserver.dsn/mydbname') 

The create_engine method does not actually establish a connection and fails, but iI if I try something that calls sqlalchemy to actually configure the connection (for example, engine.table_names() ), it will take some time, but then returns this error:

DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)') None None

I'm not sure what is going wrong how to see which connection string is really being passed to pyodbc by sqlalchemy. I am successfully using the same sqlalchemy classes with SQLite and MySQL.

Thanks in advance!

Answer 1

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

I prefer connecting without using DSN, this is another configuration task that you have to deal with during code migration.

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 detailed explanation of the various parameters of the connection string here .

Answer 2

I have update information about connecting to MSSQL Server without using DSN and using Windows Authentication. In my example, I have the following options: The name of my local server is "(localdb) \ ProjectsV12". The name of the local server that I see from the database properties (I am using Windows 10 / Visual Studio 2015). My db name - "MainTest1"

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

You must specify the driver in the connection. You can find your client version in:

Control Panel> Systems and Security> Administration Tools. > ODBC Data Sources> System DSN tab> Add

Look at the version of the native SQL client from the list.