Connect to SQL Server using SQLAlchemy

Question

I'm trying to connect to a SQL Server Express database using SQLALchemy and pyodbc, but I'm continuously getting the error:

(pyodbc.Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

And I really don't understand if my engine url is wrong or what else. My scenario is the following:

I'm on a Mac
I have a docker container (based on a Debian image with unixodbc and unixodbc-dev) in which my python app tries to connect to...
a virtualbox virtual machine running windows 8 with SQL express 2014...

I configured a user for the SQL express, with SQL Server authentication:

user: ar_user
password: ar_psw

...then:

I configured TCP ports as 1433 and disabled dynamic ports (SQL Server Configuration Manager > Network Configurations > Protocols).
I turned off Windows Firewall.
I used an Host-only adapter for the VM running windows8

now...

The VM is accessible from the host (my mac), since a:

ping -c 3 vm-ip

succeed!

But although I tried every possible permutation of user, password, ip, server name and port:

'mssql+pyodbc://ar_user:ar_psw@vm-ip/master'
'mssql+pyodbc://ar_user:ar_psw@vm-ip:1433/master'
'mssql+pyodbc://IE10WIN8\\SQLEXPRESS'
'mssql+pyodbc://ar_user:ar_psw@IE10WIN8\\SQLEXPRESS'
'mssql+pyodbc://ar_user:ar_psw@IE10WIN8\\SQLEXPRESS:1433'
'mssql+pyodbc://ar_user:ar_psw@IE10WIN8\\SQLEXPRESS:1433/master'

...and many more!

I always get the "datasource not found error". What should I do?

ps: the vm is pingable even in the docker container!

UPDATE (solved but not 100%):

I solved in this way:

I configured FreeTDS driver using /etc/odbcinst.ini in this way:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
client charset = UTF-8

and in /etc/freetds/freetds.conf:

[global]
tds version = 7.3
client charset = UTF-8

Then I created the engine using the following string:

'mssql+pyodbc://my_user:my_psw@machine_ip:1433/my_db?driver=FreeTDS'

It seems to work properly, but I get this warning:

SAWarning: Unrecognized server version info '95.12.255'. Version specific behaviors may not function properly. If using ODBC with FreeTDS, ensure TDS_VERSION 7.0 through 7.3, not 4.2, is configured in the FreeTDS configuration.

I also defined the TDS version using environment variables but it doesn't fix the issue... any idea?

Check the documentation here. You either need to supply a DSN name (as defined in your "odbc.ini" file) or you need to explicitly supply the driver= name in the connection URL. In your case a DSN connection would probably be the safest choice. — Gord Thompson
– Gord Thompson, Commented Jan 10, 2016 at 12:58
Gord is on right, DSN is the way to go. Also, make sure you've forwarded the appropriate ports (1433) from guest VM to host Mac. — FlipperPA
– FlipperPA, Commented Jan 12, 2016 at 19:58
@GordThompson and FlipperPA are correct. That seems like the ideal way to go about this. Alternatively, I recommend you use pymssql: github.com/pymssql/pymssql. Basic stress testing indicates it is slightly more performant than pyodbc — meet-bhagdev
– meet-bhagdev, Commented Feb 24, 2016 at 17:24
I tried to connect with both "mssql+pyodbc:///?odbc_connect=" and params = parse.quote( "Driver={{FreeTDS}};Server={};Port=1433;" "Database={};UID={};PWD={};" .format(db_host, db_name, db_user, db_pass)) create_engine('mssql+pyodbc:///?odbc_connect={}'.format(params) and both times I got base.py:1586: SAWarning: Unrecognized server version info '95.12.255'. If using ODBC with FreeTDS, ensure TDS_VERSION 7.0 through 7.3, not 4.2, is configured in the FreeTDS configuration. ".".join(str(x) for x in self.server_version_info)) — Andrei Sura
– Andrei Sura, Commented Mar 22, 2016 at 7:44

The Aelfinn · Accepted Answer · 2017-01-03 17:54:40Z

4

I wrote a tutorial here of how to do this. Essentially, you need to:

brew install unixodbc
brew install freetds --with-unixodbc
Add the freetds driver to odbcinst.ini
Add a DSN (Domain Source Name) to odbc.ini named "MY_DSN"
pip install pyodbc
e = create_engine("mssql+pyodbc://username:password@MY_DSN")

The walkthrough here does a much more thorough job of explaining this, including issues with SQL Server/FreeTDS Protocol Version Compatibility.

answered Jan 3, 2017 at 17:54

The Aelfinn

17.9k3 gold badges57 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connect to SQL Server using SQLAlchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related