sagekit 0.1.0

A small, dependency-light toolkit for reading data out of Sage 50 (UK/Ireland) via ODBC, with column-name auto-detection across Sage versions.

sagekit

A small, dependency-light Python toolkit for reading data directly out of Sage 50 (UK/Ireland) via ODBC -- no need to export a report to CSV first. Built for scripts that need to pull invoices, customer records, or ledger data straight from a live Sage install: reminder emails, dashboards, scheduled reports, reconciliations, and so on.

If this saves you time, consider buying me a coffee.

Features

• Connects to Sage 50 via its own ODBC driver -- nothing extra to install on the Sage side
• No secrets in code or on disk -- DSN/username/password are read from environment variables, with a safe interactive prompt (hidden password input) for anything not set
• Column-name auto-detection: Sage 50's schema varies slightly between versions, so sagekit inspects the live table rather than assuming one fixed set of column names
• DELETED_FLAG auto-detection: some installs store it as numeric (0/1), others as string ('N'/'Y') -- sagekit works out which
• Two worked examples: outstanding invoices, customer list

Installation

pip install -r requirements.txt

or, to install as an editable package:

pip install -e .

You'll also need the Sage Line 50 ODBC driver, which Sage 50 installs alongside itself -- there's nothing separate to download. Confirm it's there by searching "ODBC Data Sources (64-bit)" in the Windows Start menu; you should see a Sage-related driver listed under the "Drivers" tab, and your DSN (see below) under "System DSN" or "User DSN".

Finding your DSN

The DSN (Data Source Name) identifies which Sage company database to connect to. To find it:

1. Search "ODBC Data Sources (64-bit)" in Windows and open it.
2. Look under the System DSN or User DSN tab for an entry pointing at your Sage 50 data (often named something like SageLine50v33, with the number matching your Sage version).
3. That name is your SAGE_DSN.

If you're not sure, sagekit will prompt you for it at runtime -- you don't have to set it in advance.

Configuration

Copy .env.example to .env if you want to pre-fill values (useful for scheduled/unattended scripts):

cp .env.example .env

SAGE_DSN=SageLine50v33
SAGE_UID=MANAGER
SAGE_PWD=

.env is already in .gitignore -- never commit real credentials. Leaving SAGE_PWD blank (or unset) is recommended for anything you run by hand -- sagekit will prompt for it with hidden input instead, so your password never sits in a file or your shell history.

To load .env automatically in your own scripts, install python-dotenv and add this before importing sagekit:

from dotenv import load_dotenv
load_dotenv()

Sagekit itself doesn't require python-dotenv -- it only reads os.environ, plus interactive prompts for anything missing.

Usage

from sagekit import connect

conn = connect()   # prompts for DSN/username/password if not set
cursor = conn.cursor()
cursor.execute("SELECT * FROM AUDIT_HEADER WHERE TYPE = 'SI'")
rows = cursor.fetchall()
conn.close()

Sage 50 must be running on the machine (or reachable on the network) while you query it via ODBC -- the driver talks to the live application, not a standalone database file.

Column auto-detection

Column names vary a little between Sage 50 versions. Rather than hardcoding one version's names, use find_column() to ask the live table what it's actually called:

from sagekit import connect, find_column

conn = connect()
cursor = conn.cursor()

outstanding_col = find_column(cursor, "AUDIT_HEADER", "outstanding_amount")
cursor.execute(f"SELECT {outstanding_col} FROM AUDIT_HEADER WHERE TYPE = 'SI'")

Known concepts and the column-name variants sagekit checks for each (see sagekit/discovery.py to extend this list):

Concept	Variants checked
transaction_date	DATE, INV_DATE, TRANS_DATE, DATE_ENTERED
due_date	DUE_DATE, DATE_DUE
outstanding_amount	OUTSTANDING, OS_AMOUNT, OUTSTANDING_AMOUNT
gross_amount	GROSS_AMOUNT, AMOUNT, FOREIGN_GROSS_AMOUNT
deleted_flag	DELETED_FLAG, DELETED, RECORD_DELETED
customer_name	NAME, COMPANY_NAME, ACCOUNT_NAME
email	E_MAIL, EMAIL_1, EMAIL, EMAIL1, DIRECT_DEBITS_EMAIL
payment_due_days	PAYMENT_DUE_DAYS, PAY_DUE_DAYS
payment_due_from	PAYMENT_DUE_FROM_CODE, PAY_DUE_FROM_CODE

Or use get_columns(cursor, table) to just see everything a table has:

from sagekit import get_columns
print(get_columns(cursor, "AUDIT_HEADER"))

DELETED_FLAG auto-detection

from sagekit import detect_deleted_flag

not_deleted = detect_deleted_flag(cursor, "AUDIT_HEADER", sample_type="SI")
cursor.execute(f"SELECT * FROM AUDIT_HEADER WHERE TYPE = 'SI' AND DELETED_FLAG = {not_deleted}")

Key tables

Table	Contents
AUDIT_HEADER	One row per transaction (invoices, credits, receipts). Has TYPE, INV_REF, date/due-date, outstanding/gross amount, deleted flag
SALES_LEDGER	One row per customer account. Has name, email, payment terms, address fields
AUDIT_SPLIT	Line-level detail for each transaction (product codes, quantities, nominal codes)
STOCK	Product / stock item records
NOMINAL_LEDGER	Nominal account records

For most accounts-receivable tasks, AUDIT_HEADER joined to SALES_LEDGER on ACCOUNT_REF is all you need -- see examples/outstanding_invoices.py.

Transaction type codes

Common codes on the sales side (verify against your own install -- see below):

Code	Meaning
SI	Sales Invoice
SC	Sales Credit Note
SA	Sales Payment on Account (unallocated receipt)

These letters are generally consistent across Sage 50 installs, but custom transaction types can exist. To check what your install actually has:

from sagekit import verify_transaction_types
print(verify_transaction_types(cursor))

Examples

• examples/outstanding_invoices.py -- list outstanding sales invoices, joined to customer names
• examples/list_customers.py -- dump customer accounts with email addresses

Run either directly once you've set up your .env (or just answer the prompts):

python examples/outstanding_invoices.py
python examples/list_customers.py

Testing

pip install pytest
pytest

Tests mock pyodbc, so they run offline without a real Sage install or ODBC driver.

Troubleshooting

Symptom	Likely cause	Fix
Data source name not found	DSN name wrong or not set up	Open "ODBC Data Sources (64-bit)", check the DSN exists and matches SAGE_DSN
Login failed	Wrong username/password	Try logging into Sage 50 directly first to confirm credentials
0 rows returned	Wrong DELETED_FLAG value or TYPE code for this install	Use detect_deleted_flag() / verify_transaction_types()
Column not found	Column name varies by version	Use get_columns() or find_column() to inspect actual column names
pyodbc not installed	Missing package	pip install pyodbc
Connection hangs or times out	Sage 50 not running	Sage 50 must be open on the same machine (or accessible on the network) while querying via ODBC

Notes

• Use the 64-bit ODBC driver with 64-bit Python -- mixing 32-bit and 64-bit causes connection failures.
• Credentials are never written to disk by sagekit itself -- they live only in memory for the duration of a script (unless you choose to put them in your own .env file for unattended use, at your own risk).

License

MIT -- see LICENSE. Do whatever you like with this; a credit or a Ko-fi tip is appreciated but never required.