How do I connect Python/SQLAlchemy on UNIX and Linux to SQL Server?

<a href="/cgi-bin/account/login.cgi">Log In</a> <a href="/cgi-bin/account/logout.cgi">Log Out</a>/<a href="/cgi-bin/account/register.cgi">Register</a>

Download ODBC Drivers for Oracle®, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Easysoft Data Access

How do I connect Python/SQLAlchemy on UNIX and Linux to SQL Server?

Article:: 01000

Last Reviewed:: 21st September 2012

Revision:: 3

Use the SQL Server ODBC driver to connect Python/SQLAlchemy to Microsoft SQL Server 7.0, 2000, 2005, 2008, 2012, 2014, 2016, 2017, 2019 and Express.

The SQL Server ODBC driver is available for 32-bit and 64-bit Linux (CentOS, Debian GNU/Linux, Fedora, Mandrake/Mandriva, OpenSUSE/SUSE, RedHat, RedHat Enterprise Linux (RHEL) and Slackware) and UNIX (AIX, HP-UX and Solaris) platforms.

#! /usr/bin/env python
#
# Example: Connecting Python/SQLAlchemy on UNIX and Linux to MSSQL
#

import sqlalchemy
import pyodbc
from sqlalchemy import *
engine =  create_engine('mssql://MSSQL-SQLAlchemy-DSN', module_name='pyodbc')
connection = engine.connect()
result = connection.execute("select CompanyName from suppliers")
for row in result:
     print "CompanyName:", row["CompanyName"]
result.close()
connection.close()

We accessed SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019 and Express databases from Python/SQLAlchemy on UNIX and Linux. The example in this article will also work with earlier (7.0 and 2000) versions of SQL Server. We tested the SQL Server ODBC driver with SQLAlchemy 0.5.x on RedHat Linux and SQLAlchemy 0.7.x on Ubuntu Linux.

About SQLAlchemy

SQLAlchemy is an SQL toolkit for Python that provides a database-independent, object-oriented interface to databases such as Oracle®, SQL Server, MySQL and PostgreSQL. SQLAlchemy includes both a Python-object-based SQL expression language and an Object Relational Mapper (ORM) that lets you map Python objects to database tables without substantially changing your existing Python code.

How to Access SQL Server From Python/SQLAlchemy on UNIX and Linux

Download the SQLAlchemy distribution from the SQLAlchemy web site.
Copy the distribution file to your Python machine, unpack and cd into the directory created by unpacking the file. For example:
```
$ gunzip SQLAlchemy-0.7.8.tar.gz
$ tar -xvf SQLAlchemy-0.7.8.tar
$ cd SQLAlchemy-0.7.8
```
As root, install SQLAlchemy:
```
python setup.py install
```
Download the SQL Server ODBC driver for your Python/SQLAlchemy client platform. (Registration required.)
If the SQL Server ODBC driver is not currently available for your platform, check the list of ODBC-ODBC Bridge Client platforms. The ODBC-ODBC Bridge is an alternative SQL Server solution from Easysoft, which you can download from this site.
Install and license the SQL Server ODBC driver on the machine where Python/SQLAlchemy is installed.
For installation instructions, see the ODBC driver documentation. Refer to the documentation to see which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, SHLIB_PATH depending on the driver, platform and linker).

Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Python/SQLAlchemy. For example:

[MSSQL-SQLAlchemy-DSN]
Driver          = Easysoft ODBC-SQL Server
Server          = my_machine\SQLEXPRESS
User            = my_domain\my_user
Password        = my_password
# If the database you want to connect to is the default
# for the SQL Server login, omit this attribute
Database        = Northwind

Use isql to test the new data source. For example:
```
cd /usr/local/easysoft/unixODBC/bin
./isql -v MSSQL-SQLAlchemy-DSN
```
At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.
Download, build and install pyodbc. Test pyodbc with your SQL Server data source.
pyodbc is an open source Python module that provides access to ODBC databases. pyodbc is SQLAlchemy’s recommended Python DB API driver for SQL Server.

Note At the time of writing, SQLAlchemy did not support pyodbc with Python 3.

To test that you can connect to SQL Server from Python/SQLAlchemy and fetch some data, run the code sample shown at the start of this article. For example:

python ./sqlalchemy-mssql-code-sample.pl
CompanyName: Aux joyeux ecclésiastiques
CompanyName: Bigfoot Breweries
CompanyName: Cooperativa de Quesos 'Las Cabras'
.
.
.
CompanyName: Zaanse Snoepfabriek

Easysoft Data Access