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.

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.
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]})
```
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?`a`b`c;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?`a`b`c;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:`tom`dick`harry; age:28 29 35; hair:`fair`dark`fair; eye:`green`brown`gray)')
pykx.q['qtab'] = qtab

Update all the contents of a column

pykx.q.qsql.update(qtab, {'eye': '`blue`brown`green'})
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?`a`b`c;name:100?`nut`bolt`screw;color:100?`red`green`blue;weight:0.5*100?20;city:100?`london`paris`rome)')
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:`tom`dick`harry;age:28 29 35;hair:`fair`dark`fair;eye:`green`brown`gray)')
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']