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.
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).
[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
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.
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.
python ./sqlalchemy-mssql-code-sample.pl CompanyName: Aux joyeux ecclésiastiques CompanyName: Bigfoot Breweries CompanyName: Cooperativa de Quesos 'Las Cabras' . . . CompanyName: Zaanse Snoepfabriek
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.