Python操作SQLite/MySQL/LMDB數(shù)據(jù)庫的方法

更新時(shí)間：2019年11月07日 13:54:14 作者：騎著螞蟻流浪

這篇文章主要介紹了Python操作SQLite/MySQL/LMDB數(shù)據(jù)庫的方法，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下

1.概述

1.1前言

　　最近在存儲(chǔ)字模圖像集的時(shí)候，需要學(xué)習(xí)LMDB，趁此機(jī)會(huì)復(fù)習(xí)了SQLite和MySQL的使用，一起整理在此。

1.2環(huán)境

　　使用win7，Python 3.5.2。

2.SQLite

2.1準(zhǔn)備

　　SQLite是一種嵌入式數(shù)據(jù)庫，它的數(shù)據(jù)庫就是一個(gè)文件。Python 2.5x以上版本內(nèi)置了SQLite3，使用時(shí)直接import sqlite3即可。

2.2操作流程

　　概括地講，操作SQLite的流程是：

　　　　·通過sqlite3.open()創(chuàng)建與數(shù)據(jù)庫文件的連接對(duì)象connection

　　　　·通過connection.cursor()創(chuàng)建光標(biāo)對(duì)象cursor

　　　　·通過cursor.execute()執(zhí)行SQL語句

　　　　·通過connection.commit()提交當(dāng)前的事務(wù)，或者通過cursor.fetchall()獲得查詢結(jié)果

　　　　·通過connection.close()關(guān)閉與數(shù)據(jù)庫文件的連接

　　詳細(xì)的sqlite3模塊API可以看這里：SQLite - Python

　　總結(jié)起來就是用cursor.execute()執(zhí)行SQL語句，改變數(shù)據(jù)（插入、刪除、修改）時(shí)用connection.commit()提交變更，查詢數(shù)據(jù)時(shí)用cursor.fetchall()得到查詢結(jié)果。

2.3操作實(shí)例

2.3.1建立數(shù)據(jù)庫與建立表

#! /usr/bin/env python3
# -*- coding:utf-8 -*-
# Author  : MaYi
# Blog   : http://www.cnblogs.com/mayi0312/
# Date   : 2019-11-07
# Name   : test01
# Software : PyCharm
# Note   : 
import sqlite3
# 創(chuàng)建連接
conn = sqlite3.connect("test.db")
# 創(chuàng)建光標(biāo)
cur = conn.cursor()
# 執(zhí)行（創(chuàng)建數(shù)據(jù)表的）SQL語句
cur.execute("CREATE TABLE IF NOT EXISTS students (sid INTEGER PRIMARY KEY, name TEXT)")
# 提交
conn.commit()
# 關(guān)閉連接
conn.close()

　　這里conn是與數(shù)據(jù)庫文件test.db的連接對(duì)象，cur是conn的光標(biāo)對(duì)象，通過cur.execute()執(zhí)行建表操作，創(chuàng)建了簡(jiǎn)單的學(xué)生信息表（學(xué)號(hào), 名字），通過conn.commit()提交，最后用conn.close()關(guān)閉連接。

　　創(chuàng)建連接時(shí)，發(fā)現(xiàn)數(shù)據(jù)庫文件不存在時(shí)會(huì)自動(dòng)創(chuàng)建，這里使用了文件“test.db”，也可以使用“:memory:”建立內(nèi)存數(shù)據(jù)庫。

2.3.2插入、刪除、修改

　　為了便于多次運(yùn)行，直接使用了內(nèi)存數(shù)據(jù)庫：

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

# Author  : MaYi
# Blog   : http://www.cnblogs.com/mayi0312/
# Date   : 2019-11-07
# Name   : test01
# Software : PyCharm
# Note   : 
import sqlite3


# 創(chuàng)建鏈接
conn = sqlite3.connect(":memory:")
# 創(chuàng)建光標(biāo)
cur = conn.cursor()
# 執(zhí)行（創(chuàng)建數(shù)據(jù)表的）SQL語句
cur.execute("CREATE TABLE students (sid INTEGER PRIMARY KEY, name TEXT)")
# 執(zhí)行（插入）SQL語句
cur.execute("INSERT INTO students VALUES(?, ?)", (1, "Alice"))
cur.execute("INSERT INTO students VALUES(?, ?)", (2, "Bob"))
cur.execute("INSERT INTO students VALUES(?, ?)", (3, "Peter"))
# 執(zhí)行（查詢）SQL語句
cur.execute("SELECT * FROM students")
print(cur.fetchall()) # [(1, 'Alice'), (2, 'Bob'), (3, 'Peter')]
# 執(zhí)行（刪除）SQL語句
cur.execute("DELETE FROM students WHERE sid = ?", (1,))
# 執(zhí)行（查詢）SQL語句
cur.execute("SELECT * FROM students")
print(cur.fetchall()) # [(2, 'Bob'), (3, 'Peter')]
# 執(zhí)行（修改）SQL語句
cur.execute("UPDATE students SET name = ? WHERE sid = ?", ("Mark", 3))
# 執(zhí)行（查詢）SQL語句
cur.execute("SELECT * FROM students")
print(cur.fetchall()) # [(2, 'Bob'), (3, 'Mark')]
# 關(guān)閉鏈接
conn.close()

　　做的事情還就非常簡(jiǎn)單易懂的，向?qū)W生信息表中插入(1, Alice)、(2, Bob)、(3, Peter)三條記錄，刪除(1, Alice)，修改(3, Peter)為(3, Mark)。插入、刪除、修改后查詢數(shù)據(jù)庫中的內(nèi)容并打印出來。

“?”是sqlite3中的占位符，execute時(shí)會(huì)用第二個(gè)參數(shù)元組里的元素按順序替換。官方文檔里建議出于安全考慮，不要直接用Python做字符串拼接。

　　另外，注意不需要每次execute后調(diào)用commit。

2.3.3查詢

　　直接用上面的代碼：

# 執(zhí)行（查詢）SQL語句
cur.execute("SELECT * FROM students")
print(cur.fetchall()) # [(2, 'Bob'), (3, 'Peter')]
　　fetchall()返回的是記錄數(shù)組，可能通過WHERE子句做更細(xì)致的選擇。

3.MySQL

3.1準(zhǔn)備

　　安裝MySQL：略（百度）

　　安裝pymysql：

pip install PyMySQL

3.2操作流程

　　同為關(guān)系型數(shù)據(jù)庫，MySQL的操作方法與SQLite是大同小異的。建立連接對(duì)象與光標(biāo)對(duì)象，用execute()執(zhí)行SQL語句，commit()提交事物，fetchall()獲得所有查詢結(jié)果。

3.3操作實(shí)例

3.3.1建立與數(shù)據(jù)庫連接與建立表

　　準(zhǔn)備工作：

　　　　- 已經(jīng)創(chuàng)建了數(shù)據(jù)庫TESTDB

　　　　- 連接數(shù)據(jù)庫TESTDB使用的用戶名為“mayi”，密碼為“test123”，你可以自己設(shè)定或者直接使用root用戶名及其密碼。

　　·數(shù)據(jù)庫連接

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

# Author  : MaYi
# Blog   : http://www.cnblogs.com/mayi0312/
# Date   : 2019-11-07
# Name   : test02
# Software : PyCharm
# Note   : 
# 導(dǎo)入模塊
import pymysql

# 創(chuàng)建數(shù)據(jù)庫連接
conn = pymysql.connect(
  host="localhost",
  port=3306,
  db="testdb",
  user="mayi",
  password="test123",
  charset="utf8")
# 創(chuàng)建游標(biāo)
cur = conn.cursor()
# 執(zhí)行SQL語句（查詢Mysql版本）
cur.execute("SELECT VERSION()")
# 獲取單條數(shù)據(jù)
data = cur.fetchone()
# 打?。篋atabase version: 5.7.17-log 
print("Database version: %s " % data)
# 關(guān)閉數(shù)據(jù)庫連接
conn.close()

　　·建立表

　　如果數(shù)據(jù)庫連接存在我們可以使用execute()方法來為數(shù)據(jù)庫創(chuàng)建表，如下所示創(chuàng)建表students

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

# Author  : MaYi
# Blog   : http://www.cnblogs.com/mayi0312/
# Date   : 2019-11-07
# Name   : test02
# Software : PyCharm
# Note   : 
# 導(dǎo)入模塊
import pymysql

# 創(chuàng)建數(shù)據(jù)庫連接
conn = pymysql.connect(
  host="localhost",
  port=3306,
  db="testdb",
  user="mayi",
  password="test123",
  charset="utf8")
# 創(chuàng)建游標(biāo)
cur = conn.cursor()
# 執(zhí)行SQL語句，如果表存在則刪除
cur.execute("DROP TABLE IF EXISTS students")
# 使用預(yù)處理語句創(chuàng)建表
cre_sql = """CREATE TABLE students (
    sid INT(4) PRIMARY KEY,
    name VARCHAR(10)
    )"""
# 執(zhí)行SQL語句（建表）
cur.execute(cre_sql)

# 關(guān)閉數(shù)據(jù)庫連接
conn.close()

3.3.2插入、刪除、修改

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

# Author  : MaYi
# Blog   : http://www.cnblogs.com/mayi0312/
# Date   : 2019-11-07
# Name   : test02
# Software : PyCharm
# Note   : 
# 導(dǎo)入模塊
import pymysql

# 創(chuàng)建數(shù)據(jù)庫連接
conn = pymysql.connect(
  host="localhost",
  port=3306,
  db="testdb",
  user="mayi",
  password="test123",
  charset="utf8")
# 創(chuàng)建游標(biāo)
cur = conn.cursor()

# 執(zhí)行（插入）SQL語句
cur.execute("INSERT INTO students(sid, name) VALUES(%s, '%s')" % (1, 'Alice'))
cur.execute("INSERT INTO students(sid, name) VALUES(%s, '%s')" % (2, 'Bob'))
cur.execute("INSERT INTO students(sid, name) VALUES(%s, '%s')" % (3, 'Peter'))
# 執(zhí)行（查詢）SQL語句
cur.execute("SELECT * FROM students")
print(cur.fetchall()) # ((1, 'Alice'), (2, 'Bob'), (3, 'Peter'))
# 執(zhí)行（刪除）SQL語句
cur.execute("DELETE FROM students WHERE sid = %s" % (1,))
# 執(zhí)行（查詢）SQL語句
cur.execute("SELECT * FROM students")
print(cur.fetchall()) # ((2, 'Bob'), (3, 'Peter'))
# 執(zhí)行（修改）SQL語句
cur.execute("UPDATE students SET name = '%s' WHERE sid = %s" % ('Mark', 3))
# # 執(zhí)行（查詢）SQL語句
cur.execute("SELECT * FROM students")
print(cur.fetchall()) # ((2, 'Bob'), (3, 'Mark'))
# 提交
conn.commit()
# 關(guān)閉數(shù)據(jù)庫連接
conn.close()

3.3.3查詢
　　Python查詢MySQL使用fetchone()方法獲取單條數(shù)據(jù)，使用fetchall()方法獲取多條數(shù)據(jù)。

　　　　·fetchone()：該方法獲取下一個(gè)查詢結(jié)果集。結(jié)果集是一個(gè)對(duì)象。

　　　　·fetchall()：接收全部的返回結(jié)果條。

　　　　·rowcount：這是一個(gè)只讀屬性，并返回執(zhí)行execute()方法后影響的行數(shù)。

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

# Author  : MaYi
# Blog   : http://www.cnblogs.com/mayi0312/
# Date   : 2019-11-07
# Name   : test02
# Software : PyCharm
# Note   : 
# 導(dǎo)入模塊
import pymysql

# 創(chuàng)建數(shù)據(jù)庫連接
conn = pymysql.connect(
  host="localhost",
  port=3306,
  db="testdb",
  user="mayi",
  password="test123",
  charset="utf8")
# 創(chuàng)建游標(biāo)
cur = conn.cursor()

# 執(zhí)行（查詢）SQL語句
cur.execute("SELECT * FROM students")
# 返回影響的行數(shù)
print(cur.rowcount) # 2
# 取一條數(shù)據(jù)
print(cur.fetchone()) # (2, 'Bob')
# 取剩下所有數(shù)據(jù)
print(cur.fetchall()) # ((3, 'Mark'),)
# 關(guān)閉數(shù)據(jù)庫連接
conn.close()

4.LMDB

4.1準(zhǔn)備

　　LMDB和SQLite/MySQL等關(guān)系型數(shù)據(jù)庫不同，屬于key-value數(shù)據(jù)庫（把LMDB想成dict會(huì)比較容易理解），鍵key與值value都是字符串。

　　安裝：

pip install lmdb

　　使用時(shí)import lmdb

4.2操作流程

　　概況地講，操作LMDB的流程是：

　　　　·通過env = lmdb.open()打開環(huán)境

　　　　·通過txn = env.begin()建立事務(wù)

　　　　·通過txn.put(key, value)進(jìn)行插入和修改

　　　　·通過txn.delete(key)進(jìn)行刪除

　　　　·通過txn.get(key)進(jìn)行查詢

　　　　·通過txn.cursor()進(jìn)行遍歷

　　　　·通過txn.commit()提交更改

4.3操作實(shí)例

4.3.1建立環(huán)境

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

# Author  : MaYi
# Blog   : http://www.cnblogs.com/mayi0312/
# Date   : 2019-11-07
# Name   : test03
# Software : PyCharm
# Note   : 
import lmdb

# 打開環(huán)境
env = lmdb.open("students")

　　運(yùn)行一下，查看當(dāng)前目錄的變化：

　　可以看到當(dāng)前目錄下多了students目錄，里面有data.mdb和lock.mdb兩個(gè)文件。

4.3.2插入、刪除、修改

　　插入與修改都用put實(shí)現(xiàn)，刪除用delete實(shí)現(xiàn)。

import lmdb


# 打開環(huán)境
env = lmdb.open("students")
# 建立事務(wù)
txn = env.begin(write=True)
# 插入三條記錄
txn.put(b"1", b"Alice")
txn.put(b"2", b"Bob")
txn.put(b"3", b"Peter")
# 刪除key="1"的記錄
txn.delete(b"1")
# 修改key="3"的值為"Mark"
txn.put(b"3", b"Mark")
# 提交更改
txn.commit()

　　注意用txn=env.begin()創(chuàng)建事務(wù)時(shí)，有write=True才能夠?qū)憯?shù)據(jù)庫。

4.3.3查詢

　　查音箱記錄用get(key)，遍歷數(shù)據(jù)庫用cursor。

import lmdb
# 打開環(huán)境
env = lmdb.open("students")
# 建立事務(wù)
txn = env.begin()
# 查詢單條記錄
print(txn.get(b"3")) # b'Mark'
# b'2' b'Bob'
# b'3' b'Mark'
for key, value in txn.cursor():
  print(key, value)
# 提交更改
txn.commit()

5.學(xué)習(xí)總結(jié)

　　最后回顧一下，SQLite與MySQL都是關(guān)系型數(shù)據(jù)庫，操作時(shí)創(chuàng)建連接對(duì)象connection與光標(biāo)對(duì)象cursor，通過execute執(zhí)行SQL語句，commite提交變更，fetch得到查詢結(jié)果；LMDB是key-value數(shù)據(jù)庫，操作時(shí)建立與數(shù)據(jù)庫的連接，用put/delete改變數(shù)據(jù)，用get獲取數(shù)據(jù)。

以上所述是小編給大家介紹的Python操作SQLite/MySQL/LMDB數(shù)據(jù)庫的方法，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
如果你覺得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！

您可能感興趣的文章: