Spencer McGuire spencerm

Joined on 2026-06-16

visualkit (0.4.2)

Published 2026-10-20 17:26:28 +00:00 by spencerm

To install the package using pip, run the following command:

pip install --index-url https://gt.pescoinc.biz/api/packages/spencerm/pypi/simple/ --extra-index-url https://pypi.org/simple visualkit

For more information on the PyPI registry, see the documentation.

Read-only access to the Infor VISUAL Manufacturing 10 ERP database (SQL Server)

VisualKit

Read-only Python access to PESCO's Infor VISUAL Manufacturing 10 ERP database (SQL Server). See PLAN.md for the design and roadmap, and CHANGELOG.md for releases.

Using VisualKit in your own app or scheduled job? Start with docs/INSTALL.md. This README is for working on VisualKit itself.

VisualKit never changes anything in VISUAL. Only SELECT statements get through, and it connects to DEV unless production is chosen explicitly.

Install (development)

You need Python 3.11+ and Microsoft ODBC Driver 18 for SQL Server (64-bit). On Linux, see docs/INSTALL.md for the driver.

python -m venv .venv
.venv\Scripts\python -m pip install -e ".[dev,keyring]"
.venv\Scripts\visualkit doctor          # checks settings, driver, encryption, auth, connectivity and that it can't write

On Linux, use .venv/bin/ instead of .venv\Scripts\, and leave out keyring.

Configure

Copy .env.example to .env. The defaults point at DEV (VSQL\DEV / PESCOINCDEV). Don't put the password in .env.

Windows, before visualkit_ro exists: leave VISUAL_USER unset. VisualKit uses your Windows login (integrated auth). Keep to DEV and small queries.
With visualkit_ro: set VISUAL_USER=visualkit_ro, then either
- store the password once in Windows Credential Manager: .venv\Scripts\keyring set visualkit visualkit_ro, or
- set VISUAL_PASSWORD, or VISUAL_PASSWORD_FILE pointing at a file that holds only the password.

Production (vsql / PESCOINC) is only used with VISUAL_PROFILE=prod, or with VISUAL_SERVER=vsql and VISUAL_DATABASE=PESCOINC both set. All settings are listed in docs/INSTALL.md.

Use

from visualkit import Visual

with Visual.from_env() as v:
    print(v.server_info())                     # SQL Server version, database, login, app name, VISUAL DBVERSION
    for site in v.sites():
        print(site)

    rows = v.query("SELECT ID, DESCRIPTION FROM PART WHERE ID LIKE ?", ["L073%"], max_rows=100)
    count = v.scalar("SELECT COUNT(*) FROM PART")
    for row in v.stream("SELECT ID FROM PART"):  # fetched in batches of VISUAL_BATCH_SIZE
        ...

query() stops at VISUAL_MAX_ROWS rows (10,000) unless max_rows= says otherwise. Use stream() for big reads.
Every query has a timeout (VISUAL_TIMEOUT, 60 s).
Anything that isn't a single SELECT is refused before it reaches the server.
ID columns come back as text. Keep them that way.

Domains

Eight typed, read-only repositories hang off Visual. They return frozen pydantic models. The full reference is in docs/INSTALL.md.

from datetime import date
from visualkit import Visual, WorkOrderKey

with Visual.from_env() as v:
    # Parts
    part = v.parts.get("L073-76-2")
    parts = v.parts.get_many(["L073-76-2", "L073-76-2GA"])         # {id: Part}
    planning = v.parts.site("L073-76-2GA", "PESCO")                  # PART_SITE: price, primary location

    # Customer orders
    order, customer = v.orders.with_customer("51049")
    lines = v.orders.lines("51049")
    open_orders = v.orders.list(open_only=True, limit=50)            # a Page

    # Work orders
    wo = v.work_orders.get(WorkOrderKey.parse("L073-75-1/53/0/0"))
    ops = v.work_orders.operations(wo.key)                           # op.barcode() for Packet Station
    feed = v.work_orders.released("PESCO", date(2026, 10, 1), date(2026, 10, 31))

    # Inventory
    stock = v.inventory.on_hand("L073-76-2GA", site_id="PESCO")      # nonzero locations

    # Shipments
    packlists = v.shipments.for_order("51049")
    shipped = v.shipments.for_order_line("51049", 1)                 # [] means not shipped yet

    # Labor: tickets, demonstrated capacity, resources, departments, employees (IDs only)
    tickets = v.labor.tickets_for_work_order("L707001")              # LaborTicket list
    week = v.labor.tickets(date_between=(date(2026, 10, 8), date(2026, 10, 14)), type="R")
    hours = v.labor.hours_by_resource("PESCO", date(2026, 10, 8), date(2026, 10, 14))
    resources = v.labor.resources(site_id="PESCO", obsolete=False)  # STATUS 'O' is obsolete

    # Purchasing: POs, lines, deliveries, receipts, vendors (no vendor names)
    po = v.purchasing.get("268122")
    open_lines = v.purchasing.lines_list(open_only=True, part_id_prefix="L489-")
    pegged = v.purchasing.lines_for_work_order("L707001")            # outside service and material
    receipts = v.purchasing.receipts_for_po("268122")

    # Links: work order to customer order, and PO lines to work orders
    orders = v.links.orders_for_work_orders([WorkOrderKey("L707001")])
    po_links = v.links.for_purchase_order("268122", line_no=1)

v.links also answers "which order is this job for" through DEMAND_SUPPLY_LINK; see the reference for what it covers.

Ad-hoc SQL

For a question no repository answers, visualkit.sql runs one SELECT you wrote yourself, through the same guard. Values go in as :name parameters, never formatted into the SQL. There is no site_id: filter on SITE_ID in the query.

from visualkit import Visual, run_sql, stream_sql
from visualkit.sql import load_sql_file

with Visual.from_env() as v:
    res = run_sql(v, "SELECT TOP 10 ID, STATUS FROM PURCHASE_ORDER WHERE VENDOR_ID = :vendor "
                     "ORDER BY ID DESC", {"vendor": "HALSER"})
    print(res.columns, len(res), res.elapsed, res.sha256)

    sql = load_sql_file("open_po_lines.sql")                       # one SELECT, WITH ... allowed
    with stream_sql(v, sql, {"site": "PESCO"}) as rows:              # no row cap; run it off-hours
        for row in rows:
            ...

SET, DECLARE, @variables, temp tables, EXEC and a second statement are refused. visualkit.sql.explain_rejection(sql) says which construct is blocked and how to rewrite it. The porting notes for Insights' scripts are in docs/INSTALL.md.

List calls return a Page: page.items, page.has_more, page.next_offset.

CLI

visualkit doctor | info | sites
visualkit parts get L073-76-2 L073-76-2GA          # also: list, search, site
visualkit orders get 51049                         # also: list, lines
visualkit wo get L073-75-1/53/0/0                  # or a bare base ID: lot 1, split 0, sub 0
visualkit wo released --since 2026-10-01 --until 2026-11-14 --limit 5
visualkit wo barcode "*%2L073-75-1$53$0$0$10%*" --fetch
visualkit inventory onhand L073-76-2GA             # also: list, warehouses, locations
visualkit ship get 77881                           # also: list, for-order, for-line
visualkit labor hours --since 2026-10-08 --by resource --limit 5
visualkit labor wo L707001                         # also: tickets, resources, departments, employees
visualkit po list --open --limit 3                 # also: get, lines, wo, receipts
visualkit po wo L707001                            # PO lines pegged to a work order
visualkit vendor get HALSER                        # IDs and terms only; never the vendor name
visualkit sql "SELECT TOP 3 ID FROM PART ORDER BY ID" --json
visualkit sql --file q.sql -p site=PESCO -p since:date=2026-10-01 --explain
visualkit extract list                             # also: run, status (see Extract below)

Every data subcommand prints a table, or JSON with --json. Most take --site. List subcommands take --limit and --offset; when there are more rows, the next --offset goes to stderr. --width sets where long text is cut (40 by default). Dates are YYYY-MM-DD or ISO. Run visualkit <domain> <action> --help for the filters.

Exit codes: 0 fine, 1 not found or a VISUAL error, 2 bad settings or input. Production needs --allow-production.

Packet Station example

examples/packet_station_feed.py replaces the two Power BI feeds Packet Station reads today. It writes one JSON object with meta, rows (Job Release Information: one row per operation, with wo, lot, split, sub, seq, res, dwg, rev, ps, desc, qty, tgt, rel, want, st, plus barcode, part, site and order), materials (Job Pull: one row per material line, with wo, lot, split, sub, seq, piece, part, qty, issued, whs, loc, st, istat, dwg, desc, comm, cons), orders_by_base and orders_by_project.

Customer orders come from DEMAND_SUPPLY_LINK (v.links), not from part-number prefixes. orders_by_base holds the orders a base is pegged to directly; only whole-number top jobs (L489001) and field-service jobs are. orders_by_project holds the orders of each project's top jobs, found by PESCO's "project plus three digits" naming (L489 -> L489001); that is a naming convention, not a VISUAL relationship. Each row's order is the base's own order, else the project's first open order, else its newest order, else null, with via saying which. Order entries carry the order ID, status, customer ID and name, customer PO and dates; no amounts.

.venv\Scripts\python examples\packet_station_feed.py --since 2026-09-30 --out feed.json

--since is required. --until is optional; without it there is no upper bound, like the Power BI report.
--status picks the work order statuses for rows (default F,R,U,C). --materials-status picks the bases and legs for materials (default F,R).
--no-materials leaves materials out. --no-orders leaves out orders_by_base, orders_by_project and order. --full adds the raw work_orders, operations, requirements, parts and orders sections; orders lists the directly linked orders with their bases and lines.
PARTSPECS (ps) comes from OPERATION_BINARY; the text of material lines with no part comes from REQUIREMENT_BINARY.
Numbers are JSON numbers, dates YYYY-MM-DD, IDs strings. No cost, price or pay field is written.

On DEV, since 2026-09-30 with no upper bound: 1,533 work orders, 5,962 rows and 16,956 material lines in about 7.5 seconds. Compared with the Power BI export of 2026-10-14, every matched field agrees except where DEV changed after the export (see PLAN.md section 8b).

Extract

visualkit.extract pulls whole tables to CSV or Parquet files for Insights and other bulk readers. There are 40 ready-made jobs, three of them restricted. PART is incremental on MODIFY_DATE. LABOR_TICKET and INVENTORY_TRANS are windowed on TRANSACTION_DATE, with 730 and 1,096 days of history. Every other job is a snapshot. The full guide, with the output layout and how to read it, is in docs/INSTALL.md.

visualkit extract list                                     # jobs: mode, modified column, column count; and the presets
visualkit extract run insights --out D:\extracts\visual    # a preset, job names, or "all"
visualkit extract run part labor_ticket --out D:\extracts\visual --since 2025-01-01
visualkit extract run work_order --out x --dry-run         # print the SQL and parameters; no connection
visualkit extract status --out D:\extracts\visual          # checkpoints, last complete run, last 10 runs

run prints one line per job (rows, bytes, seconds, mode, checkpoint). A failed job is reported and the next one runs; the exit code is then 1.
--full restarts incremental jobs from their start and replaces their part files. --since sets that start for incremental jobs. --site filters the jobs that have a site column. --format parquet needs pip install "visualkit[parquet]".
Production needs --allow-production and runs only between 18:00 and 06:00 unless --force.
Presets (visualkit.extract.presets.PRESETS): insights (every ordinary job: masters, then documents, then the two windowed tables), packet_station, masters, restricted (every restricted job) and restricted_costs (the two cost jobs).
Restricted jobs work_order_cost and part_site_cost read labor and burden cost totals per work order and per part, and employee_name reads employee ID, first and last name, for VisualMCP. They are only in restricted and restricted_costs (costs only), never in all, and run only with --allow-restricted (run_job(..., allow_restricted=True)). extract list marks them RESTRICTED.

from pathlib import Path
from visualkit import Visual
from visualkit.extract import JOBS, run_job

with Visual.from_env() as v:
    summary = run_job(v, JOBS["part"], Path("extracts"))
    print(summary.rows, summary.output, summary.checkpoint_after)

Insights snapshot example

examples/insights_snapshot.py writes Insights' dated layout on top of run_job: ROOT/<DEV|PROD>/<yyyy-MM-dd_HHmm>.partial/ while running, renamed without .partial only when every job completed, with a manifest.json (who, when, parameters, and per job rows, bytes, seconds and the query's SHA-256). Each snapshot is a new folder, so incremental jobs run with full=True and the windowed tables re-read their whole window every time, as run_extract.ps1 does.

.venv\Scripts\python examples\insights_snapshot.py --out D:\visual-insights --site PESCO

--history-days N sets the labor and inventory window (default 35); --full makes it 730 days. On DEV the whole insights preset took 139 s for 3.16 million rows (349 MB of CSV); the largest read was REQUIREMENT, 984,356 rows in 50 s.

Test

.venv\Scripts\python -m pytest

Tests use a fake cursor and need no database. Integration tests run against DEV only, when asked:

$env:VISUALKIT_INTEGRATION = "1"; .venv\Scripts\python -m pytest

Release

Bump __version__ in src/visualkit/__init__.py and add a CHANGELOG.md entry.

Run the tests, then build:

.venv\Scripts\python -m pytest
.venv\Scripts\python -m build

If any runtime dependency version changed, update requirements/runtime-lock.txt. The first section lists the versions the tests ran with. The second is the keyring extra, mirrored for Windows only: on Linux the password comes from VISUAL_PASSWORD or VISUAL_PASSWORD_FILE, so keyring's Linux deps are not mirrored. The third is the parquet extra (pyarrow only; it has no dependencies). The fourth has the extras the keyring extra needs on Python 3.11 only; for those, pick releases that are at least 14 days old.

Build the wheelhouse, so installs work from PESCO's registry alone. pyodbc and pydantic_core have a separate wheel per platform and Python version, so this step matters. The dependency list is short, so plain pip download does it; copy the pins from requirements/runtime-lock.txt into the four lists. pyarrow publishes manylinux_2_28 Linux wheels only, so it is downloaded for that platform (PowerShell):

$runtime = 'annotated-types==0.8.0','pydantic-settings==2.16.0','pydantic==2.14.1','pydantic_core==2.50.1','pyodbc==5.3.0','python-dotenv==1.2.4','typing-inspection==0.4.4','typing_extensions==4.16.0'
$keyring = 'jaraco.classes==3.4.0','jaraco.context==6.1.2','jaraco.functools==4.6.0','keyring==25.7.0','more-itertools==11.2.1','pywin32-ctypes==0.2.3'
$py311   = 'backports.tarfile==1.2.0','importlib-metadata==9.0.1','zipp==4.1.0'
$parquet = 'pyarrow==26.0.0'
foreach ($plat in 'win_amd64','manylinux2014_x86_64') {
  foreach ($py in '3.11','3.12','3.13','3.14') {
    $pkgs = $runtime
    if ($plat -eq 'win_amd64') { $pkgs += $keyring; if ($py -eq '3.11') { $pkgs += $py311 } }
    foreach ($p in $pkgs) {
      .venv\Scripts\python -m pip download $p --no-deps --only-binary=:all: --platform $plat --implementation cp --python-version $py -d wheelhouse -q
      if ($LASTEXITCODE) { Write-Warning "no wheel: $p $plat cp$py" }
    }
    $pp = if ($plat -eq 'win_amd64') { $plat } else { 'manylinux_2_28_x86_64' }
    .venv\Scripts\python -m pip download $parquet --no-deps --only-binary=:all: --platform $pp --implementation cp --python-version $py -d wheelhouse -q
    if ($LASTEXITCODE) { Write-Warning "no wheel: $parquet $pp cp$py" }
  }
}

Then check that the whole tree resolves from dist and wheelhouse alone, for Linux and in a throwaway venv on Windows:

'visualkit[parquet]==0.3.0' | Set-Content $env:TEMP\vk-req.txt
foreach ($py in '3.11','3.14') {
  .venv\Scripts\python -m pip download -r $env:TEMP\vk-req.txt --no-index --find-links dist --find-links wheelhouse --only-binary=:all: --platform manylinux2014_x86_64 --platform manylinux_2_28_x86_64 --implementation cp --python-version $py -d $env:TEMP\vk-linux$py
}
python -m venv $env:TEMP\vk-venv
& $env:TEMP\vk-venv\Scripts\python -m pip install "visualkit[keyring,parquet]==0.3.0" --no-index --find-links dist --find-links wheelhouse
& $env:TEMP\vk-venv\Scripts\visualkit --version

Upload to the Gitea package registry. This needs a Gitea access token with write:package scope; paste it when twine asks for the password:
```
.venv\Scripts\python -m twine upload --repository-url https://gt.pescoinc.biz/api/packages/spencerm/pypi -u spencerm dist\*
.venv\Scripts\python scripts\missing_wheels.py
.venv\Scripts\python -m twine upload --repository-url https://gt.pescoinc.biz/api/packages/spencerm/pypi -u spencerm wheelhouse\upload\*
```
twine 7 refuses --skip-existing for registries other than PyPI, and Gitea rejects a re-upload of a file it already has. scripts\missing_wheels.py reads the registry's simple index and copies only the wheels it lacks to wheelhouse\upload\, so the second upload never hits a duplicate. Once both are uploaded, the registry install line in docs/INSTALL.md works:
```
pip install "visualkit==0.3.0" --index-url https://gt.pescoinc.biz/api/packages/spencerm/pypi/simple/
```
Tag the release: git tag v0.3.0 and git push --tags.

Source: https://gt.pescoinc.biz/spencerm/VisualKit. Proprietary, PESCO internal.

Requires Python: >=3.11

Details

PyPI

2026-10-20 17:26:28 +00:00

Proprietary - PESCO internal use

365 KiB

Assets (2)

visualkit-0.4.2-py3-none-any.whl 136 KiB

visualkit-0.4.2.tar.gz 229 KiB

Versions (6) View all

0.4.2

2026-10-20

0.4.1

2026-10-20

0.4.0

2026-10-20

0.3.0

2026-10-15

0.2.0

2026-10-15

visualkit (0.4.2)

Installation

About this package

VisualKit

Install (development)

Configure

Use

Domains

Ad-hoc SQL

CLI

Packet Station example

Extract

Insights snapshot example

Test

Release

Requirements