Connecting to a Microsoft SQL Server database on macOS using Python
Tags: python, database, microsoftsqlserver, macos
Published: 2019-08-08 10:45:09

---
title: "Connecting to a Microsoft SQL Server database on macOS using Python"
published: true
description: 
tags: [ "python", "database", "microsoftsqlserver", "macos" ]
canonical_url: https://eysermans.com/post/connecting-to-a-microsoft-sql-server-database-on-macos-using-python
---

There are always situations where I want to automate small tasks. I like using Python for these kind of things, you can quickly get something working without much hassle. I needed to perform some database changes in a Microsoft SQL Server database and wanted to connect to it using Python. On Windows this is usually pretty straight forward. But I use macOS as my main operating system and I had some hurdles along the way so here is a quick how to.

## Preparing

I found an [article with the same topic from 2017](https://www.sinafathi.com/connect-to-ms-sql-on-macos-using-python/). If [Homebrew](https://brew.sh/index_nl) isn't installed yet let's do that first. It's an excellent package manager for macOS. Paste the following command in the terminal to install it:

    /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Once finished run the following commands to install the [Microsoft ODBC Driver 13.1 for SQL Server](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017#microsoft-odbc-driver-131-for-sql-server). This driver will be used to connect to the MS SQL database.

    /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
    brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
    brew update
    brew install msodbcsql@13.1.9.2 mssql-tools@14.0.6.0

## Python package: pyodbc or pypyodbc

With the driver installed, we'll be using a Python package that uses the driver to connect to the database. There are two frequently used packages to connect in Python: [`pyodbc`](https://github.com/mkleehammer/pyodbc) and [`pypyodbc`](https://github.com/jiangwen365/pypyodbc). `pypyodbc` is a pure Python re-implementation of `pyodbc`. The main thing I took a way was that `pypyodbc` is easier to set up on different platforms. I could not get `pyodbc` working, it simply wouldn't connect to the database. 

Installing `pypyodbc` can be done using [pip, the python package installer](https://pypi.org/project/pip/).

    pip install pypyodbc

## Code

Now that the necessary components have been installed, let's open our favorite code editor and write code. The first thing that needs to be done is importing `pypyodbc`. The script starts with this:

    import pypyodbc

Then we need a connection to the database:

    sqlConnection = pypyodbc.connect(
    				"Driver={ODBC Driver 13 for SQL Server};"
            "Server=<server IP address>;"
            "Database=<database>;"
            "uid=<username>;pwd=<password>");

Note that you have to replace four values in this code: `server IP address`, `database` , `username` and `password`. The value for the driver is a hard coded value which indicates what driver to use to connect to the database, this value points to the driver that was installed earlier.

Now all what rests is use the connection and run a query.

    cursor = sqlConnection.cursor()
    cursor.execute("select * from Values")

The cursor now contains the result of our query, the property `cursor.rowcount` returns the number of rows the query returned. It's now possible to loop through the rows and access the different columns:

    for row in cursor:
    	print(cursor)
    	# first column
    	firstColumn = row[0]
    	# ...

When we're done, we need to clean up the cursor and database connection by closing it.

    cursor.close()
    sqlConnection.close()

And that's it, save the file and use the `python <filename>.py`  or `python3 <filename.py>` command, this depends on your configuration, to run. Here is the entire script:

    import pypyodbc
    
    sqlConnection = pypyodbc.connect(
    				"Driver={ODBC Driver 13 for SQL Server};"
            "Server=<server IP address>;"
            "Database=<database>;"
            "uid=<username>;pwd=<password>");
    
    cursor = sqlConnection.cursor()
    cursor.execute("select * from Values")
    
    for row in cursor:
    	print(cursor)
    	# first column
    	firstColumn = row[0]
    	# ...
    
    cursor.close()
    sqlConnection.close()

The `with` syntax can also be used to automatically close the cursor and the connection, this is another way of writing the same script:

    import pypyodbc
    
    with pypyodbc.connect(
    		"Driver={ODBC Driver 13 for SQL Server};"
            "Server=<server IP address>;"
            "Database=<database>;"
            "uid=<username>;pwd=<password>") as sqlConnection:
    	
    	with sqlConnection.cursor() as cursor:
    
    		cursor.execute("select * from Values")
    
    		for row in cursor:
    			print(cursor)
    			# first column
    			firstColumn = row[0]
    			# ...

If you're looking for some more reading on the topic: 

- [Python HOW: Connect to, and Manage a Database](https://medium.com/@DrGabrielA81/python-how-connect-to-and-manage-a-database-68b113a5ca62)