ORMs & Frameworks

SQLAlchemy + QuickBooks Desktop

Use SQLAlchemy's full ORM with QuickBooks Desktop data. Reflect tables automatically, build queries with the expression language, and use sessions for clean data access.

Quick Start

  1. Install Syntra ODBC and ensure the server is running on localhost:5433.
  2. Install dependencies. Run pip install sqlalchemy pg8000.
  3. Create an engine with the Syntra connection string.
  4. Reflect or define models and start querying.

Engine Creation

Create a SQLAlchemy engine pointed at Syntra ODBC:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine(
    "postgresql+pg8000://qbconnect:your_config_toml_password@localhost:5433/quickbooks",
    echo=False,       # set True for SQL logging
    pool_size=5,
    pool_pre_ping=True,
)

Reflecting QuickBooks Tables

Use automap_base to automatically generate ORM models from the QuickBooks schema, so there is no need to define models manually:

from sqlalchemy.ext.automap import automap_base

Base = automap_base()
Base.prepare(autoload_with=engine)

# Tables are now available as classes
Customer = Base.classes.customers
Invoice = Base.classes.invoices
InvoiceLine = Base.classes.invoice_lines
Item = Base.classes.items

# Check what tables were reflected
print("Reflected tables:", list(Base.classes.keys()))

Session Queries

Use sessions for clean, scoped data access:

with Session(engine) as session:
    # Top 10 customers by balance
    customers = (
        session.query(Customer)
        .filter(Customer.is_active == True)
        .order_by(Customer.balance.desc())
        .limit(10)
        .all()
    )

    for c in customers:
        print(f"{c.full_name}: ${c.balance:,.2f}")

    # Unpaid invoices with customer names
    results = (
        session.query(
            Invoice.ref_number,
            Invoice.txn_date,
            Customer.full_name,
            Invoice.balance_remaining,
        )
        .join(Customer, Invoice.customer_ref_list_id == Customer.list_id)
        .filter(Invoice.is_paid == False)
        .order_by(Invoice.txn_date.desc())
        .all()
    )

    print(f"\n{len(results)} unpaid invoices:")
    for inv in results:
        print(f"  #{inv.ref_number} - {inv.full_name}: ${inv.balance_remaining:,.2f}")

Manual Model Definition

If you prefer explicit models over reflection, define them with the declarative API:

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String, Numeric, Boolean
from decimal import Decimal

class Base(DeclarativeBase):
    pass

class Customer(Base):
    __tablename__ = "customers"

    list_id: Mapped[str] = mapped_column(String, primary_key=True)
    full_name: Mapped[str] = mapped_column(String)
    email: Mapped[str | None] = mapped_column(String)
    phone: Mapped[str | None] = mapped_column(String)
    balance: Mapped[Decimal] = mapped_column(Numeric)
    is_active: Mapped[bool] = mapped_column(Boolean)

Tips

  • ✓ automap_base is the fastest way to get started. It reflects all tables in one call.
  • ✓ On the Standard plan, Syntra ODBC is read-only. The Pro plan enables INSERT, UPDATE, DELETE, and void operations that write directly to QuickBooks.
  • ✓ Enable pool_pre_ping=True to gracefully handle reconnections if Syntra restarts.
  • ✓ For complex reports, use text() with raw SQL inside session.execute().

Full reference and more examples: SQLAlchemy integration docs →

QuickBooks meets SQLAlchemy

Download Syntra ODBC and reflect your QuickBooks schema into Python ORM models instantly.

Download Free Trial