Connecting to SQL Server from SQL Alchemy

[dimitris.nic...@gmail.com]

Hello

Just wanted to write this up in case it helps someone else in the community. A big part is already in the documentation, however i struggled quite a bit with the hostname-based connection. My aim was to establish a connection to a database running on SQL Server and my setup was as follows:

• SQL Server Express 2014 running on Windows Home Server 2011
• Python 2.7, SqlAlchemy 1.0.9 running on OSX Yosemite. 
  

The FreeDTS configuration file, /usr/local/Cellar/freetds/0.91.112/etc/freedts.conf has the following entry:

The port is commented out. It looks to me that with SQL Server you either specify the port or the instance, but not both. The IP is my the box's internal IP address.

The properties of my Data Source were specified in the ini file: /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbc.ini

In my case I wanted to add my Research database (which lives in the SQLEXPRESS instance, hosted by the box with the IP: 192.168.1.10) Hence I have appended in the ini file the lines below:  

• Connecting with DSN
  

As described in the documentation once you have properly specify a DSN (steps above might come handy here...)  you can connect to it as follows:

(In the examples i am quering a table on the my Research database with 183600 rowcounts)

 

import pyodbc

import sqlalchemy as sa 

sa.__version__

'1.0.9'

connStr = "mssql+pyodbc://myUsername:myPassword@Research"

engine = sa.create_engine(connStr)

connection = engine.connect()

result = connection.execute('select count(*) as counts from  core')

for row in result:

    print row['counts']

connection.close()   

183600

 

• Hostname connection (1)

Here the connection string targets the odbc shared library which in my case sits under /usr/local/lib

 

connStr = "mssql+pyodbc://myUsername:myPassword@192.168.1.10\SQLEXPRESS/Research?driver=/usr/local/lib/libtdsodbc.so"

engine = sa.create_engine(connStr)

 

connection = engine.connect()

result = connection.execute('select count(*) as counts from  core')

for row in result:

    print row['counts']

connection.close()   

183600

Note that I havent specified the port. Only the instance name and the database name. Also notice that instance name and database name are separated by a forward slash (ie: SQLEXPRESS/Research) but the host and instance name by a backslash (ie: 192.168.1.10\SQLEXPRESS)

 

• Hostname connection (2)

connStr = "mssql+pyodbc://myUsername:myPassword@192.168.1.10:64099/Research?driver=/usr/local/lib/libtdsodbc.so"

engine = sa.create_engine(connStr)

 

connection = engine.connect()

result = connection.execute('select count(*) as counts from  core')

for row in result:

    print row['counts']

connection.close() 

183600 

Here, after the host i set only the port and then the database name. The instance name does not appear anywhere in the connection string. Also the port is 64099. Quite often this port for other people is 1433. For me however the default port was 64099 as shown by the configuration manager screen attached below. In any case however the port in the connection string should be the same as the port shown in the red box below.

 

SQL Alchemy documentation emphasises that hostname connections are not preferred but nevertheless are supported. I find them however convenient to work with because if i migrate my Python application to another machine I dont have to mess around with conf and ini files in order to configure my DNS. I just have to target the odbc driver in the connection string.

HTH