Python操作SQLite/MySQL/LMDB数据库的方法
1.概述
1.1前言
最近在存储字模图像集的时候，需要学习LMDB，趁此机会复习了SQLite和MySQL的使用，一起整理在此。
1.2环境
使用win7，Python3.5.2。
2.SQLite
2.1准备
SQLite是一种嵌入式数据库，它的数据库就是一个文件。Python2.5x以上版本内置了SQLite3，使用时直接importsqlite3即可。
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/envpython3
#-*-coding:utf-8-*-
#Author:MaYi
#Blog:http://www.cnblogs.com/mayi0312/
#Date:2019-11-07
#Name:test01
#Software:PyCharm
#Note:
importsqlite3
#创建连接
conn=sqlite3.connect("test.db")
#创建光标
cur=conn.cursor()
#执行（创建数据表的）SQL语句
cur.execute("CREATETABLEIFNOTEXISTSstudents(sidINTEGERPRIMARYKEY,nameTEXT)")
#提交
conn.commit()
#关闭连接
conn.close()
这里conn是与数据库文件test.db的连接对象，cur是conn的光标对象，通过cur.execute()执行建表操作，创建了简单的学生信息表（学号,名字），通过conn.commit()提交，最后用conn.close()关闭连接。
创建连接时，发现数据库文件不存在时会自动创建，这里使用了文件“test.db”，也可以使用“:memory:”建立内存数据库。
2.3.2插入、删除、修改
为了便于多次运行，直接使用了内存数据库：
#!/usr/bin/envpython3
#-*-coding:utf-8-*-
#Author:MaYi
#Blog:http://www.cnblogs.com/mayi0312/
#Date:2019-11-07
#Name:test01
#Software:PyCharm
#Note:
importsqlite3
#创建链接
conn=sqlite3.connect(":memory:")
#创建光标
cur=conn.cursor()
#执行（创建数据表的）SQL语句
cur.execute("CREATETABLEstudents(sidINTEGERPRIMARYKEY,nameTEXT)")
#执行（插入）SQL语句
cur.execute("INSERTINTOstudentsVALUES(?,?)",(1,"Alice"))
cur.execute("INSERTINTOstudentsVALUES(?,?)",(2,"Bob"))
cur.execute("INSERTINTOstudentsVALUES(?,?)",(3,"Peter"))
#执行（查询）SQL语句
cur.execute("SELECT*FROMstudents")
print(cur.fetchall())#[(1,'Alice'),(2,'Bob'),(3,'Peter')]
#执行（删除）SQL语句
cur.execute("DELETEFROMstudentsWHEREsid=?",(1,))
#执行（查询）SQL语句
cur.execute("SELECT*FROMstudents")
print(cur.fetchall())#[(2,'Bob'),(3,'Peter')]
#执行（修改）SQL语句
cur.execute("UPDATEstudentsSETname=?WHEREsid=?",("Mark",3))
#执行（查询）SQL语句
cur.execute("SELECT*FROMstudents")
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*FROMstudents")
print(cur.fetchall())#[(2,'Bob'),(3,'Peter')]
fetchall()返回的是记录数组，可能通过WHERE子句做更细致的选择。
3.MySQL
3.1准备
安装MySQL：略（百度）
安装pymysql：
pipinstallPyMySQL
3.2操作流程
同为关系型数据库，MySQL的操作方法与SQLite是大同小异的。建立连接对象与光标对象，用execute()执行SQL语句，commit()提交事物，fetchall()获得所有查询结果。
3.3操作实例
3.3.1建立与数据库连接与建立表
准备工作：
- 已经创建了数据库TESTDB
- 连接数据库TESTDB使用的用户名为“mayi”，密码为“test123”，你可以自己设定或者直接使用root用户名及其密码。
·数据库连接
#!/usr/bin/envpython3
#-*-coding:utf-8-*-
#Author:MaYi
#Blog:http://www.cnblogs.com/mayi0312/
#Date:2019-11-07
#Name:test02
#Software:PyCharm
#Note:
#导入模块
importpymysql
#创建数据库连接
conn=pymysql.connect(
host="localhost",
port=3306,
db="testdb",
user="mayi",
password="test123",
charset="utf8")
#创建游标
cur=conn.cursor()
#执行SQL语句（查询Mysql版本）
cur.execute("SELECTVERSION()")
#获取单条数据
data=cur.fetchone()
#打印：Databaseversion:5.7.17-log
print("Databaseversion:%s"%data)
#关闭数据库连接
conn.close()
·建立表
如果数据库连接存在我们可以使用execute()方法来为数据库创建表，如下所示创建表students
#!/usr/bin/envpython3
#-*-coding:utf-8-*-
#Author:MaYi
#Blog:http://www.cnblogs.com/mayi0312/
#Date:2019-11-07
#Name:test02
#Software:PyCharm
#Note:
#导入模块
importpymysql
#创建数据库连接
conn=pymysql.connect(
host="localhost",
port=3306,
db="testdb",
user="mayi",
password="test123",
charset="utf8")
#创建游标
cur=conn.cursor()
#执行SQL语句，如果表存在则删除
cur.execute("DROPTABLEIFEXISTSstudents")
#使用预处理语句创建表
cre_sql="""CREATETABLEstudents(
sidINT(4)PRIMARYKEY,
nameVARCHAR(10)
)"""
#执行SQL语句（建表）
cur.execute(cre_sql)
#关闭数据库连接
conn.close()
3.3.2插入、删除、修改
#!/usr/bin/envpython3
#-*-coding:utf-8-*-
#Author:MaYi
#Blog:http://www.cnblogs.com/mayi0312/
#Date:2019-11-07
#Name:test02
#Software:PyCharm
#Note:
#导入模块
importpymysql
#创建数据库连接
conn=pymysql.connect(
host="localhost",
port=3306,
db="testdb",
user="mayi",
password="test123",
charset="utf8")
#创建游标
cur=conn.cursor()
#执行（插入）SQL语句
cur.execute("INSERTINTOstudents(sid,name)VALUES(%s,'%s')"%(1,'Alice'))
cur.execute("INSERTINTOstudents(sid,name)VALUES(%s,'%s')"%(2,'Bob'))
cur.execute("INSERTINTOstudents(sid,name)VALUES(%s,'%s')"%(3,'Peter'))
#执行（查询）SQL语句
cur.execute("SELECT*FROMstudents")
print(cur.fetchall())#((1,'Alice'),(2,'Bob'),(3,'Peter'))
#执行（删除）SQL语句
cur.execute("DELETEFROMstudentsWHEREsid=%s"%(1,))
#执行（查询）SQL语句
cur.execute("SELECT*FROMstudents")
print(cur.fetchall())#((2,'Bob'),(3,'Peter'))
#执行（修改）SQL语句
cur.execute("UPDATEstudentsSETname='%s'WHEREsid=%s"%('Mark',3))
##执行（查询）SQL语句
cur.execute("SELECT*FROMstudents")
print(cur.fetchall())#((2,'Bob'),(3,'Mark'))
#提交
conn.commit()
#关闭数据库连接
conn.close()
3.3.3查询
Python查询MySQL使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据。
·fetchone()：该方法获取下一个查询结果集。结果集是一个对象。
·fetchall()：接收全部的返回结果条。
·rowcount：这是一个只读属性，并返回执行execute()方法后影响的行数。
#!/usr/bin/envpython3
#-*-coding:utf-8-*-
#Author:MaYi
#Blog:http://www.cnblogs.com/mayi0312/
#Date:2019-11-07
#Name:test02
#Software:PyCharm
#Note:
#导入模块
importpymysql
#创建数据库连接
conn=pymysql.connect(
host="localhost",
port=3306,
db="testdb",
user="mayi",
password="test123",
charset="utf8")
#创建游标
cur=conn.cursor()
#执行（查询）SQL语句
cur.execute("SELECT*FROMstudents")
#返回影响的行数
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都是字符串。
安装：
pipinstalllmdb
使用时importlmdb
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/envpython3
#-*-coding:utf-8-*-
#Author:MaYi
#Blog:http://www.cnblogs.com/mayi0312/
#Date:2019-11-07
#Name:test03
#Software:PyCharm
#Note:
importlmdb
#打开环境
env=lmdb.open("students")
运行一下，查看当前目录的变化：
可以看到当前目录下多了students目录，里面有data.mdb和lock.mdb两个文件。
4.3.2插入、删除、修改
插入与修改都用put实现，删除用delete实现。
importlmdb
#打开环境
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。
importlmdb
#打开环境
env=lmdb.open("students")
#建立事务
txn=env.begin()
#查询单条记录
print(txn.get(b"3"))#b'Mark'
#b'2'b'Bob'
#b'3'b'Mark'
forkey,valueintxn.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数据库的方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。