Python操作SQLite/MySQL/LMDB数据库的方法

2019-11-25 11:27:49

字体：大中小

来源：转载

供稿：网友

1.概述

1.1前言

　　最近在存储字模图像集的时候，需要学习LMDB，趁此机会复习了SQLite和MySQL的使用，一起整理在此。

1.2环境

　　使用win7，Python 3.5.2。

2.SQLite

2.1准备

　　SQLite是一种嵌入式数据库，它的数据库就是一个文件。Python 2.5x以上版本内置了SQLite3，使用时直接import sqlite3即可。

2.2操作流程

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

　　　　・通过sqlite3.open()创建与数据库文件的连接对象connection

　　　　・通过connection.cursor()创建光标对象cursor

　　　　・通过cursor.execute()执行SQL语句

　　　　・通过connection.commit()提交当前的事务，或者通过cursor.fetchall()获得查询结果

　　　　・通过connection.close()关闭与数据库文件的连接

　　详细的sqlite3模块API可以看这里：SQLite - Python

　　总结起来就是用cursor.execute()执行SQL语句，改变数据（插入、删除、修改）时用connection.commit()提交变更，查询数据时用cursor.fetchall()得到查询结果。

2.3操作实例

2.3.1建立数据库与建立表

#! /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# 创建连接conn = sqlite3.connect("test.db")# 创建光标cur = conn.cursor()# 执行（创建数据表的）SQL语句cur.execute("CREATE TABLE IF NOT EXISTS students (sid INTEGER PRIMARY KEY, name TEXT)")# 提交conn.commit()# 关闭连接conn.close()

　　这里conn是与数据库文件test.db的连接对象，cur是conn的光标对象，通过cur.execute()执行建表操作，创建了简单的学生信息表（学号, 名字），通过conn.commit()提交，最后用conn.close()关闭连接。

　　创建连接时，发现数据库文件不存在时会自动创建，这里使用了文件“test.db”，也可以使用“:memory:”建立内存数据库。

2.3.2插入、删除、修改

　　为了便于多次运行，直接使用了内存数据库：

#! /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# 创建链接conn = sqlite3.connect(":memory:")# 创建光标cur = conn.cursor()# 执行（创建数据表的）SQL语句cur.execute("CREATE TABLE students (sid INTEGER PRIMARY KEY, name TEXT)")# 执行（插入）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"))# 执行（查询）SQL语句cur.execute("SELECT * FROM students")print(cur.fetchall()) # [(1, 'Alice'), (2, 'Bob'), (3, 'Peter')]# 执行（删除）SQL语句cur.execute("DELETE FROM students WHERE sid = ?", (1,))# 执行（查询）SQL语句cur.execute("SELECT * FROM students")print(cur.fetchall()) # [(2, 'Bob'), (3, 'Peter')]# 执行（修改）SQL语句cur.execute("UPDATE students SET name = ? WHERE sid = ?", ("Mark", 3))# 执行（查询）SQL语句cur.execute("SELECT * FROM students")print(cur.fetchall()) # [(2, 'Bob'), (3, 'Mark')]# 关闭链接conn.close()

　　做的事情还就非常简单易懂的，向学生信息表中插入(1, Alice)、(2, Bob)、(3, Peter)三条记录，删除(1, Alice)，修改(3, Peter)为(3, Mark)。插入、删除、修改后查询数据库中的内容并打印出来。

“?”是sqlite3中的占位符，execute时会用第二个参数元组里的元素按顺序替换。官方文档里建议出于安全考虑，不要直接用Python做字符串拼接。

　　另外，注意不需要每次execute后调用commit。

2.3.3查询

　　直接用上面的代码：

# 执行（查询）SQL语句cur.execute("SELECT * FROM students")print(cur.fetchall()) # [(2, 'Bob'), (3, 'Peter')]　　fetchall()返回的是记录数组，可能通过WHERE子句做更细致的选择。

3.MySQL

3.1准备

　　安装MySQL：略（百度）

　　安装pymysql：

pip install PyMySQL

3.2操作流程

　　同为关系型数据库，MySQL的操作方法与SQLite是大同小异的。建立连接对象与光标对象，用execute()执行SQL语句，commit()提交事物，fetchall()获得所有查询结果。

3.3操作实例

3.3.1建立与数据库连接与建立表

　　准备工作：

　　　　- 已经创建了数据库TESTDB

　　　　- 连接数据库TESTDB使用的用户名为“mayi”，密码为“test123”，你可以自己设定或者直接使用root用户名及其密码。

　　・数据库连接

#! /usr/bin/env python3# -*- coding:utf-8 -*-# Author  : MaYi# Blog   : http://www.cnblogs.com/mayi0312/# Date   : 2019-11-07# Name   : test02# Software : PyCharm# Note   : # 导入模块import pymysql# 创建数据库连接conn = pymysql.connect(  host="localhost",  port=3306,  db="testdb",  user="mayi",  password="test123",  charset="utf8")# 创建游标cur = conn.cursor()# 执行SQL语句（查询Mysql版本）cur.execute("SELECT VERSION()")# 获取单条数据data = cur.fetchone()# 打印：Database version: 5.7.17-log print("Database version: %s " % data)# 关闭数据库连接conn.close()

　　・建立表

　　如果数据库连接存在我们可以使用execute()方法来为数据库创建表，如下所示创建表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   : # 导入模块import pymysql# 创建数据库连接conn = pymysql.connect(  host="localhost",  port=3306,  db="testdb",  user="mayi",  password="test123",  charset="utf8")# 创建游标cur = conn.cursor()# 执行SQL语句，如果表存在则删除cur.execute("DROP TABLE IF EXISTS students")# 使用预处理语句创建表cre_sql = """CREATE TABLE students (    sid INT(4) PRIMARY KEY,    name VARCHAR(10)    )"""# 执行SQL语句（建表）cur.execute(cre_sql)# 关闭数据库连接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   : # 导入模块import pymysql# 创建数据库连接conn = pymysql.connect(  host="localhost",  port=3306,  db="testdb",  user="mayi",  password="test123",  charset="utf8")# 创建游标cur = conn.cursor()# 执行（插入）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'))# 执行（查询）SQL语句cur.execute("SELECT * FROM students")print(cur.fetchall()) # ((1, 'Alice'), (2, 'Bob'), (3, 'Peter'))# 执行（删除）SQL语句cur.execute("DELETE FROM students WHERE sid = %s" % (1,))# 执行（查询）SQL语句cur.execute("SELECT * FROM students")print(cur.fetchall()) # ((2, 'Bob'), (3, 'Peter'))# 执行（修改）SQL语句cur.execute("UPDATE students SET name = '%s' WHERE sid = %s" % ('Mark', 3))# # 执行（查询）SQL语句cur.execute("SELECT * FROM students")print(cur.fetchall()) # ((2, 'Bob'), (3, 'Mark'))# 提交conn.commit()# 关闭数据库连接conn.close()

3.3.3查询
　　Python查询MySQL使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据。

　　　　・fetchone()：该方法获取下一个查询结果集。结果集是一个对象。

　　　　・fetchall()：接收全部的返回结果条。

　　　　・rowcount：这是一个只读属性，并返回执行execute()方法后影响的行数。

#! /usr/bin/env python3# -*- coding:utf-8 -*-# Author  : MaYi# Blog   : http://www.cnblogs.com/mayi0312/# Date   : 2019-11-07# Name   : test02# Software : PyCharm# Note   : # 导入模块import pymysql# 创建数据库连接conn = pymysql.connect(  host="localhost",  port=3306,  db="testdb",  user="mayi",  password="test123",  charset="utf8")# 创建游标cur = conn.cursor()# 执行（查询）SQL语句cur.execute("SELECT * FROM students")# 返回影响的行数print(cur.rowcount) # 2# 取一条数据print(cur.fetchone()) # (2, 'Bob')# 取剩下所有数据print(cur.fetchall()) # ((3, 'Mark'),)# 关闭数据库连接conn.close()

4.LMDB

4.1准备

　　LMDB和SQLite/MySQL等关系型数据库不同，属于key-value数据库（把LMDB想成dict会比较容易理解），键key与值value都是字符串。

　　安装：

pip install lmdb

　　使用时import lmdb

4.2操作流程

　　概况地讲，操作LMDB的流程是：

　　　　・通过env = lmdb.open()打开环境

　　　　・通过txn = env.begin()建立事务

　　　　・通过txn.put(key, value)进行插入和修改

　　　　・通过txn.delete(key)进行删除

　　　　・通过txn.get(key)进行查询

　　　　・通过txn.cursor()进行遍历

　　　　・通过txn.commit()提交更改

4.3操作实例

4.3.1建立环境

#! /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# 打开环境env = lmdb.open("students")

　　运行一下，查看当前目录的变化：

　　可以看到当前目录下多了students目录，里面有data.mdb和lock.mdb两个文件。

4.3.2插入、删除、修改

　　插入与修改都用put实现，删除用delete实现。

import lmdb# 打开环境env = lmdb.open("students")# 建立事务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()创建事务时，有write=True才能够写数据库。

4.3.3查询

　　查音箱记录用get(key)，遍历数据库用cursor。

import lmdb# 打开环境env = lmdb.open("students")# 建立事务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.学习总结

　　最后回顾一下，SQLite与MySQL都是关系型数据库，操作时创建连接对象connection与光标对象cursor，通过execute执行SQL语句，commite提交变更，fetch得到查询结果；LMDB是key-value数据库，操作时建立与数据库的连接，用put/delete改变数据，用get获取数据。

以上所述是小编给大家介绍的Python操作SQLite/MySQL/LMDB数据库的方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对武林网网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！

上一篇：浅谈python中统计计数的几种方法和Counter详解

下一篇：Flask框架路由和视图用法实例分析