Skip to main content

SQLite on Replit

How file persistence works​

File persistence lets us create programs that store our files and data between runs, so our data isn't lost whenever the program ends. This kind of program can update, read, and delete data that has not been created from code within the program. Data updated from within our program is still accessible after the program ends.

Let's take a look at how file persistence works with the Replit Database.

First we run our application, import the database, add data to our database, and declare some variables.

First run:​

# Import the Replit database
from replit import db

# Add a key-value pairing to the database
db[“key1”] = “value1”

# Access and print the value associated with the key just created
print(db[“key1”])

# Declare a variable that stores an integer outside of the database
my_variable = 100

# Print the variable
print(my_variable)

Output:​

value1
100

We can print both the data from our database and our variables, because both were created within the program.

However, if we were to run the program again without the code that adds data to our database and without the declaration of our variable, only the data from the database will print. The attempt to print the undefined variable results in an error.

Second run:​

from replit import db

# Print the value from the key-value pairing created on the previous run
print(db[“key1”])

# Attempt to print from the variable which was defined in the previous run
print(my_variable)

Output:​

value1
Traceback (most recent call last):
  File "main.py", line 3, in <module>
    print(my_variable)
NameError: name 'my_variable' is not defined

The data stored in the database persists between runs and doesn't need to be created again to be accessed. However, the variable's data did not persist, leading to the variable name being undefined, and we're given an error.

SQLite database​

To create more than one database and allow for the merging of data stored in a table format, we can look to the SQLite database to store, structure and manage data in a relational database.

SQLite structures data in a table format. We can set the number of columns in our table, the names of the columns, and data types we expect to store in them. However, SQLite allows for dynamic types within each column, meaning we can insert data of a different type than we had set for a particular column. We can create multiple databases and multiple tables within each database. SQLite requires no configuration, install, or login.

A SQLite database allows for complex operations, like joining tables from different databases, all while maintaining a connection to a single database.

The basic structure of our code when using SQLite is:

  1. Import SQLite3.
  2. Create and connect to a database.
  3. Perform CRUD operations.
  4. Commit the changes made to the database.
  5. Close the connection to the database.

Here is a look at what a basic structure of your code should be when using the SQLite database:

# Import
import sqlite3

# Create the database and connection
connection = sqlite3.connect("my_database")

# Create a table for storing data
connection.execute("CREATE TABLE IF NOT EXISTS My_library (id INTEGER PRIMARY KEY, author STRING, book STRING);")

# Perform CRUD operations

# Create
connection.execute("INSERT INTO My_library (id,author,book) "
             "VALUES (1, 'Steve Biko','I write what I like.')")

# Read
cursor_object = connection.execute("SELECT * FROM My_library")
print(cursor_object.fetchall())

# Update
connection.execute("UPDATE My_library SET book = 'I WRITE WHAT I LIKE' WHERE id = 1")

# Delete
connection.execute("DELETE from My_library WHERE id = 1;")

# Commit changes
connection.commit()

# Close the connection
connection.close()

Importing the database​

We import SQLite to our program using this line of code:

import sqlite3

Creating and connecting to a database​

We can create, name, and connect to our database using the sqlite3 module and .connect() method:

connection = sqlite3.connect("my_database")

Creating tables​

SQLite uses tables to structure our data. To create a table, we use the CREATE TABLE query. To ensure we do not create a table that already exists, we can use the CREATE TABLE IF NOT EXISTS query. In this CREATE TABLE query, we assign names for our table columns along with what type of data will be stored in that column and whether the data is a primary key:

connection.execute("CREATE TABLE IF NOT EXISTS My_library (id INTEGER PRIMARY KEY, author STRING, book STRING);")

Inserting data​

To insert data into our table, we use the INSERT query. This query requires the column names we are inserting into along with the values that we will insert into each of those columns:

connection.execute("INSERT INTO My_library (id,author,book) "
             "VALUES (1, 'Steve Biko','I write what I like.')")

Alternatively, we may want to insert data into our tables using an input from an external source. To do this, we can format our query information as a string, and our column names and the data we wish to insert as key-value pairings in a dictionary:

insert_query = ("INSERT INTO My_library (id,author,book)"
                "VALUES (:id, :author, :book);")

author_parameters = {
        'id': 2,
        'author': 'Lewis Carrol',
        'book': "Alice's Trip in Wonderland"
    }

connection.execute(insert_query, author_parameters)

Reading data​

To read from the database, we can use a cursor variable that holds the data we pull from our database connection. We get data from our database using the SELECT query. We then read that data from our cursor variable.

The SELECT * query returns all the data from our database and the .fetchall() method allows us to retrieve that data from our cursor variable:

cursor_object = connection.execute("SELECT * FROM My_library")

print(cursor_object.fetchall())

Output:​

[(1, 'Steve Biko', 'I write what I like.'), (2, 'Lewis Carrol', 'Alice's Adventures in Wonderland')

The WHERE query returns all the data from our database that corresponds with our requirements:

cursor_object = connection.execute("SELECT * FROM My_library WHERE id = 1")

print(cursor_object.fetchall())
[(1, 'Steve Biko', 'I write what I like.')]

Updating data​

To update the data in our table, we execute an UPDATE query:

connection.execute("UPDATE My_library SET book = 'Alice's Adventures in Wonderland' WHERE id = 2")

Deleting data​

To delete data from our table, we execute a DELETE query:

connection.execute("DELETE from My_library WHERE id = 1;")

Committing changes​

When we have made changes to our database, we commit the changes through a connection:

connection.commit()

Closing the connection​

Finally, we close the connection to the database:

connection.close()

Summary​

The SQLite database provides an intuitive table format for our data that we can use queries to interact with. The fact we do not need to install or configure our database makes for an easy setup, and the ability to create multiple tables and databases means we can store our data separately while being able to merge files if needed.