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
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:
The output should read any version of Python above 3.7
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, row)
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,row]], 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:
To print the whole dataframe, use the to_string function as shown below:
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 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:
Or by exporting the dataframe to a more readable format
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 email@example.com to schedule a free consultation!