co3.util.db module

Example usage for this file’s utilities:

# get SA engine, creating folder hierarchy to provided DB path
engine = db.get_engine(<path>)

# execute a single SA statement, returns a CursorResult
select_results = db.sa_execute(engine, sa.select(<table>))

# convert raw results to dictionaries, keys corresponding to col names
select_dicts = db.named_results(<table>, select_results)

# use table defaults and cols to create compliant insert
insert_dicts = [ db.prepare_insert(<table>, sd) for sd in select_dicts ]

# perform a bulk insert
with engine.connect() as connection:
    connection.execute(
        sa.insert(<table>),
        insert_dicts
    )

co3.util.db.create_fts5(engine, table, columns=None, populate=False, inserts=None, reset_fts=False, tokenizer='unicode61')

Create and optionally populate an FTS5 table in SQLite. Can be used directly for existing tables in the same database. It can also be used for composite tables (i.e., those created from JOINs) or really any other data by providing explicit inserts and column names to use during population.

Parameters:

• table (Table | str) – either SQLAlchemy table instance, or table name string

• columns – list of SQLAlchemy table columns to insert into virtual table. These columns must be present in the provided table if not manually specifying inserts (since the table must be queried automatically)

• inserts

co3.util.db.create_vss0(engine, table, columns=None, populate=False, inserts=None, reset=False, embedding_size=384)

Create a VSS table.

Parameters:

• table (Table | str) – either SQLAlchemy table instance, or table name string

• columns – list of SQLAlchemy table columns to insert into virtual table. These columns must be present in the provided table if not manually specifying inserts (since the table must be queried automatically)

• inserts

co3.util.db.deferred_cd_fkey(target, **kwargs)

Prefer this when using FKEYs; need to really justify not having a CASCADE deletion enabled

co3.util.db.deferred_fkey(target, **kwargs)

co3.util.db.fts5_prep_composite(engine, table, table_name, columns=None)

Helper method for prepping JOIN tables for FTS5 creation.

co3.util.db.get_column_names_str_table(engine, table)

co3.util.db.get_engine(db_path, echo=False)

co3.util.db.named_results(table, results)

Note the implications of this for results from compound tables containing the same column names: only the last column name will be indexed.

co3.util.db.populate_fts5(engine, tables, columns=None, inserts=None)