fastlite

Unlike many Python ORMs, fastlite preserves the semantic distinction between None (SQL NULL) and an empty string ('').

• None represents "unknown" or "missing" data.
• '' represents "known to be empty."

This distinction is maintained during both Python-to-SQLite insertion and SQLite-to-Python retrieval, allowing for precise queries using WHERE column IS NULL or WHERE column = ''.

# Inserting NULL
result_null = people.insert(name=None)
assert result_null.name is None

# Inserting empty string
result_empty = people.insert(name='')
assert result_empty.name == ''

To start using fastlite, import the necessary components and use the database() function to connect to a SQLite file.

from fastlite import *

db = database("chinook.sqlite")

To use fastlite, start by importing the library and initializing a database object. You can use :memory: for an in-memory SQLite database. To create a table, use the db.create() method, passing a class definition (like a dataclass or a standard class) and specifying the primary key with the pk argument.

from fastlite import *

# Initialize database
db = database(':memory:')

# Define a schema
class People: id: int; name: str

# Create the table
people = db.create(People, pk='id')

Install the fastlite library using pip to get quality-of-life improvements for interactive SQLite usage, especially in Jupyter environments.

pip install fastlite

A Database object provides convenient access to its tables and views through the .t and .v properties.

• db.t: Returns a _TablesGetter which allows accessing tables by name as attributes or via indexing.
• db.v: Returns a _ViewsGetter which allows accessing views by name as attributes or via indexing.

Example usage:

# Accessing tables via attributes or indexing
table = db.t.users
# or
table = db.t['users']

# Example of accessing tables and views
# Assuming 'db' is an instance of Database
table = db.t.users
view = db.v.my_view

Fastlite provides a convenient way to access database tables and columns using dot notation via the .t (tables) and .c (columns) properties.

• db.t returns a _TablesGetter which allows you to access tables by name (e.g., db.t.Artist) or iterate through all tables.
• table.c returns a _ColsGetter which allows you to access columns by name (e.g., artist.c.Name).

Columns stringify into a format suitable for SQL statements (e.g., "Table"."Column"), making them easy to use in f-strings for query construction.

# Accessing tables
dt = db.t
artist = dt.Artist

# Accessing columns
ac = artist.c
print(f"select {ac.Name} from {artist}")

# Iterating through tables
for tbl in dt:
    print(tbl)

Once you have a Table or View object, you can access its columns using the .c property. This returns a _ColsGetter which allows you to reference columns for queries or inspections.

Example usage:

# Accessing columns via attributes
user_id = my_table.c.id
# or
user_id = my_table.c['id']

# Example of accessing columns
# Assuming 'tbl' is a Table object
col = tbl.c.name
# or
col = tbl.c['name']

fastlite preserves the semantic distinction between None (which maps to SQLite NULL) and an empty string '' (which maps to an empty string in SQLite). This allows for precise querying (e.g., WHERE name IS NULL vs WHERE name = '').

• Passing None for a field will set that column to NULL in the database.
• Passing '' for a field will set that column to an empty string in the database.

# Setting a value to NULL
people.update(dict(id=person.id, name=None))

# Setting a value to an empty string
people.update(dict(id=person.id, name=''))

To use fastlite, import the package and initialize a database using database(). You can then create a table by passing a class (or a class with type annotations) to the create() method, specifying the primary key with the pk argument.

from fastlite import *

# Initialize an in-memory database
db = database(':memory:')

# Define a schema using a class
class People: id: int; name: str

# Create the table in the database
people = db.create(People, pk='id')

To use your database models in other files, you can generate a dedicated Python module containing all the dataclasses for your tables and views.

1. Use create_mod(db, 'module_name') to write a .py file containing the dataclass definitions.
2. Use db.link_dcs(imported_module) to link the database tables to these newly imported dataclasses so that .cls is correctly populated.

# 1. Generate the module file
create_mod(db, 'db_dc')

# 2. In your application code:
from db_dc import *
db.link_dcs(db_dc)

# Now tables return dataclass instances
dt.Artist(limit=1)

Use db.import_file(table_name, file, ...) to import data from CSV, TSV, or other delimited formats. The file argument can be a file path, a string, bytes, or an open file handle.

csv_data = """id,name,age\n1,Alice,30\n2,Bob,25"""
table = db.import_file("people", csv_data)