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

語言: CN / TW / HK
時間 2023-02-23 10:15:23

highlight: a11y-dark

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

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

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

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

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

💡 連線資料庫

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

```python

匯入工具庫

import sqlite3

建立連線

conn = sqlite3.connect('students.db') ```

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

💡 建立表

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

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

python c = conn.cursor()

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

python 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：二進位制資料的集合，作為值儲存在資料庫中。它允許我們在資料庫中儲存文件、影象和其他多媒體檔案。

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

```python

匯入工具庫

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的程式碼：

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

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

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

💡 查詢資料

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

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

列印的輸出如下：

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

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

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

```python

匯入工具庫

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：http://github.com/ShowMeAI-Hub

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

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

Dataframe 內容如下所示：

```python

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輕鬆安裝）

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

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

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

查詢資料表的語句如下：

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

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

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

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

💡 總結

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

參考資料

本文正在參加「金石計劃 . 瓜分6萬現金大獎」

「其他文章」
「」
「」