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)