SQLAlchemy SQLite Dialect

https://docs.sqlalchemy.org/en/20/dialects/sqlite.html
Admin
SQLAlchemy SQLite Dialect provides comprehensive documentation on integrating and working with the...
Tokens:9,852
Snippets:124
Trust Score:9.7
Update:2 months ago
Show doc for...
Tokens:
Raw
### SQLCipher Connection Examples

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Examples of connecting using standard and absolute file paths, as well as passing encryption pragmas via the query string.

```python
e = create_engine("sqlite+pysqlcipher://:testing@/foo.db")
```

```python
e = create_engine("sqlite+pysqlcipher://:testing@//path/to/foo.db")
```

```python
e = create_engine(
    "sqlite+pysqlcipher://:testing@/foo.db?cipher=aes-256-cfb&kdf_iter=64000"
)
```

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

### Example TIME string representation

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

An example of the default string representation for a time object in SQLite.

```text
12:05:57.10558
```

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

### Define SQLite Connection Strings

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Examples of various connection string formats for SQLite databases, including relative, absolute, Windows, and in-memory configurations.

```python
# relative path
e = create_engine("sqlite:///path/to/database.db")
```

```python
# absolute path
e = create_engine("sqlite:////path/to/database.db")
```

```python
# absolute path on Windows
e = create_engine("sqlite:///C:\\path\\to\\database.db")
```

```python
# in-memory database (note three slashes)
e = create_engine("sqlite:///:memory:")
# also in-memory database
e2 = create_engine("sqlite://")
```

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

### Create Async Engine with Aiosqlite

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Initialize an asynchronous engine using the aiosqlite dialect.

```python
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine("sqlite+aiosqlite:///filename")
```

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

### Configure SQLite URI Connections

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Demonstrates using driver-level URIs with 'uri=true' to pass additional SQLite-specific arguments via the query string.

```python
e = create_engine("sqlite:///file:path/to/database?mode=ro&uri=true")
```

```python
e = create_engine(
    "sqlite:///file:path/to/database?"
    "check_same_thread=true&timeout=10&mode=ro&nolock=1&uri=true"
)
```

```python
sqlite3.connect(
    "file:path/to/database?mode=ro&nolock=1",
    check_same_thread=True,
    timeout=10,
    uri=True,
)
```

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

### Enable Foreign Key Support via Events

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the Engine connect event to execute PRAGMA foreign_keys=ON for every new connection.

```python
from sqlalchemy.engine import Engine
from sqlalchemy import event


@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    # the sqlite3 driver will not set PRAGMA foreign_keys
    # if autocommit=False; set to True temporarily
    ac = dbapi_connection.autocommit
    dbapi_connection.autocommit = True

    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

    # restore previous autocommit setting
    dbapi_connection.autocommit = ac
```

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

### Configure SQLite Table Options

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use these parameters within the Table constructor to enable specific SQLite features.

```python
Table("some_table", metadata, ..., sqlite_with_rowid=False)
```

```python
Table("some_table", metadata, ..., sqlite_strict=True)
```

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

### Connect to SQLite using pysqlite

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

The standard connection string format for SQLite databases using the pysqlite driver.

```text
sqlite+pysqlite:///file_path
```

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

### Configure DATE Type

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Customize the storage format and parsing regex for the DATE type.

```python
"%(year)04d-%(month)02d-%(day)02d"
```

```python
2011-03-15
```

```python
import re
from sqlalchemy.dialects.sqlite import DATE

d = DATE(
	storage_format="%(month)02d/%(day)02d/%(year)04d",
	regexp=re.compile("(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)"),
)
```

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

### Use RETURNING with INSERT, UPDATE, and DELETE

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Utilize the returning() method on DML statements to retrieve column values after execution.

```python
# INSERT..RETURNING
result = connection.execute(
    table.insert().values(name="foo").returning(table.c.col1, table.c.col2)
)
print(result.all())

# UPDATE..RETURNING
result = connection.execute(
    table.update()
    .where(table.c.name == "foo")
    .values(name="bar")
    .returning(table.c.col1, table.c.col2)
)
print(result.all())

# DELETE..RETURNING
result = connection.execute(
    table.delete()
    .where(table.c.name == "foo")
    .returning(table.c.col1, table.c.col2)
)
print(result.all())
```

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

### Configure DATETIME Type

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Customize the storage format and parsing regex for the DATETIME type.

```python
"%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
```

```python
2021-03-15 12:05:57.105542
```

```python
import re
from sqlalchemy.dialects.sqlite import DATETIME

dt = DATETIME(
	storage_format=(
		"%(year)04d/%(month)02d/%(day)02d %(hour)02d:%(minute)02d:%(second)02d"
	),
	regexp=r"(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)",
)
```

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

### Register User-Defined Functions with Event Listeners

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Attach Python functions to SQLite connections using the 'connect' event listener to ensure they are available across pooled connections.

```python
from sqlalchemy import create_engine
from sqlalchemy import event
from sqlalchemy import text


def udf():
    return "udf-ok"


engine = create_engine("sqlite:///./db_file")


@event.listens_for(engine, "connect")
def connect(conn, rec):
    conn.create_function("udf", 0, udf)


for i in range(5):
    with engine.connect() as conn:
        print(conn.scalar(text("SELECT UDF()")))
```

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

### Define Aiosqlite Connection String

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

The standard connection string format for the aiosqlite dialect.

```text
sqlite+aiosqlite:///file_path
```

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

### Configure native datetime parsing for SQLite

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use native_datetime=True and connect_args to enable pysqlite's PARSE_DECLTYPES and PARSE_COLNAMES options.

```python
engine = create_engine(
    "sqlite://",
    connect_args={
        "detect_types": sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
    },
    native_datetime=True,
)
```

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

### Emit BEGIN manually using event hooks

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Disables SQLite's automatic transaction control and emits BEGIN manually via event hooks for cross-compatibility.

```python
from sqlalchemy import create_engine, event

engine = create_engine("sqlite:///myfile.db")


@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable sqlite3's emitting of the BEGIN statement entirely.
    dbapi_connection.isolation_level = None


@event.listens_for(engine, "begin")
def do_begin(conn):
    # emit our own BEGIN.   sqlite3 still emits COMMIT/ROLLBACK correctly
    conn.exec_driver_sql("BEGIN")
```

```python
from sqlalchemy import create_engine, event
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine("sqlite+aiosqlite:///myfile.db")


@event.listens_for(engine.sync_engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable aiosqlite's emitting of the BEGIN statement entirely.
    dbapi_connection.isolation_level = None


@event.listens_for(engine.sync_engine, "begin")
def do_begin(conn):
    # emit our own BEGIN.  aiosqlite still emits COMMIT/ROLLBACK correctly
    conn.exec_driver_sql("BEGIN")
```

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

### Import SQLite Dialect Types

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Import valid SQLite types directly from the dialect module.

```python
from sqlalchemy.dialects.sqlite import (
	BLOB,
	BOOLEAN,
	CHAR,
	DATE,
	DATETIME,
	DECIMAL,
	FLOAT,
	INTEGER,
	NUMERIC,
	JSON,
	SMALLINT,
	TEXT,
	TIME,
	TIMESTAMP,
	VARCHAR,
)
```

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

### Configure Shared-Cache Memory Database

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Enables multiple connections to access the same in-memory database using URI mode.

```python
engine = create_engine("sqlite:///file::memory:?cache=shared&uri=true")
```

```python
engine = create_async_engine(
    "sqlite+aiosqlite:///file::memory:?cache=shared&uri=true"
)
```

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

### Create Independent Shared-Cache Databases

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Uses distinct names in the URI to maintain separate in-memory databases within the same process.

```python
engine_a = create_engine(
    "sqlite:///file:db_a?mode=memory&cache=shared&uri=true"
)
engine_b = create_engine(
    "sqlite:///file:db_b?mode=memory&cache=shared&uri=true"
)
```

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

### Custom SQLCipher Driver Integration

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use a custom compatible DBAPI module by passing it to the module parameter of create_engine.

```python
import sqlcipher_compatible_driver

from sqlalchemy import create_engine

e = create_engine(
    "sqlite+pysqlcipher://:password@/dbname.db",
    module=sqlcipher_compatible_driver,
)
```

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

### Configure Connection Pooling for Temporary Tables

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Maintains a single connection per thread or globally to ensure temporary tables persist across checkouts.

```python
# maintain the same connection per thread
from sqlalchemy.pool import SingletonThreadPool

engine = create_engine("sqlite:///mydb.db", poolclass=SingletonThreadPool)


# maintain the same connection across all threads
from sqlalchemy.pool import StaticPool

engine = create_engine("sqlite:///mydb.db", poolclass=StaticPool)
```

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

### Configure StaticPool for Serialized Access

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Forces all checkouts to return the same connection, suitable only for single-threaded test suites.

```python
from sqlalchemy.pool import StaticPool

engine = create_engine(
    "sqlite://",
    connect_args={"check_same_thread": False},
    poolclass=StaticPool,
)
```

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

### Configure Shared-Cache Memory Database

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use a shared-cache URI for in-memory databases to allow multiple concurrent coroutines with independent transaction states.

```python
engine = create_async_engine(
    "sqlite+aiosqlite:///file::memory:?cache=shared&uri=true"
)
```

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

### Handle Mixed String and Binary Data

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use this custom TypeDecorator to normalize mixed string and binary data retrieved from SQLite columns into bytes.

```python
from sqlalchemy import String
from sqlalchemy import TypeDecorator


class MixedBinary(TypeDecorator):
    impl = String
    cache_ok = True

    def process_result_value(self, value, dialect):
        if isinstance(value, str):
            value = bytes(value, "utf-8")
        elif value is not None:
            value = bytes(value)

        return value
```

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

### Pysqlcipher Connection String Format

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

The standard format for connecting to a SQLCipher-encrypted SQLite database.

```text
sqlite+pysqlcipher://:passphrase@/file_path[?kdf_iter=<iter>]
```

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

### Customize TIME storage format

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Configure the TIME type with a custom storage format and regular expression for parsing incoming data.

```python
import re
from sqlalchemy.dialects.sqlite import TIME

t = TIME(
    storage_format="%(hour)02d-%(minute)02d-%(second)02d-%(microsecond)06d",
    regexp=re.compile("(\d+)-(\d+)-(\d+)-(?:-(\d+))?"),
)
```

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

### Enable SQLite Regular Expression Support

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Implements the REGEXP operator in SQLite by registering a Python function using the create_function hook.

```python
def regexp(a, b):
    return re.search(a, b) is not None


sqlite_connection.create_function(
    "regexp",
    2,
    regexp,
)
```

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

### Define SET Clause for DO UPDATE

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Provide a dictionary to the set_ parameter to define the values used during an update conflict.

```python
>>> stmt = insert(my_table).values(id="some_id", data="inserted value")

>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=["id"], set_=dict(data="updated value")
... )

>>> print(do_update_stmt)

INSERT INTO my_table (id, data) VALUES (?, ?)
ON CONFLICT (id) DO UPDATE SET data = ?
```

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

### Define a Partial Index

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the sqlite_where argument within an Index definition to create a partial index with a WHERE clause.

```python
tbl = Table("testtbl", m, Column("data", Integer))
idx = Index(
    "test_idx1",
    tbl.c.data,
    sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10),
)
```

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

### Demonstrate SQLite Dotted Column Name Bug

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Illustrates the inconsistent column naming behavior in older SQLite versions when using UNION queries.

```python
import sqlite3

assert sqlite3.sqlite_version_info < (
    3,
    10,
    0,
), "bug is fixed in this version"

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

cursor.execute("create table x (a integer, b integer)")
cursor.execute("insert into x (a, b) values (1, 1)")
cursor.execute("insert into x (a, b) values (2, 2)")

cursor.execute("select x.a, x.b from x")
assert [c[0] for c in cursor.description] == ["a", "b"]

cursor.execute("""
    select x.a, x.b from x where a=1
    union
    select x.a, x.b from x where a=2
    """)
assert [c[0] for c in cursor.description] == ["a", "b"], [
    c[0] for c in cursor.description
]
```

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

### Rendered Partial Index DDL

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

The resulting SQL generated by the partial index definition.

```sql
CREATE INDEX test_idx1 ON testtbl (data)
WHERE data > 5 AND data < 10
```

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

### Define primary key constraint with FAIL algorithm

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the sqlite_on_conflict_primary_key parameter on a Column object to apply conflict resolution to an inline primary key.

```python
some_table = Table(
    "some_table",
    metadata,
    Column(
        "id",
        Integer,
        primary_key=True,
        sqlite_on_conflict_primary_key="FAIL",
    ),
)
```

```sql
CREATE TABLE some_table (
    id INTEGER NOT NULL,
    PRIMARY KEY (id) ON CONFLICT FAIL
)
```

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

### Insert.on_conflict_do_nothing

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Specifies a DO NOTHING action for the ON CONFLICT clause in a SQLite INSERT statement.

```APIDOC
## Insert.on_conflict_do_nothing(index_elements=None, index_where=None)

### Description
Specifies a DO NOTHING action for ON CONFLICT clause.

### Parameters
- **index_elements** (Iterable[Column | str | DDLConstraintColumnRole] | None) - Optional - A sequence of column names or objects used to infer a target index or unique constraint.
- **index_where** (WhereHavingRole | None) - Optional - Additional WHERE criterion for inferring a conditional target index.
```

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

### Perform Upsert with DO UPDATE and DO NOTHING

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the insert() function from sqlalchemy.dialects.sqlite to generate statements that handle unique constraint conflicts.

```python
>>> from sqlalchemy.dialects.sqlite import insert

>>> insert_stmt = insert(my_table).values(
...     id="some_existing_id", data="inserted value"
... )

>>> do_update_stmt = insert_stmt.on_conflict_do_update(
...     index_elements=["id"], set_=dict(data="updated value")
... )

>>> print(do_update_stmt)

INSERT INTO my_table (id, data) VALUES (?, ?)
ON CONFLICT (id) DO UPDATE SET data = ?

>>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(index_elements=["id"])

>>> print(do_nothing_stmt)

INSERT INTO my_table (id, data) VALUES (?, ?)
ON CONFLICT (id) DO NOTHING
```

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

### Enable AUTOINCREMENT Keyword

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the sqlite_autoincrement=True flag within the Table construct to explicitly render the AUTOINCREMENT keyword in DDL.

```python
Table(
    "sometable",
    metadata,
    Column("id", Integer, primary_key=True),
    sqlite_autoincrement=True,
)
```

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

### Default TIME storage format

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

The default string format used by the SQLite TIME type to store time objects.

```text
%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d
```

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

### Enable modern SQLite transaction control via autocommit

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Sets the autocommit attribute to False at the connect level using connect_args for both synchronous and asynchronous engines.

```python
from sqlalchemy import create_engine

engine = create_engine(
    "sqlite:///myfile.db", connect_args={"autocommit": False}
)
```

```python
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "sqlite+aiosqlite:///myfile.db", connect_args={"autocommit": False}
)
```

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

### Accessing Dotted Column Names

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Demonstrates that even when keys are filtered, the original dotted names remain addressable in the row.

```python
>>> row = result.first()
>>> row["a"]
1
>>> row["x.a"]
1
>>> row["b"]
1
>>> row["x.b"]
1
```

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

### Enable driver-level AUTOCOMMIT for SQLite

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Configures the engine to use SQLAlchemy's driver-level autocommit by setting the isolation_level parameter to AUTOCOMMIT.

```python
eng = create_engine("sqlite:///myfile.db", isolation_level="AUTOCOMMIT")
```

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

### sqlalchemy.dialects.sqlite.insert(table)

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Constructs a SQLite-specific Insert object that supports additional SQLite features like ON CONFLICT.

```APIDOC
## sqlalchemy.dialects.sqlite.insert(table)

### Description
Constructs a sqlite-specific variant Insert construct.

### Parameters
- **table** (DMLTableArgument) - Required - The table to be inserted into.

### Returns
- **Insert** (sqlalchemy.dialects.sqlite.Insert) - The SQLite-specific Insert construct.
```

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

### Configure autocommit via PoolEvents.connect hook

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Sets the autocommit attribute on the dbapi_connection using an event hook. This approach is specific to the sqlite3 driver.

```python
from sqlalchemy import create_engine, event

engine = create_engine("sqlite:///myfile.db")


@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # enable autocommit=False mode
    dbapi_connection.autocommit = False
```

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

### Specify Conflict Target with Partial Index

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use index_elements and index_where to target specific unique constraints or partial indexes during an upsert.

```python
>>> stmt = insert(my_table).values(user_email="a@b.com", data="inserted data")

>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=[my_table.c.user_email],
...     index_where=my_table.c.user_email.like("%@gmail.com"),
...     set_=dict(data=stmt.excluded.data),
... )

>>> print(do_update_stmt)

INSERT INTO my_table (data, user_email) VALUES (?, ?)
ON CONFLICT (user_email)
WHERE user_email LIKE '%@gmail.com'
DO UPDATE SET data = excluded.data
```

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

### Persist SQL NULL with JSON type

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the null() construct to explicitly persist a SQL NULL value when using the JSON type.

```python
from sqlalchemy import null

conn.execute(table.insert(), {"data": null()})
```

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

### Define NOT NULL constraint with FAIL algorithm

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the sqlite_on_conflict_not_null parameter on a Column object to apply conflict resolution to a NOT NULL constraint.

```python
some_table = Table(
    "some_table",
    metadata,
    Column("id", Integer, primary_key=True),
    Column(
        "data", Integer, nullable=False, sqlite_on_conflict_not_null="FAIL"
    ),
)
```

```sql
CREATE TABLE some_table (
    id INTEGER NOT NULL,
    data INTEGER NOT NULL ON CONFLICT FAIL,
    PRIMARY KEY (id)
)
```

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

### Traceback of Dotted Column Name Bug

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

The assertion error resulting from the unexpected column names returned by the SQLite driver.

```text
Traceback (most recent call last):
  File "test.py", line 19, in <module>
    [c[0] for c in cursor.description]
AssertionError: ['x.a', 'x.b']
```

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

### Define UNIQUE constraint with IGNORE algorithm

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the sqlite_on_conflict parameter within a UniqueConstraint object to specify a conflict resolution algorithm.

```python
some_table = Table(
    "some_table",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("data", Integer),
    UniqueConstraint("id", "data", sqlite_on_conflict="IGNORE"),
)
```

```sql
CREATE TABLE some_table (
    id INTEGER NOT NULL,
    data INTEGER,
    PRIMARY KEY (id),
    UNIQUE (id, data) ON CONFLICT IGNORE
)
```

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

### Define column-level UNIQUE constraint with IGNORE

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Apply the sqlite_on_conflict_unique parameter to a Column object when using the unique=True flag.

```python
some_table = Table(
    "some_table",
    metadata,
    Column("id", Integer, primary_key=True),
    Column(
        "data", Integer, unique=True, sqlite_on_conflict_unique="IGNORE"
    ),
)
```

```sql
CREATE TABLE some_table (
    id INTEGER NOT NULL,
    data INTEGER,
    PRIMARY KEY (id),
    UNIQUE (data) ON CONFLICT IGNORE
)
```

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

### Use with_variant for Integer Affinity

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Apply with_variant to ensure a BigInteger type renders as INTEGER on SQLite, enabling autoincrement behavior for primary keys.

```python
table = Table(
    "my_table",
    metadata,
    Column(
        "id",
        BigInteger().with_variant(Integer, "sqlite"),
        primary_key=True,
    ),
)
```

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

### Insert.on_conflict_do_update

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Specifies a DO UPDATE SET action for the ON CONFLICT clause in a SQLite INSERT statement.

```APIDOC
## Insert.on_conflict_do_update(index_elements=None, index_where=None, set_=None, where=None)

### Description
Specifies a DO UPDATE SET action for ON CONFLICT clause.

### Parameters
- **index_elements** (Iterable[Column | str | DDLConstraintColumnRole] | None) - Optional - A sequence of column names or objects used to infer a target index or unique constraint.
- **index_where** (WhereHavingRole | None) - Optional - Additional WHERE criterion for inferring a conditional target index.
- **set_** (Mapping | ColumnCollection | None) - Optional - A mapping of columns to values to be updated.
- **where** (WhereHavingRole | None) - Optional - WHERE criterion for the update operation.
```

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

### Disable connection pooling for file-based SQLite

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the NullPool class to disable connection pooling, which can help resolve file locking issues.

```python
from sqlalchemy import NullPool

engine = create_engine("sqlite:///myfile.db", poolclass=NullPool)
```

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

### Disable Dotted Column Filtering per Engine

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Configure the engine to globally disable dotted column name filtering.

```python
engine = create_engine(
    "sqlite://", execution_options={"sqlite_raw_colnames": True}
)
```

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

### sqlalchemy.dialects.sqlite.TIME

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Represents a Python time object in SQLite using a string format.

```APIDOC
## sqlalchemy.dialects.sqlite.TIME

### Description
Represents a Python time object in SQLite using a string. The default storage format is '%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d'.

### Parameters
- **truncate_microseconds** (bool) - Optional - When True, microseconds are truncated from the time. Cannot be used with storage_format or regexp.
- **storage_format** (str) - Optional - Format string applied to the dict with keys hour, minute, second, and microsecond.
- **regexp** (re.Pattern) - Optional - Regular expression applied to incoming result rows to parse strings into time objects.
```

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

### Disable Dotted Column Filtering per Connection

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the sqlite_raw_colnames execution option to return unmodified column names.

```python
result = conn.execution_options(sqlite_raw_colnames=True).exec_driver_sql(
    """
    select x.a, x.b from x where a=1
    union
    select x.a, x.b from x where a=2
    """
)
assert result.keys() == ["x.a", "x.b"]
```

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

### Conditional Update on Conflict

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Limits rows updated during a conflict using the where parameter in on_conflict_do_update.

```python
>>> stmt = insert(my_table).values(
...     id="some_id", data="inserted value", author="jlh"
... )

>>> on_update_stmt = stmt.on_conflict_do_update(
...     index_elements=["id"],
...     set_=dict(data="updated value", author=stmt.excluded.author),
...     where=(my_table.c.status == 2),
... )
>>> print(on_update_stmt)

INSERT INTO my_table (id, data, author) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author
WHERE my_table.status = ?
```

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

### Skip Rows on Conflict

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Skips insertion when a conflict occurs using on_conflict_do_nothing, either by specifying index elements or globally.

```python
>>> stmt = insert(my_table).values(id="some_id", data="inserted value")
>>> stmt = stmt.on_conflict_do_nothing(index_elements=["id"])
>>> print(stmt)

INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING
```

```python
>>> stmt = insert(my_table).values(id="some_id", data="inserted value")
>>> stmt = stmt.on_conflict_do_nothing()
>>> print(stmt)

INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT DO NOTHING
```

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

### Override DDL Name with Compiler Extension

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Define a custom BigInteger subclass and use the @compiles decorator to force the DDL name to INTEGER specifically for the SQLite dialect.

```python
from sqlalchemy import BigInteger
from sqlalchemy.ext.compiler import compiles


class SLBigInteger(BigInteger):
    pass


@compiles(SLBigInteger, "sqlite")
def bi_c(element, compiler, **kw):
    return "INTEGER"


@compiles(SLBigInteger)
def bi_c(element, compiler, **kw):
    return compiler.visit_BIGINT(element, **kw)


table = Table(
    "my_table", metadata, Column("id", SLBigInteger(), primary_key=True)
)
```

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

### sqlalchemy.dialects.sqlite.JSON

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

The JSON type for SQLite, which adapts JSON operations to use the JSON1 extension functions.

```APIDOC
## sqlalchemy.dialects.sqlite.JSON

### Description
Constructs a JSON type for SQLite. This type supports persistence of JSON values and index operations by adapting them to the SQLite JSON1 extension.

### Parameters
- **none_as_null** (bool) - Optional - If True, persists the Python value None as a SQL NULL instead of the JSON encoding of null.
```

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

### SQLAlchemy Dotted Column Name Filtering

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

SQLAlchemy automatically filters out dots in column names to maintain predictable result keys.

```python
from sqlalchemy import create_engine

eng = create_engine("sqlite://")
conn = eng.connect()

conn.exec_driver_sql("create table x (a integer, b integer)")
conn.exec_driver_sql("insert into x (a, b) values (1, 1)")
conn.exec_driver_sql("insert into x (a, b) values (2, 2)")

result = conn.exec_driver_sql("select x.a, x.b from x")
assert result.keys() == ["a", "b"]

result = conn.exec_driver_sql("""
    select x.a, x.b from x where a=1
    union
    select x.a, x.b from x where a=2
    """)
assert result.keys() == ["a", "b"]
```

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

### Reference Excluded Values in Upsert

Source: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html

Use the excluded attribute on the insert object to reference the values that were proposed for insertion.

```python
>>> stmt = insert(my_table).values(
...     id="some_id", data="inserted value", author="jlh"
... )

>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=["id"],
...     set_=dict(data="updated value", author=stmt.excluded.author),
... )

>>> print(do_update_stmt)

INSERT INTO my_table (id, data, author) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author
```
Tokens:
Raw