Use Python to operate on the SQLite database

SQLite is an embedded database whose database is a file. Because SQLite itself is written by C and is small in size, it is often integrated into a variety of applications, even in IOS and Android's APP.

Python has SQLite3 built in, so you don't need to install anything to use SQLite in Python.

Before using SQLite, we need to understand a few concepts:

A table is a collection of relational data in a database. A database usually contains multiple tables, such as tables for students, tables for classes, tables for schools, and so on. Tables are associated with each other by foreign keys.

To operate on a relational database, you first connect to the database, and one database connection is called Connection.

After connecting to the database, you need to open the cursor, call it Cursor, execute the SQL statement through Cursor, and then, get the execution result.

1. Connect to the database

import sqlite3
# The database name 
db_name = "test.db"
# The name of the table 
table_name = "catalog"
conn = sqlite3.connect(db_name)

2. Open the cursor

rs = conn.cursor()

3. Build table

sql = 'create table ' + table_name + ' (id varchar(20) primary key, pid integer, name varchar(10))'
try:
 rs.execute(sql)
 print(" Build table success ")
except:
 print(" To build the table failed ")

4. Add, delete, change and check operation

#  Add: add 3 records 
sql = "Insert into " + table_name + " values ('001', 1, ' zhang 3')"
try:
 rs.execute(sql)
 # Commit the transaction 
 conn.commit()
 print(" Insert the success ")
except:
 print(" Insert the failure ")
sql = "Insert into " + table_name + " values ('002', 2, ' li 4')"
try:
 rs.execute(sql)
 # Commit the transaction 
 conn.commit()
 print(" Insert the success ")
except:
 print(" Insert the failure ")
sql = "Insert into " + table_name + " values ('003', 3, ' The king 5')"
try:
 rs.execute(sql)
 # Commit the transaction 
 conn.commit()
 print(" Insert the success ")
except:
 print(" Insert the failure ")
#  Delete: delete pid Is equal to the 3 The record of 
sql = "Delete from " + table_name + " where pid = 3"
try:
 rs.execute(sql)
 conn.commit()
 print(" Delete the success ")
except:
 print(" Delete failed ")
#  Change: pid Is equal to the 2 The record of pid Instead of 1
sql = "Update " + table_name + " set pid = 1 where pid = 2"
try:
 rs.execute(sql)
 conn.commit()
 print(" Modify the success ")
except:
 print(" Modify the failure ")
#  check 
#  Query all table names in the database 
sql = "Select name From sqlite_master where type = 'table'"
res = rs.execute(sql)
print(res.fetchall())
#  All records in the query table 
sql = "Select * from " + table_name
try: 
 res = rs.execute(sql)
 print(res.fetchall())
except:
 print([])

5. Close the cursor

rs.close()

Close the database connection

conn.close()