SqlDaf overview

SqlDaf

Bases: Daf

A Daf whose rows live in a SQLite table.

Use it for a table too large to hold in memory. The rows stay on disk in a SQLite database file, and SQL does the work. When the rows you need are few, pull them into an ordinary Daf with to_daf(), and work on them there.

A SqlDaf is a Daf, so isinstance(sdaf, Daf) is True. But it has only the methods written for SQL. Every other Daf method raises NotImplementedError, which names it. So nothing quietly loads a large table into memory. For anything not covered yet, sql() runs a SQL statement of your own.

The database is the file named by the class setting SqlDaf.db_path. Set it once, for example once for each job, and every SqlDaf opens that file. A db_path given to the constructor overrides it for that table. With neither, the constructor raises ValueError. The class setting engine is 'sqlite', the only engine so far.

Make a table with the constructor, as a Daf is made: give its name, its columns and its rows. Open a table that is already in the file by its name alone. Its keyfield and dtypes come back as they were, from the metadata that SqlDaf keeps in the table daf_tables of the same file. A table made by other code has no such record, so give them to the constructor. SqlDaf.from_csv() and SqlDaf.from_daf() load a file or a Daf into a new table.

A SqlDaf is a parent or a child.

• A parent holds its rows, in a table of its own: one made by the constructor, a loader or copy(), or one already in the file.
• A child is a selection. It holds references to rows of its parent, in order, as a selection of a Daf holds the same row lists. select_where(), select_by_dict(), select_where_sql() and indexing, as in sdaf[2:5] or sdaf['b2'], each make one. A child of a child is a child of the same parent. is_child tells them apart, and parent_sdaf is the parent of a child.

Making a child does not read its rows. Some methods run a query at once for their arguments: select_krows() checks the keys, select_by_dict() with expectmax finds its rows to count them, and groupby() finds the groups. The rows of a child are fixed the first time it is read whole: by to_daf(), len(), bool(), to_dict(), to_list() or to_value(). After that, it always means the same rows, as a selection of a Daf does. select_record() and iloc() read one row and fix nothing, unless iloc() is given a negative position, which needs len(). A change to the values in those rows shows in it, since it refers to them. copy(inplace=True) gives it rows of its own.

Every SqlDaf on one file shares one connection, which it holds in its attributes. So any two of them can be joined, and a child of one can be used with another. They also share its transactions: see transaction(). SqlDaf.close() closes the file, for every SqlDaf on it. Otherwise it stays open until the program ends. Each statement is committed when it finishes. So committed changes to permanent tables remain, whether the file is closed or the program ends. delete() deletes a table from the file.

A table made by copy(), or by reading a join, is a TEMP table. It belongs to the connection, and is removed when the connection closes. save() makes it a permanent table in the file, under a name, to be opened again later.

Do not delete rows from a parent that has children. len() of a child counts its references, so it would still count a deleted row, though to_daf() leaves it out. Worse, SQLite gives the daf_rid of a deleted last row to the next row added. A child made before the delete then shows that new row, which it never selected. Leave rows out with a selection instead, as in a Daf.

A parent that has had rows deleted cannot be read by position. Positions in a parent are looked up by daf_rid, and a delete leaves a gap there. So iloc(), iloc_exists() and sdaf[n] or sdaf[n:m] on it raise ValueError. Read positions from copy(), which numbers the rows again, or select the rows you want. A selection numbers its own rows, so positions in it always work.

To read one row, use select_record() by key or iloc() by position. Each is one query, returns a dict, and makes no selection.

The methods, by what they do:

• Size: len(), bool(), num_rows(), num_cols(), shape() and columns().
• Looping, in batches: for row in sdaf, iter_dict(), iter_klist() and iter_list().
• One row: select_record() by key, and iloc() by position.
• Columns: col(), col_to_la(), icol(), icol_to_la() and cols_to_dol().
• Selecting: select_where(), select_by_dict(), select_where_sql(), split_where(), select_cols() and indexing.
• By key: key in sdaf, keys(), select_records_daf(), select_krows(), get_existing_keys() and delete_records().
• The first match: select_first_row_by_dict().
• Groups and counts: groupby(), groupby_cols(), group_where(), groupsum_daf(), sum(), daf_valuecount(), value_counts_daf() and the valuecounts_for_* methods.
• Functions over rows: apply() and reduce().
• Adding rows: append(), record_append(), extend(), assign_record() and concat().
• Changing values: sdaf[key, col] = v, sdaf[key] = row, sdaf[:, col] = values, update_record_krow(), update_by_keylist(), assign_col(), apply_to_col() and apply_in_place().
• Columns and layout: insert_col(), drop_cols(), rename_cols(), set_cols(), set_dtypes(), attach_schema() and clone_empty().
• Repeated keys: remove_dups(). Sorting: sort_by_colname() and sort_by_colnames().
• Joining: join(). Choosing columns: calc_cols().
• Into memory: to_daf(), to_dict(), to_list(), to_value(), to_lod(), to_dod() and to_json().
• Display and files: to_md(), to_md_cols(), to_csv_buff() and to_csv_file().
• Tables: from_csv(), from_daf(), copy(), save(), delete() and table_exists().
• The database: close(), transaction() and sql(), which runs any SQL statement.
• Keys and types: set_keyfield(), ensure_key_index() and apply_dtypes().

Work with large tables in whole statements. One query costs about 12 to 24 us, against under 1 us for a lookup in memory, because each one passes through sqlite3. Many rows in one statement are far cheaper per row.

There are two kinds of NULL. Python NULL is daffodil's missing value: the empty string '', which daffodil names NULL, as in from daffodil.daf import NULL. SQL NULL is the database's. A Python None written to a SqlDaf is stored as SQL NULL too, so it reads back as Python NULL. SqlDaf writes Python NULL as SQL NULL, and reads SQL NULL back as Python NULL. A table written by other code may hold the text '' instead, which also reads as Python NULL. So the methods that group, count or select by value treat SQL NULL and '' as the same value, as a Daf does. In Python, test a value with val is NULL. This works because CPython keeps one shared empty string, and it is faster than val == ''. In your own SQL, test for both, as (col IS NULL OR col = '').

Avoid a Python NULL key, the empty string in the keyfield. If you allow one, the results of SqlDaf are undefined. The key methods compare keys with = in SQL, and SQL NULL equals nothing, so such a row is not found by key, select_records_daf(), delete_records() or join(). The unique index also lets several SQL NULL keys in. A Daf treats a Python NULL key as a key like any other, so the two differ. Give every row a real key. Making the key index raises ValueError on a Python NULL key, and so does concat() of one.

Until apply_dtypes() converts them, values loaded from a CSV file are text, in SQL too. Text is never less than a number in SQLite, so WHERE n < 5 finds nothing on a text column, and MAX() gives the last in text order.

The name is the name of the table in the database. A child gets a generated name, such as daf_tmp_marks_v3, which names its reference table once its rows are found.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['ballot_id', 'v'], lol=[['b1', 1], ['b2', '']], keyfield='ballot_id')
>>> marks_sdaf.sql('SELECT ballot_id FROM marks WHERE v IS NULL').to_list()
['b2']
>>> SqlDaf('marks').keyfield
'ballot_id'

Constructor

__init__

__init__(
    name,
    cols=None,
    lol=None,
    keyfield="",
    dtypes=None,
    db_path="",
)

Make a new table, or open one that is in the database file.

With cols, a new table is made in the file, with the rows of lol, read one at a time, so an iterator of any length works. The table, its rows and its metadata are made in one transaction: if any row fails, nothing is made. A Python NULL cell is stored as SQL NULL. The table gets a hidden first column, daf_rid, an INTEGER PRIMARY KEY, which numbers the rows in order. It is not one of the columns(). The columns are declared with no SQL type, so an int stays an int, and text stays text. A cell that holds a list or a dict cannot be stored yet.

Without cols, the table of that name is opened. Its keyfield and dtypes are those recorded when it was made, unless they are given here. Nothing is read yet.

The keyfield is only recorded. Its unique index is made when a key is first used, as a Daf builds its key index.

Parameters:

Name	Type	Description	Default
name	str	The name of the table.	required
cols	T_ls | None	The column names of a new table, in order. None to open a table that is there.	None
lol	Iterable[Iterable[Any]] | None	The rows of a new table, each the values in the order of cols.	None
keyfield	str	The column that holds the key of each row. For a table that is opened, the recorded one if empty.	''
dtypes	T_dtype_dict | None	The types of the columns, kept for apply_dtypes(). Nothing is converted.	None
db_path	str	The database file. The class setting SqlDaf.db_path if empty.	''

Raises:

Type	Description
ValueError	There is no database path, a new table's name is taken, a column is named daf_rid, or lol has no cols.
KeyError	There is no table of that name to open, or the keyfield is not a column.
FileNotFoundError	A table is to be opened, and the database file is not there.
NotImplementedError	The class setting engine is not 'sqlite'.

Loading a table

from_csv classmethod

from_csv(
    path, name="", keyfield="", delimiter=",", db_path=""
)

Load a CSV file into a new table, without building it in memory.

This only reads, as Daf.from_csv() does without dtypes. Every value is stored as the text of the file. Call apply_dtypes() afterwards to convert the columns you need.

The first line gives the column names. The rest is read one row at a time, so the file is never all in memory. An empty cell is stored as SQL NULL, and comes back as NULL. A row shorter than the header is padded with Python NULL, and a longer one raises ValueError. As with the constructor, if any row fails, no table is made.

Parameters:

Name	Type	Description	Default
path	str	The CSV file.	required
name	str	The name of the new table. The file name without its extension if empty.	''
keyfield	str	The column that holds the key of each row.	''
delimiter	str	The field separator.	','
db_path	str	The database file. The class setting SqlDaf.db_path if empty.	''

Returns:

Type	Description
SqlDaf	A SqlDaf for the new table.

Raises:

Type	Description
ValueError	A table of that name exists, a column is named daf_rid, or a row is longer than the header.
KeyError	The keyfield is not one of the columns.

from_daf classmethod

from_daf(data_daf, name, keyfield='', db_path='')

Write an in-memory Daf into a new table.

Each value is stored as it is, so an int stays an int. A NULL cell is stored as SQL NULL. The new SqlDaf keeps the Daf's dtypes, for apply_dtypes(). The keyfield is the Daf's, unless one is given. As with the constructor, if any row fails, no table is made.

Parameters:

Name	Type	Description	Default
data_daf	Daf	The Daf to write. It is not changed.	required
name	str	The name of the new table.	required
keyfield	str	The column that holds the key of each row. The Daf's keyfield if empty.	''
db_path	str	The database file. The class setting SqlDaf.db_path if empty.	''

Returns:

Type	Description
SqlDaf	A SqlDaf for the new table.

Raises:

Type	Description
ValueError	A table of that name exists, or a column is named daf_rid.
KeyError	The keyfield is not one of the columns.

table_exists classmethod

table_exists(name, db_path='')

Say whether the database file has a table of this name.

Use it to decide whether to open a table or to build it, as from its CSV files. It returns False if the database file does not exist, and does not create the file.

Parameters:

Name	Type	Description	Default
name	str	The name of the table.	required
db_path	str	The database file. The class setting SqlDaf.db_path if empty.	''

Returns:

Type	Description
bool	True if the table is there.

Raises:

Type	Description
ValueError	There is no database path.

Tables

name property

name

The name of the table in the database. copy(name=) and save(name) give a new one.

is_child property

is_child

True for a child, a selection that refers to rows of its parent. False for a parent, which holds its own rows.

copy

copy(
    level=None,
    name="",
    deep=False,
    for_sorting=False,
    inplace=False,
)

Copy the rows into a TEMP table of their own, as Daf.copy() copies a Daf.

The copy is a parent, with daf_rid, the rows in order, and this SqlDaf's keyfield and dtypes. Nothing ties it to the rows it came from: a change to one does not show in the other. A SQL cell holds a plain value, so every level gives what deep gives in a Daf. The level is only checked, as Daf.copy() checks it.

With inplace=True, this SqlDaf becomes the copy, and is returned. Use it on a child, to make it a table of its own, independent of its parent. As in a Daf, a level that would not copy the rows, such as sortable, raises ValueError then.

The copy is a TEMP table, so it is removed when the connection closes. save() keeps it in the file.

Parameters:

Name	Type	Description	Default
level	str | T_copybits | None	As in Daf.copy(). With inplace, it must copy the rows: editable or deep.	None
name	str	The name of the copy. Generated if empty, as in daf_tmp_marks_v4.	''
deep	bool	Deprecated, as in Daf.copy().	False
for_sorting	bool	Deprecated, as in Daf.copy().	False
inplace	bool	If True, this SqlDaf becomes the copy, and is returned.	False

Returns:

Type	Description
SqlDaf	The copy, a new SqlDaf, or this one if inplace.

Raises:

Type	Description
ValueError	A table of that name exists, level is not a level, or inplace with a level that does not copy the rows.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['ballot_id', 'v'], lol=[['b1', 1], ['b2', 0]])
>>> sel_sdaf = marks_sdaf.select_where_sql('v = 1')
>>> sel_sdaf.copy(inplace=True).is_child
False
>>> _ = marks_sdaf.sql("UPDATE marks SET v = 9")
>>> sel_sdaf.to_daf().lol
[['b1', 1]]

save

save(name='')

Keep this table in the database file, as a permanent table, to be opened again later.

The name is the one this SqlDaf has, unless one is given: the name given to the constructor, or to copy(name=). A table with only a generated name, such as daf_tmp_marks_v4, needs one. The keyfield and dtypes are recorded with it, so SqlDaf(name) opens it as it was.

• A TEMP table, made by copy(), a sort or a join, is moved into the file. The TEMP table is removed.
• A permanent table saved under its own name is left as it is, and its keyfield and dtypes are recorded again.
• A permanent table saved under a new name is copied to it, and this SqlDaf then refers to the copy. The first table stays.

A child has no rows of its own. Make it a table first, with copy(inplace=True).

A name already in use raises ValueError. To replace a table with a sorted or changed copy, delete the old table first, as in SqlDaf('items').delete(), then save the copy under its name.

Parameters:

Name	Type	Description	Default
name	str	The name to keep it under. This SqlDaf's name if empty.	''

Returns:

Type	Description
SqlDaf	This SqlDaf, as a permanent table.

Raises:

Type	Description
ValueError	This is a child, the name is a generated one, or a table of that name exists.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['ballot_id', 'v'], lol=[['b1', 1], ['b2', 0]], keyfield='ballot_id')
>>> agreed_sdaf = marks_sdaf.select_where_sql('v = 1').copy(name='agreed').save()
>>> SqlDaf('agreed').keyfield, len(SqlDaf('agreed'))
('ballot_id', 1)

delete

delete()

Delete this table from the database file, with its metadata.

The table is dropped. Inside a transaction() block that then raises, the drop is undone with the rest of the block. Otherwise it is final. A child has no table of its own to delete. Delete its parent, or let the child go. After this, the SqlDaf, and any child of it, can no longer be read.

Raises:

Type	Description
ValueError	This is a child.

The database

close classmethod

close(db_path='')

Close a database file: the connection that every SqlDaf on it shares.

It closes a file, not one table, so call it on the class, as SqlDaf.close(). Called on an instance, it still closes the file of db_path, or of the class setting, not the file that instance uses.

The permanent tables in the file stay, with every committed change. SqlDaf(name) opens them again, on a new connection. TEMP tables, such as those made by copy() or a sort, are removed. Every SqlDaf that was on the file can no longer be used. sqlite3 raises ProgrammingError. A file that is not open is left as it is.

Without close(), the file stays open until the program ends. Each statement is committed when it finishes, or at the end of a transaction() block. So a committed change is kept either way.

Parameters:

Name	Type	Description	Default
db_path	str	The database file. The class setting SqlDaf.db_path if empty.	''

Raises:

Type	Description
ValueError	There is no database path.

transaction

transaction()

Run the statements inside the with block as one transaction, on this database file.

A transaction belongs to the file, not to this table. Every SqlDaf on the file shares one connection, so the work of any of them inside the block is committed or undone with the rest. Wrap the writes of a stage that must succeed or fail together in one block. Methods that change several things run their own transaction(). Inside a block, it becomes part of the block. Do not run BEGIN, COMMIT or ROLLBACK through sql(): they would end a block that another SqlDaf opened.

Everything in the block is committed together when it ends, or undone together if it raises. It also makes a run of small statements faster, because the file is locked once for the block instead of once per statement. On 1,000,000 rows, a lookup by key took 12.4 us inside a transaction, against 24.1 us outside.

While the block is open, no other connection can write to the database file. So keep it to the work that needs it.

A transaction() inside another one becomes a savepoint: if the inner block raises, only its own work is undone.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['ballot_id', 'v'], lol=[['b1', 1]])
>>> with marks_sdaf.transaction():
...     _ = marks_sdaf.sql("UPDATE marks SET v = 2")
>>> marks_sdaf.to_daf().lol
[['b1', 2]]

sql

sql(stmt, params=())

Run a SQL statement of your own, and return its result as a Daf.

This is the fallback for anything SqlDaf does not do yet. The statement runs on this SqlDaf's connection, so it can use every table in the file, and the TEMP tables of the connection. Refer to this table by sdaf.name.

Pass values as params, with ? in the statement, instead of putting them in the text.

A SELECT gives a Daf of its rows, with its column names. SQL NULL becomes Python NULL. A statement that returns no rows, such as UPDATE or CREATE, gives an empty Daf. Outside transaction(), it is committed when it finishes.

The whole result is pulled into memory, so select only what you need. Do not run BEGIN, COMMIT or ROLLBACK here. Use transaction(), which every SqlDaf on the file shares.

A change to a table's columns made here, such as ALTER TABLE, is not seen by a SqlDaf already open on that table. Open it again by name to see the new columns. Nor does it update the keyfield and dtypes that SqlDaf records. For column changes, prefer insert_col(), drop_cols() and rename_cols(), which keep both up to date.

Parameters:

Name	Type	Description	Default
stmt	str	One SQL statement.	required
params	Iterable[Any]	Values for the ? marks in the statement, in order.	()

Returns:

Type	Description
Daf	A Daf of the rows the statement returns.

Keys and types

set_keyfield

set_keyfield(keyfield='')

Set the column that holds the key of each row.

As in a Daf, nothing is built yet. The unique index on the column is made when a key is first used, by ensure_key_index(). An empty keyfield clears it. Avoid Python NULL in the column: a Python NULL key gives undefined results, as SqlDaf explains.

On a permanent table with a record in the metadata, the record is changed too, so the table opens with this keyfield next time. A table made by other code has no record until save() makes one.

Parameters:

Name	Type	Description	Default
keyfield	str	The column. '' for none.	''

Returns:

Type	Description
SqlDaf	This SqlDaf.

Raises:

Type	Description
KeyError	The keyfield is not a column.

key_index_name

key_index_name()

The name of the unique index on the keyfield, as in daf_ix_marks_ballot_id.

ensure_key_index

ensure_key_index()

Make the unique index on the keyfield of a parent, if it is not there yet.

A Daf builds its key index the first time a key is needed. A SqlDaf does the same: the methods that use keys call this first. On 1,000,000 rows it took 0.50 s, about what a Daf takes. After that, a lookup by key uses the index instead of reading every row.

The index is unique. A Daf keeps the last row of a repeated key without a word. A SqlDaf raises instead, so a duplicate is never missed. Once the index is there, adding a row with a key that is already present raises too.

A Python NULL key, SQL NULL or the text '', raises ValueError when the index is made. SqlDaf's results with one would be undefined, and the unique index does not stop several SQL NULL keys. The check uses the new index, so it costs about 0.3 ms. A Daf accepts a Python NULL key.

Returns:

Type	Description
SqlDaf	This SqlDaf.

Raises:

Type	Description
ValueError	There is no keyfield, this is a selection, or a key is a Python NULL. Nothing is made.
IntegrityError	A key is repeated. Nothing is made.

apply_dtypes

apply_dtypes(*, dtypes=None, cols=None, silent_error=False)

Convert the columns to their dtypes, in place, in the database.

New dtypes given here are kept in the metadata record of the table, if it has one.

A table loaded from a CSV file holds text. Call this to turn the columns you need into numbers. Each value is converted by the same function Daf.apply_dtypes() uses, so the results are the same as in memory.

• '12' becomes 12, and '2.5' with dtype int becomes 2.
• A value that does not convert, such as 'abc', stays as it is.
• Python NULL stays Python NULL, except in a bool column, where it becomes 0, as in a Daf.
• A str column is not touched.

Conversion does not check that every value got the requested type. A converted column can still hold some text. silent_error does not change that. It is about a column that has no dtype, not about a value that does not convert.

All the columns are converted in one pass over the table, in one transaction. On 1,000,000 rows, two int columns took 1.8 s.

Only int, float, bool and str can be converted yet. A list or a dict column stays as the text of the file, as Python writes it. reduce(by='sparse_row') reads that text as it goes.

On a selection, the rows are first copied into a TEMP table of its own, as copy(inplace=True) does, and the copy is converted. The parent is not changed. A Daf selection shares its rows, so there the parent's rows are converted too. Converting is costly, so convert a selection rather than a large parent that is not needed otherwise.

Parameters:

Name	Type	Description	Default
dtypes	T_dtype_dict | None	The types to convert to, by column. It may name columns that are not there. This SqlDaf's dtypes if None.	None
cols	str | T_ls | None	Convert only these columns, or this one column. All the columns of the dtypes if None.	None
silent_error	bool	If False, a column to convert that has no dtype raises ValueError. If True, it is left as it is.	False

Returns:

Type	Description
SqlDaf	This SqlDaf, whose table has been changed.

Raises:

Type	Description
KeyError	A name in cols is not a column.
ValueError	A column to convert has no dtype, and silent_error is False.
NotImplementedError	A dtype is not int, float, bool or str.