Python

Python applications connect to Syntra ODBC in two ways: through the bundled Syntra ODBC driver using pyodbc, or through SQLAlchemy with the pure-Python pg8000 dialect for ORM and query-builder workflows.

The examples on this page are verified end to end against a running Syntra ODBC server. If they do not run on your machine, confirm the prerequisites below before debugging your own code.

Prerequisites

• Syntra ODBC installed and running on localhost:5433
• Python 3.8 or later
• The Syntra ODBC installer adds a driver named Syntra ODBC - QuickBooks ODBC to your system’s ODBC Data Source Administrator. You can confirm it is present by running pyodbc.drivers() in a Python REPL.

Using pyodbc

pyodbc is the standard Python binding for ODBC drivers.

Install

pip install pyodbc

Connect and Query

import pyodbc

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

cursor = conn.cursor()
cursor.execute("""
    SELECT name, balance, phone
    FROM customers
    WHERE balance > 0
    ORDER BY balance DESC
""")

for row in cursor.fetchall():
    print(row.name, row.balance, row.phone)

cursor.close()
conn.close()

The UID is qbconnect by default; change it if you set a different username in config.toml. The PWD comes from the [auth] section of config.toml.

Using with pandas

import pandas as pd
import pyodbc

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

df = pd.read_sql("""
    SELECT txn_date, ref_number, customer_ref_full_name, balance_remaining
    FROM invoices
    WHERE txn_date >= '2025-01-01'
""", conn)

print(df.describe())
conn.close()

Alternative: PostgreSQL Unicode driver

If you already have the PostgreSQL Unicode(x64) ODBC driver installed (for example, from another project), it also works. Swap the DRIVER= part of the connection string:

conn = pyodbc.connect(
    "DRIVER={PostgreSQL Unicode(x64)};"
    "SERVER=127.0.0.1;PORT=5433;"
    "DATABASE=quickbooks;UID=qbconnect;PWD=yourpassword;"
)

Both drivers have been verified to run the same queries against the same Syntra server.

Using SQLAlchemy

SQLAlchemy provides an ORM and expression language. Use the pure-Python pg8000 dialect to avoid compiling any native extensions.

Install

pip install sqlalchemy pg8000

Connect and Query

from sqlalchemy import create_engine, text

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

with engine.connect() as conn:
    result = conn.execute(text("""
        SELECT name, balance
        FROM customers
        WHERE is_active = true
        ORDER BY name
    """))
    for row in result:
        print(row)

Using with pandas

import pandas as pd
from sqlalchemy import create_engine

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

df = pd.read_sql_table("customers", engine)
print(df.head())

Forcing a Live Query

To bypass the cache and query QuickBooks directly, set QB_MAX_STALENESS before your query:

cursor.execute("SET QB_MAX_STALENESS = 0")
cursor.execute("SELECT * FROM customers WHERE list_id = '80000001-1234567890'")

Writing Data

On the Pro plan, Syntra supports INSERT, UPDATE, and DELETE operations that write directly to QuickBooks:

cursor.execute("""
    INSERT INTO customers (name, phone, email)
    VALUES (?, ?, ?)
""", ("Acme Corp", "555-0100", "billing@acme.com"))
conn.commit()

See INSERT / UPDATE / DELETE for supported entities and syntax.

Tips

• Connection pooling: For SQLAlchemy, use its built-in pool. For pyodbc, open one connection per worker and reuse it.
• Timeouts: Add Timeout=10; to the connection string to avoid hanging if the Syntra service is unavailable.
• Type mapping: QuickBooks monetary amounts are returned as Decimal types for precision. See the SQL type reference for the full list of Syntra SQL types and how they map to Python types.
• Parameterized queries: Use ? placeholders in pyodbc; SQLAlchemy uses :name bind parameters via text().