Skip to content
Connect > Client Libraries > Use Cases

Use case: quant analytics with pandas

Goal. A researcher pulls data out of the in-memory engine into pandas, for a notebook, a backtest, or an ad-hoc study, using standard Python database tools rather than the realtime client. No callbacks, no subscriptions: just query and get a DataFrame.

There are two ways in, both over the JDBC (SQL) protocol: the DB-API 2.0 driver and the SQLAlchemy dialect. Use SQLAlchemy if you want pandas.read_sql or the ORM; use the DB-API for plain cursor access.

Both need Python 3.14

The DB-API and SQLAlchemy layers both import the compiled native module, so both require Python 3.14. pyami.pyjdbc is native-free but plaintext-only (no TLS), and it is in the source repository only: pip install pyami-3forge does not install it. See Python client → Pure-Python JDBC.

SQLAlchemy + pandas (the common path)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
from sqlalchemy import create_engine
import pandas as pd

engine = create_engine("ami+jdbc://demo:demo123@localhost:3280")

with engine.connect() as conn:
    quotes = pd.read_sql_table("QUOTES", con=conn)                  # whole table
    ibm    = pd.read_sql_query(
        'SELECT SYM, bid, ask, last FROM QUOTES WHERE SYM == "IBM"',
        con=conn)                                                  # filtered

print(quotes.describe())

The connection URL is ami+jdbc://<user>:<password>@<host>:<port> with optional query parameters:

1
create_engine("ami+jdbc://demo:demo123@localhost:3280?use_ssl=false&timeout_ms=30000&limit=50000")

Note the expression syntax in WHERE: equality is == and string literals use double quotes. The dialect also supports reflection (get_table_names, get_columns) so tools that introspect the schema work.

DB-API 2.0 (plain cursor access)

1
2
3
4
5
6
7
8
from pyami.db.ami_dbapi import ami_dbapi as db

conn = db.connect(host="localhost", port=3280, username="demo", password="demo123")
cur = conn.cursor()
cur.execute("SELECT SYM, bid, ask FROM QUOTES")
for row in cur.fetchall():
    ...
conn.close()

connect(host, port=3280, username, password, timeout_ms=60000, limit=10000, use_ssl=True, …). The paramstyle is named:

1
cur.execute("SELECT * FROM QUOTES WHERE SYM == :sym", {"sym": "IBM"})

Limits to plan around

Results are capped, raise the limit for a full pull

The default row limit is 10 000 (DB-API limit=, or ?limit= on the SQLAlchemy URL). A study over a larger table must raise it or page the query, or it silently gets only the first 10 000 rows. The default query timeout is 60 000 ms on the DB-API / SQLAlchemy path.

No transactions

The DB-API driver has no transactions, commit and rollback are no-ops. This is a read-and-load path, not a unit-of-work path. For high-volume writes, use the native client's bulk insert (see Python client → Bulk insert).

Date/time columns carry epoch integers with no time zone

A temporal value comes back as a raw epoch integer (seconds / milliseconds / nanoseconds) with no time-zone attached, convert it in your client and agree the zone out of band. The pure-Python pyami.pyjdbc layer cannot decode temporal columns at all; if you must use it, select the instant as a Long epoch or a String. See Concepts → The type system.

Connecting securely

On a mutual-TLS server, the DB-API path takes the certificate arguments directly, this is the verified route for a secured read:

1
2
3
4
conn = db.connect(host="localhost", port=3280,
                  trusted_server_pub_file="keys/server.pub",
                  client_cert_file="keys/client.crt.pem",
                  client_key_file="keys/client.key.pem")

For SQLAlchemy, forward the same arguments to the underlying DB-API connect via connect_args:

1
2
3
4
5
engine = create_engine("ami+jdbc://demo:demo123@localhost:3280", connect_args={
    "trusted_server_pub_file": "keys/server.pub",
    "client_cert_file": "keys/client.crt.pem",
    "client_key_file": "keys/client.key.pem",
})

The plain ami+jdbc://… URL forms shown earlier assume a listener with no keystore (plain TCP). See Configuration → Connecting to a secured server.

What this shows

  • Standard tools work: the SQLAlchemy dialect and DB-API driver mean pandas, notebooks, and ORM code talk to 3forge Enterprise with no bespoke client.
  • This is a read/analytics path: query and load; for streaming use the Center subscription, for writes use bulk insert.