Read any table in Python

A data table is a table of an upstream SQL database that your project reads as it is: production by month, a lithology log, laboratory results. It is not a table of wells, and reading it creates nothing in the project. A project administrator declares it once in the Workspace (Sources → How to read → Data table): optionally a key, and per column a description, a unit and a missing-value marker. The declaration is the project’s; each selection of the table (also made by a project administrator) is its selector’s own and is read with their own database sign-in. client is an ophiolite.Client for your project — see Build on Ophiolite for a key and the SDK. A well table is read as described in Read a connected source in Python.

Read it, verified

Pick the source by its id from client.sources() (its profile is table/1):

source = client.source(source_id)
snapshot = source.read(expect_revision=source.revision)
print(snapshot.profile, snapshot.key, len(snapshot), "rows")
for column in snapshot.not_read:
    print("not read:", column["name"], column["db_type"])

Before any row is returned, the SDK checks that the server returned the source you selected and that the payload’s SHA-256 equals the manifest’s checksum and the revision. snapshot.key is the declared key, or None: an unkeyed table keeps its rows in the order of their values, and repeated rows stay.

A column of a type that is not read (see the table below) is listed in snapshot.not_read, and every such read warns naming it. The revision covers only the columns read: a change only in a column that is not read does not change the revision. When you need every column, source.read(require_all_columns=True) refuses instead and returns no rows.

Into a DataFrame

frame = snapshot.to_frame()
print(frame.dtypes)
units = {c["name"]: c["unit"] for c in frame.attrs["columns"] if c["unit"]}
print(units, frame.attrs["revision"][:12])

Each column read becomes one DataFrame column, typed by how the database declares it; nothing is converted. frame.attrs["columns"] holds each column’s database type, description, unit and missing marker as declared; frame.attrs["not_read"] the columns not read.

Database type	Read as	In Python (snapshot.rows)	In the DataFrame
text, varchar, char, uuid	text	str, as stored	string
smallint, integer, bigint	integer	str of the exact digits	Int64
real, double precision	float	float	Float64
numeric, decimal	decimal	str at the declared scale ("1520.250")	Decimal objects; decimal="float" gives Float64 (lossy)
boolean	boolean	bool	boolean
date	date	str, YYYY-MM-DD	datetime64[s]
timestamp	timestamp	str, ISO without zone	datetime64 at the smallest exact resolution
timestamp with time zone	timestamptz	str, UTC ending in Z	datetime64[…, UTC] at the smallest exact resolution
anything else (bytea, json and jsonb, arrays, time, interval, money, geometric and PostGIS types, enums, user-defined types)	not read	absent	absent; named in frame.attrs["not_read"]

A date or timestamp column that pandas cannot hold exactly (infinity, a date outside the years pandas holds, more fractional digits than nanoseconds) stays string, and frame.attrs["text_fallbacks"] says why; nothing is clamped or rounded. SQLite tables read text, integer, float, boolean, date and timestamp columns; a SQLite column with no declared type, or declared NUMERIC or DECIMAL, is not read.

The declared missing values, and decimals as numbers

numbers = snapshot.to_frame(missing="declared", decimal="float")
print(numbers.attrs["transformations"])

missing="declared" turns the values equal to a column’s declared missing marker into NA; decimal="float" turns exact decimals into floats, which can round. Both are listed in frame.attrs["transformations"], and snapshot.rows never changes.

Keep a copy

frame.to_csv("table-from-source.csv", index=False)

The file is your own copy: it is not shared with anyone and not kept up to date.

When a read is refused

• SourceNeedsReview: the table’s columns changed (“Table schema changed; review and save a new table declaration”). An administrator saves a new declaration in the Workspace and you review your selection.
• SourceRevisionUnavailable: the revision you hold can no longer be read — the rows changed, or a new declaration was saved and your selection is not yet reviewed.
• SourceRevisionDiffers: with expect_revision, the server holds another revision; no rows are given.
• SourceSignInRowsDiffer: you read under another database sign-in than the one you reviewed with, and it returns other rows; review the selection under the sign-in you use now.

From the command line

ophiolite sources describe SOURCE_ID --json
ophiolite sources read SOURCE_ID --expect-revision REVISION --out table.csv
ophiolite sources read SOURCE_ID --require-all-columns --out table.csv

describe names the columns, the key, the declared units and the columns not read; it reads the whole table once.