An Introduction to SQLite with Python

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

Word: earlier than getting began, it’s good to be accustomed to SQL. Should you aren’t, you may need to try 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. It's possible 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 consumer is used to hook up with it. SQLite is completely different: it’s generally known as an embedded database, as a result of it’s included in a program as a library. All the info is saved in a file — often with a .db extension — and you've got features that permit you to run SQL statements or do every other operation on the database.

The file-based storage resolution additionally offers 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 sort of utility?

Effectively, there are a number of circumstances the place SQLite excels:

• Being included on most cellular working programs, like Android and iOS, SQLite might be an ideal alternative if you need a self-contained and serverless information storage resolution.

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

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

Then again, what are some causes for not selecting SQLite?

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

• SQLite nonetheless a file-based information storage resolution, not a service. You'll be able to’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 plenty of languages although, together with Python. The sqlite3 module offers an SQL interface and requires not 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 primary 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 unhealthy 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 shall be an occasion of the Connection object. If the strive fails, we print the exception acquired and the connection to the database is closed. As acknowledged within the official documentation, every open SQLite database is represented by a Connection object. Every time now we have to execute an SQL command, the Connection object has a technique referred to as cursor(). In database applied sciences, a cursor is a management construction that allows traversal over the information 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 the place our Python script is, we must always see a brand new file referred to as database.db. This file has been created routinely by sqlite3.

Create, learn and modify information

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 must execute a easy SQL assertion. On this instance, we’ll create a college students desk that can include the next information:

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

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


cursor.execute("INSERT INTO scholar VALUES (1, 'John', 'Smith')")
print("Insert #1 executed!")
cursor.execute("INSERT INTO scholar VALUES (2, 'Lucy', 'Jacobs')")
print("Insert #2 executed!")
cursor.execute("INSERT INTO scholar VALUES (3, 'Stephan', 'Taylor')")
print("Insert #3 executed!")
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 any case of our modifications have been executed, we name conn.commit() to commit the pending transaction to the database. With out calling the commit() methodology, any pending change to the database shall 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 referred to as information:

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

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

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

At this level, you might need observed that, contained in the cursor.execute() methodology, we put the SQL command that should be executed. Nothing modifications 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? Because of this, sqlite3 has some fancy issues referred to as placeholders. Placeholders permit us to make use of parameter substitution, which can 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 scholar VALUES (?, ?, ?)'
  cur = conn.cursor()
  cur.execute(command, (student_id, identify, surname, ))
  conn.commit()

We create a technique referred to as insert_command(). This methodology takes 4 arguments: the primary one is a Connection occasion, and the opposite three shall be utilized in our SQL command.

Every ? contained in the command variable represents a placeholder. Which means that, for those who name the insert_command perform with student_id=1, identify="Jason" and surname="Inexperienced", the INSERT assertion will change into INSERT INTO scholar VALUES(1, 'Jason', 'Inexperienced').

Once we name the execute() perform, we move our command and the entire variables that shall be substituted to the placeholders. To any extent further, each time we have to insert a row within the scholar desk, we name the insert_command() methodology with the parameters required.

Transactions

Even for those who 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 guaranteeing information integrity. It may 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 maintain calling the commit() methodology. If we name Connection.shut() with out doing a commit(), the entire modifications we made through the transaction shall be misplaced.
• We will’t open a transaction in the identical course of utilizing BEGIN.

The answer? Deal with transactions explicitly.

How? Through the use of the perform 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 express utilization of transactions:

import sqlite3
dbName = 'database.db'

def insert_command(conn, student_id, identify, surname):
  command = 'INSERT INTO scholar VALUES (?, ?, ?)'
  cur = conn.cursor()
  cur.execute("BEGIN")
  strive:
    cur.execute(command, (student_id, identify, surname, ))
    cur.execute("COMMIT")
  besides conn.Error as e:
    print("Received 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 scholar(
  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 scholar")
for row in findRecords:
  print(row)

conn.shut()

Conclusion

I hope you now have a superb understanding of what SQLite is, how you need to use it on your Python tasks, and the way a few of its superior options work. The specific administration of transactions may be a bit tough at first, however it could definitely enable you take advantage of sqlite3.

Associated studying: