Sqlite via Sqlalchemy (with aiosqlite)¶
Description:
- Work with data in Sqlite
Resources:
- [GitHub] https://github.com/sqlalchemy/sqlalchemy
- [Docs] https://docs.sqlalchemy.org/en/20/
- [Tutorial] https://docs.sqlalchemy.org/en/20/tutorial/index.html
- [Examples] https://github.com/sqlalchemy/sqlalchemy/tree/main/examples
- [Docs - Aiosqlite] https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#module-sqlalchemy.dialects.sqlite.aiosqlite
Source and license:
- Some of these examples are adapted from the SQLAlchemy documentation (asyncio extension and ORM tutorial) (https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.html), Copyright 2005-2026 SQLAlchemy authors and contributors, licensed under MIT. That code stays under its own license; see THIRD_PARTY_NOTICES.md.
Example 1: sqlalchemy with aiosqlite¶
Create a listener for a sqlite database using sqlalchemy
In [ ]:
Copied!
import asyncio
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.asyncio import create_async_engine
from pathlib import Path
from fsspec import filesystem
# Create a new dir
Path("/tmp/ganymede_demo/sqlite").mkdir(parents=True, exist_ok=True)
# Get the zip archive from URL
libarchive_fs = filesystem("libarchive", fo="https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip")
# Download it locally
libarchive_fs.get(lpath="/tmp/ganymede_demo/sqlite/chinook.db", rpath="chinook.db")
# Create the dsn
db_name = '/tmp/ganymede_demo/sqlite/chinook.db'
dsn = f"sqlite+aiosqlite:///{db_name}"
meta = MetaData()
t1 = Table(
"t1", meta, Column("id", Integer, primary_key=True), Column("name", String)
)
async def async_main():
# engine is an instance of AsyncEngine
engine = create_async_engine(
dsn,
echo=True,
)
# conn is an instance of AsyncConnection
async with engine.begin() as conn:
print("Connected to database")
# to support SQLAlchemy DDL methods as well as legacy functions, the
# AsyncConnection.run_sync() awaitable method will pass a "sync"
# version of the AsyncConnection object to any synchronous method,
# where synchronous IO calls will be transparently translated for
# await.
await conn.run_sync(meta.drop_all)
await conn.run_sync(meta.create_all)
# for normal statement execution, a traditional "await execute()"
# pattern is used.
await conn.execute(
t1.insert(), [{"name": "some name 1"}, {"name": "some name 2"}]
)
async with engine.connect() as conn:
print("Connected to database")
# the default result object is the
# sqlalchemy.engine.Result object
result = await conn.execute(t1.select())
# the results are buffered so no await call is necessary
# for this case.
print(result.fetchall())
# for a streaming result that buffers only segments of the
# result at time, the AsyncConnection.stream() method is used.
# this returns a sqlalchemy.ext.asyncio.AsyncResult object.
async_result = await conn.stream(t1.select())
# this object supports async iteration and awaitable
# versions of methods like .all(), fetchmany(), etc.
async for row in async_result:
print(row)
asyncio.run(async_main())
import asyncio
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.asyncio import create_async_engine
from pathlib import Path
from fsspec import filesystem
# Create a new dir
Path("/tmp/ganymede_demo/sqlite").mkdir(parents=True, exist_ok=True)
# Get the zip archive from URL
libarchive_fs = filesystem("libarchive", fo="https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip")
# Download it locally
libarchive_fs.get(lpath="/tmp/ganymede_demo/sqlite/chinook.db", rpath="chinook.db")
# Create the dsn
db_name = '/tmp/ganymede_demo/sqlite/chinook.db'
dsn = f"sqlite+aiosqlite:///{db_name}"
meta = MetaData()
t1 = Table(
"t1", meta, Column("id", Integer, primary_key=True), Column("name", String)
)
async def async_main():
# engine is an instance of AsyncEngine
engine = create_async_engine(
dsn,
echo=True,
)
# conn is an instance of AsyncConnection
async with engine.begin() as conn:
print("Connected to database")
# to support SQLAlchemy DDL methods as well as legacy functions, the
# AsyncConnection.run_sync() awaitable method will pass a "sync"
# version of the AsyncConnection object to any synchronous method,
# where synchronous IO calls will be transparently translated for
# await.
await conn.run_sync(meta.drop_all)
await conn.run_sync(meta.create_all)
# for normal statement execution, a traditional "await execute()"
# pattern is used.
await conn.execute(
t1.insert(), [{"name": "some name 1"}, {"name": "some name 2"}]
)
async with engine.connect() as conn:
print("Connected to database")
# the default result object is the
# sqlalchemy.engine.Result object
result = await conn.execute(t1.select())
# the results are buffered so no await call is necessary
# for this case.
print(result.fetchall())
# for a streaming result that buffers only segments of the
# result at time, the AsyncConnection.stream() method is used.
# this returns a sqlalchemy.ext.asyncio.AsyncResult object.
async_result = await conn.stream(t1.select())
# this object supports async iteration and awaitable
# versions of methods like .all(), fetchmany(), etc.
async for row in async_result:
print(row)
asyncio.run(async_main())