Python SQLite 資料庫教學與範例

本篇 ShengYu 介紹 Python SQLite 資料庫教學與範例。

以下 Python SQLite 資料庫範例大概分為以下幾部份,

  • Python SQLite CREATE 用法與範例
  • Python SQLite INSERT 用法與範例
  • Python SQLite SELECT 用法與範例
  • Python SQLite UPDATE 用法與範例
  • Python SQLite DELETE 用法與範例

這邊先介紹 Python SQLite 最基本的建立 DB 資料庫連線與關閉 DB 資料庫。
例如我要建立 tutorial.db 這個 database 連線,

python3-sqlite3-tutorial-open-db.py
1
2
3
4
5
6
7
8
9
10
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3

con = sqlite3.connect("tutorial.db")
cur = con.cursor()

# ...

con.close()

以上就是最簡單的 Python SQLite 範例,接下來我們來看看 Python SQLite 常見的操作範例。

Python SQLite CREATE 用法與範例

以下示範 Python SQLite CREATE 語法新建資料表,

python3-sqlite3-tutorial-create.py
1
2
3
4
5
6
7
8
9
10
11
#!/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()

Python SQLite INSERT 用法與範例

以下示範 Python SQLite INSERT 語法新增/插入資料,

python3-sqlite3-tutorial-insert.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/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() # Remember to commit the transaction after executing INSERT.
con.close()

以下示範 Python SQLite INSERT 語法插入多筆資料,插入大量多筆資料可使用 cur.executemany(),在 executemany 參數中可以用 ? 表示之後會帶入的資料,

python3-sqlite3-tutorial-insert2.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/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() # Remember to commit the transaction after executing INSERT.
con.close()

Python SQLite SELECT 用法與範例

以下示範 Python SQLite SELECT 語法查詢資料,
Python SQLite 如果要查詢 tutorial.db 資料庫裡有什麼資料表的話可以這樣寫,

python3-sqlite3-tutorial-select.py
1
2
3
4
5
6
7
8
9
10
11
12
#!/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()

執行結果如下,

1
[('movie',)]

Python SQLite 如果要查詢 movie 資料表裡有什麼資料的話可以這樣寫,

python3-sqlite3-tutorial-select2.py
1
2
3
4
5
6
7
8
9
10
11
12
#!/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()

執行結果如下,

1
[('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)]

Python SQLite 如果要查詢 movie 資料表裡 score 欄位的話可以這樣寫,

python3-sqlite3-tutorial-select3.py
1
2
3
4
5
6
7
8
9
10
11
12
#!/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()

執行結果如下,

1
[(8.2,), (7.5,), (7.9,), (7.5,), (8.0,)]

如果要查詢 movie 資料表裡 year 跟 title 欄位的話並且按 year 排序的話可以這樣寫,
上面是示範 fetchall 一次取得所有結果,以下示範用 for 迴圈來處理 query 每筆結果的資料,

python3-sqlite3-tutorial-select4.py
1
2
3
4
5
6
7
8
9
10
11
#!/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()

執行結果如下,

1
2
3
4
5
(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")

以下示範 Python SQLite SELECT 搭配 WHERE 語法查詢特定條件的資料,

python3-sqlite3-tutorial-select-where.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#!/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()

執行結果如下,

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

Python SQLite UPDATE 用法與範例

以下示範 Python SQLite UPDATE 語法更新資料,
如果 movie 資料表找到 score 分數為 8.2 的資料時,更新它們的 title 與 year,

python3-sqlite3-tutorial-update.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/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()

如果有多筆符合的話會更新多筆。

Python SQLite DELETE 用法與範例

以下示範 Python SQLite DELETE 語法刪除資料表,
Python SQLite 如果要刪除 movie 資料表裡的 score 分數為 8.2 的資料可以這樣寫,

python3-sqlite3-tutorial-delete.py
1
2
3
4
5
6
7
8
9
10
11
12
#!/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")
cur.execute("DELETE FROM movie WHERE score=?", (8.2,))

con.commit()
con.close()

Python SQLite 如果要刪除 movie 資料表裡的所有資料可以這樣寫,

python3-sqlite3-tutorial-delete2.py
1
2
3
4
5
6
7
8
9
10
11
#!/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()

以上就是 Python SQLite 資料庫教學與範例介紹,
如果你覺得我的文章寫得不錯、對你有幫助的話記得 Facebook 按讚支持一下!

其它參考
https://docs.python.org/zh-tw/3/library/sqlite3.html

其它相關文章推薦
Python 新手入門教學懶人包