Database Programming With SQLite in Python

Almost every application needs a persistent layer to persist data. We can save data in files in our own way, in our own format. We can develop a mini database system on our own to persist data. But none of these methods are very effective in the long run or for relatively large projects. You may need relation between your sets of data and you will not be able to achieve that with your custom mini database or custom file format. Yes, you can redesign its algorithm, but that will be time consuming. You will focus less on your business logic and more on unnecessary complex tasks. Worse still, the file format or the mini database system you develope may have bugs that can hamper your or your client's' business.

Again, it is also not practical to crush a butterfly on a wheel. It is stupid to install and configure a large database system like Oracle or MySQL for running a script of few lines. If your application is big and you need to move data to another system you cannot do by copying and pasting a few files when you are using a large database system. Also, external database systems may take more resource and sometimes need connection the internet.

To save ourselves from a lot of problems like those discussed above, we need a better alternative. The system needs to be lite but also not compromise the  quality. SQLite is our savior. SQLite implements SQL as the query language for interacting with the database. It brings portability to the table. SQLite is a file based database system. You do not need to install a big database system for smaller tasks. The SQLite database system can be run in the same process or the same thread as the main application. SQLite is available for every popular systems and a library is available in almost every modern language. SQLite is also available for modern mobile operating systems and embedded systems. You don't need authentication system to use an SQLite database. All the data is saved in a single file and you can move that file directly to another system to be used without any problem. There is no need for external tools to migrate data from one system to another system.

Such a great system cannot live without any disadvantages. SQLite has some disadvantages too. For example, it does not support all SQL functions. Multiple user or client cannot use the same database at the same time. Running on single threaded system, it cannot handle large number of requests at the same time. There are some other disadvantages too, but the sweetness of it outweighs everything else.

Getting Started

To get started with SQLite in Python you don’t need anything special. The functions for interacting with the SQLite database are within the standard library of Python. So, you do not need to install any external library for working with SQLite database. As for your IDE or code editor of choice you can use anything you prefer. I am using Python 3.6 at the moment, but you can use another version.

So, let's create a python script with the name py_sqlite.py. We want to name the database file as mydb.sqlite. I am keeping that name in a variable. You can use the literal string if you want. You need to import the module called sqlite3 to get the SQLite related functions.

Connecting to the Database

As I told before you do not need any type of authentication credentials for connecting with the database as you need for other larger database systems. You need to use the connect() function from the sqlite3 module to connect with the database file. You can also create the database in the memory. But in our case we are not going to use the memory based database, but instead, we want to persist our data in a database file.

So, our initial code should look like the following:

import sqlite3

db_filename = "mydb.sqlite"

con = sqlite3.connect(db_filename)

After completing your work, do not forget to close the connection.

import sqlite3

db_filename = "mydb.sqlite"
con = sqlite3.connect(db_filename)

# Everything else go here

con.close()

Run the script now. You will see a new file in the current working directory with the filename mydb.sqlite. If there is no database file with the name asked to connect, SQLite will create an empty database file to work with.

Creating and Deleting Tables

Now, we have an empty database. We need to create tables to start putting the data into the database. But before that we need to create a cursor object to run commands or execute the statements.

import sqlite3

db_filename = "mydb.sqlite"
con = sqlite3.connect(db_filename)

cur = con.cursor()

# Execution commands go here

con.commit()
con.close()

To commit the database transaction you need to call the commit() method on the connection object.

To create a table call the execute() method on the cursor object with proper SQL statement for creating table.

cur.execute('CREATE TABLE person(name text, gender text, color text)')

To delete the table you need to use SQL DROP TABLE with the table name.

cur.execute('DROP TABLE person')

To create and delete tables at the same time to see if everything is alright run the following Python code.

import sqlite3

db_filename = "mydb.sqlite"
con = sqlite3.connect(db_filename)

cur = con.cursor()

# Create table
cur.execute('CREATE TABLE person(name text, gender text, color text)')
# Drop Table
cur.execute('DROP TABLE person')

con.commit()
con.close()

Inserting Data

To insert data into the table as rows you need to call the execute() method on the cursor with the SQL INSERT statements. My code looks like the following:

import sqlite3

db_filename = "mydb.sqlite"
con = sqlite3.connect(db_filename)
cur = con.cursor()

cur.execute('CREATE TABLE person(name text, gender text, color text)')
# Insert data
cur.execute("INSERT INTO person VALUES ('John','Male','White')")
cur.execute("INSERT INTO person VALUES ('Scott','Male','White')")
cur.execute("INSERT INTO person VALUES ('Faulk','Male','Black')")
cur.execute("INSERT INTO person VALUES ('Brad','Male','White')")

con.commit()
con.close()

Retrieving Data

On SQL systems you need to execute SELECT statements for retrieving data from the database. Let's retrieve all the rows that we inserted from the database. To execute SELECT you again need to invoke the execute() method.

import sqlite3

db_filename = "mydb.sqlite"
con = sqlite3.connect(db_filename)
cur = con.cursor()

cur.execute("SELECT name, gender, color from person")
res = cur.fetchall()

for row in res:
   print(row)

con.commit()
con.close()

Outputs:

('John', 'Male', 'White')
('Scott', 'Male', 'White')
('Faulk', 'Male', 'Black')
('Brad', 'Male', 'White')

To get only one result from the resultset you need to invoke fetchone() on the cursor object instead of fetchall().

Update Data

Updating in SQLite is as easy or as hard as executing an SQL UPDATE statement.

import sqlite3

db_filename = "mydb.sqlite"
con = sqlite3.connect(db_filename)
cur = con.cursor()

# Update row
cur.execute("UPDATE person SET name='Fraud Faulk' where name='Faulk'")

cur.execute("SELECT name, gender, color from person")
res = cur.fetchall()

for row in res:
   print(row)

con.commit()
con.close()

The output is:

('John', 'Male', 'White')
('Scott', 'Male', 'White')
('Fraud Faulk', 'Male', 'Black')
('Brad', 'Male', 'White')

Look at the third row to see the changed data.

It's really fun and easy to work with SQLite in Python. As SQLite works with SQL syntax, you need to have basic knowledge of SQL. There are many other functions in the library. Have a look at the official documentation to have a look at all of them. I tried to cover the most important aspects of SQLite in Python in this article. In some future articles I will focus on more specific and advanced topics.

Need to brush up on your Python? Check out our review of the best online Python courses.