Reading¶

Size, looping, single rows and columns, and moving rows into memory.

Size¶

len ¶

__len__()

Count the rows.

For a parent, this runs a COUNT. For a child, its rows are found first, and then its references are counted, which is one lookup. A row deleted from the parent is still counted, so do not delete rows from a parent that has children.

Returns:

Type	Description
`int`	The number of rows.

len ¶

len()

Count the rows, as len(sdaf) does. It is here so that code written for a Daf, which calls daf.len(), runs on a SqlDaf.

bool ¶

__bool__()

Say whether there is at least one row.

This stops at the first row, so it is fast on a large table. A child has its rows found first, and then its references are checked, as len() counts them.

Returns:

Type	Description
`bool`	True if there is a row.

num_rows ¶

num_rows()

Count the rows, as len() does.

num_cols ¶

num_cols()

Count the columns.

The hidden daf_rid column is not counted.

Returns:

Type	Description
`int`	The number of columns.

shape ¶

shape()

Give the number of rows and the number of columns, as Daf.shape() does.

A table with no rows gives (0, 0), as in a Daf. The rows are counted with len().

Returns:

Type	Description
`Tuple[int, int]`	A tuple of the number of rows and the number of columns.

Looping¶

iter ¶

__iter__()

Loop over the rows as dicts, as in for row in sdaf.

Rows are read in order, in batches, so a table of any size fits in memory. Each dict is a copy: changing it does not change the table. A Daf can give KeyedList rows instead, by its itermode. A SqlDaf always gives dicts. Use iter_klist() for KeyedList rows.

Do not change the table inside the loop. Whether the loop sees the change is not defined.

Returns:

Type	Description
`Iterator[T_da]`	The rows, as dicts.

iter_dict ¶

iter_dict()

Loop over the rows as dicts, as Daf.iter_dict() does.

Rows are read in order, in batches. SQL NULL comes back as Python NULL. Each dict is a copy, so changing it does not change the table.

Returns:

Type	Description
`Iterator[T_da]`	Each row as a dict of column name to value.

iter_klist ¶

iter_klist()

Loop over the rows as KeyedList objects.

A row reads by column name, as in a Daf. But each row is a copy. In a Daf, assigning to a KeyedList row changes the Daf. Here it changes only the copy.

Returns:

Type	Description
`Iterator[KeyedList]`	Each row as a KeyedList.

iter_list ¶

iter_list()

Loop over the rows as lists, with the values in column order.

Each list is a copy. In a Daf, these are the Daf's own row lists, and assigning to one changes the Daf. Here it changes only the copy.

Returns:

Type	Description
`Iterator[T_la]`	Each row as a list.

One row¶

select_record ¶

select_record(key, silent_error=True)

Read one row by its key, as a dict, as Daf.select_record() does, in one query.

The keyfield's unique index is made first if it is not there yet, so the lookup does not read every row. It took about 18 us on 1,000,000 rows, or 9 us inside transaction().

On a selection, only the selection's rows are searched. A key that is in the parent but not in the selection is missing. The call makes no selection. On a selection whose rows are not yet found, it does not fix them: they are still found when it is first read.

Parameters:

Name	Type	Description	Default
`key`	`Any`	The key of the row.	required
`silent_error`	`bool`	If True, a missing key gives an empty dict. If False, it raises KeyError.	`True`

Returns:

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

Raises:

Type	Description
`KeyError`	The key is missing, and `silent_error` is False.
`ValueError`	There is no keyfield.

iloc ¶

iloc(irow=0, include_cols=None, rtype='dict')

Read one row by position, as Daf.iloc() does, in one query.

On a selection, the position is within the selection's own rows, in its order. The call makes no selection. On a selection whose rows are not yet found, it does not fix them: they are still found when the selection is first read. In a parent with daf_rid, or a child whose rows are found, the position is looked up directly. Otherwise SQLite steps over the rows before it, which takes longer the further in it is. A negative position counts from the end, which costs a len() first.

Parameters:

Name	Type	Description	Default
`irow`	`int`	The position of the row. Negative counts from the end.	`0`
`include_cols`	`T_ls \| None`	Only these columns, in this order. All if None.	`None`
`rtype`	`str`	'dict' for a dict, or 'list' for a list of the values.	`'dict'`

Returns:

Type	Description
`T_da \| T_la`	The row. Python NULL for SQL NULL.

Raises:

Type	Description
`IndexError`	There is no row at that position.
`KeyError`	A name in `include_cols` is not a column.
`NotImplementedError`	`rtype` is not 'dict' or 'list'.
`ValueError`	This is a parent that has had rows deleted. See SqlDaf.

iloc_exists ¶

iloc_exists(irow)

Say whether there is a row at this position, from 0.

Parameters:

Name	Type	Description	Default
`irow`	`int`	The position, from 0.	required

Returns:

Type	Description
`bool`	True if a row is there.

Raises:

Type	Description
`ValueError`	This is a parent that has had rows deleted. See SqlDaf.

Columns¶

col ¶

col(
    colname,
    *,
    unique=False,
    omit_nulls=False,
    silent_error=False,
    astype=None,
    indirect_col=None,
    default=NULL,
)

Get one column as a list, by name, as Daf.col() does.

Only that column is read into memory, or only indirect_col for a value inside a dict column. The options act as in a Daf. With omit_nulls, both SQL NULL and the text '' are left out, since both read as Python NULL.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column, or the key inside `indirect_col`.	required
`unique`	`bool`	If True, leave out repeated values and keep the order.	`False`
`omit_nulls`	`bool`	If True, leave out Python NULL.	`False`
`silent_error`	`bool`	If True, a name that is not a column gives an empty list.	`False`
`astype`	`Callable \| str \| type \| None`	A type or function to convert each value with.	`None`
`indirect_col`	`str \| None`	A column that holds a dict, to read `colname` from.	`None`
`default`	`Any`	The value for a dict that lacks `colname`.	`NULL`

Returns:

Type	Description
`T_la`	The values of the column, in row order.

Raises:

Type	Description
`ColumnNotFoundError`	The name is not a column, and `silent_error` is False.

col_to_la ¶

col_to_la(
    colname,
    *,
    unique=False,
    omit_nulls=False,
    silent_error=False,
    astype=None,
    indirect_col=None,
    default=NULL,
)

Get one column as a list, by name. This does the same as col(). See that method.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column, or the key inside `indirect_col`.	required
`unique`	`bool`	If True, leave out repeated values and keep the order.	`False`
`omit_nulls`	`bool`	If True, leave out Python NULL.	`False`
`silent_error`	`bool`	If True, a name that is not a column gives an empty list.	`False`
`astype`	`Callable \| str \| type \| None`	A type or function to convert each value with.	`None`
`indirect_col`	`str \| None`	A column that holds a dict, to read `colname` from.	`None`
`default`	`Any`	The value for a dict that lacks `colname`.	`NULL`

Returns:

Type	Description
`T_la`	The values of the column, in row order.

icol ¶

icol(icol)

Get one column as a list, by its place among the columns, as Daf.icol() does.

The place is looked up in the column names, so it does not depend on the rows. A negative place counts from the end. A place out of range raises IndexError, unless the table has no rows, as in a Daf.

Parameters:

Name	Type	Description	Default
`icol`	`int`	The place of the column, from 0.	required

Returns:

Type	Description
`T_la`	The values of the column, in row order.

Raises:

Type	Description
`IndexError`	The place is out of range, and the table has rows.

icol_to_la ¶

icol_to_la(icol, unique=False, omit_nulls=False)

Get one column as a list, by its place among the columns, with options.

See icol() for the place, and col() for the options.

Parameters:

Name	Type	Description	Default
`icol`	`int`	The place of the column, from 0.	required
`unique`	`bool`	If True, leave out repeated values and keep the order.	`False`
`omit_nulls`	`bool`	If True, leave out Python NULL.	`False`

Returns:

Type	Description
`T_la`	The values of the column, in row order.

Raises:

Type	Description
`IndexError`	The place is out of range, and the table has rows.

cols_to_dol ¶

cols_to_dol(colname1, colname2)

Map each value of one column to the values seen with it in another, as Daf.cols_to_dol() does.

Only the two columns are read into memory. If a name is not a column, or there are no rows, the result is empty.

Parameters:

Name	Type	Description	Default
`colname1`	`str`	The column of keys.	required
`colname2`	`str`	The column of values.	required

Returns:

Type	Description
`Dict[Any, T_la]`	A dict from each value of the first column to the values of the second, in the order first seen.

Into memory¶

to_daf ¶

to_daf()

Pull every row into an ordinary Daf in memory.

Use it once the rows you need are few enough to hold. The rows come in order. A SQL NULL comes back as Python NULL. The new Daf has this SqlDaf's name, keyfield and dtypes. Nothing ties it to the table afterwards: a change to one does not show in the other. A child has its rows found first.

Returns:

Type	Description
`Daf`	A Daf of every row.

to_dict ¶

to_dict()

Read a table of one row as a dict, as Daf.to_dict() does.

A child has its rows found first. For a row by key or by position, select_record() and iloc() are cheaper: they make no selection.

Returns:

Type	Description
`T_da`	The row as a dict. An empty dict if there are no rows.

Raises:

Type	Description
`ValueError`	There is more than one row.

to_list ¶

to_list()

Read a table of one row, or of one column, as a list, as Daf.to_list() does.

One column gives every value of the column, in order, so select first. A child has its rows found first.

Returns:

Type	Description
`T_la`	The values of the row, or of the column.

Raises:

Type	Description
`ValueError`	There is more than one row and more than one column.

to_value ¶

to_value()

Read a table of one row and one column as its value, as Daf.to_value() does.

A child has its rows found first.

Returns:

Type	Description
`Any`	The value. Python NULL for SQL NULL.

Raises:

Type	Description
`ValueError`	The table is not one row and one column.

to_lod ¶

to_lod()

Make a list of dicts, one for each row, as Daf.to_lod() does.

Every row is read into memory. Use iter_dict() to go through the rows one at a time.

Returns:

Type	Description
`List[T_da]`	The rows as dicts, in order.

to_dod ¶

to_dod(remove_keyfield=True)

Make a dict of dicts, named by the keyfield, as Daf.to_dod() does.

Every row is read into memory, through to_daf().

Parameters:

Name	Type	Description	Default
`remove_keyfield`	`bool`	If True, leave the keyfield out of the inner dicts.	`True`

Returns:

Type	Description
`Dict[Any, T_da]`	Each row as a dict, under its key.

Raises:

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

to_json ¶

to_json(concise=True)

Make JSON text that holds the whole table, as Daf.to_json() does.

Every row is read into memory, through to_daf(). The text holds the rows, the column names, the dtypes, the keyfield and the name. Daf.from_json() reads it back as a Daf.

Parameters:

Name	Type	Description	Default
`concise`	`bool`	If True, leave out the parts that are empty.	`True`

Returns:

Type	Description
`str`	The JSON text.

Display and files¶

to_md ¶

to_md(
    max_rows=0,
    max_cols=0,
    just="",
    shorten_text=True,
    max_text_len=80,
    smart_fmt=False,
    include_summary=False,
    disp_cols=None,
    header=None,
)

Make a Markdown table, as Daf.to_md() does.

Set max_rows for a large table. Then only the first and last rows are read. Without it, every row is read into memory.

Parameters:

Name	Type	Description	Default
`max_rows`	`int`	The most rows to show, the first and the last. 0 for all.	`0`
`max_cols`	`int`	The most columns to show, the first and the last. 0 for all.	`0`
`just`	`str`	The justification of each column: '<', '^' or '>'.	`''`
`shorten_text`	`bool`	If True, shorten long text, keeping the ends.	`True`
`max_text_len`	`int`	The length past which text is shortened.	`80`
`smart_fmt`	`bool`	If True, limit the digits after the point in numbers.	`False`
`include_summary`	`bool`	If True, add a line with the shape, keyfield and name.	`False`
`disp_cols`	`Any`	Names to show in place of the column names.	`None`
`header`	`Any`	A header to use in place of the column names.	`None`

Returns:

Type	Description
`str`	The Markdown text.

to_md_cols ¶

to_md_cols(
    max_rows=0,
    max_cols=0,
    just="",
    shorten_text=True,
    max_text_len=80,
    smart_fmt=False,
    include_summary=False,
    disp_cols=None,
)

Make a Markdown table with each row as a column, as Daf.to_md_cols() does.

Set max_rows for a large table. Then only the first and last rows are read.

Parameters:

Name	Type	Description	Default
`max_rows`	`int`	The most rows to show, the first and the last. 0 for all.	`0`
`max_cols`	`int`	The most columns to show, the first and the last. 0 for all.	`0`
`just`	`str`	The justification of each column: '<', '^' or '>'.	`''`
`shorten_text`	`bool`	If True, shorten long text, keeping the ends.	`True`
`max_text_len`	`int`	The length past which text is shortened.	`80`
`smart_fmt`	`bool`	If True, limit the digits after the point in numbers.	`False`
`include_summary`	`bool`	Not used, as in a Daf.	`False`
`disp_cols`	`T_ls \| None`	Names to show in place of the column names.	`None`

Returns:

Type	Description
`str`	The Markdown text.

daf_to_lol_summary ¶

daf_to_lol_summary(
    max_rows=10, max_cols=10, disp_cols=None
)

Give the header and the rows to display, as Daf.daf_to_lol_summary() does.

With max_rows, only the first and last rows are read, around a row of '...'. Without it, every row is read.

Parameters:

Name	Type	Description	Default
`max_rows`	`int`	The most rows to show. 0 for all.	`10`
`max_cols`	`int`	The most columns to show. 0 for all.	`10`
`disp_cols`	`Any`	Names to show in place of the column names.	`None`

Returns:

Type	Description
`T_lola`	The header, then the rows.

to_csv_buff ¶

to_csv_buff(line_terminator=None, include_header=True)

Make CSV text from the rows, as Daf.to_csv_buff() does.

The whole text is built in memory, so use it for a selection of modest size. To write a large table, use to_csv_file(), which writes as it reads.

Parameters:

Name	Type	Description	Default
`line_terminator`	`str \| None`	The line ending. If None, `\r\n` is used.	`None`
`include_header`	`bool`	If True, write the column names first.	`True`

Returns:

Type	Description
`T_buff`	The CSV text.

to_csv_file ¶

to_csv_file(
    file_path="", line_terminator=None, include_header=True
)

Write the rows to a CSV file, as Daf.to_csv_file() does.

A local file is written as the rows are read, 10,000 at a time, so a table larger than memory can be written. The text is the same as a Daf of the same rows would write: each cell with str(), Python NULL and SQL NULL as nothing, and \r\n line endings by default. A path that starts with s3 is not streamed: the text is built in memory and uploaded, as a Daf does.

Parameters:

Name	Type	Description	Default
`file_path`	`str \| Path`	Where to write the file.	`''`
`line_terminator`	`str \| None`	The line ending. If None, `\r\n` is used.	`None`
`include_header`	`bool`	If True, write the column names first.	`True`

Returns:

Type	Description
`str`	The path that was written.

Examples:

>>> import os, tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 1], ['b2', '']], cols=['ballot_id', 'v']), name='marks')
>>> path = marks_sdaf.to_csv_file(os.path.dirname(SqlDaf.db_path) + '/marks.csv')
>>> open(path, newline='').read()
'ballot_id,v\r\nb1,1\r\nb2,\r\n'

Reading¶

Size¶

__len__ ¶

len ¶

__bool__ ¶

num_rows ¶

num_cols ¶

shape ¶

Looping¶

__iter__ ¶

iter_dict ¶

iter_klist ¶

iter_list ¶

One row¶

select_record ¶

iloc ¶

iloc_exists ¶

Columns¶

col ¶

col_to_la ¶

icol ¶

icol_to_la ¶

cols_to_dol ¶

Into memory¶

to_daf ¶

to_dict ¶

to_list ¶

to_value ¶

to_lod ¶

to_dod ¶

to_json ¶

Display and files¶

to_md ¶

to_md_cols ¶

daf_to_lol_summary ¶

to_csv_buff ¶

to_csv_file ¶

len ¶

bool ¶

iter ¶