Python sqlite3模块使用及连接SQLite数据库进行常见操作

SQLite 是一个软件库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。

Python的数据库模块有统一的接口标准,所以数据库操作都有统一的模式,基本上都是涉及数据库的增、删、改、查,主要遵循下面几步:

一、创建数据表-CREATE

#!/usr/bin/env python3

import sqlite3

conn = sqlite3.connect('data.db')
curs = conn.cursor()
print("Connect Successfully")

curs.execute('''CREATE TABLE STUDENT(
	ID INT PRIMARY KEY     NOT NULL,
	NAME           TEXT    NOT NULL,
	AGE            INT)'''
)
conn.commit()
print("Create Successfully");
curs.close()
conn.close()
sqlite> .tables
STUDENT
sqlite> .schema
CREATE TABLE STUDENT(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT);

二、插入数据-INSERT

#!/usr/bin/env python3

import sqlite3

conn = sqlite3.connect('data.db')
curs = conn.cursor()
print("Connect Successfully")

curs.execute("INSERT INTO STUDENT VALUES(?, ?, ?)", (1, '谢银才', 88))
curs.execute("INSERT INTO STUDENT VALUES(?, ?, ?)", (2, '欧阳锋', 26))
curs.execute("INSERT INTO STUDENT VALUES(?, ?, ?)", (3, '李红芳', 18))
curs.execute("INSERT INTO STUDENT VALUES(?, ?, ?)", (4, '张宏芳', 28))
conn.commit()
print("Insert Successfully");
curs.close()
conn.close()
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM STUDENT;
ID          NAME        AGE
----------  ----------  ----------
1           谢银才         88
2           欧阳锋         26
3           李红芳         18
4           张宏芳         28

三、删除数据-DELETE

#!/usr/bin/env python3

import sqlite3

conn = sqlite3.connect('data.db')
curs = conn.cursor()
print("Connect Successfully")

curs.execute("DELETE FROM STUDENT WHERE ID=?", (4,))
conn.commit()
print("Delete Successfully");
curs.close()
conn.close()
sqlite> SELECT * FROM STUDENT;
ID          NAME        AGE
----------  ----------  ----------
1           谢银才         88
2           欧阳锋         26
3           李红芳         18

四、修改数据-UPDATE

#!/usr/bin/env python3

import sqlite3

conn = sqlite3.connect('data.db')
curs = conn.cursor()
print("Connect Successfully")

curs.execute("UPDATE STUDENT SET AGE=? WHERE ID=?", (20, 3))
conn.commit()
print("Update Successfully");
curs.close()
conn.close()
sqlite> SELECT * FROM STUDENT;
ID          NAME        AGE
----------  ----------  ----------
1           谢银才         88
2           欧阳锋         26
3           李红芳         20

五、查找数据-SELECT

#!/usr/bin/env python3

import sqlite3

conn = sqlite3.connect('data.db')
curs = conn.cursor()
print("Connect Successfully")

print("ID", "NAME", "AGE", sep="\t\t")
curs.execute("SELECT ID, NAME, AGE FROM STUDENT")
rows = curs.fetchall()
for row in rows:
	print(row[0], row[1], row[2], sep="\t\t")

print("Select Successfully")
curs.close()
conn.close()
Connect Successfully
ID		NAME		AGE
1		谢银才		88
2		欧阳锋		26
3		李红芳		18
Select Successfully
sqlite> SELECT * FROM STUDENT;
ID          NAME        AGE
----------  ----------  ----------
1           谢银才         88
2           欧阳锋         26
3           李红芳         20

Leave a Reply

Your email address will not be published. Required fields are marked *