An Introduction to SQLite with Python

On this article, we’ll kick the tires of SQLite. We’ll learn to use SQLite via a Python library known as sqlite3. On the very finish, we’ll discover some extra superior options supplied by sqlite3 to make our job simpler.

Observe: earlier than getting began, it’s good to be accustomed to SQL. In the event you aren’t, you may need to take a look at Merely SQL.

What’s SQLite?

The motto of SQLite is: “Small. Quick. Dependable. Select any three.”

SQLite is an embedded database library written in C. Chances are you’ll be accustomed to different database applied sciences like MySQL or PostgreSQL. These use a client-server method: the database is put in as a server, after which a shopper is used to connect with it. SQLite is completely different: it’s referred to as an embedded database, as a result of it’s included in a program as a library. All the information is saved in a file — normally with a .db extension — and you’ve got features that help you run SQL statements or do every other operation on the database.

The file-based storage resolution additionally gives concurrent entry, which means that a number of processes or threads can entry the identical database. Okay, so what are the usages of SQLite? Is it appropriate for any type of utility?

Nicely, there are a couple of instances the place SQLite excels:

• Being included on most cellular working techniques, like Android and iOS, SQLite may very well be an ideal selection if you need a self-contained and serverless information storage resolution.

• As an alternative of utilizing large CSV information, you may exploit the ability of SQL and put all of your information right into a single SQLite database.

• SQLite can be utilized to retailer configuration information to your functions. In reality, SQLite is 35% quicker than a file-based system like a configuration file.

However, what are some causes for not selecting SQLite?

• In contrast to MySQL or PostgreSQL, SQLite lacks multi-user functionalities.

• SQLite nonetheless a file-based information storage resolution, not a service. You possibly can’t handle it as a course of, you may’t begin or cease it, or handle the useful resource utilization.

The Python interface to SQLite

As I stated within the introduction, SQLite is a C library. There are interfaces written in numerous languages although, together with Python. The sqlite3 module gives an SQL interface and requires no less than SQLite 3.7.15.

The superior factor is that sqlite3 comes with Python, so that you don’t want to put in something.

Getting Began with sqlite3

It’s time to code! On this first half, we’ll create a fundamental database. The very first thing to do is create a database and connect with it:

import sqlite3
dbName = 'database.db'

strive:
  conn = sqlite3.join(dbName)
  cursor = conn.cursor()
  print("Database created!")

besides Exception as e:
  print("One thing dangerous occurred: ", e)
  if conn:
    conn.shut()

On line 1, we import the sqlite3 library. Then, inside a strive/besides code block, we name sqlite3.join() to initialize a connection to the database. If every little thing goes proper, conn will likely be an occasion of the Connection object. If the strive fails, we print the exception obtained and the connection to the database is closed. As said within the official documentation, every open SQLite database is represented by a Connection object. Every time we now have to execute an SQL command, the Connection object has a way known as cursor(). In database applied sciences, a cursor is a management construction that permits traversal over the data in a database.

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

> Database created!

If we have a look at the folder the place our Python script is, we should always see a brand new file known as database.db. This file has been created routinely by sqlite3.

Create, learn and modify data

At this level, we’re able to create a brand new desk, add the primary entries and execute SQL instructions like SELECT, UPDATE or DROP.

To create a desk, we simply have to execute a easy SQL assertion. On this instance, we’ll create a college students desk that can comprise the next information:

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

create_query = '''CREATE TABLE IF NOT EXISTS pupil(
  id INTEGER PRIMARY KEY,
  identify TEXT NOT NULL,
  surname TEXT NOT NULL);
  '''
cursor.execute(create_query)
print("Desk created!")


cursor.execute("INSERT INTO pupil VALUES (1, 'John', 'Smith')")
print("Insert #1 performed!")
cursor.execute("INSERT INTO pupil VALUES (2, 'Lucy', 'Jacobs')")
print("Insert #2 performed!")
cursor.execute("INSERT INTO pupil VALUES (3, 'Stephan', 'Taylor')")
print("Insert #3 performed!")
conn.commit()
conn.shut()

We create a desk and name the cursor.execute() methodology, which is used after we need to execute a single SQL assertion.

Then, we do an INSERT for every row we need to add. In spite of everything of our adjustments have been performed, we name conn.commit() to commit the pending transaction to the database. With out calling the commit() methodology, any pending change to the database will likely be misplaced. Lastly, we shut the connection to the database by calling the conn.shut() methodology.

Okay, now let’s question our database! We’ll want a variable to save lots of the outcomes of our question, so let’s save the results of cursor.execute() to a variable known as data:

data = cursor.execute("SELECT * FROM pupil")
for row in findrecords:
  print(row)

After executing this, we’ll see all the data to stdout:

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

At this level, you may need observed that, contained in the cursor.execute() methodology, we put the SQL command that have to be executed. Nothing adjustments within the Python syntax if we need to execute one other SQL command like UPDATE or DROP.

The Placeholders

The cursor.execute() methodology wants a string as an argument. Within the earlier part, we noticed tips on how to insert information into our database, however every little thing was hard-coded. What if we have to retailer within the database one thing that’s in a variable? For that reason, sqlite3 has some fancy issues known as placeholders. Placeholders enable us to make use of parameter substitution, which is able to make inserting a variable into a question a lot simpler.

Let’s see this instance:

def insert_command(conn, student_id, identify, surname):
  command = 'INSERT INTO pupil VALUES (?, ?, ?)'
  cur = conn.cursor()
  cur.execute(command, (student_id, identify, surname, ))
  conn.commit()

We create a way known as insert_command(). This methodology takes 4 arguments: the primary one is a Connection occasion, and the opposite three will likely be utilized in our SQL command.

Every ? contained in the command variable represents a placeholder. Which means, when you name the insert_command operate with student_id=1, identify="Jason" and surname="Inexperienced", the INSERT assertion will grow to be INSERT INTO pupil VALUES(1, 'Jason', 'Inexperienced').

Once we name the execute() operate, we move our command and all the variables that will likely be substituted to the placeholders. Any further, each time we have to insert a row within the pupil desk, we name the insert_command() methodology with the parameters required.

Transactions

Even when you aren’t new to the definition of a transaction, let me give a fast recap of its significance. A transaction is a sequence of operations carried out on a database that’s logically handled as a single unit.

An important advantage of a transaction is making certain information integrity. It is likely to be ineffective within the instance we launched above, however after we take care of extra information saved in a number of tables, transactions do make the distinction.

Python’s sqlite3 module begins a transaction earlier than execute() and executemany() executes INSERT, UPDATE, DELETE, or REPLACE statements. This means two issues:

• We should handle calling the commit() methodology. If we name Connection.shut() with out doing a commit(), all the adjustments we made through the transaction will likely be misplaced.
• We are able to’t open a transaction in the identical course of utilizing BEGIN.

The answer? Deal with transactions explicitly.

How? By utilizing the operate name sqlite3.join(dbName, isolation_level=None) as a substitute of sqlite3.join(dbName). By setting isolation_level to None, we power sqlite3 to by no means open transactions implicitly.

The next code is a rewriting of the earlier code, however with the specific utilization of transactions:

import sqlite3
dbName = 'database.db'

def insert_command(conn, student_id, identify, surname):
  command = 'INSERT INTO pupil VALUES (?, ?, ?)'
  cur = conn.cursor()
  cur.execute("BEGIN")
  strive:
    cur.execute(command, (student_id, identify, surname, ))
    cur.execute("COMMIT")
  besides conn.Error as e:
    print("Bought an error: ", e)
    print("Aborting...")
    cur.execute("ROLLBACK")

conn = sqlite3.join(dbName, isolation_level=None)
cursor = conn.cursor()
print("Database created!")


create_query = '''CREATE TABLE IF NOT EXISTS pupil(
  id INTEGER PRIMARY KEY,
  identify TEXT NOT NULL,
  surname TEXT NOT NULL);
  '''
cursor.execute(create_query)
print("Desk 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 pupil")
for row in findRecords:
  print(row)

conn.shut()

Conclusion

I hope you now have a superb understanding of what SQLite is, how you should utilize it to your Python initiatives, and the way a few of its superior options work. The specific administration of transactions is likely to be a bit difficult at first, however it may actually show you how to profit from sqlite3.

Associated studying: