Programming

Python + QuickBooks Desktop

Connect Python to QuickBooks Desktop with the bundled ODBC driver via pyodbc, or with SQLAlchemy and pandas for higher-level access. Query invoices, customers, and items with the same SQL you already know.

Quick Start

  1. Install Syntra ODBC and ensure the server is running on localhost:5433.
  2. Install a client library. Run pip install pyodbc (or sqlalchemy pg8000, pandas).
  3. Connect using the Syntra credentials. Host: localhost, port: 5433, user: syntra, password: from config.toml.
  4. Run SQL queries. Use any QuickBooks table, such as invoices, customers, items, etc.

pyodbc

Connect through the bundled Syntra ODBC driver. The example below is verified end-to-end against a running Syntra server.

import pyodbc

conn = pyodbc.connect(
    "DRIVER={Syntra ODBC - QuickBooks ODBC};"
    "SERVER=localhost;PORT=5433;"
    "DATABASE=quickbooks;UID=qbconnect;PWD=your_config_toml_password;"
)

cur = conn.cursor()
cur.execute("""
    SELECT c.full_name, c.email, c.balance
    FROM customers c
    WHERE c.is_active = true
    ORDER BY c.balance DESC
""")

for row in cur.fetchmany(20):
    print(row)

cur.close()
conn.close()

SQLAlchemy

Use SQLAlchemy's engine for connection pooling and ORM features. See the dedicated SQLAlchemy guide for advanced usage.

from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+pg8000://qbconnect:your_config_toml_password@localhost:5433/quickbooks"
)

with engine.connect() as conn:
    result = conn.execute(text("SELECT full_name, balance FROM customers"))
    for row in result:
        print(row)

pandas

Load QuickBooks data directly into a DataFrame for analysis, pivoting, and export.

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+pg8000://qbconnect:your_config_toml_password@localhost:5433/quickbooks"
)

df = pd.read_sql("""
    SELECT
        i.txn_date,
        i.ref_number     AS InvoiceNumber,
        c.full_name      AS Customer,
        il.item_ref_full_name,
        il.quantity,
        il.rate,
        il.amount
    FROM invoices i
    JOIN customers c ON i.customer_ref_list_id = c.list_id
    JOIN invoice_lines il ON i.txn_id = il.txn_id
    WHERE i.txn_date >= '2025-01-01'
    ORDER BY i.txn_date DESC
""", engine)

print(df.head(10))
print(f"Total revenue: ${df['amount'].sum():,.2f}")

Tips

  • ✓ Store your password in an environment variable. Never hard-code credentials in scripts.
  • ✓ Use parameterized queries with ? placeholders in pyodbc to avoid SQL injection.
  • ✓ For large datasets, use server_side_cursors in SQLAlchemy to stream rows without loading everything into memory.

Full reference and more examples: Python integration docs →

Query QuickBooks from Python

Download Syntra ODBC and start analyzing QuickBooks data with Python in minutes.

Download Free Trial