[python] 基於Dataset庫運算元據庫

落痕的寒假發表於2023-12-29

dataset庫是Python中一個用於運算元據庫的簡單庫，它提供了一種簡潔的方式與各種關係型資料庫進行互動，例如SQLite、MySQL、PostgreSQL 等。你可以使用dataset庫來執行查詢、插入、更新和刪除操作，而無需編寫複雜的SQL語句。dataset庫適用於小規模的資料儲存和查詢場景，相比csv和json檔案只能透過程式語言來處理資料，dataset支援使用SQL語言進行查詢，提供了豐富的查詢功能和靈活性。對於大規模資料和高併發場景，專業資料庫系統如MySQL、PostgreSQL等更具優勢。

dataset官方倉庫地址：dataset。dataset官方檔案地址：dataset-doc。安裝命令如下：

pip install dataset

import dataset
# 檢視版本
dataset.__version__

'1.6.2'

1 使用說明

1.1 資料庫操作

資料庫建立

connect函式是dataset庫中用於連線到資料庫並返回一個資料庫物件。它的使用方式如下：

# 建立一個連線到名為mydatabase.db的SQLite資料庫的物件
# 如果要連線的資料庫不存在，則自動建立
db = dataset.connect('sqlite:///mydatabase.db')
# 如果要連線到MySQL資料庫（需要安裝相應元件），可以使用以下語法：
# db = dataset.connect('mysql://user:password@localhost/mydatabase')
# 如果要連線到PostgreSQL資料庫（需要安裝相應元件），可以使用以下語法：
# db = dataset.connect('postgresql://user:password@localhost/mydatabase')

表格查詢與建立

dataset提供get_table函式或直接表名索引來建立或載入一個表格。同時，dataset也提供create_table函式建立一個新的表格，也可以使用load_table函式載入現有的表格。

# 使用db['table_name']語法獲取指定表的引用，其中table_name是表的名稱
# 如果表不存在，dataset庫將自動建立它，但是隻有資料插入才會儲存
table = db['mytable']
# 或者使用如下函式：
# table = db.get_table('mytable')

# 建立一個新表格，原有表格會被覆蓋
# 該表沒有自定義的主鍵或型別。Dataset庫會預設建立一個名為 'id' 的整數型別的主鍵。
table = db.create_table('mytable')

此外也可以指定表格建立時的各種引數，dataset主要支援型別有：

db.types.integer：表示整數型別。
db.types.float：表示浮點數型別。
db.types.boolean：表示布林型別。
db.types.datetime：表示日期時間型別。
db.types.date：表示日期型別。
db.types.text：表示文字字串型別。

# 指定了自定義主鍵為 'age'
# db.create_table('mytable', 'age')
# 指定了自定義的主鍵為 'city'，並且指定該主鍵的型別為文字型別
# db.create_table('mytable', primary_id='city', primary_type=db.types.text)
# 指定沒有主鍵
# db.create_table('mytable', primary_id=False)

# 載入現有表格
table = db.load_table('mytable')
# 如果表格不存在，則會報錯
# table = db.load_table('user')

# 也可以透過has_table函式查詢是否存在某個表
db.has_table('user')

False

# 檢視當前資料庫下所有表格
db.tables

[]

直接在資料庫上執行sql查詢語句

dataset庫提供query函式用於過濾和查詢資料集。它接受一個字串引數，該引數列示一條 SQL 查詢語句，用於選擇符合條件的資料。

# statement = 'SELECT * FROM mytable'
# for row in db.query(statement):
#    print(row)

1.2 表格操作

插入資料

# 使用db['table_name']語法獲取指定表的引用，其中table_name是表的名稱
# 如果表不存在，dataset庫將自動建立它
table = db['mytable']

insert用於插入資料。

# 插入一條包含名字、年齡和電子郵件的記錄
data = {'name': 'John', 'age': 25, 'email': 'john@example.com'}
table.insert(data)
# 插入一條包含名字、年齡,電子郵件的記錄為空
table.insert(dict(name='Alice', age=25))

insert_ignore用於插入一行資料，但只有在keys中列的值不重複時才會插入。

table.insert_ignore(dict(name='Alice', age=25),keys=['name'])

False

insert_many函式用於向資料庫表中批次插入資料。它接受一個列表作為引數，該列表中的每個元素都是一個字典，表示要插入的一行資料。使用該函式要比逐個新增快很多。

data = [
    {'name': 'John', 'age': 25, 'city': 'New York'},
    {'name': 'Alice', 'age': 30, 'city': 'Los Angeles'},
    {'name': 'Bob', 'age': 35, 'city': 'San Francisco'}
]
table.insert_many(data)

插入列

# 插入名為 key 的列，資料型別為文字（text），並且設定了唯一約束（unique=True）
# table.create_column('key', db.types.text,unique=True)
# 插入名為 food 的列，資料型別為文字（text）
table.create_column('food', db.types.text)

# 建立一個列，第一個值為列名，第二個值用於自動猜測該列的型別
table.create_column_by_example('length', 4.2)

檢視資料

# 檢視錶格行數
len(table)

# 獲取表中的所有資料
data = list(table.all())

# 列印資料
for row in data:
    print(row)

OrderedDict([('id', 1), ('name', 'John'), ('age', 25), ('email', 'john@example.com'), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 2), ('name', 'Alice'), ('age', 25), ('email', None), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 3), ('name', 'John'), ('age', 25), ('email', None), ('city', 'New York'), ('food', None), ('length', None)])
OrderedDict([('id', 4), ('name', 'Alice'), ('age', 30), ('email', None), ('city', 'Los Angeles'), ('food', None), ('length', None)])
OrderedDict([('id', 5), ('name', 'Bob'), ('age', 35), ('email', None), ('city', 'San Francisco'), ('food', None), ('length', None)])

# 直接列印資料
for row in table:
    print(row)

OrderedDict([('id', 1), ('name', 'John'), ('age', 25), ('email', 'john@example.com'), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 2), ('name', 'Alice'), ('age', 25), ('email', None), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 3), ('name', 'John'), ('age', 25), ('email', None), ('city', 'New York'), ('food', None), ('length', None)])
OrderedDict([('id', 4), ('name', 'Alice'), ('age', 30), ('email', None), ('city', 'Los Angeles'), ('food', None), ('length', None)])
OrderedDict([('id', 5), ('name', 'Bob'), ('age', 35), ('email', None), ('city', 'San Francisco'), ('food', None), ('length', None)])

# 表的列名
table.columns

['id', 'name', 'age', 'email', 'city', 'food', 'length']

# 是否存在某列
table.has_column('sex')

False

查詢資料

# 根據欄位查詢
results = table.find(name='John')
# 遍歷結果
for row in results:
    print(row['name'], row['city'])

John None
John New York

# 多欄位查詢
results = table.find(name='John',city=None)
# 遍歷結果
for row in results:
    print(row['name'], row['city'])

John None

# 僅返回第一個結果
results =  table.find_one(name='John')
results

OrderedDict([('id', 1),
             ('name', 'John'),
             ('age', 25),
             ('email', 'john@example.com'),
             ('city', None),
             ('food', None),
             ('length', None)])

# 基於id查詢
results = table.find(id=[1, 2, 4])
# 遍歷結果
for row in results:
    print(row['name'], row['city'])

John None
Alice None
Alice Los Angeles

# 找出age大於等於30的結果
results = table.find(age={'>=': 30})
for row in results:
    print(row['name'], row['age'])

Alice 30
Bob 35

# 找出age在21到30之間的結果
results = table.find(age={'between': [21, 30]})
for row in results:
    print(row['name'], row['age'])

John 25
Alice 25
John 25
Alice 30

# 篩選某個欄位下的唯一項
results = table.distinct('name')
for row in results:
    print(row)

OrderedDict([('name', 'Alice')])
OrderedDict([('name', 'Bob')])
OrderedDict([('name', 'John')])

dataset庫還支援更豐富的查詢操作，具體可以檢視檔案：dataset-query。

更新資料

update函式用於更新單條記錄。

# 第一引數為要更新的欄位和對應的新值，如果欄位不存在則會新增該欄位
# 第二個引數keys用於指定根據哪些列來篩選要更新的物件
# 下面程式碼表示將name為'John'的行更新資料
affected_rows = table.update(dict(name= 'John',age=23,email="qq.com"), keys=['name'])
print("受影響的行數：", affected_rows)

受影響的行數： 2

# 獲取表中的所有資料
data = list(table.all())

# 列印資料
for row in data:
    print(row)

OrderedDict([('id', 1), ('name', 'John'), ('age', 23), ('email', 'qq.com'), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 2), ('name', 'Alice'), ('age', 25), ('email', None), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 3), ('name', 'John'), ('age', 23), ('email', 'qq.com'), ('city', 'New York'), ('food', None), ('length', None)])
OrderedDict([('id', 4), ('name', 'Alice'), ('age', 30), ('email', None), ('city', 'Los Angeles'), ('food', None), ('length', None)])
OrderedDict([('id', 5), ('name', 'Bob'), ('age', 35), ('email', None), ('city', 'San Francisco'), ('food', None), ('length', None)])

update_many也被提供用於批次更新資料。

# 更新多行資料
rows = [
    {'name': 'Alice', 'age': 123},
    {'name': 'Bob', 'age': 18}
]
# 該函式沒有返回值
table.update_many(rows,keys=['name'])

此外也可以用upsert和upsert_many插入更新單條或多條記錄，即如果存在具有匹配關鍵字的行，則這些行將被更新，否則將在表中插入新行。具體使用類似update和update_many。

刪除資料

delete函式用於刪除行，如下所示：

for row in table:
    print(row)

OrderedDict([('id', 1), ('name', 'John'), ('age', 23), ('email', 'qq.com'), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 2), ('name', 'Alice'), ('age', 123), ('email', None), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 3), ('name', 'John'), ('age', 23), ('email', 'qq.com'), ('city', 'New York'), ('food', None), ('length', None)])
OrderedDict([('id', 4), ('name', 'Alice'), ('age', 123), ('email', None), ('city', 'Los Angeles'), ('food', None), ('length', None)])
OrderedDict([('id', 5), ('name', 'Bob'), ('age', 18), ('email', None), ('city', 'San Francisco'), ('food', None), ('length', None)])

# 刪除age為23的記錄
table.delete(age=23)
for row in table:
    print(row)

OrderedDict([('id', 2), ('name', 'Alice'), ('age', 123), ('email', None), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 4), ('name', 'Alice'), ('age', 123), ('email', None), ('city', 'Los Angeles'), ('food', None), ('length', None)])
OrderedDict([('id', 5), ('name', 'Bob'), ('age', 18), ('email', None), ('city', 'San Francisco'), ('food', None), ('length', None)])

# 刪除age為30且name為'John'的記錄
table.delete(age=30, name='John')
for row in table:
    print(row)

OrderedDict([('id', 2), ('name', 'Alice'), ('age', 123), ('email', None), ('city', None), ('food', None), ('length', None)])
OrderedDict([('id', 4), ('name', 'Alice'), ('age', 123), ('email', None), ('city', 'Los Angeles'), ('food', None), ('length', None)])
OrderedDict([('id', 5), ('name', 'Bob'), ('age', 18), ('email', None), ('city', 'San Francisco'), ('food', None), ('length', None)])

# 找出age大於等於30的結果
table.delete(age={'>=': 30})
for row in table:
    print(row)

OrderedDict([('id', 5), ('name', 'Bob'), ('age', 18), ('email', None), ('city', 'San Francisco'), ('food', None), ('length', None)])

drop_column函式會從資料集中刪除指定的列，如下所示：

# sqlite不支援
# table.drop_column('age')
# for row in table:
#     print(row)

1.3 事務操作

事務是一組資料庫操作，要麼全部成功執行，要麼全部回滾。這可以確保資料的一致性和完整性。下面程式碼展示瞭如何建立一個事務物件。在這種情況下，所有更新都會立即提交，或者，在出現異常的情況下，立即回滾所有操作。事務透過上下文管理器得到支援，因此可以透過with語句使用：

with dataset.connect('sqlite:///mydatabase.db') as tx:
    # 在這裡執行資料庫操作
    # 如果所有操作都成功，事務會自動提交
    # 如果發生錯誤，事務會自動回滾
    tx['user'].insert(dict(name='John Doe', age=46, country='China'))

當然以上程式碼可以顯式呼叫函式begin、commit和rollback來獲得相同的功能：

db = dataset.connect('sqlite:///mydatabase.db')
db.begin()
try:
    db['user'].insert(dict(name='John Doe', age=46, country='China'))
    db.commit()
except:
    db.rollback()

除此之外，也支援巢狀事務如下所示：

db = dataset.connect()
with db as tx1:
    tx1['user'].insert(dict(name='John Doe', age=46, country='China'))
    with db as tx2:
        tx2['user'].insert(dict(name='Jane Doe', age=37, country='France', gender='female'))

2 參考

Python運算元據庫（3）
2019-01-09
Python
Python學習：運算元據庫
2020-12-28
Python
運算元據庫
2020-07-23
運算元據庫表
2019-01-23
jmeter運算元據庫
2024-08-23
JMeter
DDL:運算元據庫
2024-08-30
教你如何用python運算元據庫mysql!!
2019-02-19
PythonMySql
利用 Sequelize 來運算元據庫
2019-03-01
java 運算元據庫備份
2021-09-23
Java
Android中使用LitePal運算元據庫
2020-03-13
Android
Django在Ubuntu下運算元據庫
2020-11-12
DjangoUbuntu
基於Python的介面自動化實戰-基礎篇之pymysql模組運算元據庫
2021-01-05
PythonMySql
python運算元據
2024-08-17
Python
肖sir__jmeter之運算元據庫
2024-04-11
JMeter
資料庫誤運算元據恢復
2023-04-28
資料庫
Pandas 基礎 (19) - 運算元據庫 (read_sql, to_sql)
2019-05-13
SQL
如何讓Designer更好地運算元據庫物件
2023-04-27
物件
lavavel 中運算元據庫查詢別名
2019-12-02
spring-boot-route（九）整合JPA運算元據庫
2020-10-08
Springboot
spring-boot-route（七）整合jdbcTemplate運算元據庫
2020-10-07
SpringbootJDBC
spring-boot-route（八）整合mybatis運算元據庫
2020-10-07
SpringbootMyBatis
uniapp單機軟體運算元據庫（安卓）
2024-08-12
APP安卓
Go語言運算元據庫及其常規操作
2021-05-13
Go
sql運算元據庫（2）--->DQL、資料庫備份和還原
2021-01-04
SQL資料庫
好程式設計師分享DDL之運算元據庫
2019-07-17
程式設計師
HelloDjango 系列教程：第 04 篇：Django 遷移、運算元據庫
2019-08-01
Django
Golang 學習系列第四天：運算元據庫 PostgreSQL
2020-07-18
GolangSQL
Flink的DataSet基本運算元總結
2020-10-15
一文快速回顧 Java 運算元據庫的方式-JDBC
2023-03-18
JavaJDBC
到底應該先操作快取還是先運算元據庫？
2023-02-14
快取
前端筆記之伺服器&Ajax（中）MySQL基礎操作&PHP運算元據庫&Ajax
2019-04-15
前端筆記伺服器MySqlPHP
MySQL DML運算元據
2024-10-22
MySql
透過延時從庫+binlog複製，恢復誤運算元據
2024-12-04
併發環境下，先運算元據庫還是先操作快取？
2023-12-29
快取
分散式高效能狀態與原子運算元據庫slock簡介
2021-12-21
分散式
Oracle OCP(10)：運算元據
2019-01-22
Oracle
Python量化資料倉儲搭建系列2：Python運算元據
2021-11-02
Python
基於python的集合運算
2018-10-02
Python