Python SDK Introduction
Install
pip install sqlitecloud
Basic Usage
We aim for full compatibility with the established sqlite3 API, with the primary distinction being that our driver connects to SQLite Cloud databases. This allows you to migrate your SQLite to the cloud while continuing to use your existing codebase.
import sqlitecloud
# Open the connection to SQLite Cloud
conn = sqlitecloud.connect("sqlitecloud://myhost.sqlite.cloud:8860?apikey=myapikey")
# You can autoselect the database during the connect call
# by adding the database name as path of the SQLite Cloud
# connection string, eg:
# conn = sqlitecloud.connect("sqlitecloud://myhost.sqlite.cloud:8860/mydatabase?apikey=myapikey")
db_name = "chinook.sqlite"
conn.execute(f"USE DATABASE {db_name}")
cursor = conn.execute("SELECT * FROM albums WHERE AlbumId = ?", (1, ))
result = cursor.fetchone()
print(result)
conn.close()
Using SQLite Cloud with Pandas
import io
import pandas as pd
import sqlitecloud
dfprices = pd.read_csv(
io.StringIO(
"""DATE,CURRENCY,PRICE
20230504,USD,201.23456
20230503,USD,12.34567
20230502,USD,23.45678
20230501,USD,34.56789"""
)
)
# Your SQLite Cloud connection string
conn = sqlitecloud.connect("sqlitecloud://myhost.sqlite.cloud:8860/mydatabase.sqlite?apikey=myapikey")
conn.executemany("DROP TABLE IF EXISTS ?", [("PRICES",)])
# Write the dataframe to the SQLite Cloud database as a table PRICES
dfprices.to_sql("PRICES", conn, index=False)
# Create the dataframe from the table PRICES on the SQLite Cloud database
df_actual_prices = pd.read_sql("SELECT * FROM PRICES", conn)
# Inspect the dataframe
print(df_actual_prices.head())
# Perform a simple query on the dataframe
query_result = df_actual_prices.query("PRICE > 50.00")
print(query_result)
Using SQLite Cloud with SQLAlchemy
pip install sqlalchemy-sqlitecloud
import sqlalchemy
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.dialects import registry
from sqlalchemy.orm import backref, declarative_base, relationship, sessionmaker
Base = declarative_base()
class Artist(Base):
__tablename__ = "artists"
ArtistId = Column("ArtistId", Integer, primary_key=True)
Name = Column("Name", String)
Albums = relationship("Album", backref=backref("artist"))
class Album(Base):
__tablename__ = "albums"
AlbumId = Column("AlbumId", Integer, primary_key=True)
ArtistId = Column("ArtistId", Integer, ForeignKey("artists.ArtistId"))
Title = Column("Title", String)
# Your SQLite Cloud connection string
connection_string = "sqlitecloud://myhost.sqlite.cloud:8860/mydatabase.sqlite?apikey=myapikey"
engine = sqlalchemy.create_engine(connection_string)
Session = sessionmaker(bind=engine)
session = Session()
name = "John Doe"
query = sqlalchemy.insert(Artist).values(Name=name)
result_insert = session.execute(query)
title = "The Album"
query = sqlalchemy.insert(Album).values(
ArtistId=result_insert.lastrowid, Title=title
)
session.execute(query)
query = (
sqlalchemy.select(Artist, Album)
.join(Album, Artist.ArtistId == Album.ArtistId)
.where(Artist.ArtistId == result_insert.lastrowid)
)
result = session.execute(query).fetchone()
print("Artist Name: " + result[0].Name)
print("Album Title: " + result[1].Title)