TursoDB: managed SQLite in Python
Introduction to SQLite and Turso
SQLite has always been one of the most widely used databases, but it has never achieved the same level of popularity as other databases, such as Postgres, MySQL (relational), or MongoDB (non-relational) and Redis (key-value).
SQLite is even included in the standard library of some of the most popular programming languages, such as Python and PHP. This widespread adoption of SQLite is evident in our daily lives, often without us even realizing it.
The Purpose of this Post
This brings me to the purpose of this post, which I created because I was surprised to find almost no tutorials on how to use Turso with the Python ecosystem. In contrast, I found numerous references on using Turso with the JavaScript/TypeScript ecosystem.
Getting Started with Turso
Okay enough with the blabbering, lets get down into code! Before creating your SQLite database, you need to install the Turso CLI. Open your terminal, as we will use the command-line interface for the first stage. You can refer to Turso's documentation for guidance: here.
Installing Turso CLI
Follow the documentation according to your operating system! I'm using Linux, so the process mentioned in this first step is specific to Linux. However, the rest of this article can be applied independently of your operating system.
curl -sSfL https://get.tur.so/install.sh | bash
Signing Up and Creating a Database
Next you need to sign up / log in to Turso. Once you've done that, go back to your terminal:
*Login:
turso auth login
*Create database:
turso db create db-name
*Retrieve database credentials:
turso db show --url db-name
turso db tokens create db-name
You need to save the credentials above for later use
Connecting to Turso with Python
The first stage is done, moving on to the next stage: connecting with Python. First part of the second stage we need to install some libraries:
pip install libsql-experimental python-dotenv
libsql : the SQL driver.
python-dotenv : to set up environment variables.
Setting Up Environment Variables
Make a .env file to store your DB credentials:
TURSO_DATABASE_DATABASE_URL=your_database_url
TURSO_AUTH_TOKEN=your_database_token
Connecting to the Database
Make a new python file: tursoDB.py
import os
import libsql_experimental as libsql
from dotenv import load_dotenv
load_dotenv()
url = os.environ.get("TURSO_DATABASE_DATABASE_URL")
auth_token = os.environ.get("TURSO_AUTH_TOKEN")
conn = libsql.connect("db-name.db", sync_url=url, auth_token=auth_token)
conn.sync()
The code is straightforward: import the necessary libraries, set up the environment variables, and finally connect to the database.
The next part of the code involves creating a table, inserting data, and then printing out the data (inside the same tursoDB.py). I'll create a stock portfolio table as an example.
## Create table
conn.execute('''CREATE TABLE IF NOT EXISTS portfolio (
portid INTEGER PRIMARY KEY AUTOINCREMENT,
stock TEXT,
price FLOAT,
volume INTEGER
);
''')
## Insert data into table
conn.execute('''
INSERT INTO portfolio (stock, price, volume)
VALUES
('TLKM', 10000, 500000),
('INCO', 13000, 800000),
('PGAS', 4000, 2000000),
('BBRI', 6000, 1200000),
('GOTO', 50, 52000000);
''')
conn.commit()
## Creating a view
conn.execute('''
CREATE VIEW portfolio_view AS
SELECT portid, stock, price, volume, price * volume AS value
FROM portfolio;
''')
query = conn.execute('SELECT * FROM portfolio_view;')
print("\nfrom TursoDB:")
print(query.fetchall())
Running the Python Program
Go back to your terminal and execute the python program:
python tursoDB.py
The output should be like this:
from TursoDB:
[(1, 'TLKM', 10000.0, 500000, 5000000000.0), (2, 'INCO', 13000.0, 800000, 10400000000.0), (3, 'PGAS ', 4000.0, 2000000, 8000000000.0), (4, 'BBRI', 6000.0, 1200000, 7200000000.0), (5, 'GOTO', 50.0, 52 000000, 2600000000.0)]
Note: I used a view instead of a direct query to request data from the database. This is because I needed to create a new column, "value," which is the result of multiplying "price" by "volume."
Each time we need the same set of data, we can simply use the "portfolio_view" as the database query and retrieve the same data – a neat trick!