SQLite#

pyinaturalist_convert.sqlite

Helper utilities to load data directly from CSV into a SQLite database

class pyinaturalist_convert.sqlite.ChunkReader(f, chunk_size=2000, fields=None, **kwargs)#

Bases: object

A CSV reader that yields chunks of rows

Parameters:

  • chunk_size (int) – Number of rows to yield at a time

  • fields (Optional[List[str]]) – List of fields to include in each chunk

class pyinaturalist_convert.sqlite.XFormChunkReader(f, chunk_size=2000, fields=None, transform=None, **kwargs)#

Bases: ChunkReader

A CSV reader that yields chunks of rows, and applies a transform callback to each row

Parameters:

  • chunk_size (int) – Number of rows to yield at a time

  • fields (Optional[List[str]]) – List of fields to include in each chunk

  • transform (Optional[Callable]) – Callback to transform a row before inserting into the database

pyinaturalist_convert.sqlite.get_fields(csv_path, delimiter=',')#
Return type:

List[str]

pyinaturalist_convert.sqlite.load_table(csv_path, db_path, table_name=None, column_map=None, pk='id', progress=None, delimiter=',', transform=None)#

Load a CSV file into a sqlite3 table. This is less efficient than the sqlite3 shell .import command, but easier to use.

Example

# Minimal example to load data into a ‘taxon’ table in ‘my_database.db’ >>> from pyinaturalist_convert import load_table >>> load_table(‘taxon.csv’, ‘my_database.db’)

Parameters:

  • csv_path (Union[Path, str]) – Path to CSV file

  • db_path (Union[Path, str]) – Path to SQLite database

  • table_name (Optional[str]) – Name of table to load into (defaults to csv_path basename)

  • column_map (Optional[Dict]) – Dictionary mapping CSV column names to SQLite column names. And columns not listed will be ignored.

  • pk (str) – Primary key column name

  • progress (Optional[MultiProgress]) – Progress bar, if tracking loading from multiple files

  • transform (Optional[Callable]) – Callback to transform a row before inserting into the database

pyinaturalist_convert.sqlite.vacuum_analyze(table_names, db_path=PosixPath('/home/docs/.local/share/pyinaturalist/observations.db'), show_spinner=False)#

Vacuum a SQLite database and analzy one or more tables. If loading multiple tables, this should be done once after loading all of them.