Basics of Working with the SQLite Database in Python

A database is one of the most useful and popular files for storing data; they can be used to store any kind of data, including text, numbers, images, binary data, files, etc. SQLite is a relational database management system based on the SQL language. It is a C library, and it provides an API to work with other programming languages, including Python. It does not require a separate server process to be run as needed in large database engines like MySQL and Postgresql.

It is swift and lightweight, and the entire database is stored in a single disk file, which makes it portable like CSV or other data storage files. Many applications use SQLite for internal data storage, mainly in environments like mobile devices or small applications.

SQLite Database for Python

Let’s take a deep dive into SQLite with the python programming language. In this tutorial, we will learn the advantages of using SQLite, basics of python sqlite3 module, Creating a table in a database, Inserting data into the table, Querying data from the table, and Updating data of the table.

Advantages of using SQLite

The main advantages of using SQLite are:

• SQLite does not require a separate server process or system to operate as many big databases engine needed.
• SQLite comes with zero-configuration, which means no setup or administration needed, which makes it simple to use.
• We will get a single database file, and all the information is stored under the only file, which makes the file portable, unlike other databases that spit out several files.
• It comes preinstalled with Python Standard Library so you can use it without any further installation.
• SQLite is written in ANSI-C, which makes it faster. It also provides simple and easy-to-use API with Python and many other programming languages.
• SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT), so it does not matter which environment we are using.

SQLite is also used in Google Chrome to store the cookies, User data, and other important data, including user passwords. The Android OS also uses SQLite as its primary database engine to store data.

Python SQLite3 module

To use SQLite, we need Python to be installed in our system. If you don’t have Python already installed in your system, you can refer to our step by step guide to install Python in Linux. We could use SQLite in Python using the sqlite3 module available in Python’s standard library. Gerhard Häring wrote the sqlite3 module; it provides a SQL interface compliant with the DB-API 2.0. It comes preinstalled with the Python standard library, so we don’t need to worry about any further installation.

Creating Connection to a Database

The first step while working with SQLite in Python is setting up a connection with a database. We can do this by using the connect() method of sqlite3 to set up a connection. Look at the following code for an example. You can simply copy the code into an IDE or a Text Editor and execute it. If you have a problem with choosing IDE for Python, you can refer to our guide on comparing the best python IDE. It is recommended to rewrite the code again in your IDE, and if you want to copy the code, please check the syntax with the code present here.

# importing the required modules
import sqlite3

# setting up the connection with database
conn = sqlite3.connect("sample.db")
print("Successfully connected to the database")
# closing the connection
conn.close()

The above program will create a connection with the SQLite database file “sample.db.” It will give the following output in the terminal.

Let’s see what is happening in the above code. In the first line, we have imported the sqlite3 module, which will help us to work with SQLite databases in Python.

In the second line, we create a connection with an SQLite database file named “sample.db” using the connect() function. The connect() function accepts the path to the database file as an argument. If the file does not exist in the given path, then it will itself create a new database file with the given name in that path. The connect() function will return a database object in our program; we store the returned object into a variable named conn.

The third line in our program is a simple print statement to display a message about a successful connection. The last line of the program breaks the connection with the database using the close() function of the connection object.

In the previous example, we have created the database into the disk, but we can also create a database into the primary memory RAM. Creating a database in the RAM makes the execution of the database faster than usual. Still, the database will be created temporarily, and as the program execution stops, it will delete the database from memory. We can create a database in the memory by supplying the particular name :memory: as an argument to the connect() function. See the below program as an illustration.

import sqlite3
conn = sqlite3.connect(":memory:")
print("\n [+] Database has been successfully created in the Memory")
conn.close()

The above program will create a database in the RAM, and we can use it for performing almost every task we can do with databases created in the disk. This method is useful while creating a temporary virtual database for some reason.

SQLite3 cursor

Acursor the object is our interface to the database, that allows running anySQL query on the database. To execute any SQL scripts using sqlite3, we must need to create a cursor object. To create a cursor object, we need to use the cursor() method of the connection object. We can create a cursor object of our database using the following code.

# importing the required modules
import sqlite3
# setting up the connection to the database
conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.close()
# closing the connection
conn.close()

When the program is executed, the output will be seen as shown in the below image.

Let’s see how the above code works. In the above code, the first, second, third, is setting up a connection with the database, as shown earlier. In the fourth line, we have used the cursor() method of the connection object to create a cursor object and store the returned cursor object in a variable named “cur”. The fifth line is a general print() statement. In the sixth line, we destroyed the cursor object from memory by using the close() method of the cursor object.

SQLite Data Types

Before proceeding further, let us first understand SQLite data types. SQLite database engine has several storage classes to store many types of data, including text, binary data, Integer, etc. Each value has one of the following data types.

SQLite DataTypes:

• NULL: As it implies, it does not contain anything.
• INTEGER: It stores a numeric value like numbers and other integers.
• REAL: The value includes decimals
• TEXT: It’s a text string.
• BLOB: This is the binary data and is used to store images and files.

Comparison of SQLite and Python Data Types

There will be many times when we need to use python data types to store some SQL data and perform some activities. For such a thing to do, we need to know which SQL data types relate to which python data types.

The following Python types are somewhat similar to the SQLite data types:

Creating a Table using SQLite

To create a table using SQLite, we need to use the CREATE TABLE statement of SQL in the execute() method of the cursor object. The basic syntax of the CREATE TABLE statement in SQL is shown below:

CREATE TABLE table_name(
    column_name Data_type constraint,
    ...
    ...
    ...
    column_name Data_type constraint
);

To use the above SQLite statement in Python, we need to run the below example program. It will create a table named employee in our database.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
table = cur.execute(""" CREATE TABLE employee(
    id INT PRIMARY KEY,
    name CHAR(25),
    salary CHAR(25),
    joining_date DATE
);
""")
print("\n [+] The table has been created Successfully ")
cur.close()
conn.close()

In the above program, we created an employee table with the attributes id, name, salary, and joining_date. This table can now be used for storing data or querying data as per requirement. You will see the following output in the terminal.

In the above code, we have used the execute() method of the cursor object to run the SQL command to create a table with the given columns.

Inserting Data in a Table

We have created a table in our SQLite database. Now let us insert some data in it using SQL. The basic syntax of the INSERT statement of SQL is :

INSERT INTO table_name (columns_name_1, columns_name_2,...) VALUES (columns_data_1, columns_data_1,...)

In the above syntax, the table_name is the name of the table in which we want to insert our data. The column_name_1, column_name_2,… are the name of the columns present in the table. The column_data_1, column_data_2,… are the data we want to insert in the given columns.

Let us see a practical demo to insert data into a table. We will add some data into our table named employee using SQLite and Python. Run the below code to insert some data into the table.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1001, 'David', 50000, '1-08-2019')")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1002, 'Sam', 80000, '3-09-2020')")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1003, 'Roshan', 90000, '8-08-2020')")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1004, 'Kishan', 100000, '9-09-2020')")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1005, 'Ankit', 111000, '10-05-2019')")
print("\n [+] The Data has been inserted Successfully ")
cur.close()
conn.commit()
conn.close()

The above code will insert some data into the employee table we have created earlier. Let’s see what is happening in the code. The first five lines are used to create a connection with the database and setting up the cursor. In the lines from six to ten, we have to use the INSERT command of SQL to insert data into the employee table. We have to use the columns name of the employee table in the first parenthesis and the data for the columns in the second parenthesis. We just need to use the commit() method of the connection object before disconnecting with the database else the changes we made will not be saved into the database.

Querying Data from a Table

We have learned how to insert data in an SQLite database, but we also need to query data from the database to be used by our program or users. To query data, we can use the SELECT statement of the SQL under the execute() method. The basic syntax of the SELECT statement is shown below.

SELECT columns_names FROM table_name

The columns_names in the syntax will be the name of columns we need to query. These columns must be present in the table whose name is given in place of table_name. Now let us see how we could use this syntax to query data from our employee table. Just run the following code to see an illustration.

import sqlite3
conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("SELECT id,name FROM employee")
table = cur.fetchall()
for i in table:
print(i)

cur.close()
conn.commit()
conn.close()

The output provided by the above program is shown below.

The above program will query the employee table for the columns id and name. We can collect the data that has been returned by using the fetchall() method of the cursor object. The returned data is a python list containing the rows we queried. To display individual rows, we have to use the Python for loop to iterate over the list; you can read more about the Python for loop here. Now let us see some useful things that we can be performed with the SELECT statement.

Fetch all Data form a table

Sometimes there is the need to fetch all the records from a database table. To get all the records using the SELECT statement of SQL, we need to follow the basic syntax given below:

SELECT * FROM table_name

The * symbol will be used to denote all the columns, and by using this, we can query all the columns of an SQLite table. To fetch all the records from the table employee we have created earlier, we need to run the following code.

import sqlite3
conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("SELECT * FROM employee")
rows = cur.fetchall()
print("\n [+] Querying the data \n")
for i in rows:
print(i)

cur.close()
conn.commit()
conn.close()

The above code will display all the records present in the employee table that we have created earlier. The output of the program will be something like this:

Query data in Specific order

Sometimes we need to query data from a table in a definite order like Ascending or Descending. We can use the SELECT statement with the ORDER BY keyword to display data in order. The basic syntax of the ORDER BY keyword in SELECT statement is:

SELECT columns_name FROM table_name ORDER BY columns_name

Let us see how we can use the ORDER BY keyword to display data from the employee table order by the name.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("SELECT * FROM employee ORDER BY name")
table = cur.fetchall()
for i in table:
print(i)

cur.close()
conn.commit()
conn.close()

You may see the output of the above code, as shown below.

You may notice in the output that the data has been displayed in the ascending order of the column name.

Updating records in a Table

There are many situations when we want to update the table of our databases. For example, if we are using the database for a school application, then we will need to update the data if a student transferred to a new city. We can quickly update a row of any table of our database using the UPDATE statement of SQL in the execute() method. We will need to use the WHERE clause of SQL as a condition to select the employee. The basic syntax of the UPDATE statement is shown below.

UPDATE table_name SET update_required WHERE Some_condition

See the below example as an illustration of the UPDATE statement.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
print("\n [+] Data Before Update\n")
cur.execute("SELECT * FROM employee")
before = cur.fetchall()
for i in before:
print(i)

cur.execute("UPDATE employee SET name = 'Aditya' where name = 'Sam'")
print("\n [+] Data After Update\n")
cur.execute("SELECT * FROM employee")
after = cur.fetchall()
for i in after:
print(i)

cur.close()
conn.commit()
conn.close()

The above program will update the table employee. It replaces the name Sam with the name Aditya wherever it appears in the table. See the below image for the output of the program.

Conclusion

That’s our comprehensive guide for performing some basic SQLite database-related tasks using Python. In the upcoming tutorial, we will see a few more advanced usages that should take you to the next level of learning SQLite database for Python. Stay tuned to FOSSLinux.