Python and SQL Server using PyODBC
Connecting to SQL SERVER with Python is easier than you might expect. With the PyODBC module, you can create
an ODBC connection to your SQL Server instance.
First you need access to the PyODBC module, which comes with certain implementations of Python. If you use
Anaconda Core package, you should have it by default. https://docs.continuum.io/anaconda/pkg-docs.html
You can also access it from the creator: Michael Kleehammer on GitHub: https://github.com/mkleehammer/pyodbc
For this exmaple code, I’m using an Intel based computer running Windows 10 OS, and SQL Server 2016 Developer Edition.
The steps are straightforward enough:
1. import the pyodbc module
2. create a connection object – with a connection string
3. create a cursor object based on the connection
4. create a string to form a SQL DML command: (Select, Insert, Update, Delete)
5. use the cursor object execute method to execute the command string
6. use one of the cursor fetch statements to pull the data: fetchone( ), fetchall( ), or fetchmany( )
7. iterate through the data using a loop – foreach or while
8. close the cursor and the connection
*Noteworthy: This is a simple example highlighting the connection and querying of a SQL Server database. You would normally enclose this code in try…catch logic with explicit error handling in a “real” work environment.
I’ve coded a basic example of the steps outlined above. Again, as in other Python examples, I’ve copied
screen output from my Jupyter notebook. I’ve created a TestDB database on SQL Server, with a test table
to use with this demo. I’ll be using Windows Authentication to access SQL Server.
SQL table creation:
PyODBC import, connection build, query execution, and displaying results:
SQL Statistics (keys, tables, etc.):
SQL Stats II:
In this blog I’ve demostrated how you can import the PyODBC module to create a connection, execute a SQL command,
and retrieve data from your SQL Server instance. I’ve also included examples for retrieving metadata from your
SQL Server instance. PyODBC is a feature rich module for accessing a SQL Server database.
See the references below for more information on the topic.