Example 1¶
Download a remote sqlite database and create a new table in it
In [ ]:
Copied!
import asyncio
import aiosqlite
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")
async def create_table():
"""Create a new table on the database we downloaded"""
# Replace 'mydatabase.db' with your desired database name
db_name = '/tmp/ganymede_demo/sqlite/chinook.db'
async with aiosqlite.connect(db_name) as db:
# Perform database operations within a transaction
async with db.execute('PRAGMA foreign_keys=ON;'):
await db.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
''')
await db.commit()
# Run the create_table coroutine
asyncio.run(create_table())
import asyncio
import aiosqlite
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")
async def create_table():
"""Create a new table on the database we downloaded"""
# Replace 'mydatabase.db' with your desired database name
db_name = '/tmp/ganymede_demo/sqlite/chinook.db'
async with aiosqlite.connect(db_name) as db:
# Perform database operations within a transaction
async with db.execute('PRAGMA foreign_keys=ON;'):
await db.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
''')
await db.commit()
# Run the create_table coroutine
asyncio.run(create_table())
Example 2¶
Download a remote sqlite database, create a new table in it, insert data into the table, and read the data back
In [ ]:
Copied!
import asyncio
import aiosqlite
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")
async def execute_query():
db_name = '/tmp/ganymede_demo/sqlite/chinook.db'
async with aiosqlite.connect(db_name) as db:
# Perform database operations within a transaction
async with db.execute('PRAGMA foreign_keys=ON;'):
await db.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
''')
await db.execute("INSERT INTO users (username, email) VALUES ('example_user', 'user@example.com')")
await db.commit()
async with db.execute("SELECT * FROM users") as cursor:
async for row in cursor:
print(row)
asyncio.run(execute_query())
import asyncio
import aiosqlite
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")
async def execute_query():
db_name = '/tmp/ganymede_demo/sqlite/chinook.db'
async with aiosqlite.connect(db_name) as db:
# Perform database operations within a transaction
async with db.execute('PRAGMA foreign_keys=ON;'):
await db.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
''')
await db.execute("INSERT INTO users (username, email) VALUES ('example_user', 'user@example.com')")
await db.commit()
async with db.execute("SELECT * FROM users") as cursor:
async for row in cursor:
print(row)
asyncio.run(execute_query())