Use sqlite3 to build a embedded database in Python

Last Updated on 2021-03-17 by Clay

Introduction

SQLite is a embedded database that is different from a general server-client structure database. SQLite is integrated into the application. If the data is relatively fixed and the amount is small, then SQLite can be said to be more appropriate.

Today I want to record is how to use the SQLite database in Python and how to use simple SQL syntax to perform the following commands:

• Create
• Insert
• Select
• Update
• Delete

The Python module I use is sqlite3.

Connect to database

If we want to use SQLite database in Python, we can use a built-in module named sqlite3 to do it. It is meaning we don’t need to install it.

First, we can use the following command to connect the database.

# -*- coding: utf-8 -*-
import sqlite3
db = sqlite3.connect('TEST.db')
cursor = db.cursor()
print('Connect ok')

Output:

Connect ok

If the database does not exist, it will be created in current project directory automatically.

Create

Okay, now we connect to the database. Maybe we need to create a TABLE.

# Create table
cursor.execute(
'''CREATE TABLE HUMAN
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL);''')

print('Table created.')
db.commit()

Output:

Table created.

As you can see, the table we named it to “HUMAN” and the table has the following fields:

• ID
• NAME
• AGE

Insert

Now that we have a table, we need to insert some data.

# Insert
cursor.execute('''INSERT INTO HUMAN (ID,NAME,AGE) VALUES (1, 'Clay', 25)''')
cursor.execute('''INSERT INTO HUMAN (ID,NAME,AGE) VALUES (2, 'Wendy', 16)''')

db.commit()
print('Insert ok')

Output:

Insert ok

Here we have inserted two pieces of data, namely Clay and Wendy.

Select

Now that we have data in our table, let’s simply perform the query action.

# Select
results = cursor.execute('''SELECT * FROM HUMAN''')
for item in results:
    print(item)

Output:

(1, 'Clay', 25)
(2, 'Wendy', 16)

Now we can show the two data we inserted.

Update

If we have already inserted our data, but suddenly we need to update the data, we can “update” the old data without deleting the old data and inserting it again.

# Update
results = cursor.execute('''UPDATE HUMAN set AGE = 26 WHERE ID = 1''')
db.commit()


# Select
results = cursor.execute('''SELECT * FROM HUMAN''')
for item in results:
    print(item)

Output:

(1, 'Clay', 26)
(2, 'Wendy', 16)

We can see that data “Clay” that was just 25 years old, is now 26 years old.

Delete

If want to completely delete a piece of data, what should we do?

# Delete
cursor.execute('''DELETE FROM HUMAN WHERE ID = 2''')
db.commit()


# Select
results = cursor.execute('''SELECT * FROM HUMAN''')
for item in results:
    print(item)

Output:

(1, 'Clay', 26)

We can delete the data with ID=2 via this command.

Finally, don’t forget to close the database.

db.close()

References

• https://docs.python.org/3.6/library/sqlite3.html

Read More

• MySQL download and installation steps note