# Usage It is assumed that you have at least a little familiarity with sqlite. If you're unfamiliar, consider checking out [sqlitetutorial.net](https://www.sqlitetutorial.net/). If you're familiar, note that we turn on foreign key enforcement! coolqlite should feel relatively familiar those used to the stdlib's {mod}`sqlite3` module. First, we {meth}`~coolqlite.connect`, passing a filesystem path, or `:memory:` for an in-memory database. ```python import coolqlite db = coolqlite.connect(":memory:") ``` Let's create a simple table to work on for some TTRPG nerdery. ## What does it look like? Let's play with a database for TTRPG nerdery. ```python db.run(t"""create table CharacterClass ( id INTEGER NOT NULL PRIMARY KEY , name TEXT NOT NULL UNIQUE ) strict""") ``` Note the following: 1. Even though we didn't bind any parameters, we still used a t-string. Coolqlite insists on this to avoid the mistake of using an f-string when you _do_ mean to bind parameters. 2. We made a [strict](https://www.sqlite.org/stricttables.html) table, because type checking is our friend. 3. We said `NOT NULL` for the primary key. [Sqlite allows null primary keys by default.][sqlite primary key]. Technically this isn't necessary when using a strict table, but let's call it out. 4. We used {meth}`~coolqlite.Connection.run`. Use `run()` when you don't care about the output. [sqlite primary key]: https://www.sqlite.org/lang_createtable.html#the_primary_key Let's insert some data: ```python for class_ in "manifold timepiece growth drain dream".split(): db.run(t"insert into CharacterClass(name) values ({class_})") ``` We did some interpolation there, but coolqlite analyzes the t-string to properly parameterize the query. ## How does reading data work? Putting data into a database and never reading it isn't so useful. So let's see all the ways to get the data out! ```python from coolqlite import Results def get_all_classes() -> Results: return db.query(t"select id, name from CharacterClass") def get_classes_like(pattern: str) -> Results: return db.query(t"select * from CharacterClass where name LIKE {pattern}") def get_class_named(name: str) -> Results: return db.query(t"select id, name from CharacterClass where name = {pattern}") ``` Right now, we just care about looking at every row in the `CharacterClass` table. But many times, you care about the number of results you get, and anything different is an error. `Results`has many ways to look at the rows: - Iterate over however many rows there are, because it's an iterable. - Get whatever the next one is (or `None` if done), with {meth}`Results.next() `. - Get _exactly_ one row with {meth}`Results.one() `, no more and no less, throwing an exception otherwise. - Get _exactly_ one or _exactly_ none with {meth}`Results.one_or_none() `. The signature looks the same as `Results.next()`, but it will throw an exception if there are two or more rows. - Get _at least_ one row, allowing more, with {meth}`Results.at_least_one() `. The above mentioned exception would be a {exc}`~coolqlite.QueryResultCountError`. {class}`coolqlite.Row` is a subclass of {class}`sqlite3.Row`, but it has a nicer `repr` and will properly throw a `KeyError` if a column is missing. Let's try some of them! ```python from contextlib import suppressing from coolqlite import QueryResultCountError id_ = get_class_named("manifold").one()["id"] # uhh there might have been one named this? result = get_class_named("timekeeper").one_or_none() assert result is None # guess not. with suppressing(QueryResultCountError): get_class_named("gunk").one() assert False # no gunk :( # there might be a lot of these, let's do it one at a time. results = get_all_classes() row = results.next() if row is not None: row = results.next() # this is technically the same as next(iter(results), None), # but a little more convenient. # Maybe a user types `d` in a search box # and you want to suggest completions: for id_, name in get_classes_like("d%"): print(f"{name} has id {id_}") ``` (It's technically a subclass of `Results` called `QueryResults`, which adds access to `rowcount` and `lastrowid`.) ## What about inserting things other than `str`s and `int`s? Sqlite understands some basic python types natively, but others need converting. A `Connection` keeps track of converter functions that work either by type or by name. ### Type Converters This works the same way as a {func}`functools.singledispatch`. For a simple case, let's store UUIDs as strings in the database: ```python from coolqlite import ToSqlFnRegistry, connect from uuid import UUID funcs = ToSqlFnRegistry() # Also works as a decorator, see singledispatch docs. funcs.register(UUID, str) db = connect(":memory":, to_sql_fns=funcs) ``` ### Named Converters Named converters called by name in the t-string, where the "format specifier" would be normally: ```python db.run(t"insert into Table(column) values({some_value:converter_name_here})") ``` Let's start with a common example-- very few applications can avoid {class}`~datetime.datetime`s. Here's where we encounter an opinionated default of coolqlite: > "In the face of ambiguity, refuse the temptation to guess." > _-- The Zen Of Python_ We offer easy-to-use helpers that are named converters by default. They turn temporal values into iso-formatted strings. (With a space instead of `T` for datetimes). You could easily wire them up as type converters if you want! This is a format sqlite recognizes as a [time value](https://sqlite.org/lang_datefunc.html#time_values), and is easy to read when querying the database. There's [some reasons](./ADRs.md#001-how-are-datetimes-handled) behind this, but the short version is: 1. Sqlite temporal functions assume everything is UTC unless there's an explicit offset. They also assume an integer is a julian day counter instead of a unix epoch, unless explicitly told otherwise. 2. For storing certain events, especially in the future, the wall-clock time might actually be the _only_ correct one! (Read that link for details.) Maybe this feels like a bit much for "Basic Usage", but it's unavoidable. #### Date & Time Examples Let's get set up: ```python >>> from zoneinfo import ZoneInfo >>> import coolqlite >>> import datetype >>> >>> def set_up_table(db: coolqlite.Connection): ... db.run(t"""create table JournalEntry ... ( created_at TEXT NOT NULL default (concat(current_timestamp, 'Z')) ... , about_dt TEXT NOT NULL ... , note TEXT NOT NULL default '' ... ) strict""") >>> >>> db = coolqlite.connect(":memory:") >>> set_up_table(db) >>> >>> NY = ZoneInfo("America/New_York") >>> def add_entries(db: coolqlite.Connection): ... # db would handle this for us, but why not. ... created_at = datetype.AwareDateTime.now(NY) ... about_dt = datetime(2024, 6, 25, 9, 23, tzinfo=NY) ... note = "A really cool game came out on this day!" ... ... db.run( ... t"insert into JournalEntry values ({created_at:z}, {about_dt:keeptz}, {note})" ... ) ... ... created_at = datetype.AwareDateTime.now(NY) ... # events far in the future will likely have timezone changes by then, ... # so if the wall-clock time is important, DON'T store it as offset-aware! ... about_dt = datetime(2112, 6, 7, 12, 00) ... note = "At noon, meet friend coming back from mars, where they drive in fancy cars." ... ... db.run( ... t"insert into JournalEntry values ({created_at:z}, {about_dt:naive}, {note})" ... ) >>> >>> add_entries(db) ``` And let's get the data back out: ```python >>> for journal_entry in db.query(t"select * from JournalEntry"): ... print(journal_entry) ``` Look at what each converter did: - `z` converted the aware datetime to UTC - `keeptz` kept the offset - `naive` allowed a datetime without an offset There's also `ANY_temporal` for when you just want it in iso-format, aware or not. We put in temporal types, and got back out strings. Can we get those back to how they were? ## Converting when reading You could convert manually on each field when you access them on the `Row`: ```python >>> from coolqlite.from_sql.datetime_ import aware_dt_from_iso, dt_from_iso >>> >>> def describe_entry(created_at: datetime, about_dt: datetime, note: str): ... print(f"On {created_at:%c}, we wrote the following about {about_dt:%c}:\n{note}") >>> >>> for row in db.query(t"select created_at, about_dt, note from JournalEntry"): ... created_at = aware_dt_from_iso(row["created_at"]) ... about_dt = dt_from_iso(row["about_dt"]) ... note = row["note"] ... describe_entry(created_at, about_dt, note) On Mon Feb 23 17:29:03 2026, we wrote the following about Tue Jun 25 09:23:00 2024: A really cool game came out on this day! On Mon Feb 23 17:29:03 2026, we wrote the following about Tue Jun 7 12:00:00 2112: At noon, meet friend coming back from mars, where they drive in fancy cars. ``` But that's a little tedious. Luckily, we have the power of `cattrs` on our side. Anything it can convert from a mapping or tuple, we can convert a row into. ```python >>> from dataclasses import dataclass >>> @dataclass >>> class JournalEntry: ... created_at: datetype.AwareDateTime ... # could also just say datetype.DateTime. ... about_dt: datetype.AwareDateTime | datetype.NaiveDateTime ... note: str ... def __str__(self): ... return f"On {self.created_at:%c}, we wrote the following about {self.about_dt:%c}:\n{self.note}" >>> for entry in db.query(t"select * from JournalEntry", JournalEntry): ... print(entry) On Mon Feb 23 17:29:03 2026, we wrote the following about Tue Jun 25 09:23:00 2024: A really cool game came out on this day! On Mon Feb 23 17:29:03 2026, we wrote the following about Tue Jun 7 12:00:00 2112: At noon, meet friend coming back from mars, where they drive in fancy cars. ``` ### Just one column Many times, you'll want to select just a single column. There's a shortcut for that: ```python >>> from datetype import DateTime >>> future_entry = db.colquery( ... t"select about_dt from JournalEntry where note like '%mars%'", DateTime >>> ).one() >>> print(future_entry) 2112-06-07 12:00:00 ``` If you don't specify a type at the end, it will fall back to a `SqliteType`, i.e. any of the primitives sqlite supports. ### Type Checking We try to make sure type checkers stay happy. Unfortunately, there's 2 weaknesses we've found so far. First, {class}`typing.NewType`. Cattrs [can handle them](https://catt.rs/en/stable/strategies.html#union-passthrough), but since they're not a "type" per se, type checkers will complain when you use them for `colquery`. You've got two ways around this: 1. map: ```python UserId = typing.NewType("UserId", int) results = db.colquery(t"select id from users", int).map(UserId) typing.assert_type(results, QueryResults[UserId]) ``` 2. map, but lambda: ```python results = db.colquery(t"select id from users", int).map(lambda x: UserId(x)) ``` MyPy will accept either, but pyright doesn't like the first, sadly. Second, unions. They're also not a "real type" apparently. Luckily, we can "trick" the type checkers by {meth}`~coolqlite.Connection.query`ing a tuple of that type and unpacking the first column (not `colquery`ing). We even offer a helper for that. ```python from coolqlite import first_col results = db.query(t"select 1", tuple[int | None]).map(first_col) typing.assert_type(results, QueryResults[int | None]) ``` ## Transactions > _You are a regular guy! You can do that transaction!_ > -- Jacob Wysocki Because the connection is in autocommit mode, you don't need transactions for one-off changes. However, if you're making multiple changes that need to either all succeed or all fail, you'll want to do that in a transaction. `coolqlite` does transactions through sqlite's [savepoints][savepoint-docs]. They're more flexible and powerful than plain `BEGIN..COMMIT/ROLLBACK`. [savepoint-docs]: https://sqlite.org/lang_savepoint.html ```{eval-rst} .. include:: /../src/coolqlite/transactions.py :start-after: =========== :end-before: """ ``` ## Inspecting Queries And A Neat Shortcut Queries first get "analyzed", turning a template into something structured. They are then "built" by converting each value. You can inspect at either stage of the journey, and supply either result as an argument for query methods. Names may be mangled. ```python >>> from coolqlite.query import analyze_query >>> from pprint import pprint >>> dt = datetime(2112, 6, 7, 12, 00) >>> analyzed = analyze_query(t"select {dt:naive}") >>> print(f"query: {analyzed.query_str!r}") >>> for param in analyzed.params.values(): ... pprint(param) >>> # or could put the template here directly. >>> built = db.build(analyzed) >>> assert analyzed.query_str == built.query_str >>> for param in built.params.values(): ... pprint(param) query: 'select :_CQL_dt_naive' Param(param_name='_CQL_dt_naive', original_param_expr='dt', converter_name='naive', original_value=datetime.datetime(2112, 6, 7, 12, 0)) ConvertedParam(param_name='_CQL_dt_naive', original_param_expr='dt', converter_name='naive', original_value=datetime.datetime(2112, 6, 7, 12, 0), converted_value='2112-06-07 12:00:00') ``` This is mainly useful for debugging, but there's a neat trick you can do: make functions that mark the expected input types and names, and give you an analyzed query _and the type it should return_. ```python def get_ids_and_names(status: str): query = analyze_query(t"select id, name from Table where status = {status}") row_type = tuple[int, str] return query, row_type for id_, name in db.query(*get_ids_and_names("active")): ... ``` ## Programmactically Modifying Query Text For when you programmactically need to build a query, you can get around the parameter-binding behavior. The built-in named converter `DANGER` will let you do this. :::{danger} If you directly use user input for this, you are likely vulnerable to SQL Injection. If you're using user input for something like selecting columns to filter by, always always always parse that input into something controlled and limited, like an enum. You have been warned. ::: ```python >>> from enum import StrEnum >>> class SelectableColumn(StrEnum): ... CREATED_AT = "created_at" ... ABOUT_DT = "about_dt" ... NOTE = "note" >>> class ComparisonOperator(StrEnum): ... EQ = "=" ... LIKE = "like" >>> # say this comes from a web form somewhere. >>> def select_from_user_condition(column_UNTRUSTED: str, op_UNTRUSTED: str, condition: str): ... column = SelectableColumn(column_UNTRUSTED) ... op = ComparisonOperator(op_UNTRUSTED) ... return analyze_query(t"""select * from JournalEntry ... where {column:DANGER} {op:DANGER} {condition}""") >>> select_from_user_condition("note", "like", "%mars%").query_str 'select * from JournalEntry where note like :condition' >>> select_from_user_condition("' or TRUE; --", "", "").query_str ValueError: "' or TRUE; --" is not a valid SelectableColumn ``` You could even put all of the inputs into a dataclass, and have cattrs convert the input into it, so you can get the errors in parallel. Neat! ## Reading Redux ### Taking Advantage of Results If you need to do some more advanced logic than SQL will allow, you might iterate over rows and `yield` some derived data. What's annoying is you then lose the Results niceities from that. But guess what? 1. You can use _any_ iterator (and thus iterable or generator) with Results. 2. We offer a {func}`~coolqlite.as_results` decorator to make that even easier. ```python from coolqlite import as_results, Connection @as_results def advanced_query(db: Connection): # imagine this is something more complex # than could easily be done in SQL. for name in db.colquery(t"select name from Table", str): if name.startswith("Alex"): yield name[::-1] else: yield name only_one = advanced_query(db).one() ``` Note that information from `QueryResults` can't be passed along, but the information might not be accurate anymore based on your transformation anyway.