Python SQLite数据库使用示范与性能优化的建议

创建与关闭

Python SQLite 最基本的建立 DB 数据库连接与关闭 DB 数据库。

下面要建立 tutorial.db 这个 database 连接：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
# ...
con.close()

CREATE 用法

CREATE 语法新建数据表：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
# 新建数据表
cur.execute("CREATE TABLE movie(title, year, score)")
con.commit()
con.close()

INSERT 用法与

SQLite INSERT 语法新增/插入数据：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
cur.execute("INSERT INTO movie VALUES('Monty Python and the Holy Grail', 1975, 8.2),('And Now for Something Completely Different', 1971, 7.5)")
con.commit()  # 记得在执行 INSERT 后提交事务
con.close()

SQLite INSERT 语法插入多笔数据，插入大量多笔数据可使用 cur.executemany()，在 executemany 参数中可以用 ? 表示之后会带入的数据：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
data = [("Monty Python Live at the Hollywood Bowl", 1982, 7.9), ("Monty Python's The Meaning of Life", 1983, 7.5), ("Monty Python's Life of Brian", 1979, 8.0)]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # 记得在执行 INSERT 后提交事务
con.close()

SELECT 用法

SELECT 语法查询数据，如果要查询 tutorial.db 数据库里有什么数据表的话可以使用下面代码：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
# 查询数据
ret = cur.execute("SELECT name FROM sqlite_master")
print(ret.fetchall())
con.close()

执行结果如下，

['movie',]

如果要查询 movie 数据表里有什么数据的话可以这样写：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
# 查询数据
ret = cur.execute("SELECT * FROM movie")
print(ret.fetchall())
con.close()

执行结果如下，

('Monty Python and the Holy Grail', 1975, 8.2), ('And Now for Something Completely Different', 1971, 7.5), ('Monty Python Live at the Hollywood Bowl', 1982, 7.9), ('Monty Python's The Meaning of Life', 1983, 7.5), ('Monty Python's Life of Brian', 1979, 8.0)

如果要查询 movie 数据表里 score 栏目的话可以这样写：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
# 查询数据
ret = cur.execute("SELECT score FROM movie")
print(ret.fetchall())
con.close()

执行结果如下，

(8.2,), (7.5,), (7.9,), (7.5,), (8.0,)

如果要查询 movie 数据表里 year 跟 title 栏目的话并且按 year 排序的话可以这样写，

上面是示范 fetchall 一次取得所有结果，以下示范用 for 循环来处理 query 每笔结果的数据，

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
    print(row)
con.close()

执行结果如下，

(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, 'Monty Python's Life of Brian')
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, 'Monty Python's The Meaning of Life')

SELECT 搭配 WHERE 语法查询特定条件的数据

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
ret = cur.execute("SELECT title, year FROM movie WHERE title='Monty Python and the Holy Grail'")
ret = cur.execute("SELECT title, year FROM movie WHERE year=1971")
print(ret.fetchall())
ret = ret.fetchone()
if ret is None:
    print("is None")
else:
    print(ret)
con.close()

执行结果如下，

('Monty Python and the Holy Grail', 1975)

UPDATE 用法

SQLite UPDATE 语法更新数据，如果 movie 数据表找到 score 分数为 8.2 的数据时，更新它们的 title 与 year，

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
cur.execute("UPDATE movie SET title=?, year=? WHERE score=?", ("12345", 2000, 8.2))
con.commit()
con.close()

如果有多笔符合的话会更新多笔。

DELETE 用法

DELETE 语法删除数据表，如果要删除 movie 数据表里的 score 分数为 8.2 的数据可以这么写：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
cur.execute("DELETE FROM movie WHERE score=?", (8.2,))
con.commit()
con.close()

如果要删除 movie 数据表里的所有数据可以这么写：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
cur.execute("DELETE FROM movie")
con.commit()
con.close()