Connecting LibSQL database with Python
Introduction
LibSQL is an Open Contribution fork of SQLite. Open Contribution means that it allows suggestions and contributions from the community as opposed to SQLite which is open source but doesn’t accept community contributions.
Installation of LibSQL Client(s)
There are two libraries for LibSQL to interact with Python, the libsql-client and the libsql-experimental-python. The former is the recommended client as it is stable, whereas the latter is in development and has the latest features from the libsql database engine, however, it is compatible with the sqlite module.
There are differences in how to connect and fetch responses in each of them, but if you want to get started quickly you can safely turn with the libsql-client as it is recommended from the official documentation. If you want to connect an existing SQLite database to libsql, you can turn up the libsql-experimental-pyhon package.
We will explore both of them in this article. Let’s dive straight into the installation of the package.
LibSQL Client
So, Libsql-client is the Python package provided by Turso as a Python client to interact with a libsql/sqlite database.
Installation
The installation of a Python package is as simple as a pip install.
pip install libsql-client
Connecting to the database
Connecting to a libsql database is as simple as SQLite for the local database file. However, if you have a libsql database over the edge(Turso), you can use the API provided by SQLD to connect to that database.
Connecting Local database file
To connect to a simple libsql database file, you can either create a file as mydb.db or just move into the code straight away.
import libsql_client
client = libsql_client.create_client_sync("file:temp.db")
result = client.execute("SELECT 1;")
for row in result.rows:
print(row)
client.close()
OR
import libsql_client
with libsql_client.create_client_sync("file:temp.db") as client:
result = client.execute("SELECT 1;")
for row in result.rows:
print(row)
So, in the above code, the client object is a client of the libsql database. We use the create_client_sync method that accepts a few parameters url i.e. the URL of the database we want to connect to, and the auth_token which we will see when we want to connect to a database on the edge.
There is also create_client method which is an async method. You can turn up to this method if you are looking for async connections.
We have used file:temp.db i.e. to connect a local file in the current directory named temp.db . This method will return a Client object in this case a ClientSync object which is a wrapper around the Client object. We will be using the methods available for the Client class later while querying in detail. You can see that we have used the execute method from the client object.
Connecting to a cloud(turso) database
To connect to a libsql database on the edge i.e. turso you need to specify the auth token(JWT) in the create_client method.
You can create the token for accessing the database with the
turso db tokens create mydb-name
This command will create a JWT_TOKEN , store this token securely and this will be used to access the database which is hosted on the Turso cloud.
export JWT_TOKEN="YOURTOKEN"
OR
## Save it in the .env file
## JWT_TOKEN=YOURTOKEN
This will store the token in the environment variable and can be later used from the local environment.
import libsql_client
with libsql_client.create_client_sync(
"libsql://dbname-orgname.turso.io",
auth_token="secret.something.secret"
) as client:
result = client.execute("SELECT 1;")
for row in result.rows:
print(row)
The auth_token is the JWT Token that we created in the previous step, you can load the token as an Environment variable with the following code:
import os
JWT_TOKEN = os.environ.get("JWT_TOKEN")
## OR
# pip install python-dotenv
from dotenv import load_dotenv
load_dotenv()
JWT_TOKEN = os.environ.get("JWT_TOKEN")
This token can be used to authenticate the connection to the Turso database. The rest of the connection code remains as it is.
Running Queries and Fetching results
Now, that we have the connection, we can move into querying the database through the client connection.
LibSQL-Client (SQLite compatible)
Installation
The installation of the libsql-experimental-python package is simple with the pip install command with the name of the package.
pip install libsql-experimental
Now, we can move into connecting to the database with Python script.
Connecting to the database
Connecting to a libsql database is as simple as SQLite for the local database file.