BloG

An Introduction to SQLite with Python

Nov 21, 2022

In this text, we’ll kick the tires of SQLite. We’ll learn use SQLite through a Python library called sqlite3. On the very end, we’ll explore some more advanced features provided by sqlite3 to make our job easier.

Note: before getting began, it’s good to be aware of SQL. If you happen to aren’t, you would possibly want to examine out Simply SQL.

What’s SQLite?

The motto of SQLite is: “Small. Fast. Reliable. Select any three.”

SQLite is an embedded database library written in C. You might be aware of other database technologies like MySQL or PostgreSQL. These use a client-server approach: the database is installed as a server, after which a client is used to connect with it. SQLite is different: it’s generally known as an embedded database, since it’s included in a program as a library. All the information is stored in a file — often with a .db extension — and you will have functions that let you run SQL statements or do every other operation on the database.

The file-based storage solution also provides concurrent access, meaning that multiple processes or threads can access the identical database. Okay, so what are the usages of SQLite? Is it suitable for any form of application?

Well, there are a number of cases where SQLite excels:

  • Being included on most mobile operating systems, like Android and iOS, SQLite could possibly be an ideal selection for those who need a self-contained and serverless data storage solution.

  • As an alternative of using huge CSV files, you’ll be able to exploit the ability of SQL and put all of your data right into a single SQLite database.

  • SQLite may be used to store configuration data in your applications. Actually, SQLite is 35% faster than a file-based system like a configuration file.

Alternatively, what are some reasons for not selecting SQLite?

  • Unlike MySQL or PostgreSQL, SQLite lacks multi-user functionalities.

  • SQLite still a file-based data storage solution, not a service. You may’t manage it as a process, you’ll be able to’t start or stop it, or manage the resource usage.

The Python interface to SQLite

As I said within the introduction, SQLite is a C library. There are interfaces written in plenty of languages though, including Python. The sqlite3 module provides an SQL interface and requires at the very least SQLite 3.7.15.

The awesome thing is that sqlite3 comes with Python, so that you don’t must install anything.

Getting Began with sqlite3

It’s time to code! In this primary part, we’ll create a basic database. The very first thing to do is create a database and hook up with it:

import sqlite3
dbName = ‘database.db’

try:
conn = sqlite3.connect(dbName)
cursor = conn.cursor()
print(“Database created!”)

except Exception as e:
print(“Something bad happened: “, e)
if conn:
conn.close()

On line 1, we import the sqlite3 library. Then, inside a try/except code block, we call sqlite3.connect() to initialize a connection to the database. If all the pieces goes right, conn will probably be an instance of the Connection object. If the try fails, we print the exception received and the connection to the database is closed. As stated within the official documentation, each open SQLite database is represented by a Connection object. Every time we have now to execute an SQL command, the Connection object has a technique called cursor(). In database technologies, a cursor is a control structure that permits traversal over the records in a database.

Now, if we execute this code we must always get the next output:

> Database created!

If we have a look at the folder where our Python script is, we must always see a latest file called database.db. This file has been created robotically by sqlite3.

Create, read and modify records

At this point, we’re able to create a latest table, add the primary entries and execute SQL commands like SELECT, UPDATE or DROP.

To create a table, we just must execute a straightforward SQL statement. In this instance, we’ll create a students table that may contain the next data:

id name surname
1 John Smith
2 Lucy Jacobs
3 Stephan Taylor

After the print(“Database created!”) line, add this:

create_query = ”’CREATE TABLE IF NOT EXISTS student(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
surname TEXT NOT NULL);
”’

cursor.execute(create_query)
print(“Table created!”)

cursor.execute(“INSERT INTO student VALUES (1, ‘John’, ‘Smith’)”)
print(“Insert #1 done!”)
cursor.execute(“INSERT INTO student VALUES (2, ‘Lucy’, ‘Jacobs’)”)
print(“Insert #2 done!”)
cursor.execute(“INSERT INTO student VALUES (3, ‘Stephan’, ‘Taylor’)”)
print(“Insert #3 done!”)
conn.commit()
conn.close()

We create a table and call the cursor.execute() method, which is used when we wish to execute a single SQL statement.

Then, we do an INSERT for every row we wish so as to add. In spite of everything of our changes have been done, we call conn.commit() to commit the pending transaction to the database. Without calling the commit() method, any pending change to the database will probably be lost. Lastly, we close the connection to the database by calling the conn.close() method.

Okay, now let’s query our database! We’ll need a variable to save lots of the outcomes of our query, so let’s save the results of cursor.execute() to a variable called records:

records = cursor.execute(“SELECT * FROM student”)
for row in findrecords:
print(row)

After executing this, we’ll see the entire records to stdout:

(1, ‘John’, ‘Smith’)
(2, ‘Lucy’, ‘Jacobs’)
(3, ‘Stephan’, ‘Taylor’)

At this point, you would possibly have noticed that, contained in the cursor.execute() method, we put the SQL command that should be executed. Nothing changes within the Python syntax if we wish to execute one other SQL command like UPDATE or DROP.

The Placeholders

The cursor.execute() method needs a string as an argument. Within the previous section, we saw insert data into our database, but all the pieces was hard-coded. What if we want to store within the database something that’s in a variable? Because of this, sqlite3 has some fancy things called placeholders. Placeholders allow us to make use of parameter substitution, which is able to make inserting a variable into a question much easier.

Let’s see this instance:

def insert_command(conn, student_id, name, surname):
command = ‘INSERT INTO student VALUES (?, ?, ?)’
cur = conn.cursor()
cur.execute(command, (student_id, name, surname, ))
conn.commit()

We create a technique called insert_command(). This method takes 4 arguments: the primary one is a Connection instance, and the opposite three will probably be utilized in our SQL command.

Each ? contained in the command variable represents a placeholder. Which means that, for those who call the insert_command function with student_id=1, name=”Jason” and surname=”Green”, the INSERT statement will turn into INSERT INTO student VALUES(1, ‘Jason’, ‘Green’).

After we call the execute() function, we pass our command and the entire variables that will probably be substituted to the placeholders. Any longer, each time we want to insert a row in the coed table, we call the insert_command() method with the parameters required.

Transactions

Even for those who aren’t latest to the definition of a transaction, let me give a fast recap of its importance. A transaction is a sequence of operations performed on a database that’s logically treated as a single unit.

A very powerful advantage of a transaction is ensuring data integrity. It is likely to be useless in the instance we introduced above, but once we cope with more data stored in multiple tables, transactions do make the difference.

Python’s sqlite3 module starts a transaction before execute() and executemany() executes INSERT, UPDATE, DELETE, or REPLACE statements. This suggests two things:

  • We must maintain calling the commit() method. If we call Connection.close() without doing a commit(), the entire changes we made throughout the transaction will probably be lost.
  • We will’t open a transaction in the identical process using BEGIN.

The answer? Handle transactions explicitly.

How? Through the use of the function call sqlite3.connect(dbName, isolation_level=None) as a substitute of sqlite3.connect(dbName). By setting isolation_level to None, we force sqlite3 to never open transactions implicitly.

The next code is a rewriting of the previous code, but with the specific usage of transactions:

import sqlite3
dbName = ‘database.db’

def insert_command(conn, student_id, name, surname):
command = ‘INSERT INTO student VALUES (?, ?, ?)’
cur = conn.cursor()
cur.execute(“BEGIN”)
try:
cur.execute(command, (student_id, name, surname, ))
cur.execute(“COMMIT”)
except conn.Error as e:
print(“Got an error: “, e)
print(“Aborting…”)
cur.execute(“ROLLBACK”)

conn = sqlite3.connect(dbName, isolation_level=None)
cursor = conn.cursor()
print(“Database created!”)

create_query = ”’CREATE TABLE IF NOT EXISTS student(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
surname TEXT NOT NULL);
”’

cursor.execute(create_query)
print(“Table created!”)

insert_command(conn , 1, ‘John’, ‘Smith’)
insert_command(conn , 2, ‘Lucy’, ‘Jacobs’)
insert_command(conn , 3, ‘Stephan’, ‘Taylor’)
insert_command(conn , 4, ‘Joseph’, ‘Random’)
findRecords = cursor.execute(“SELECT * FROM student”)
for row in findRecords:
print(row)

conn.close()

Conclusion

I hope you now have an excellent understanding of what SQLite is, how you need to use it in your Python projects, and the way a few of its advanced features work. The specific management of transactions is likely to be a bit tricky at first, but it may definitely assist you benefit from sqlite3.

Related reading: