### Install fastsql Library

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Install the fastsql library using pip.

```sh
pip install fastsql
```

--------------------------------

### Example: Get Item by PK (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates retrieving an item from a `DBTable` instance using primary key access (`[]`) after adding it.

```python
users.xtra(name='jph')

assert users['jph']==u0
users['jph']
```

--------------------------------

### Example: Get Item by PK - Not Found (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates attempting to retrieve an item that does not exist using primary key access (`[]`) and verifying that it raises an error.

```python
users.xtra(name='rlt')
test_fail(lambda: users['jph']==u0)
```

--------------------------------

### Example: List Table Names (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates listing the names of tables available in the metadata.

```python
' '.join(dbm.tables)
```

--------------------------------

### Example: Access Metadata (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates accessing the database metadata object.

```python
dbm = db.meta
```

--------------------------------

### Example: Insert Item - Compound PK (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates inserting an item into a `DBTable` configured with a compound primary key, preparing for the `__contains__` check example.

```python
students.insert(Student(1, 2021, 'jph'))
```

--------------------------------

### Installing fastsql via pip in Shell

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Command to install the fastsql library using the pip package manager.

```sh
pip install fastsql
```

--------------------------------

### Example: List Table Columns (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates listing the columns of a specific table object.

```python
list(t.c)
```

--------------------------------

### Example: Execute Raw SQL (Commented Out) (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

A commented-out example demonstrating how to execute a raw SQL `DELETE` statement using the patched `sql` method and commit the transaction.

```python
# dbm.sql('delete from todo')
# db.conn.commit()
```

--------------------------------

### Example: Get Rows from Table (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates using the patched `get` method on a `Table` object to retrieve rows matching a condition with a limit.

```python
t.get(t.c.title.startswith('d'), limit=5)
```

--------------------------------

### Example: Update Item - Verify Change (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates updating an item and then verifying that the update was successful by retrieving the item and checking its properties.

```python
users.xtra(name='rlt')
u.pwd = 'foo'
users.update(u)
users.xtra()
test_eq(users['jph'].pwd, 'new')
```

--------------------------------

### Example: Execute Raw SQL - Select (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates executing a raw SQL `SELECT` statement using the patched `sql` method and accessing the results.

```python
rs = dbm.sql('select * from user')
rs[0]
```

--------------------------------

### Example: Update Item (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates updating an existing item in a `DBTable` instance using the `update` method.

```python
users.xtra(name='jph')
u.pwd = 'new'
users.update(u)
users.xtra()
users()
```

--------------------------------

### Example: Print Generated SQL Query (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates printing the SQLAlchemy query object generated by the `select`, `where`, and `limit` methods before execution.

```python
print(t.select().where(t.c.title.startswith('d')).limit(5))
```

--------------------------------

### Example: Delete Item (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates deleting an item from a `DBTable` instance using the `delete` method and verifying its deletion.

```python
assert users.delete('jph')
test_fail(lambda: users['jph'])
```

--------------------------------

### Example: Access Specific Table (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates accessing a specific table object from the metadata using the patched attribute access.

```python
t = dbm.todo
```

--------------------------------

### Example: Close Database Connection (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates closing the database connection using the patched `close` method on the metadata object.

```python
dbm.close()
```

--------------------------------

### Patch SQLAlchemy Table for Get (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Patches the SQLAlchemy `Table` object to add a `get` method, providing a simplified way to select rows with optional `where` and `limit` clauses.

```python
#| export
@patch
def get(self:Table, where=None, limit=None):
    "Select from table, optionally limited by `where` and `limit` clauses"
    return self.metadata.conn.sql(self.select().where(where).limit(limit))
```

--------------------------------

### Querying Tables with DBTable Instance - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates various ways to query data from a `DBTable` instance by calling it like a function. Examples include fetching all rows, filtering with a `where` clause and parameters, ordering results, and using `xtra_id` for implicit filtering.

```python
assert users()==[u0,u1]
users()

r = users(where="pwd LIKE :pwd", pwd="b%")
assert r==[u1]
r

users.xtra(name='rlt')
users(order_by='name')

users(where='name="rlt"')

users(where='name="jph"')

assert len(todos())==2
todos()
```

--------------------------------

### Example: Check Contains - Single PK (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates using the `in` operator to check for the existence of items in a `DBTable` with a single primary key.

```python
assert not 'jph' in users
assert 'rlt' in users
```

--------------------------------

### Example: Check Contains - Compound PK (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates using the `in` operator to check for the existence of items in a `DBTable` with a compound primary key.

```python
assert (1,2021) in students
assert (1,2030) not in students
```

--------------------------------

### Implement DBTable Get Item by PK (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Patches the `DBTable` class to implement the `__getitem__` method, allowing retrieval of a single row by its primary key. Raises `NotFoundError` if the item does not exist.

```python
#| export
@patch
def __getitem__(self:DBTable, key):
    "Get item with PK `key`"
    qry = self._pk_where('select', key)
    result = self.conn.execute(qry).first()
    if not result: raise NotFoundError()
    return self.cls(**result._asdict())
```

--------------------------------

### Creating Tables with db.create - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates how to use the `db.create` method to define and create database tables based on data classes (`User`, `Todo`, `Student`), specifying single or composite primary keys.

```python
users = db.create(User, pk='name')
todos = db.create(Todo, pk='id')
students = db.create(Student, pk=('id', 'grad_year'))
```

--------------------------------

### Showing Documentation for DBTable.__call__ in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Uses `nbdev.showdoc` to display the documentation for the `__call__` method of the `DBTable` class, which is used for querying.

```python
show_doc(DBTable.__call__)
```

--------------------------------

### Instantiate fastsql Database

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Instantiate the fastsql Database class, connecting to an in-memory SQLite database.

```python
db = Database("sqlite:///:memory:")
```

--------------------------------

### Initializing Database Connection - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Establishes a connection to a PostgreSQL database instance using the `Database` class. The connection string specifies the database type and location.

```python
db = Database("postgresql://")
```

--------------------------------

### Importing fastsql and nbdev in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Imports necessary modules from the fastsql core library and nbdev for documentation generation.

```python
#| hide
from fastsql.core import *
from nbdev.showdoc import show_doc
```

--------------------------------

### Create Database Tables from Classes

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Create database tables using the defined Python classes and specify primary keys.

```python
users = db.create(User, pk='name')
todos = db.create(Todo, pk='id')
```

--------------------------------

### Import fastsql and SQLAlchemy

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Import necessary classes and modules from fastsql and sqlalchemy.

```python
from fastsql import *
import sqlalchemy as sa
```

--------------------------------

### Creating Data Objects in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Creates instances of the `User` and `Todo` classes with sample data, representing records to be inserted.

```python
u0 = User('jph','foo')
u1 = User('rlt','bar')
t0 = Todo('do it', 'jph')
t1 = Todo('build it', 'jph')
t2 = Todo('write book', 'rlt')
```

--------------------------------

### Importing Dependencies for FastSQL Core

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Imports necessary modules from `dataclasses`, `sqlalchemy`, `sqlalchemy.orm`, `fastcore.utils`, `fastcore.test`, and `itertools` to support the core functionality of the FastSQL library, including database interaction and testing utilities.

```python
from dataclasses import dataclass,is_dataclass,MISSING,fields
import sqlalchemy as sa
from sqlalchemy.orm import Session
from fastcore.utils import *
from fastcore.test import test_fail,test_eq
from itertools import starmap
```

--------------------------------

### Creating Database Tables from Models in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Creates database tables (`users`, `todos`) based on the `User` and `Todo` classes using `db.create()`, specifying primary keys.

```python
users = db.create(User, pk='name')
todos = db.create(Todo, pk='id')
```

--------------------------------

### Print Database Schema

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Print the schema of the database, showing the tables and their columns.

```python
print(db.schema())
```

--------------------------------

### Creating Data Objects - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Creates instances of data classes (`User`, `Todo`) that will be used for insertion into the database tables.

```python
u0 = User('jph','foo')
u1 = User('rlt','bar')
t0 = Todo('do it', 'jph')
t1 = Todo('get it done', 'rlt')
```

--------------------------------

### Importing fastsql and SQLAlchemy in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Imports all components from the fastsql library and the sqlalchemy library, aliasing it as `sa`.

```python
from fastsql import *
import sqlalchemy as sa
```

--------------------------------

### Import SQLAlchemy and fastcore Modules (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Imports necessary libraries including SQLAlchemy components for database interaction and fastcore utilities.

```python
#| export
from fastcore.net import urlsave

from collections import namedtuple
from sqlalchemy import create_engine,text,MetaData,Table,Column,engine,sql
from sqlalchemy.sql.base import ReadOnlyColumnCollection
from sqlalchemy.engine.base import Connection
from sqlalchemy.engine.cursor import CursorResult
```

--------------------------------

### Adding create Method to Database - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Adds a `create` method to the `Database` class using `@patch`. This method takes a data class, optional primary key(s), and table name, generates SQLAlchemy columns from the class fields, creates the table in the database if it doesn't exist, and returns a `DBTable` instance.

```python
#| export
@patch
def create(self:Database, cls:type, pk='id', name:str|None=None):
    "Get a table object, creating in DB if needed"
    pk = listify(pk)
    flexiclass(cls)
    if name is None: name = camel2snake(cls.__name__)
    cols = [_column(o.name, o.type, primary=o.name in pk) for o in fields(cls)]
    tbl = sa.Table(name, self.meta, *cols, extend_existing=True)
    return DBTable(tbl, self, cls)
```

--------------------------------

### Create Objects for Insertion

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Create instances of the Python classes representing records to be inserted into the tables.

```python
u0 = User('jph','foo')
u1 = User('rlt','bar')
t0 = Todo('do it', 'jph')
t1 = Todo('build it', 'jph')
t2 = Todo('write book', 'rlt')
```

--------------------------------

### Defining Data Models for User and Todo in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Defines Python classes (`User`, `Todo`) that will serve as schemas for database tables, including type hints and default values.

```python
class User: name:str; pwd:str
class Todo: title:str; name:str; id:int=None; done:bool=False; details:str=''
```

--------------------------------

### Inserting Data into Tables - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Demonstrates inserting data objects (`User`, `Todo` instances) into their respective database tables using the `insert` method of the `DBTable` instances. It shows how the returned object includes database-assigned values.

```python
t = todos.insert(t0)
assert t.id
t

u = users.insert(u0)
assert u.name=='jph'
users.insert(u1)
u

todos.insert(t1)
```

--------------------------------

### Initializing In-Memory SQLite Database with FastSQL in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Creates an in-memory SQLite database instance using the FastSQL `Database` class.

```python
db = Database("sqlite:///:memory:")
```

--------------------------------

### Instantiating In-Memory FastSQL Database

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Creates an instance of the `Database` class, connecting to an in-memory SQLite database. This is a common pattern for testing or temporary database operations without needing a persistent file or server.

```python
db = Database("sqlite:///:memory:")
```

--------------------------------

### Implementing FastSQL Database Connection Class

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Defines the `Database` class which establishes and manages a connection to a SQLAlchemy database using a connection string. It initializes a SQLAlchemy engine, reflects metadata, creates a connection, and provides an `execute` method for running SQL statements.

```python
class Database:
    "A connection to a SQLAlchemy database"
    def __init__(self, conn_str):
        self.conn_str = conn_str
        self.engine = sa.create_engine(conn_str)
        self.meta = sa.MetaData()
        self.meta.reflect(bind=self.engine)
        self.meta.bind = self.engine
        self.conn = self.engine.connect()
        self.meta.conn = self.conn
    
    def execute(self, st, params=None, opts=None): return self.conn.execute(st, params, execution_options=opts)

    def __repr__(self): return f"Database({self.conn_str})"
```

--------------------------------

### Printing Database Schema - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Calls the `schema` method on the database instance to print the structure of all tables currently in the database.

```python
print(db.schema())
```

--------------------------------

### Printing Database Schema in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Prints the generated schema of the database, showing the structure of the created tables and their columns.

```python
print(db.schema())
```

--------------------------------

### Inserting Data Objects into Tables in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Inserts the created `User` and `Todo` objects into their respective database tables using the `insert()` method.

```python
users.insert(u0)
users.insert(u1)
todos.insert(t0)
todos.insert(t1)
todos.insert(t2)
```

--------------------------------

### Execute SQLAlchemy Join Query

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Construct and execute a complex query using SQLAlchemy's select and join constructs via the fastsql database object.

```python
query = sa.select(uc.name, uc.pwd, tc.title).select_from(
    tt.join(ut, tc.name == uc.name))
list(db.execute(query))
```

--------------------------------

### Executing SQLAlchemy Join Query in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Constructs a SQLAlchemy query to join the `users` and `todos` tables based on the user name and executes it using the FastSQL database instance's `execute()` method.

```python
query = sa.select(uc.name, uc.pwd, tc.title).select_from(
    tt.join(ut, tc.name == uc.name))
list(db.execute(query))
```

--------------------------------

### Patch SQLAlchemy for SQL Execution (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Patches `CursorResult` to add a `tuples` method for fetching results as named tuples, and patches `Connection` and `MetaData` to add `sql` methods for executing raw SQL strings and returning results.

```python
#| export
@patch
def tuples(self:CursorResult, nm='Row'):
    "Get all results as named tuples"
    rs = self.mappings().fetchall()
    nt = namedtuple(nm, self.keys())
    return [nt(**o) for o in rs]

@patch
def sql(self:Connection, statement, nm='Row', *args, **kwargs):
    "Execute `statement` string and return results (if any)"
    if isinstance(statement,str): statement=text(statement)
    t = self.execute(statement)
    try: return t.tuples()
    except ResourceClosedError: pass # statement didn't return anything

@patch
def sql(self:MetaData, statement, *args, **kwargs):
    "Execute `statement` string and return `DataFrame` of results (if any)"
    return self.conn.sql(statement, *args, **kwargs)
```

--------------------------------

### nbdev Export Command (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Includes the nbdev command used to export the notebook code to a Python module.

```python
#|hide
import nbdev; nbdev.nbdev_export()
```

--------------------------------

### Import ResourceClosedError (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Imports a specific exception class from SQLAlchemy.

```python
from sqlalchemy.exc import ResourceClosedError
```

--------------------------------

### Adding __call__ Method for Querying to DBTable - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Adds a `__call__` method to `DBTable` using `@patch`, allowing the instance to be called like a function for querying. It constructs a SQLAlchemy SELECT query based on parameters like `where`, `where_args`, `order_by`, `limit`, `offset`, and `select`, executes it, and returns a list of data objects.

```python
#| export
@patch
def __call__(
    self:DBTable,
    where:str|None=None,  # SQL where fragment to use, for example `id > ?`
    where_args: Iterable|dict|NoneType=None, # Parameters to use with `where`; iterable for `id>?`, or dict for `id>:id`
    order_by: str|None=None, # Column or fragment of SQL to order by
    limit:int|None=None, # Number of rows to limit to
    offset:int|None=None, # SQL offset
    select:str = "*", # Comma-separated list of columns to select
    **kw  # Combined with `where_args`
)->list:  # List of returned objects
    "Result of `select` query on the table"
    if select == "*": query = sa.select(self.table)
    else:
        columns = [sa.text(col.strip()) for col in select.split(',')]
        query = sa.select(*columns).select_from(self.table)
    if where_args: kw = {**kw, **where_args}
    xtra = self.xtra_id
    if xtra:
        xw = ' and '.join(f'"{k}" = {v!r}' for k,v in xtra.items())
        where = f'{xw} and {where}' if where else xw
    if where:
        where = sa.text(where)
        if kw: where = where.bindparams(**kw)
        query = query.where(where)
    if order_by: query = query.order_by(sa.text(order_by))
    if limit is not None: query = query.limit(limit)
    if offset is not None: query = query.offset(offset)
    rows = self.conn.execute(query).all()
    return [self.cls(**row._asdict()) for row in rows]
```

--------------------------------

### Defining Column Helper Functions - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Defines a mapping from Python types to SQLAlchemy types and a helper function `_column` to create SQLAlchemy `Column` objects with a given name, type, and primary key status.

```python
#| export
_type_map = {int: sa.Integer, str: sa.String, bool: sa.Boolean}
def _column(name, typ, primary=False): return sa.Column(name, _type_map[typ], primary_key=primary)
```

--------------------------------

### Fetch Record by Primary Key

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Fetch a single record from a table directly by its primary key using square bracket notation.

```python
user = users['rlt']
user
```

--------------------------------

### Insert Objects into Tables

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Insert the created objects into their corresponding fastsql tables using the insert method.

```python
users.insert(u0)
users.insert(u1)
todos.insert(t0)
todos.insert(t1)
todos.insert(t2)
```

--------------------------------

### Adding schema Method to Database - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Adds a `schema` method to the `Database` class using `@patch`. This method inspects the database engine and returns a formatted string listing all tables and their columns, indicating primary keys.

```python
#| export
@patch
def schema(self:Database):
    "Show all tables and columns"
    inspector = sa.inspect(self.engine)
    res = ''
    for table_name in inspector.get_table_names():
        res += f"Table: {table_name}\n"
        pk_cols = inspector.get_pk_constraint(table_name)['constrained_columns']
        for column in inspector.get_columns(table_name):
            pk_marker = '*' if column['name'] in pk_cols else '-'
            res += f"  {pk_marker} {column['name']}: {column['type']}\n"
    return res
```

--------------------------------

### Updating Record and Fetching in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Modifies a retrieved user object, updates the corresponding record in the database using `update()`, and then fetches the updated record to verify.

```python
user.pwd = 'baz'
users.update(user)
users['rlt']
```

--------------------------------

### Defining Data Models for FastSQL Tables

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Defines simple Python classes (`User`, `Todo`, `Student`) using type hints, intended to represent database table schemas. These classes will be used by FastSQL to interact with SQLAlchemy tables, mapping class attributes to table columns.

```python
class User: name:str; pwd:str
class Todo: title:str; name:str; id:int; done:bool=False; details:str=''
class Student: id:int; grad_year:int; name:str
```

--------------------------------

### Fetching Record by Primary Key in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Retrieves a single record from the `users` table using the primary key ('rlt') via dictionary-like access (`users['rlt']`).

```python
user = users['rlt']
user
```

--------------------------------

### Querying All Records from Table in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Calls the `users` table object like a function (`users()`) to retrieve all records from the table.

```python
users()
```

--------------------------------

### Patch SQLAlchemy Metadata and Columns for Access (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Patches SQLAlchemy's `MetaData` and `ReadOnlyColumnCollection` to improve usability by adding table and column names to `__dir__` and enabling attribute-style access for tables on `MetaData`.

```python
#| export
@patch
def __dir__(self:MetaData): return self._orig___dir__() + list(self.tables)

@patch
def __dir__(self:ReadOnlyColumnCollection): return self._orig___dir__() + self.keys()

def _getattr_(self, n):
    if n[0]=='_': raise AttributeError
    if n in self.tables: return self.tables[n]
    raise AttributeError

MetaData.__getattr__ = _getattr_
```

--------------------------------

### Query All Records from Table

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Query all records from a table by calling the table object like a function. This uses the DBTable.__call__ method.

```python
users()
```

--------------------------------

### Adding _pk_where Helper Method to DBTable - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Adds a private helper method `_pk_where` to `DBTable` using `@patch`. This method constructs a SQLAlchemy `and_` condition for filtering based on the table's primary key(s) and any `xtra_id` values.

```python
#| export
@patch
def _pk_where(self:DBTable, meth,key):
    if not isinstance(key,tuple): key = (key,)
    xtra = self.xtra_id
    pkv = zip(self.pks, key + tuple(xtra.values()))
    cond = sa.and_(*[col==val for col,val in pkv])
```

--------------------------------

### Defining DBTable Class - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Defines the `DBTable` class, which represents a connection to a SQLAlchemy table. It handles table creation if needed and provides properties for accessing table metadata, primary keys, and the database connection.

```python
#| export
class DBTable:
    "A connection to a SQLAlchemy table, created if needed"
    def __init__(self, table: sa.Table, database: Database, cls):
        self.table,self.db,self.cls,self.xtra_id = table,database,cls,{}
        table.create(self.db.engine, checkfirst=True)

    def __repr__(self) -> str: return self.table.name
    
    @property
    def t(self)->tuple: return self.table,self.table.c
    
    @property
    def pks(self)-> tuple: return tuple(self.table.primary_key) + tuple(self.table.c[o] for o in self.xtra_id.keys())
    
    @property
    def conn(self): return self.db.conn

    def xtra(self, **kwargs):
        "Set `xtra_id`"
        self.xtra_id = kwargs
```

--------------------------------

### Checking Table Existence - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Calls the `exists` method on a `DBTable` instance (`users`) to check if the corresponding table is present in the database.

```python
users.exists()
```

--------------------------------

### Access SQLAlchemy Metadata

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Access the underlying SQLAlchemy MetaData object from the fastsql database instance.

```python
dbm = db.meta
```

--------------------------------

### Define Python Classes for Tables

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Define Python classes (optionally dataclasses) that represent the schema for database tables.

```python
class User: name:str; pwd:str
class Todo: title:str; name:str; id:int=None; done:bool=False; details:str=''
```

--------------------------------

### Querying Records with WHERE Clause in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Queries the `todos` table, filtering records where the `name` column matches the provided parameter `:name` using a `where` clause.

```python
todos(where="name = :name", name="jph")
```

--------------------------------

### Querying Records with LIKE Clause in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Queries the `todos` table, filtering records where the `title` column matches the pattern "%% it%%" using the LIKE operator in a `where` clause.

```python
todos(where="title LIKE :title", title="%% it%%")
```

--------------------------------

### Checking Table Existence in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Checks if the `users` table exists in the database using the `exists()` method of the `DBTable` object.

```python
users.exists()
```

--------------------------------

### Accessing SQLAlchemy Metadata in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Retrieves the underlying SQLAlchemy `MetaData` object from the FastSQL database instance using the `.meta` attribute.

```python
dbm = db.meta
```

--------------------------------

### Define NotFoundError Exception (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Defines a custom exception `NotFoundError` used to signal when a requested database item is not found.

```python
#| export
class NotFoundError(Exception): pass
```

--------------------------------

### Access SQLAlchemy Table and Columns

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Access the underlying SQLAlchemy Table and Column objects from a fastsql DBTable instance using the '.t' attribute.

```python
ut,uc = users.t
tt,tc = todos.t
```

--------------------------------

### Query Records with LIKE Clause

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Query records from a table using a LIKE clause in the WHERE statement for pattern matching. This uses the DBTable.__call__ method.

```python
todos(where="title LIKE :title", title="%% it%%")
```

--------------------------------

### Patch SQLAlchemy Metadata for Close (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Patches the `MetaData` object to add a `close` method, providing a convenient way to close the underlying database connection.

```python
#| export
@patch
def close(self:MetaData):
    "Close the connection"
    self.conn.close()
```

--------------------------------

### Query Records with WHERE Clause

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Query records from a table using a WHERE clause to filter results, passing parameters as keyword arguments. This uses the DBTable.__call__ method.

```python
todos(where="name = :name", name="jph")
```

--------------------------------

### Check if Table Exists

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Check if a specific table exists in the database.

```python
users.exists()
```

--------------------------------

### Accessing SQLAlchemy Table and Column Objects in Python

Source: https://github.com/answerdotai/fastsql/blob/main/index.ipynb

Retrieves the underlying SQLAlchemy `Table` and column collection objects from the FastSQL `DBTable` instances using the `.t` attribute.

```python
ut,uc = users.t
tt,tc = todos.t
```

--------------------------------

### Adding insert Method to DBTable - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Adds an `insert` method to the `DBTable` class using `@patch`. This method takes an object, converts it to a dictionary (including `xtra_id`), inserts it into the table using SQLAlchemy, commits the transaction, and returns the inserted object populated with database-generated values (like auto-incremented IDs).

```python
#| export
@patch
def insert(self:DBTable, obj):
    "Insert an object into this table, and return it"
    d = {**asdict(obj), **self.xtra_id}
    result = self.conn.execute(sa.insert(self.table).values(**d).returning(*self.table.columns))
    row = result.one()  # Consume the result set
    self.conn.commit()
    return self.cls(**row._asdict())
```

--------------------------------

### Implement DBTable Update (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Patches the `DBTable` class to implement an `update` method, allowing modification of an existing row based on primary key and provided data. Commits the changes and returns the updated row.

```python
#| export
@patch
def update(self:DBTable, obj=None, **kw):
    d = {**asdict(obj or {}), **kw, **self.xtra_id}
    pks = tuple(d[k.name] for k in self.table.primary_key)
    qry = self._pk_where('update', pks).values(**d).returning(*self.table.columns)
    result = self.conn.execute(qry)
    row = result.one()
    self.conn.commit()
    return self.cls(**row._asdict())
```

--------------------------------

### Implement DBTable Delete (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Patches the `DBTable` class to implement a `delete` method, removing a row based on its primary key. Commits the change and returns the number of rows deleted.

```python
#| export
@patch
def delete(self:DBTable, key):
    "Delete item with PK `key` and return count deleted"
    result = self.conn.execute(self._pk_where('delete', key))
    self.conn.commit()
    return result.rowcount
```

--------------------------------

### Update a Record

Source: https://github.com/answerdotai/fastsql/blob/main/README.md

Modify an object representing a record and then update the corresponding record in the table using the update method.

```python
user.pwd = 'baz'
users.update(user)
users['rlt']
```

--------------------------------

### Adding exists Method to DBTable - Python

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Adds an `exists` method to the `DBTable` class using `@patch`. This method checks if the table associated with the `DBTable` instance currently exists in the connected database.

```python
#| export
@patch
def exists(self:DBTable):
    "Check if this table exists in the DB"
    return sa.inspect(self.db.engine).has_table(self.table.name)
```

--------------------------------

### Implement DBTable Contains (Python)

Source: https://github.com/answerdotai/fastsql/blob/main/00_core.ipynb

Patches the `DBTable` class to implement the `__contains__` method, allowing checking for the existence of an item by its primary key using the `in` operator. Handles both single and compound primary keys.

```python
#| export
@patch
def __contains__(self:DBTable,
                 pk_values: Union[list, tuple, str, int] # A single value, or a tuple of values for tables that have a compound primary key
    ) -> bool:
    "Is the item with the specified primary key value in this table?"
    if isinstance(pk_values, (str, int)): pk_values = (pk_values,)
    try:
        self[pk_values]
        return True
    except NotFoundError:
        return False
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.