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()