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 |
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 |
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']