Sqlite

SQLite is a performant flat-file database that’s often touted as the “zero-config” database. The Parsons class uses the python3 built-in sqlite3 connector.

Quickstart

Instantiate Sqlite from passed variables

from parsons import Sqlite
sqlite = Sqlite(db_path='local.db')

Query database

tbl = sqlite.query('select * from my_table')

Copy data to database

tbl = Table.from_csv('my_file.csv') # Load from a CSV or other source.
sqlite.copy(tbl, 'my_destination_table')

API

class parsons.databases.sqlite.sqlite.Sqlite(db_path)

query(sql: str, parameters: list | dict | None = None) → Table | None

Execute a query against the database, using the existing connection within the Sqlite object. Will return None if the query returns zero rows.

Parameters:

• sql (str) – str A valid SQL statement

• parameters (list | dict | None) – list A list of python variables to be converted into SQL values in your query

Returns:

Table

See Table for output options.

Return type:

Table | None

query_with_connection(sql: str, connection: Connection, parameters: list | dict | None = None, commit: bool = True, return_values: bool = True)

Execute a query against the database, with an existing connection. Useful for batching queries together. Will return None if the query returns zero rows.

Parameters:

• sql (str) – str A valid SQL statement

• connection (Connection) – obj A connection object obtained from redshift.connection()

• parameters (list | dict | None) – list A list of python variables to be converted into SQL values in your query

• commit (bool) – boolean Whether to commit the transaction immediately. If False the transaction will be committed when the connection goes out of scope and is closed (or you can commit manually with connection.commit()).

• return_values (bool)

Returns:

Table

See Table for output options.

generate_data_types(table: Table) → dict[str, str]

Generate column data types

Parameters:

table (Table)

Return type:

dict[str, str]

copy(tbl: Table, table_name: str, if_exists: Literal['fail', 'append', 'drop', 'truncate'] = 'fail', strict_length: bool = False, force_python_sdk: bool = False)

Copy a Table to Sqlite.

Parameters:

• tbl (Table) – Table A Parsons table object

• table_name (str) – str The destination schema and table (e.g. my_schema.my_table)

• if_exists (Literal['fail', 'append', 'drop', 'truncate']) – str If the table already exists, either fail, append, drop or truncate the table.

• strict_length (bool) – bool If the database table needs to be created, strict_length determines whether the created table’s column sizes will be sized to exactly fit the current data, or if their size will be rounded up to account for future values being larger then the current dataset. Defaults to False.

• force_python_sdk (bool) – bool Use the python SDK to import data to sqlite3, even if the sqlite3 cli utility is available for more efficient loading. Defaults to False.

import_table_iteratively(tbl: Table, table_name: str, if_exists: str, chunksize=10000) → None

Import a CSV row by row using the python sqlite3 API.

Iterates over chunks of length chunksize

It is generally more efficient to use the sqlite3 CLI to import a CSV, but not all machines have the shell utility available, so we can fall back to this method.

Parameters:

• tbl (Table)

• table_name (str)

• if_exists (str)

Return type:

None

table_exists(table_name: str, view: bool = False) → bool

Check if a table or view exists in the database.

Parameters:

• table_name (str) – str The table name and schema (e.g. myschema.mytable).

• view (bool) – boolean Check to see if a view exists by the same name. Defaults to False.

Returns:

boolean

True if the table exists and False if it does not.

Return type:

bool