Python中內建資料庫！SQLite使用指南！ ⛵

ShowMeAI發表於2022-12-02
Python資料庫SQLite

? 作者：韓信子@ShowMeAI
? Python3◉技能提升系列：https://www.showmeai.tech/tutorials/56
? 本文地址：https://www.showmeai.tech/article-detail/390
? 宣告：版權所有，轉載請聯絡平臺與作者並註明出處
? 收藏ShowMeAI檢視更多精彩內容

Python 是一個廣泛使用的程式語言，在各個領域都能發揮很大的作用，而且安裝 Python 環境的同時，我們也安裝了很多其他出色的工具，其中當然少不了資料庫。

Python 內建了 SQLite3，在 Python 中使用 SQLite，不需要安裝任何東西，可以直接使用。我們只需要匯入內建 Python 庫sqlite3就可以開始使用這個資料庫啦！

在本篇內容中，ShowMeAI將帶大家一起來了解，如何基於 Python 環境連線到資料庫、建立表、插入資料，查詢資料，以及與 Pandas 工具庫搭配使用。

對於 SQL 更詳盡的內容，歡迎大家查閱ShowMeAI製作的速查表：

? 程式語言速查表 | SQL 速查表

? 連線資料庫

要使用資料庫，我們需要先連線資料庫。在 Python 中很簡單，我們只需匯入sqlite3工具庫並使用.connect函式，函式的引數是資料庫名稱，在本例中為students.db。

# 匯入工具庫
import sqlite3
# 建立連線
conn = sqlite3.connect('students.db')

我們第1次執行上面程式碼的話，會在工作目錄中建立一個名為“students.db”的新檔案。

? 建立表

接下來我們可以在連線的資料庫中建立一個表，並將資料插入其中。

在建立表之前，我們需要建立一個遊標 cursor（用於建立連線以執行 SQL 查詢的物件），我們將使用它來建立表、插入資料等。具體的操作如下程式碼：

c = conn.cursor()

完成遊標建立後，我們可以使用 .execute方法執行SQL語句，在我們的資料庫中建立一個新表。在引號內，我們編寫了建表 SQL 語句，使用CREATE TABLE語句：

c.execute("""CREATE TABLE students (
name TEXT,
age INTEGER,
height REAL
)""")

我們在建立表的欄位時，需要定義資料型別。SQLite 只有 5 種資料型別：

  • Null：缺失值
  • INTEGER：沒有小數點的數字（例如，1、2、3、4）
  • REAL：帶小數點的數字（例如，6.2、7.6、11.2）
  • TEXT：任何字元資料
  • Blob：二進位制資料的集合，作為值儲存在資料庫中。它允許我們在資料庫中儲存文件、影像和其他多媒體檔案。

我們要提交上述語句，並關閉連線。截止目前的完整程式碼如下：

# 匯入工具庫
import sqlite3

# 建立連線
conn = sqlite3.connect('students.db')

# 遊標
c = conn.cursor()  

# 建表語句
c.execute("""CREATE TABLE students (
            name TEXT,
            age INTEGER,
            height REAL
    )""")

# 執行
conn.commit()

# 關閉連線
conn.close()

? 插入資料

我們可以使用.execute執行INSERT INTO語句在“students”表中插入一行資料。下面是新增一個20 歲，身高 1.9 米的學生mark的程式碼：

c.execute("INSERT INTO students VALUES ('mark', 20, 1.9)")

我們也可以一次插入多行，換成.executemany方法即可。不過注意一下，我們在INSERT語句中會使用?作為佔位符。程式碼如下所示：

all_students = [
('john', 21, 1.8),
('david', 35, 1.7),
('michael', 19, 1.83),
]
c.executemany("INSERT INTO students VALUES (?, ?, ?)", all_students)

? 查詢資料

我們可以使用SELECT語句檢視我們的資料，注意一下如果要獲取資料並輸出，需要執行.fetchall方法：

c.execute("SELECT * FROM students")
print(c.fetchall())

列印的輸出如下：

[(‘mark’, 20, 1.9), (‘john’, 21, 1.8), (‘david’, 35, 1.7), (‘michael’, 19, 1.83)]

當然，大家其實可以配合一些線上工具來完成資料的直觀查詢，例如 ?SQLiteViewer。我們只需拖動前面 Python 程式碼生成的 .db 資料庫檔案進去，即可檢視其內容。

截止目前為止的所有程式碼如下

# 匯入工具庫
import sqlite3

# 建立連線
conn = sqlite3.connect('students.db')

# 遊標
c = conn.cursor()  

# 建表語句
c.execute("""CREATE TABLE students (
            name TEXT,
            age INTEGER,
            height REAL
    )""")

# 插入單條資料
c.execute("INSERT INTO students VALUES ('mark', 20, 1.9)")


# 插入多條資料
all_students = [
    ('john', 21, 1.8),
    ('david', 35, 1.7),
    ('michael', 19, 1.83),
]
c.executemany("INSERT INTO students VALUES (?, ?, ?)", all_students)

# 查詢資料
c.execute("SELECT * FROM students")
print(c.fetchall())

# 執行
conn.commit()

# 關閉連線
conn.close()

其實大家在SQL中的更高階的複雜查詢，都可以透過上述方式進行查詢和互動

? SQLite 配合 Pandas 應用

SQLite 可以與 Pandas 中的Dataframe搭配使用。

例如，我們有一個名為?population_total.csv的 csv 檔案，大家可以透過 ShowMeAI 的百度網盤地址下載。

? 實戰資料集下載（百度網盤）：公✦眾✦號『ShowMeAI研究中心』回覆『實戰』，或者點選 這裡 獲取本文 [61]Python內建資料庫SQLite使用指南 『SQLite示例資料集』

⭐ ShowMeAI官方GitHub：https://github.com/ShowMeAI-Hub

我們可以方便地使用 Pandas 讀取它：

import pandas as pd
df = pd.read_csv("population_total.csv")

Dataframe 內容如下所示：

>>> df             country year    population0             China  2020.0  1.439324e+09
1             China  2019.0  1.433784e+09
2             China  2018.0  1.427648e+09
3             China  2017.0  1.421022e+09
4             China  2016.0  1.414049e+09
...             ...     ...           ...
4180  United States  1965.0  1.997337e+08
4181  United States  1960.0  1.867206e+08
4182  United States  1955.0  1.716853e+08
4183          India  1960.0  4.505477e+08
4184          India  1955.0  4.098806e+08

我們可以把 pandas Dataframe 形態的資料一次性匯入 SQLite 資料庫中，這裡我們需要藉助 sqlalchemy 工具庫（可以透過pip install sqlalchemy輕鬆安裝）

from sqlalchemy import create_engine
engine = create_engine('sqlite://', echo=False)

下面我們就可以輕鬆把資料匯入資料庫並建立 population 表：

df.to_sql("population", con=engine)

查詢資料表的語句如下：

engine.execute("SELECT * FROM population").fetchall()

如果你想建立表的同時生成一個 sqlite 檔案（前面的操作，生成的是記憶體資料庫），可以如下方式操作。（我們建立了一個mydb.db檔案作為資料庫的實體檔案）。

from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydb.db")
df.to_sql("population", engine)

這個mydb.db就和前面的使用方式一樣啦，我們也可以使用 SQLite 檢視器檢視資料內容。

? 總結

以上就是ShowMeAI帶大家簡單瞭解python的內建資料庫SQLite的使用方法，我們可以很方便地完成建表、插入資料、查詢資料，也可以配合pandas進行靈活使用，大家快快用起來吧！

參考資料

推薦閱讀

相關文章