Use the SQL Server ODBC driver to connect Python/SQLAlchemy to Microsoft 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()
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.
$ gunzip SQLAlchemy-0.7.8.tar.gz $ tar -xvf SQLAlchemy-0.7.8.tar $ cd SQLAlchemy-0.7.8
python setup.py install
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.
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).
[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
cd /usr/local/easysoft/unixODBC/bin ./isql -v SQLAlchemy-DSN
At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.
pyodbc is an open source Python module that provides access to ODBC databases. pyodbc is SQLAlchemys recommended Python DB API driver for SQL Server.
Note At the time of writing, SQLAlchemy did not support pyodbc with Python 3.
python ./sqlalchemy-code-sample.pl CompanyName: Aux joyeux ecclésiastiques CompanyName: Bigfoot Breweries CompanyName: Cooperativa de Quesos 'Las Cabras' . . . CompanyName: Zaanse Snoepfabriek