Modifying¶

Adding rows, changing values, and changing the columns.

Changing values¶

setitem ¶

__setitem__(slice_spec, value)

Assign by key and column name, as in sdaf[key, 'v'] = 1.

These forms are supported:

sdaf[key, col] = value sets one cell. A list of one value gives that value, as in a Daf.
sdaf[key] = row sets the whole row with that key, from a dict or a list. Columns the row lacks become Python NULL, so a dict without the keyfield empties the key, which raises ValueError.
sdaf[:, col] = values sets a column. A list goes in row order. A short list leaves the rows past its end as they were, and a long one is cut. A single value sets every row.

A row is found by its key, never by its position, so these work after a delete. The key must be a str. An int in brackets is a position in a Daf, so an int row raises NotImplementedError, even when the keyfield holds ints. To change a row with an int key, use update_record_krow(). On a selection, the parent's rows change, as a Daf selection shares its rows.

Parameters:

Name	Type	Description	Default
`slice_spec`	`Any`	A key, or a tuple of a key or `:` and a column name.	required
`value`	`Any`	The value, row or list to assign.	required

Raises:

Type	Description
`KeyError`	The key or the column is not found.
`NotImplementedError`	Any other form, such as an int row or several columns.
`ValueError`	The change gives a Python NULL key. Nothing changes.
`IntegrityError`	The change gives a repeated key. Nothing changes.

assign_record ¶

assign_record(record)

Put one row in by its key, replacing the row that has that key, as Daf.assign_record() does.

The whole row is replaced, in its place. Columns the dict lacks become Python NULL. A new key is added at the end. Use update_record_krow() to change only some cells.

Parameters:

Name	Type	Description	Default
`record`	`T_da`	The row, as a dict. It must have the keyfield.	required

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`KeyError`	The record lacks the keyfield.
`ValueError`	There is no keyfield, or the key is a Python NULL.

update_record_krow ¶

update_record_krow(key, record=None)

Change some cells in the row that has this key, as Daf.update_record_krow() does.

Only the columns named in the dict change. An int is always a key here, never a position, so it suits a keyfield of ints. It is one UPDATE. On a selection, the parent's row changes, and only a row of the selection is found. For many rows that take the same values, use update_by_keylist().

Parameters:

Name	Type	Description	Default
`key`	`Any`	The key of the row to change.	required
`record`	`T_da \| None`	The new values, by column name. Names that are not columns are ignored.	`None`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed. A record of None changes nothing.

Raises:

Type	Description
`KeyError`	No row has this key.
`ValueError`	There is no keyfield, or the change gives a Python NULL key. Nothing changes.
`IntegrityError`	The change gives a repeated key. Nothing changes.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> ids_sdaf = SqlDaf('ids', cols=['id', 'v'], lol=[[10, 'a'], [20, 'b']], keyfield='id')
>>> _ = ids_sdaf.update_record_krow(20, {'v': 'q'})
>>> ids_sdaf.select_record(20)
{'id': 20, 'v': 'q'}

update_by_keylist ¶

update_by_keylist(keylist=None, record=None)

Change some cells in the rows that have the given keys, as Daf.update_by_keylist() does.

Only the columns named in the dict change. A key that is not found is skipped. It is one UPDATE for all the keys. For one row, update_record_krow() raises KeyError for a missing key. On a selection, the parent's rows change, as a Daf selection shares its rows.

Parameters:

Name	Type	Description	Default
`keylist`	`T_la \| None`	The keys of the rows to change.	`None`
`record`	`T_da \| None`	The new values, by column name. Names that are not columns are ignored.	`None`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed. With no keyfield, keys or record, nothing happens.

Raises:

Type	Description
`ValueError`	The change gives a Python NULL key. Nothing changes.
`IntegrityError`	The change gives a repeated key. Nothing changes.

assign_col ¶

assign_col(colname, la=None, default=NULL)

Fill a column by name with the values of a list, or add it if it is new, as Daf.assign_col() does.

The values go to the rows in order. A short list is filled out with default, and a long one is cut. With no list, every row gets default. A new column is added at the end, with ALTER TABLE, on a parent only.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column name.	required
`la`	`T_la \| None`	The values, in row order.	`None`
`default`	`Any`	The value for rows that the list does not reach.	`NULL`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`NotImplementedError`	The column is new, and this is a selection.
`ValueError`	The change gives a Python NULL key. Nothing changes.
`IntegrityError`	The change gives a repeated key. Nothing changes.

apply_to_col ¶

apply_to_col(col, func, **kwargs)

Replace each value of a column by the result of a function, in place, as Daf.apply_to_col() does.

It is one UPDATE, with the function registered in SQLite. The function gets Python NULL for SQL NULL. An error it raises is raised as itself, and nothing changes.

Parameters:

Name	Type	Description	Default
`col`	`str`	The column.	required
`func`	`Callable`	Called as func(value, **kwargs). It returns the new value.	required
`**kwargs`	`Any`	Passed on to func.	`{}`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`ColumnNotFoundError`	The name is not a column.
`ValueError`	The change gives a Python NULL key. Nothing changes.
`IntegrityError`	The change gives a repeated key. Nothing changes.

apply_in_place ¶

apply_in_place(func, by='row', rowkeys=None, **kwargs)

Apply a function to each row and store the results, as Daf.apply_in_place() does.

With by='row', the function gets each row as a dict and returns a dict. Its values are written back by column name. A column the dict lacks keeps its value. With by='row_klist', the function gets a KeyedList, changes it, and returns nothing. The whole row is written back.

Rows are read and written in batches, in one transaction. If the function raises, nothing changes. With rowkeys, only those rows change, and a key that is not found is skipped, as in a Daf. On a selection, the parent's rows change.

Parameters:

Name	Type	Description	Default
`func`	`Callable[..., Any]`	The function to apply to each row.	required
`by`	`str`	'row' or 'row_klist'.	`'row'`
`rowkeys`	`T_la \| None`	The keys of the rows to change. All rows if None.	`None`
`**kwargs`	`Any`	Passed on to func.	`{}`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`NotImplementedError`	`by` is not 'row' or 'row_klist'.
`ValueError`	With `by='row'` the function returned None, or a key became a Python NULL.
`IntegrityError`	A key became repeated. Nothing changes.

Adding rows¶

append ¶

append(
    data_item=None,
    respect_kd=False,
    *,
    lol=None,
    la=None,
    fast=False,
)

Add one row, or several, to the end, as Daf.append() does.

A dict or a KeyedList is one row, placed by column name.
A list of values is one row, in column order. A short list is padded with Python NULL.
A list of dicts is several rows. See extend().
A Daf or a SqlDaf is several rows. See concat().
lol= is several rows, each a list. la= is one row.

Each call is one transaction, and each commit writes to disk. On 1,000,000 rows, one call took 9.7 ms on its own, and 55 us inside transaction(). So add many rows in one call, with extend(), or put a loop of single rows inside transaction().

Keys are unique in a SqlDaf. A key that is already there raises sqlite3.IntegrityError, and nothing is added. With respect_kd=True, the row with that key is replaced, in its place. A Python NULL key raises ValueError, as in concat().

On a selection, the selection gets rows of its own first, as concat() does. Its parent is not changed.

Parameters:

Name	Type	Description	Default
`data_item`	`Any`	A dict, a KeyedList, a list, a list of dicts, or a Daf.	`None`
`respect_kd`	`bool`	If True, replace the row that has the same key.	`False`
`lol`	`T_lola \| None`	Several rows, each a list in column order.	`None`
`la`	`T_la \| None`	One row, a list in column order.	`None`
`fast`	`bool`	Accepted for Daf code. Every row is checked anyway.	`False`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`TypeError`	More than one of `data_item`, `lol` and `la`, or a row of an unknown type.
`ValueError`	A row has more values than there are columns, or a key is a Python NULL.
`IntegrityError`	A key would be repeated. Nothing is added.

record_append ¶

record_append(record, respect_kd=True)

Add one row given as a dict or a KeyedList, as Daf.record_append() does.

By default the row with the same key is replaced, in its place. This is the default here, unlike append(). An empty record adds nothing.

Parameters:

Name	Type	Description	Default
`record`	`T_da \| KeyedList`	The row, as a dict or a KeyedList.	required
`respect_kd`	`bool`	If True, the default, replace the row that has the same key.	`True`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`TypeError`	The record is not a dict or a KeyedList.
`ValueError`	The key is a Python NULL.
`IntegrityError`	The key is there, and `respect_kd` is False. Nothing is added.

extend ¶

extend(records_lod=None, respect_kd=False, *, lol=None)

Add several rows, as dicts or as lists, as Daf.extend() does.

Each dict is placed by column name. Each list of lol is in column order. All rows are added in one transaction. If one fails, none is added.

Parameters:

Name	Type	Description	Default
`records_lod`	`List[T_da] \| None`	The rows, as dicts.	`None`
`respect_kd`	`bool`	If True, replace the row that has the same key.	`False`
`lol`	`T_lola \| None`	The rows, as lists in column order.	`None`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`TypeError`	Both `records_lod` and `lol` are given, or a row of `lol` is not a list.
`ValueError`	A row has more values than there are columns, or a key is a Python NULL.
`IntegrityError`	A key would be repeated. Nothing is added.

concat ¶

concat(other_instance, respect_kd=False)

Add the rows of another Daf or SqlDaf to the end of this one, as Daf.concat() does.

The other must have the same columns, in the same order, as in a Daf. A SqlDaf of the same database file is copied in SQL, in its row order, so no row comes into Python. An in-memory Daf is written row by row. All of it is one transaction.

If this is a child, it first becomes a table of its own, by copy(inplace=True), as appending to a selection of a Daf adds only to that selection. The parent is not changed.

A Daf appends a row whose key is already there without a word. A SqlDaf with a keyfield has a unique index on it, so a repeated key raises instead, and nothing is added. A Python NULL key raises ValueError, and nothing is added.

Parameters:

Name	Type	Description	Default
`other_instance`	`Daf`	The Daf or SqlDaf whose rows are added. It is not changed.	required
`respect_kd`	`bool`	If True, a row whose key is there replaces that row, in its place.	`False`

Returns:

Type	Description
`SqlDaf`	This SqlDaf.

Raises:

Type	Description
`KeyError`	The columns differ.
`ValueError`	A key added is a Python NULL. Nothing is added.
`IntegrityError`	A key would be repeated. Nothing is added.
`NotImplementedError`	The other is a SqlDaf this connection cannot read.

Removing rows¶

delete_records ¶

delete_records(keys_ls, inplace=False, silent_error=False)

Remove the rows with these keys, as Daf.delete_records() does. A Python NULL key gives undefined results: see SqlDaf.

By default nothing is changed: a selection without those rows is returned, as select_records_daf(keys, inverse=True) gives. That is always safe.

With inplace=True, the rows are removed from this SqlDaf, and it is returned.

On a selection, its references to those rows are removed, and the rest renumbered. The parent is not changed, so this is safe, as in a Daf.
On a parent, the rows are deleted from the database. After that, it cannot be read by position: see SqlDaf. A selection made from it earlier still counts a deleted row in len(), though to_daf() leaves it out. So delete in place from a parent only when nothing else uses it.

The keys may be in any form that select_records_daf() takes. A key that is not found raises KeyError, and nothing is removed, unless silent_error is True.

Parameters:

Name	Type	Description	Default
`keys_ls`	`Any`	The keys of the rows to remove, in any form `select_records_daf()` takes.	required
`inplace`	`bool`	If True, remove the rows from this SqlDaf and return it. If False, return a selection.	`False`
`silent_error`	`bool`	If True, keys that are not found are ignored.	`False`

Returns:

Type	Description
`SqlDaf`	This SqlDaf if `inplace`, otherwise a selection without the rows.

Raises:

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

remove_dups ¶

remove_dups(keyfield='')

Split the rows into those with a unique key and those with a repeated key, as Daf.remove_dups() does.

Only the key column is compared, not the whole row. For each key, the last row is the unique one. The earlier rows with that key are the duplicates. The unique rows come in the order in which their keys first appear. The duplicates keep this table's order.

Both are selections of this table's rows, as a Daf shares its rows with both. Both have the key column as their keyfield. SQL NULL and '' are one key here, as Python NULL is one key in a Daf. No key index is made, so the column may hold repeated keys. Each side is one GROUP BY, run when it is first read.

A key of several columns is not supported, since a SqlDaf keyfield is one column.

Parameters:

Name	Type	Description	Default
`keyfield`	`str`	The column that identifies a row. If empty, the keyfield of this SqlDaf.	`''`

Returns:

Type	Description
`Tuple[SqlDaf, SqlDaf]`	A tuple of the selection of unique rows and the selection of duplicate rows.

Raises:

Type	Description
`KeysDisabledError`	There is no `keyfield`, and this SqlDaf has none.
`KeyError`	The key column is not found.
`NotImplementedError`	The key is several columns.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['id', 'v'], lol=[[1, 'a'], [2, 'b'], [1, 'c']])
>>> unique_sdaf, dups_sdaf = marks_sdaf.remove_dups('id')
>>> unique_sdaf.to_daf().lol, dups_sdaf.to_daf().lol
([[1, 'c'], [2, 'b']], [[1, 'a']])

Columns and layout¶

insert_col ¶

insert_col(colname, col_la=None, icol=-1, default=NULL)

Add a named column, or fill it if the name is there, as Daf.insert_col() does.

A new column goes at the end, since ALTER TABLE adds columns only there. Any other position raises NotImplementedError. To read the columns in another order, use select_cols(). It gives new rows, in the order you name.

A list that is too short is filled out with default. With no list, every row gets default. If the name is there, its values are replaced, and icol is ignored. An empty name does nothing. A new column is added on a parent only, as assign_col() adds it.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The name of the column.	required
`col_la`	`T_la \| None`	The values, in row order.	`None`
`icol`	`int`	The position. Only the end is supported: -1, or the number of columns.	`-1`
`default`	`Any`	The value for rows that the list does not reach.	`NULL`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`NotImplementedError`	A new column at a position other than the end, or on a selection.

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')
>>> marks_sdaf.insert_col('w', ['x']).to_daf().lol
[['b1', 1, 'x'], ['b2', 0, '']]

drop_cols ¶

drop_cols(exclude_cols=None)

Remove columns from the table, in place, as Daf.drop_cols() does.

Each column goes with ALTER TABLE DROP COLUMN, which rewrites the table. On 1,000,000 rows of 6 columns, one drop took 0.73 s, twice the time of copy(). A name that is not a column is ignored. With no names, nothing happens. The dtypes lose the columns dropped.

If the keyfield is dropped, it is cleared, as in a Daf, and its index goes with it. Set a keyfield again with set_keyfield().

A selection has its parent's columns, so a drop on one raises NotImplementedError. A selection made before the drop still names the dropped column. Reading it then raises sqlite3.OperationalError, so make it again.

Parameters:

Name	Type	Description	Default
`exclude_cols`	`T_ls \| None`	The names of the columns to remove.	`None`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`NotImplementedError`	This is a selection.
`OperationalError`	An index of your own is on a column dropped. Nothing changes.

Examples:

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

rename_cols ¶

rename_cols(from_to_dict)

Rename columns in place, as Daf.rename_cols() does.

Each is ALTER TABLE RENAME COLUMN, which changes only the schema. On 1,000,000 rows it took 15 ms. Names that are not in the mapping stay as they are. Names in the mapping that are not columns are ignored. Names may be swapped, as in {'a': 'b', 'b': 'a'}. The dtypes are renamed too.

The keyfield follows the rename, as in a Daf. Its index is made again under the new name, the next time a key is used.

A Daf lets two columns get the same name. A SqlDaf raises then, and nothing changes. A selection has its parent's columns, so a rename on one raises NotImplementedError. A selection made before the rename still names the old columns. Reading it then raises sqlite3.OperationalError, so make it again.

Parameters:

Name	Type	Description	Default
`from_to_dict`	`T_ds`	Maps old names to new names.	required

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`NotImplementedError`	This is a selection.
`ValueError`	A new name is reserved by SqlDaf. Nothing changes.
`OperationalError`	Two columns would have the same name. Nothing changes.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['id', 'v'], lol=[['b1', 1]], keyfield='id')
>>> marks_sdaf.rename_cols({'id': 'ballot_id'}).columns(), marks_sdaf.keyfield
(['ballot_id', 'v'], 'ballot_id')

set_cols ¶

set_cols(
    new_cols=None, sanitize_cols=True, unnamed_prefix="col"
)

Set the column names by position, in place, as Daf.set_cols() does.

The names are made as in a Daf. Without a list, they are A, B, C and so on. With sanitize_cols, a repeated name gets a suffix, and an empty name becomes the prefix and its position. Each name that changes is renamed as rename_cols() renames it, so the rows stay as they are. The keyfield and the dtypes follow the names.

Parameters:

Name	Type	Description	Default
`new_cols`	`T_ls \| None`	The names, in order. If None, spreadsheet names are made.	`None`
`sanitize_cols`	`bool`	If True, make the names unique and not empty.	`True`
`unnamed_prefix`	`str`	The start of a name made for an empty one.	`'col'`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`AttributeError`	The number of names is not the number of columns, as in a Daf.
`NotImplementedError`	This is a selection.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> SqlDaf('t', cols=['a', 'b', 'c'], lol=[[1, 2, 3]]).set_cols(['x', 'x', '']).columns()
['x', 'x_1', 'col2']

set_dtypes ¶

set_dtypes(default_type=str, typ_to_cols_dict=None)

Set the dtype of every column, from a default and a few exceptions, as Daf.set_dtypes() does.

No value is converted. apply_dtypes() does that. On a permanent table with a record, the record is changed too, as set_keyfield() changes it. The record can store these dtypes: int, float, bool, str, list and dict.

Parameters:

Name	Type	Description	Default
`default_type`	`Type`	The type of every column that is not listed.	`str`
`typ_to_cols_dict`	`Dict[Type, T_ls] \| None`	Maps a type to the names of the columns of that type.	`None`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`NotImplementedError`	A type the record cannot hold, on a table with a record. Nothing changes.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> SqlDaf('t', cols=['a', 'b'], lol=[['1', 'x']]).set_dtypes(str, {int: ['a']}).dtypes
{'a': <class 'int'>, 'b': <class 'str'>}

attach_schema ¶

attach_schema(schema)

Attach a @schemaclass to this SqlDaf, as Daf.attach_schema() does.

It stores the schema, then fills in the dtypes and the keyfield, each only if there is none. No value is converted, and nothing is checked against the rows. Call apply_dtypes() to convert. On a permanent table with a record, the record is changed too, as set_keyfield() changes it.

A Daf with no columns takes them from the schema. A SqlDaf gets its columns when its table is made, so one with no columns raises NotImplementedError.

Parameters:

Name	Type	Description	Default
`schema`	`type`	A class decorated with `@schemaclass`.	required

Returns:

Type	Description
`SqlDaf`	This SqlDaf, which has been changed.

Raises:

Type	Description
`TypeError`	The class is not a `@schemaclass`.
`KeyError`	The schema's keyfield is not a column.
`NotImplementedError`	This SqlDaf has no columns, or a dtype the record cannot hold.

Examples:

>>> import tempfile
>>> from daffodil.lib.schemaclass import schemaclass
>>> @schemaclass
... class Mark:
...     __keyfield__ = 'ballot_id'
...     ballot_id: str = ''
...     v: int = 0
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['ballot_id', 'v'], lol=[['b1', '1']]).attach_schema(Mark)
>>> marks_sdaf.keyfield, marks_sdaf.dtypes
('ballot_id', {'ballot_id': <class 'str'>, 'v': <class 'int'>})

clone_empty ¶

clone_empty(lol=None, cols=None, name='')

Make a new table with the same layout, as Daf.clone_empty() does.

It has the same columns, keyfield and dtypes, and no rows unless lol is given. It is a TEMP table, as copy() makes, so save() keeps it. With cols that differ, it has those columns. The dtypes then keep the entries for the columns still there. The keyfield is cleared if its column is gone.

Parameters:

Name	Type	Description	Default
`lol`	`T_lola \| None`	Rows for the new table, written as the constructor writes them. None for no rows.	`None`
`cols`	`T_ls \| None`	Column names to use instead of these.	`None`
`name`	`str`	The name of the new table. Generated if empty, as in daf_tmp_marks_v4.	`''`

Returns:

Type	Description
`SqlDaf`	The new SqlDaf.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> marks_sdaf = SqlDaf('marks', cols=['ballot_id', 'v'], lol=[['b1', 1]], keyfield='ballot_id')
>>> empty_sdaf = marks_sdaf.clone_empty()
>>> empty_sdaf.columns(), empty_sdaf.keyfield, len(empty_sdaf)
(['ballot_id', 'v'], 'ballot_id', 0)

Sorting¶

sort_by_colname ¶

sort_by_colname(
    colname,
    *,
    reverse=False,
    length_priority=False,
    as_str=False,
)

Sort the rows by one column, in place, as Daf.sort_by_colname() does.

See sort_by_colnames() for how the rows are sorted and where they go.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column to sort by.	required
`reverse`	`bool`	If True, sort from high to low.	`False`
`length_priority`	`bool`	If True, sort by length first, then by value.	`False`
`as_str`	`bool`	If True, sort by the text of each value, and Python NULL sorts as ''.	`False`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, now a sorted TEMP table.

Raises:

Type	Description
`KeyError`	The column name is not found.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> SqlDaf('t', cols=['a'], lol=[['10'], ['99'], ['8']]).sort_by_colname('a', length_priority=True).col('a')
['8', '10', '99']

sort_by_colnames ¶

sort_by_colnames(
    colnames,
    reverse=False,
    length_priority=False,
    as_str=False,
)

Sort the rows by several columns, in place, as Daf.sort_by_colnames() does.

The first column is the main sort key. The others break ties. Rows that tie on every column keep their order, as Python's sort keeps them.

Where the sorted rows go:

The rows are copied in sorted order into a TEMP table, and this SqlDaf becomes it. This is copy(inplace=True) with an ORDER BY, in one statement. The table it came from is not changed, so its other selections stay as they were. A Daf selection sorts its own list of rows, and leaves its parent alone too. On a permanent table, the table in the file keeps its order. Use save() to keep the sorted copy. On 1,000,000 rows of 6 columns, a sort by one text column took 2.4 s, against 1.0 s for a Daf.

How values compare:

Python NULL, SQL NULL or '', sorts first, as an empty cell sorts first in a Daf.
Text compares by code point, as Python compares it.
length_priority sorts by the length of the text first, then by the text. That gives numeric order for whole numbers held as text, when none is negative or has a leading zero. It is not a numeric sort for negative numbers or decimals.
as_str sorts by the text of each value. A float such as 1e20 is written differently by SQLite.
A column that mixes numbers and text sorts every number before all text. A Daf raises TypeError on such a column instead.

Parameters:

Name	Type	Description	Default
`colnames`	`T_ls`	The columns to sort by, main key first.	required
`reverse`	`bool`	If True, sort from high to low.	`False`
`length_priority`	`bool`	If True, sort by length first, then by value.	`False`
`as_str`	`bool`	If True, sort by the text of each value, and Python NULL sorts as ''.	`False`

Returns:

Type	Description
`SqlDaf`	This SqlDaf, now a sorted TEMP table.

Raises:

Type	Description
`KeyError`	A column name is not found.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> SqlDaf('t', cols=['p', 'q'], lol=[[2, 'b'], [1, 'z'], [1, 'a']]).sort_by_colnames(['p', 'q']).to_daf().lol
[[1, 'a'], [1, 'z'], [2, 'b']]

Modifying¶

Changing values¶

__setitem__ ¶

assign_record ¶

update_record_krow ¶

update_by_keylist ¶

assign_col ¶

apply_to_col ¶

apply_in_place ¶

Adding rows¶

append ¶

record_append ¶

extend ¶

concat ¶

Removing rows¶

delete_records ¶

remove_dups ¶

Columns and layout¶

insert_col ¶

drop_cols ¶

rename_cols ¶

set_cols ¶

set_dtypes ¶

attach_schema ¶

clone_empty ¶

Sorting¶

sort_by_colname ¶

sort_by_colnames ¶

setitem ¶