How to use the Quickbooks ODBC Driver with Python

Here is a quick guide on how to get access to all the information in QuickBooks from Python.

The final code created in this tutorial is hosted on the Depot Analytics GitHub page: https://github.com/Depot-Analytics/quickbooks-pyodbc-tutorial

Requirements

• 64-bit Host Computer (Windows/Mac)

• Quickbooks ODBC Driver (either Desktop or Online) installed on the development host machine

  • Some good drivers can be found here and here - they both have simple setup guides for both online QuickBooks and the local desktop version. QODBC is strongly recommended over CDATA for pricing, support, and functionality reasons

• This guide will assume the use of a Windows 11 development machine, but steps on other version of Windows and MacOS are very similar

Test ODBC Driver Connection

1. Check that the ODBC driver is connected properly

Search for "ODBC" in the windows menu and click on "ODBC Data Sources (64-bit)"

Click "System DSN" (some drivers place the driver in "User DSN"), click on the 64-bit version of the ODBC driver on the host machine, and click "Confgure..."

Click "Test Connection" on the dialog that appears - here are some examples of what that may look like:

Check that the following dialog appears

If this dialog does not appear, be sure that you have followed the proper steps for installing your ODBC driver and go to the driver provider's troubleshooting guides

Install Python and Libraries

1. Download Python 3.7+ using the guide here for Windows

2. Check that Python installed correctly

In a command terminal, type this command:

python3 --version

The output should read any version of Python above 3.7

Python 3.7.9

3. Install Pip and the required libraries

Open a command prompt and type the following commands:

python3 -m ensurepip
mkdir quickbooks-pyodbc-tutorial
cd quickbooks-pyodbc-tutorial
python3 -m venv quickbooks-odbc-env
quickbooks-odbc-env\Scripts\activate.bat

These commands will ensure that Pip is installed and create a virtual environment for QuickBooks ODBC work.

Install the required libraries in the virtual environment that was just activated:

python3 -m pip install pandas pyodbc

Make the Python Connection

1. Create a file called "quickbooks_connect.py"

Each of the following steps will by typed directly into quickbooks_connect.py.

2. At the top of the python file, import the libraries

import pyodbc
import pandas as pd

3. Define connection string

The string following 'DSN' comes from the "Name" field in the ODBC Data Source Administrator from earlier - be sure to select the correct name for the ODBC driver that is in use

ODBC_CONNECTION_STR = f'DSN=QuickBooks Online Data 64-Bit;'

4. Establish connectivity and get a database cursor object

connection = pyodbc.connect(ODBC_CONNECTION_STR)
cursor = connection.cursor()

5. Execute a desired T-SQL command

One of the easy ways to find out what tables are accessible through the ODBC driver is by running the "SP_TABLES" command as shown below.

SQL_COMMAND = "SP_TABLES"

cursor.execute(SQL_COMMAND)
for row in cursor.fetchall():
    # 2 is the index of the actual table name
    # 4 is the index of the table descriptor
    print(row[2], row[4])

This will print a list of all the tables that are queryable from the driver.

"Fetches" from the cursor (cursor.fetchall, cursor.fetchone, etc) return an iterator that yields tuples of values from the database. But it would be helpful if this data was more pythonic, in pandas for example.

All that needs to be done is to format the data and cast it appropriately - try this instead:

SQL_COMMAND = "SP_TABLES"
cursor.execute(SQL_COMMAND)

table_df = pd.DataFrame(columns=['Name', 'Description'])

for row in cursor.fetchall():
    # 2 is the index of the actual table name
    table_name_and_description = pd.DataFrame(data=[[row[2],row[4]]], columns=['Name', 'Description'], index=[len(table_df)])
    table_df = pd.concat([table_df, table_name_and_description])

Printing the dataframe can be done like this:

print(table_df)

To print the whole dataframe, use the to_string function as shown below:

print(table_df.to_string())

A Table of Pandas

1. Take a look at the Customer table

Since this is the most commonly used table, the example will query it, but the same principals apply to querying any table in the database

2. Get all of the columns in the Customer table

COLLECT_TABLE_INFO="SP_COLUMNS Customer"
cursor.execute(COLLECT_TABLE_INFO)
df_columns = [row[3] for row in cursor.fetchall()]

Get all of the Customers using the SELECT * T-SQL command

SQL_COMMAND = "SELECT * FROM Customer"
cursor.execute(SQL_COMMAND)
customer_df = pd.DataFrame(data=map(list, cursor.fetchall()), columns=df_columns)

The map function is used here to cast each item within the iterator that is returned from fetchall() to a list.

This makes the data readable to the pd.DataFrame() constructor function.

3. View the customers

This can be done by printing the dataframe directly:

print(customer_df.to_string())

Or by exporting the dataframe to a more readable format

print(customer_df.to_csv('customers.csv'))

Now the customers are viewable in the local directory as "customers.csv"

Get your QuickBooks Data Working for You

Now the whole world of SQL queries directly translated into Python and Pandas is available to you using these techniques.

If you need some help making your QuickBooks data work for you, reach out to us at info@depotanalytics.co to schedule a free consultation!