Selecting and indexing¶

Each selection is a child: it refers to rows of its parent, and runs no query until it is read.

Indexing with brackets¶

getitem ¶

__getitem__(sel)

Select rows by position or by key, as daf[n], daf[n:m] and daf[key] do.

Each gives a selection, not an ordinary Daf, so a large slice stays in the database. Read it with to_daf(), or to_dict() for one row. For one row as a dict, iloc() and select_record() are cheaper: they make no selection.

An int is always a position, from 0, even when the keyfield holds ints. Negative counts from the end. A position past the end raises IndexError, as in a Daf. For an int key, use select_record(), or select_records_daf() for several.
A slice of positions, as in sdaf[10:20]. A step is not supported yet. A slice past the end is empty, as in a Daf.
A str is a key. The keyfield's unique index is made first. A missing key raises KeyError, as in a Daf.
A list of keys gives their rows in the order of the list, as select_records_daf() does. A missing key raises KeyError, as in a Daf.

Rows and columns together, as in sdaf[key, 'col'] or sdaf[:, ['a', 'b']], give new rows with only those columns, as select_cols() makes them. The rows part takes any form above, or : for all rows. The columns part is a name, a list of names, or : for all. Read one value with to_value(), as in a Daf.

A value read this way makes a TEMP table, which costs about 1 ms. In a loop over many keys, select_record() is about 30 times faster.

A negative position, or a slice with a negative end, costs a len() first. In a parent with daf_rid, or a child whose rows are found, positions are looked up directly. Otherwise SQLite steps over the rows before them.

Parameters:

Name	Type	Description	Default
`sel`	`Any`	An int, a slice, a key or a list of keys. Or a pair of rows and columns.	required

Returns:

Type	Description
`SqlDaf`	A selection. New rows of the chosen columns, for a pair.

Raises:

Type	Description
`IndexError`	An int position past the end.
`KeyError`	A key that is missing, or a name that is not a column.
`NotImplementedError`	Any other form, such as a list of positions, or a slice with a step.
`ValueError`	An int or a slice, on a parent that has had rows deleted. See SqlDaf.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['ballot_id', 'v'], lol=[['b1', 1], ['b2', 0], ['b3', 1]], keyfield='ballot_id')
>>> marks_sdaf[['b3', 'b1']].to_daf().to_lod()
[{'ballot_id': 'b3', 'v': 1}, {'ballot_id': 'b1', 'v': 1}]
>>> marks_sdaf['b2', 'v'].to_value()
0

By condition¶

select_where ¶

select_where(where, indirect_col=None, cols=None)

Select the rows for which a Python function is true, as Daf.select_where() does.

The function gets each row as a KeyedList, with Python NULL for a missing value, and reads cells by name, as in row['n']. It is registered with SQLite and called for each row when the selection is first read. If it raises, that error is raised, as itself.

Name the columns it reads in cols. It is then given only those, which is faster. A row of every column costs about 1 us. If the test only compares columns to values, use select_by_dict() or select_where_sql(), which call no Python function.

Parameters:

Name	Type	Description	Default
`where`	`Callable[[Any], Any]`	A function of a row, true for the rows to keep.	required
`indirect_col`	`str \| None`	Not supported on a SqlDaf yet.	`None`
`cols`	`T_ls \| None`	The columns the function reads. All if None.	`None`

Returns:

Type	Description
`SqlDaf`	A selection.

Raises:

Type	Description
`KeyError`	A name in `cols` is not a column.
`NotImplementedError`	`indirect_col` is given.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 1], ['b2', 0]], cols=['ballot_id', 'v']), name='marks')
>>> marks_sdaf.select_where(lambda row: row['v'] == 1, cols=['v']).to_list()
['b1', 1]

select_where_sql ¶

select_where_sql(where_sql, params=())

Select the rows for which a SQL condition is true.

The condition is the text after WHERE. Name the columns as they are, in double quotes if they need it, and pass values as params, with ? in the text. Python NULL in params becomes SQL NULL. Test for a missing value with IS NULL. This is the fastest way to select, since SQLite does all the work.

Nothing runs yet. The rows are found when the selection is first read. See SqlDaf.

Parameters:

Name	Type	Description	Default
`where_sql`	`str`	The condition, as SQL text.	required
`params`	`Iterable[Any]`	Values for the `?` marks in it, in order.	`()`

Returns:

Type	Description
`SqlDaf`	A selection.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 1], ['b2', 0]], cols=['ballot_id', 'v']), name='marks')
>>> marks_sdaf.select_where_sql('"v" = ?', [0]).to_list()
['b2', 0]

select_by_dict ¶

select_by_dict(
    selector_da, expectmax=-1, inverse=False, keyfield=""
)

Select the rows that match every field of a dict, or any one of a list of dicts.

It works as Daf.select_by_dict() does, with the SQL written for you. A dict is an AND of equalities, and a list of dicts is an OR of those. With inverse=True, the rows that do not match. An empty dict matches every row, and an empty list none. A dict that names a column that is not there matches no row. Python NULL as a value matches a missing value.

Values are compared with SQL's IS, which treats SQL NULL as a value, so the inverse keeps rows with a missing value. As in Python, 1 equals 1.0, and the text '1' does not equal 1.

Nothing runs yet, unless expectmax is given: then its rows are found at once, to count the matches.

Parameters:

Name	Type	Description	Default
`selector_da`	`T_da \| List[T_da]`	A dict of column and value, or a list of such dicts.	required
`expectmax`	`int`	If not -1, and more rows match, raise LookupError.	`-1`
`inverse`	`bool`	If True, select the rows that do not match.	`False`
`keyfield`	`str`	The keyfield of the selection. This SqlDaf's if empty.	`''`

Returns:

Type	Description
`SqlDaf`	A selection.

Raises:

Type	Description
`LookupError`	More than `expectmax` rows match.
`TypeError`	`selector_da` is not a dict, or a list or tuple of dicts.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 1], ['b2', '']], cols=['ballot_id', 'v']), name='marks')
>>> marks_sdaf.select_by_dict({'v': ''}).to_list()
['b2', '']

split_where ¶

split_where(where, *, indirect_col=None, cols=None)

Split the rows in two, by a Python function that is true or false for each row.

This is deprecated, as Daf.split_where() is. Use group_where() with a function that returns True or False.

It works as Daf.split_where() does, with the rows staying in the database. It is two calls of select_where(): one keeps the rows where the function is true, and the other the rest. Nothing runs yet. Each side is found when it is first read, and the function is called again for each side that is read. So it should give the same answer each time.

Name the columns the function reads in cols, as in select_where(). If the test is that columns equal values, two calls of select_by_dict(), the second with inverse=True, are much faster, since they call no function. On 1,000,000 rows, reading both sides took 1.79 s here, and 0.21 s that way.

Parameters:

Name	Type	Description	Default
`where`	`Callable[[Any], Any]`	A function of a row, true for the rows of the first side.	required
`indirect_col`	`str \| None`	Not supported on a SqlDaf yet.	`None`
`cols`	`T_ls \| None`	The columns the function reads. All if None.	`None`

Returns:

Type	Description
`Tuple[SqlDaf, SqlDaf]`	A tuple of the selection of the rows where the function is true and the selection of the others.

Raises:

Type	Description
`KeyError`	A name in `cols` is not a column.
`NotImplementedError`	`indirect_col` is given.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 10], ['b2', 20], ['b3', 30]], cols=['ballot_id', 'n']), name='marks')
>>> big_sdaf, small_sdaf = marks_sdaf.split_where(lambda row: row['n'] > 10, cols=['n'])
>>> big_sdaf.to_daf().lol, small_sdaf.to_daf().lol
([['b2', 20], ['b3', 30]], [['b1', 10]])

select_first_row_by_dict ¶

select_first_row_by_dict(selector_da, inverse=False)

Get the first row that matches every field of a dict, as Daf.select_first_row_by_dict() does.

The matching rule is that of select_by_dict(). The search stops at the first match.

Parameters:

Name	Type	Description	Default
`selector_da`	`T_da`	The column names and the values they must have.	required
`inverse`	`bool`	If True, find the first row that does not match.	`False`

Returns:

Type	Description
`T_da`	The row as a dict, or an empty dict if none matches.

Columns¶

select_cols ¶

select_cols(cols=None, exclude_cols=None)

Make a new table with only some columns, chosen by name, as Daf.select_cols() does.

The columns come in the order of cols. A name given twice is used once. A name that is not a column raises KeyError. With no cols, all columns are kept, in this table's order. Then exclude_cols leaves out the columns you name. A name in exclude_cols that is not a column is ignored. The rows keep their order.

The result is new rows, as in a Daf, not a selection of this table's rows. It is resolved the first time it is read, into a TEMP table, as a join is. The keyfield and dtypes carry over for the columns kept.

Parameters:

Name	Type	Description	Default
`cols`	`T_ls \| None`	The names of the columns to keep, in the order you want. If empty, all columns.	`None`
`exclude_cols`	`T_ls \| None`	The names of the columns to leave out.	`None`

Returns:

Type	Description
`SqlDaf`	A SqlDaf of the chosen columns, resolved when first read.

Raises:

Type	Description
`KeyError`	A name in `cols` is not a column.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['ballot_id', 'v', 'n'], lol=[['b1', 1, 10], ['b2', 0, 20]], keyfield='ballot_id')
>>> marks_sdaf.select_cols(['n', 'ballot_id']).to_daf().lol
[[10, 'b1'], [20, 'b2']]
>>> marks_sdaf.select_cols(exclude_cols=['v']).columns()
['ballot_id', 'n']

calc_cols ¶

calc_cols(
    include_cols=None,
    exclude_cols=None,
    include_types=None,
    exclude_types=None,
)

Choose column names by rules, as Daf.calc_cols() does.

It reads only the column names and the dtypes, never the rows.

Parameters:

Name	Type	Description	Default
`include_cols`	`Iterable \| None`	Keep only these names.	`None`
`exclude_cols`	`Iterable \| None`	Leave out these names.	`None`
`include_types`	`List[Type] \| None`	Keep only columns whose dtype is one of these.	`None`
`exclude_types`	`List[Type] \| None`	Leave out columns whose dtype is one of these.	`None`

Returns:

Type	Description
`T_ls`	The chosen column names, in column order.

By key¶

contains ¶

__contains__(key)

Say whether a row has this key, as in key in sdaf.

It is one lookup on the keyfield, through its index. A table with no rows contains nothing, as in a Daf.

Parameters:

Name	Type	Description	Default
`key`	`Any`	The key to look for.	required

Returns:

Type	Description
`bool`	True if a row has this key.

Raises:

Type	Description
`ValueError`	There is no keyfield, and there are rows.

keys ¶

keys()

Get the keys, as a list of the keyfield's values in row order, as Daf.keys() does.

Every key is read into memory. To pass many keys to select_records_daf() or delete_records() without reading them, give the SqlDaf itself: its keys are then used in SQL. Without a keyfield, as in a Daf, the answer is an empty list.

Returns:

Type	Description
`T_la`	The keys, or an empty list if there is no keyfield.

get_existing_keys ¶

get_existing_keys(keylist)

Keep the keys of a list that have a row, in the order of the list, as Daf.get_existing_keys() does.

The keys are looked up in SQL, all at once. Without a keyfield, the result is empty, as in a Daf.

Parameters:

Name	Type	Description	Default
`keylist`	`T_la`	The keys to check.	required

Returns:

Type	Description
`T_la`	The keys from the list that have a row.

select_krows ¶

select_krows(krows, inverse=False, silent_error=False)

Select rows by key, as Daf.select_krows() does.

A key, or a list or other iterable of keys, selects those rows, in the order of the keys. This is select_records_daf().
A tuple of two keys selects every row from the first key to the last, both included, in row order. Either may be None, for the start or the end. A tuple of one key runs to the end.

A key range is found by the place of its keys among the rows, not by row positions. So it works on a table that has had rows deleted.

A slice is not supported. In a Daf, a slice here means row positions. Use sdaf[n:m].

Parameters:

Name	Type	Description	Default
`krows`	`Any`	A key, a list of keys, or a tuple of a first and last key.	required
`inverse`	`bool`	If True, select the rows that are not selected.	`False`
`silent_error`	`bool`	If True, keys that are not found are ignored. A key of a range must be found.	`False`

Returns:

Type	Description
`SqlDaf`	A selection.

Raises:

Type	Description
`KeyError`	A key is not found.
`NotImplementedError`	`krows` is a slice.
`TypeError`	`krows` is None, or a tuple that is empty or longer than two.
`ValueError`	There is no keyfield.

select_records_daf ¶

select_records_daf(
    keys_ls, inverse=False, silent_error=False
)

Select the rows with these keys, as Daf.select_records_daf() does.

The keys may be a list or any iterable, an in-memory Daf, or another SqlDaf of the same database file. A SqlDaf's keys are used in SQL, as a join, so no key comes into Python. The other forms are written to a TEMP table first. The selected rows come in the order of the keys, as in a Daf. With inverse=True, the rows whose key is not among them, in this table's order.

A Python NULL key gives undefined results: see SqlDaf.

As in a Daf, a key that is not found raises KeyError, unless silent_error is True. That check runs at once, as one query. The selection itself runs when first read.

Parameters:

Name	Type	Description	Default
`keys_ls`	`Any`	The keys: a list, an iterable, a Daf or a SqlDaf.	required
`inverse`	`bool`	If True, select the rows whose key is not among them.	`False`
`silent_error`	`bool`	If True, keys that are not found are ignored.	`False`

Returns:

Type	Description
`SqlDaf`	A selection.

Raises:

Type	Description
`KeyError`	A key is not found, and `silent_error` is False.
`ValueError`	This SqlDaf, or a SqlDaf given as the keys, has no keyfield.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_daf = Daf(lol=[['b1', 1], ['b2', 0], ['b3', 1]], cols=['ballot_id', 'v'], keyfield='ballot_id')
>>> marks_sdaf = SqlDaf.from_daf(marks_daf, name='marks')
>>> marks_sdaf.select_records_daf(['b3', 'b1']).to_daf().to_lod()
[{'ballot_id': 'b3', 'v': 1}, {'ballot_id': 'b1', 'v': 1}]

Selecting and indexing¶

Indexing with brackets¶

__getitem__ ¶

By condition¶

select_where ¶

select_where_sql ¶

select_by_dict ¶

split_where ¶

select_first_row_by_dict ¶

Columns¶

select_cols ¶

calc_cols ¶

By key¶

__contains__ ¶

keys ¶

get_existing_keys ¶

select_krows ¶

select_records_daf ¶

getitem ¶

contains ¶