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

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

Use the SQL Server ODBC driver to connect SQLAlchemy to SQL Server.

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

import sqlalchemy
import pyodbc
from sqlalchemy import *
engine =  create_engine('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()

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

  1. Download the SQLAlchemy distribution from the SQLAlchemy web site.
  2. 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
  3. As root, install SQLAlchemy: 
    python setup.py install
  4. Download the SQL Server ODBC driver for your Python SQLAlchemy client platform.

    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.

  5. Install and license the SQL Server ODBC driver on the machine where SQLAlchemy is installed.

    For installation instructions, refer to the ODBC driver documentation. Refer to the documentation to find out which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, SHLIB_PATH depending on the driver, platform, and linker).

  6. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from SQLAlchemy. For example: 
    [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
  7. Use isql to test the new data source. For example: 
    cd /usr/local/easysoft/unixODBC/bin
./isql -v SQLAlchemy-DSN

    At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

  8. 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.

  9. To test that you can connect to SQL Server from SQLAlchemy and fetch some data, run the code sample shown at the start of this article. For example: 
    python ./sqlalchemy-code-sample.pl
CompanyName: Aux joyeux ecclésiastiques
CompanyName: Bigfoot Breweries
CompanyName: Cooperativa de Quesos 'Las Cabras'
.
.
.
CompanyName: Zaanse Snoepfabriek

Further information