Query

pykx.query

Query interfaces for PyKX.

QSQL

QSQL(q)

Generates and submits functional q SQL queries.

Instances of this class can be accessed as the qsql attribute of any pykx.Q. For instance, pykx.q.qsql, or pykx.QConnection(...).qsql.

The QSQL class provides Python users with a method of querying q simple, keyed, splayed and partitioned tables using a single set of functionality.

This is achieved by wrapping the logic contained within the q functional select, exec, update, and delete functionality. For more information on this functionality please refer to Chapter 9 Section 12 of Q for Mortals.

While it is also conceivable that the interface could compile a qSQL statement to achieve the same end goal there are a number of advantages to using the more complex functional form.

1. Users that are unfamiliar with q who use the interface are introduced to the more powerful version of querying with q, while still operating within a familiar setting.
2. Using the functional form provides the ability when running functional updates to update the q tables with data derived from Python:

qtable = pykx.q('([]1 2 3;4 5 6)')
pykx.q.qsql.update(qtable, {'x': [10, 20, 30]})
3. It makes development and maintenance of the interface easier when dealing across the forms of supported table within q within which the functional forms of interacting with tables are more natural.

select

select(table, columns=None, where=None, by=None)

Apply a q style select statement on tables defined within the process.

This implementation follows the q functional select syntax with limitations on structures supported for the various clauses a result of this.

Parameters:

Name Type Description Default
table Union[k.Table, str]

The q table or name of the table (provided the table is named within the q memory space) on which the select statement is to be applied.

required
columns Optional[Union[Dict[str, str], k.Dictionary]]

A dictionary mapping the name to be given to a column and the logic to be applied in aggregation to that column both as strings.

None
where Optional[Union[List[str], str, k.SymbolAtom, k.SymbolVector]]

Conditional filtering used to select subsets of the data on which by-clauses and appropriate aggregations are to be applied.

None
by Optional[Union[Dict[str, str], k.Dictionary]]

A dictionary mapping the names to be assigned to the produced columns and the columns whose results are used to construct the groups of the by clause.

None

Examples:

Define a q table in python, and give it a name in q memory

qtab = pykx.q('([]col1:100?abc;col2:100?1f;col3:100?0b;col4:100?10f)')
pykx.q['qtab'] = qtab

Select all items in the table

pykx.q.qsql.select(qtab)
pykx.q.qsql.select('qtab')

Filter table based on various where conditions

pykx.q.qsql.select(qtab, where='col2<0.5')
pykx.q.qsql.select(qtab, where=['col1=a', 'col2<0.3'])

Retrieve statistics by grouping data on symbol columns

pykx.q.qsql.select(qtab, columns={'maxCol2': 'max col2'}, by={'col1': 'col1'})
pykx.q.qsql.select(qtab, columns={'avgCol2': 'avg col2', 'minCol4': 'min col4'}, by={'col1': 'col1'})

Retrieve grouped statistics with restrictive where condition

pykx.q.qsql.select(qtab, columns={'avgCol2': 'avg col2', 'minCol4': 'min col4'}, by={'col1': 'col1'}, where='col3=0b')

exec

exec(table, columns=None, where=None, by=None)

Apply a q style exec statement on tables defined within the process.

This implementation follows the q functional exec syntax with limitations on structures supported for the various clauses a result of this.

Parameters:

Name Type Description Default
table Union[k.Table, str]

The q table or name of the table (provided the table is named within the q memory space) on which the exec statement is to be applied.

required
columns Optional[Union[Dict[str, str], k.Dictionary]]

A dictionary mapping the name to be given to a column and the logic to be applied in aggregation to that column both as strings. A string defining a single column to be retrieved from the table as a list.

None
where Optional[Union[List[str], str, k.SymbolAtom, k.SymbolVector]]

Conditional filtering used to select subsets of the data on which by clauses and appropriate aggregations are to be applied.

None
by Optional[Union[Dict[str, str], k.Dictionary]]

A dictionary mapping the names to be assigned to the produced columns and the the columns whose results are used to construct the groups of the by clause.

None

Examples:

Define a q table in python and named in q memory

qtab = pykx.q('([]col1:100?abc;col2:100?1f;col3:100?0b;col4:100?10f)')
pykx.q['qtab'] = qtab

Select last item of the table

pykx.q.qsql.exec(qtab)
pykx.q.qsql.exec('qtab')

Retrieve a column from the table as a list

pykx.q.qsql.exec(qtab, 'col3')

Retrieve a set of columns from a table as a dictionary

pykx.q.qsql.exec(qtab, {'symcol': 'col1'})
pykx.q.qsql.exec(qtab, {'symcol': 'col1', 'boolcol': 'col3'})

Filter columns from a table based on various where conditions

pykx.q.qsql.exec(qtab, 'col3', where='col1=a')
pykx.q.qsql.exec(qtab, {'symcol': 'col1', 'maxcol4': 'max col4'}, where=['col1=a', 'col2<0.3'])

Retrieve data grouping by data on symbol columns

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

Retrieve grouped statistics with restrictive where condition

pykx.q.qsql.exec(qtab, columns={'avgCol2': 'avg col2', 'minCol4': 'min col4'}, by={'col1': 'col1'}, where='col3=0b')

update

update(table, columns=None, where=None, by=None, modify=False)

Apply a q style update statement on tables defined within the process.

This implementation follows the q functional update syntax with limitations on structures supported for the various clauses a result of this.

Parameters:

Name Type Description Default
table Union[k.Table, str]

The q table or name of the table (provided the table is named within the q memory space) on which the update statement is to be applied.

required
columns Optional[Union[Dict[str, str], k.Dictionary]]

A dictionary mapping the name of a column present in the table or one to be added to the contents which are to be added to the column, this content can be a string denoting q data or the equivalent Python data.

None
where Optional[Union[List[str], str, k.SymbolAtom, k.SymbolVector]]

Conditional filtering used to select subsets of the data on which by-clauses and appropriate aggregations are to be applied.

None
by Optional[Union[Dict[str, str], k.Dictionary]]

A dictionary mapping the names to be assigned to the produced columns and the columns whose results are used to construct the groups of the by clause.

None
modify bool

Whether the result of a delete is to be saved. This holds when table is the name of a table in q memory, as outlined at: https://code.kx.com/q/basics/qsql/#result-and-side-effects.

False

Examples:

Define a q table in python and named in q memory

qtab = pykx.q('([]name:tomdickharry; age:28 29 35; hair:fairdarkfair; eye:greenbrowngray)')
pykx.q['qtab'] = qtab

Update all the contents of a column

pykx.q.qsql.update(qtab, {'eye': 'bluebrowngreen'})
pykx.q.qsql.update(qtab, {'age': [25, 30, 31]})

Update the content of a column restricting scope using a where clause

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

Define a q table suitable for by clause example

byqtab = pykx.q('([]p:100?abc;name:100?nutboltscrew;color:100?redgreenblue;weight:0.5*100?20;city:100?londonparisrome)')
pykx.q['byqtab'] = byqtab

Apply an update grouping based on a by phrase

pykx.q.qsql.update(byqtab, {'weight': 'avg weight'}, by={'city': 'city'})

Apply an update grouping based on a by phrase and persist the result using the modify keyword

python pykx.q.qsql.update('byqtab', columns={'weight': 'avg weight'}, by={'city': 'city'}, modify=True) pykx.q['byqtab']

delete

delete(table, columns=None, where=None, modify=False)

Apply a q style delete statement on tables defined within the process.

This implementation follows the q functional delete syntax with limitations on structures supported for the various clauses a result of this.

Parameters:

Name Type Description Default
table Union[k.Table, str]

The q table or name of the table (provided the table is named within the q memory space) on which the delete statement is to be applied.

required
columns Optional[Union[List[str], k.SymbolVector]]

Denotes the columns to be deleted from a table.

None
where Optional[Union[List[str], str, k.SymbolAtom, k.SymbolVector]]

Conditional filtering used to select subsets of the data which are to be deleted from the table.

None
modify bool

Whether the result of a delete is to be saved. This holds when table is the name of a table in q memory, as outlined at: https://code.kx.com/q/basics/qsql/#result-and-side-effects.

False

Examples:

Define a q table in python and named in q memory

qtab = pykx.q('([]name:tomdickharry;age:28 29 35;hair:fairdarkfair;eye:greenbrowngray)')
pykx.q['qtab'] = qtab

Delete all the contents of the table

pykx.q.qsql.delete(qtab)
pykx.q.qsql.delete('qtab')

Delete single and multiple columns from the table

pykx.q.qsql.delete(qtab, 'age')
pykx.q.qsql.delete('qtab', ['age', 'eye'])

Delete rows of the dataset based on where condition

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

Delete a column from the dataset named in q memory and persist the result using the modify keyword

pykx.q.qsql.delete('qtab', 'age', modify=True)
pykx.q['qtab']`