Detailed explanation of examples of Python operations on SQLite databases

This article mainly introduces a simple tutorial on Python's operation of SQLite database. SQLite is an embedded database, and its database is a file. Since SQLite itself is written in C and is small in size, it is often integrated into various applications, even in IOS and Android APPs.

SQLite is an embedded database, and its database is a file. Since SQLite itself is written in C and is small in size, it is often integrated into various applications, even in IOS and Android APPs.

Python has built-in SQLite3, so when using SQLite in Python, you don’t need to install anything, just use it directly.

Before using SQLite, we must first understand a few concepts:

A table is a collection of relational data stored in a database. A database usually contains Contains multiple tables, such as student tables, class tables, school tables, etc. Tables are related through foreign keys.

To operate a relational database, you must first connect to the database. A 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 obtain the execution result.

1. Connect to the database

import sqlite3
#数据库名
db_name = "test.db"
#表名
table_name = "catalog"
conn = sqlite3.connect(db_name)

2. Open the cursor

rs = conn.cursor()

3. Create table

sql = 'create table ' + table_name + ' (id varchar(20) primary key, pid integer, name varchar(10))'
try:
 rs.execute(sql)
 print("建表成功")
except:
 print("建表失败")

4. Add, delete, modify and check operations

# 增：增加三条记录
sql = "Insert into " + table_name + " values ('001', 1, '张三')"
try:
 rs.execute(sql)
 #提交事务
 conn.commit()
 print("插入成功")
except:
 print("插入失败")
sql = "Insert into " + table_name + " values ('002', 2, '李四')"
try:
 rs.execute(sql)
 #提交事务
 conn.commit()
 print("插入成功")
except:
 print("插入失败")
sql = "Insert into " + table_name + " values ('003', 3, '王五')"
try:
 rs.execute(sql)
 #提交事务
 conn.commit()
 print("插入成功")
except:
 print("插入失败")
# 删：删除pid等于3的记录
sql = "Delete from " + table_name + " where pid = 3"
try:
 rs.execute(sql)
 conn.commit()
 print("删除成功")
except:
 print("删除失败")
# 改：将pid等于2的记录的pid改为1
sql = "Update " + table_name + " set pid = 1 where pid = 2"
try:
 rs.execute(sql)
 conn.commit()
 print("修改成功")
except:
 print("修改失败")
# 查
# 查询数据库中所有表名
sql = "Select name From sqlite_master where type = 'table'"
res = rs.execute(sql)
print(res.fetchall())
# 查询表中所有记录
sql = "Select * from " + table_name
try: 
 res = rs.execute(sql)
 print(res.fetchall())
except:
 print([])

5. Close the cursor

rs.close()

6. Close the database connection

conn.close()

The above is the detailed content of Detailed explanation of examples of Python operations on SQLite databases. For more information, please follow other related articles on the PHP Chinese website!