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