How to use python sqlite module to perform database operations.

Sqlite is faster and light database which requires zero configuration, no separate database server and its easy to move arround. I have used sqlite on most of my projects including Bongobinary website.

In this python sqlite tutorial we are going to see how to use the python sqlite module to work with sqlite3 database in python. Good news is that we don’t need any third party library to use sqlite database as it comes with python3 by defaults.

The specific things which we are going to cover includes how to create table, how to insert data, how to delete and update the inserted data in our sqlite database (CRUD) operation. Create Read Update and Delete. By the end of this tutorial you will be able to work well with python sqlite module.

Requirements:

We are going to use python3 and sqlite3 database, so before starting to follow along make sure you already have the python3 installed on your machine concerning sqlite3 you don’t have to do anything about it as it comes in default with python3.

Roadmap:

The path we are going to take for this article includes the following.

1. Establishing database connection.

2. Creating database table

2.1 Table deletions

3. Data insertions

4. Get inserted data id

5. Reading data from table

5.1 Fetch first result from the table

6. Updating data

7. Deleting data from table

8. Conclussion

1. Establishing database connection.

Inorder to start using sqlite database in python you have to first import python sqlite3 module from standard library which we will use for database operations.

import sqlite3

After importing the python sqlite module, we then required to state where we want want to persist our data, This can either be in memory or on physical database file. For most case you will have to go with physical database file as your data will persist for long time as long as the file or the data is not deleted.

#For in memory database
database = sqlite3.connect(":memory:")

#For file database, The prefered way 
database = sqlite3.connect("database.sqlite3")

if you try to execute this file on your machine you should find new database created (“database.sqlite3”)

Its that simple, now we already have our sqlite database. In this article we will be using the file database to store our data.

2. Table creations and deletions

Inorder to be able to create table on our database we first need to have the cursor object. We can get the cursor from the database object we had before. The cursor we get can help us with the database operations like creation and deletion of table and more.

cursor = database.cursor()

cursor.execute(“CREATE TABLE IF NOT EXISTS user(id INTEGER PRIMARY KEY, name TEXT, location_name TEXT, phone TEXT unique)”)

database.commit()

Explanation:

We are using CREATE TABLE IF NOT EXISTS statement to make sure that we don’t get Table already exists sqlite3 OperationalError.

Our database user table has 4 columns . id , name , location_name and phone column we have made the phone number unique to ensure that no user will be registered with the same phone number in the future.

2.1. Table deletions

Now have our user table ready for storing user informations, incase we changed our mind and decided to delete this table we could use cursor object to delete the table as follows.

cursor.execute('''DROP TABLE IF EXISTS user''')
database.commit()

make sure you include database.commit() statement whenever you use cursor for data manipulations or creations otherwise the changes might be ignored.

3. Data insertion

Now we know how to create and delete table using python sqlite module, the next step will be on how to insert data. Like other operations we are going to see how to do data insertions using the cursor object we had previously created from database object.

name_one = "Sophia"
location_name_one = "Kimara"
phone_one = "+255019000007"

name_two = "Salome"
location_name_two = "Bunju"
phone_two = "+255019000004"

#inserting data for user one
results = cursor.execute('''INSERT INTO user(name,location_name,phone) VALUES (?, ?,?)''',(name_one,location_name_one,phone_one))
print(results)

#inserting data for user two
results2 = cursor.execute('''INSERT INTO user(name,location_name,phone) VALUES (?, ?,?)''',(name_two,location_name_two,phone_two))
print(results2)


#committing our changes to database
database.commit()

In the code example above we have seen how to insert data into user table, we are recommended to use ‘?’ placeholder for the place of values instead of passing values direct to avoid sql-injection. We also saw that the data is passed as a tuple .

I personally don’t recommend using tuple. So in the coming example we are going to insert data into our database using dictionary.

#using dictionary as data source
user_one = {
    "name" : "Sia",
    "location_name" : "Magomeni",
    "phone":"234999999"
}
user_two = {
    "name" : "Salma",
    "location_name" : "Manzese",
    "phone":"23499999449"
}

cursor.execute('''INSERT INTO user(name,location_name,phone) VALUES (:name,:location_name,:phone)''',{**user_one})

cursor.execute('''INSERT INTO user(name,location_name,phone) VALUES (:name,:location_name,:phone)''',{**user_two})

database.commit()

As we can see when we use python dictionary to insert data into our table the code is more readable compared to using tuple for data insertion.

4. How to get inserted data id

While we insert data into our user table we can easily get the inserted data id using the lastrowid attribute of the cursor object.

#get last insert id
print(cursor.lastrowid)

5. Reading data from table

As with other database operation cursor object is also used while we want to get data from the database table.

cursor.execute('''SELECT * FROM user''')
user_data = cursor.fetchall()
for user in user_data:
    print(user[0],user[1],user[2],user[3])

5.1 Fetch the first results from the table

To fetch only the first result from the list you can use the following code segment. By utilizing the fetchone method of cursor object.

user = cursor.fetchone()
print(user[0],user[1],user[2],user[3])

6. Updating data in our table

The updating and deleting process of data is also simple when using python sqlite3 module. The following include the technique of updating data from our user table.

#user with id = 1 before updating
cursor.execute('''SELECT * FROM user WHERE id = ? ''', (chosen_id,))
user = cursor.fetchone()
print(user[0],user[1],user[2],user[3])

chosen_location = "Zanzibar"
chosen_id = 1
cursor.execute("UPDATE user SET location_name = ? WHERE id = ?", (chosen_location,chosen_id))
database.commit()

#now the user with id = 1 has location name = "Zanzibar"
cursor.execute('''SELECT * FROM user WHERE id = ? ''', (chosen_id,))
user = cursor.fetchone()
print(user[0],user[1],user[2],user[3])

Note: on place of chosen_id we have include comma , as the execute method of cursor expects the second parameter of a type tuple.

7. Deleting data from our table

Like updating process , the deletion of data from our sqlite database table is as well simple. Take a look of the following code segment.

user_id = 2
cursor.execute('DELETE FROM user WHERE id = ? ', (user_id,))
database.commit()

Make sure you remember to call the commit method. Without this neither deletion nor updating will work.

CONCLUSSION.

I hope the expanation and code examples which were provided for this python sqlite tutorial were useful to you. Please dont forget to write your comment and suggestion bellow or on instagram, facebook and twiiter @bongobinary please share this article with others and dont forget to follow like and subscribe @bongobinary on facebook , twitter, instagram and youtube. Till next time happy coding.

BONGOBINARY

Python sqlite module tutorial