Querying PyKX data

There are a number of ways to query tables using PyKX.

Directly using q.
The qSQL API.
The ANSI SQL API.

Each of these methods can be used both locally using Embedded q and over IPC using a QConnection instance.

Directly using q

For users that have previous knowledge of kdb+/q and wish to directly write their queries in pure q they can directly write and execute queries in the same way they would in a q process.

For example you can do this to run qSQL queries directly, where q is either Embedded q or an instance of a pykx.QConnection.

>>> q('select from t') # where t is a table in q's memory
>>> q('{[t] select from t}', tab) # where tab is a PyKX Table object

Query APIs

PyKX has two main APIs to help query local tables as well as tables over IPC. The first API is the qSQL API which can be used to generate functional q SQL queries on tables. The second API is the ANSI SQL API which supports a large subset of ANSI SQL.

qSQL API

The qSQL API provides various helper functions around generating selecting, updating, deleteing and executing various functional qSQL queries.

For example you can do this to execute a functional qSQL select, where q is Embedded q or a pykx.QConnection instance.

# select from table object
>>> kx.q.qsql.select(qtab, columns={'maxCol2': 'max col2'}, by={'col1': 'col1'})
# or by name
>>> kx.q.qsql.select('qtab', columns={'maxCol2': 'max col2'}, by={'col1': 'col1'})

Or you can use this to run a functional qSQL execute.

>>> kx.q.qsql.exec(qtab, columns={'avgCol2': 'avg col2', 'minCol4': 'min col4'}, by={'col1': 'col1'})

You can also update rows within tables using qSQL for example.

>>> kx.q.qsql.update(qtab, {'eye': ['blue']}, where='hair=`fair')

You can also delete rows of a table based on vairious conditions using qSQL.

>>> kx.q.qsql.delete('qtab', where=['hair=`fair', 'age=28'])

When operating on in-memory tables, updates can be persisted for select, update and delete calls. For example, using the inplace keyword for select statements.

>>> qtab = kx.Table(data = {'a': [1, 2, 3], 'b': ['a', 'b', 'c']})
>>> qtab
pykx.Table(pykx.q('
a b
---
1 a
2 b
3 c
'))
>>> kx.q.qsql.select(qtab, where=['a in 1 2'], inplace=True)
pykx.Table(pykx.q('
a b
---
1 a
2 b
'))
>>> qtab  # Query has been persisted
pykx.Table(pykx.q('
a b
---
1 a
2 b
'))

ANSI SQL API

The ANSI SQL API can be used to prepare and execute SQL queries, on q tables. The use of this API also requires an extra feature flag to be present on your / the servers license to be used.

For example you can do this to execute a SQL query against a table named trades in q's memory using either Embedded q or over IPC using a pykx.QConnection.

>>> q.sql('select * from trades where date = $1 and price < $2', date(2022, 1, 2), 500.0)

You can also directly pass a pykx.Table object in as a variable to SQL queries.

>>> q.sql('select * from $1', trades) # where `trades` is a `pykx.Table` object

Finally, you can prepare a SQL query and then when it is used later the types will be forced to match in order for the query to run.

>>> import pykx as kx
>>> p = kx.q.sql.prepare('select * from trades where date = $1 and price < $2',
    kx.DateAtom,
    kx.FloatAtom
)
>>> kx.q.sql.execute(p, date(2022, 1, 2), 500.0)