Python

Python connects to Syntra through the bundled Syntra ODBC driver via pyodbc. The installer registers the driver system-wide, so once Syntra is running there is no further setup.

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 (the default).
• Python 3.8 or later.
• The installer adds a driver named Syntra ODBC - QuickBooks ODBC to the system’s ODBC Data Source Administrator. Confirm it is registered by running pyodbc.drivers() in a REPL and looking for that entry.

Install

pip install pyodbc

Connect and query

The simplest connection uses the default DSN the installer registered:

import pyodbc

conn = pyodbc.connect("DSN=Syntra QuickBooks")

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

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

cursor.close()
conn.close()

If you prefer driver-direct (no DSN), specify every parameter inline:

conn = pyodbc.connect(
    "Driver={Syntra ODBC - QuickBooks ODBC};"
    "Server=127.0.0.1;Port=5433;"
    "Database=qbconnect;Uid=qbconnect;Pwd=changeme;"
)

The Uid and Pwd come from the [auth] section of config.toml. The default installer values are qbconnect / changeme — change them in production.

Using with pandas

pandas.read_sql accepts a pyodbc connection directly:

import pandas as pd
import pyodbc

conn = pyodbc.connect("DSN=Syntra QuickBooks")

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()

For heavy data pulls, prefer pandas.read_sql_query with a chunksize to stream rows instead of buffering the full result set:

for chunk in pd.read_sql_query(sql, conn, chunksize=10_000):
    process(chunk)

Parameterized queries

Always use parameter placeholders — never string-format values into SQL. pyodbc uses ?:

cursor.execute(
    "SELECT list_id, full_name FROM customers WHERE balance > ? AND is_active = true",
    (1000,),
)

Forcing a live query (bypass cache)

The local cache answers most SELECTs. To pull a fresh read straight from QuickBooks for a specific query, set QB_MAX_STALENESS on the session before running it:

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

The setting applies for the lifetime of that connection.

Writing data

INSERT, UPDATE, and DELETE are available on every paid plan (Standard and Pro) and write straight to QuickBooks:

cursor.execute("""
    INSERT INTO customers (name, phone, email)
    VALUES (?, ?, ?)
""", ("Acme Corp", "555-0100", "[email protected]"))
conn.commit()

Multi-line transactions (one invoice with N lines, etc.) use the grouped multi-row VALUES pattern:

cursor.execute("""
    INSERT INTO invoices
        (customer_ref_full_name, ref_number, memo,
         line_item_ref_full_name, line_quantity, line_rate)
    VALUES
        (?, ?, ?, ?, ?, ?),
        (?, ?, ?, ?, ?, ?)
""", (
    "Acme Corp", "INV-1001", "June services",
    "Consulting:Hourly", 10, 150.00,
    "Acme Corp", "INV-1001", "June services",
    "Travel:Mileage", 120, 0.67,
))
conn.commit()

See INSERT / UPDATE / DELETE for the full set of patterns, including shared-line parents (bills with expense vs. item lines) and journal entries.

Tips

• Connection reuse. Open one connection per worker/process and reuse it. ODBC connections are relatively expensive to open.
• Timeouts. Append ;Timeout=10 to the connection string to fail fast if Syntra is unavailable. For long-running pulls, raise cursor.timeout (seconds) to extend the per-query deadline.
• Type mapping. Monetary amounts come back as decimal.Decimal for precision; dates as datetime.date. See the SQL type reference for the full mapping.
• SET statements are per-connection. SET QB_MAX_STALENESS, SET QB_TIMEOUT, and similar settings do not persist beyond the current connection.
• Custom fields appear as custom_* columns once Syntra has synced the DataExt definitions. See Custom Fields.