当前位置: 开发笔记 > 编程语言 > 正文

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

作者：adfa3sd5f6a | 来源：互联网 | 2023-05-19 02:49

1.概述1.1前言最近在存储字模图像集的时候，需要学习LMDB，趁此机会复习了SQLite和MySQL的使用，一起整理在此。1.2环境使用win7，Python

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(&＃63;, &＃63;)", (1, "Alice"))
cur.execute("INSERT INTO students VALUES(&＃63;, &＃63;)", (2, "Bob"))
cur.execute("INSERT INTO students VALUES(&＃63;, &＃63;)", (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 = &＃63;", (1,))
# 执行（查询）SQL语句
cur.execute("SELECT * FROM students")
print(cur.fetchall()) # [(2, 'Bob'), (3, 'Peter')]
# 执行（修改）SQL语句
cur.execute("UPDATE students SET name = &＃63; WHERE sid = &＃63;", ("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)。插入、删除、修改后查询数据库中的内容并打印出来。

“&＃63;”是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数据库的方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对编程笔记网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！

推荐阅读

join
如何从列表中删除所有零？

本文介绍了如何使用python从列表中删除所有的零，并将结果以列表形式输出，同时提供了示例格式。 ... [详细]

蜡笔小新 2023-12-13 13:02:00
python
Java String与StringBuffer的区别及其应用场景

本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]

蜡笔小新 2023-12-13 19:21:06
string
iOS数据库Sqlite的SQL语句分类和常见约束关键字

本文介绍了iOS数据库Sqlite的SQL语句分类和常见约束关键字。SQL语句分为DDL、DML和DQL三种类型，其中DDL语句用于定义、删除和修改数据表，关键字包括create、drop和alter。常见约束关键字包括if not exists、if exists、primary key、autoincrement、not null和default。此外，还介绍了常见的数据库数据类型，包括integer、text和real。 ... [详细]

蜡笔小新 2023-12-12 18:42:03
join
MyBatis多表查询与动态SQL使用

本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]

蜡笔小新 2023-12-12 17:12:51
php
SQL日志收缩及截断方法详解

本文详细介绍了SQL日志收缩的方法，包括截断日志和删除不需要的旧日志记录。通过备份日志和使用DBCC SHRINKFILE命令可以实现日志的收缩。同时，还介绍了截断日志的原理和注意事项，包括不能截断事务日志的活动部分和MinLSN的确定方法。通过本文的方法，可以有效减小逻辑日志的大小，提高数据库的性能。 ... [详细]

蜡笔小新 2023-12-14 18:23:25
php
eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）

本文介绍了eclipse学习中的第三章内容，主要讲解了ssh中的Hibernate的缓存，包括2级缓存和get方法、load方法的区别。文章还涉及了项目实践和相关知识点的讲解。 ... [详细]

蜡笔小新 2023-12-14 00:31:35
php
图解redis的持久化存储机制RDB和AOF的原理和优缺点

本文通过图解的方式介绍了redis的持久化存储机制RDB和AOF的原理和优缺点。RDB是将redis内存中的数据保存为快照文件，恢复速度较快但不支持拉链式快照。AOF是将操作日志保存到磁盘，实时存储数据但恢复速度较慢。文章详细分析了两种机制的优缺点，帮助读者更好地理解redis的持久化存储策略。 ... [详细]

蜡笔小新 2023-12-13 20:24:11
fetch
sklearn数据集库中的常用数据集类型介绍

本文介绍了sklearn数据集库中常用的数据集类型，包括玩具数据集和样本生成器。其中详细介绍了波士顿房价数据集，包含了波士顿506处房屋的13种不同特征以及房屋价格，适用于回归任务。 ... [详细]

蜡笔小新 2023-12-13 17:45:15
string
ASP.NET Tips: 获取插入记录的ID的方法详解

本文详细介绍了在ASP.NET中获取插入记录的ID的几种方法，包括使用SCOPE_IDENTITY()和IDENT_CURRENT()函数，以及通过ExecuteReader方法执行SQL语句获取ID的步骤。同时，还提供了使用这些方法的示例代码和注意事项。对于需要获取表中最后一个插入操作所产生的ID或马上使用刚插入的新记录ID的开发者来说，本文提供了一些有用的技巧和建议。 ... [详细]

蜡笔小新 2023-12-13 17:03:18
fetch
游标的使用笔记

本文介绍了游标的使用方法，并以一个水果供应商数据库为例进行了说明。首先创建了一个名为fruits的表，包含了水果的id、供应商id、名称和价格等字段。然后使用游标查询了水果的名称和价格，并将结果输出。最后对游标进行了关闭操作。通过本文可以了解到游标在数据库操作中的应用。 ... [详细]

蜡笔小新 2023-12-13 15:24:30
string
SpringJdbcTemplate的使用详解

本文详细介绍了Spring的JdbcTemplate的使用方法，包括执行存储过程、存储函数的call()方法，执行任何SQL语句的execute()方法，单个更新和批量更新的update()和batchUpdate()方法，以及单查和列表查询的query()和queryForXXX()方法。提供了经过测试的API供使用。 ... [详细]

蜡笔小新 2023-12-13 14:27:11
php
高质量SQL书写的30条建议

本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]

蜡笔小新 2023-12-13 13:24:33
php
问题2：重新命名或者移动数据文件、日志文件到新的位置

本文讨论了在数据库打开和关闭状态下，重新命名或移动数据文件和日志文件的情况。针对性能和维护原因，需要将数据库文件移动到不同的磁盘上或重新分配到新的磁盘上的情况，以及在操作系统级别移动或重命名数据文件但未在数据库层进行重命名导致报错的情况。通过三个方面进行讨论。 ... [详细]

蜡笔小新 2023-12-13 13:02:24
php
用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性

ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ... [详细]

蜡笔小新 2023-12-13 09:49:28
schema
Oracle seg,V$TEMPSEG_USAGE与Oracle排序的关系及使用方法

本文介绍了Oracle seg,V$TEMPSEG_USAGE与Oracle排序之间的关系，V$TEMPSEG_USAGE是V_$SORT_USAGE的同义词，通过查询dba_objects和dba_synonyms视图可以了解到它们的详细信息。同时，还探讨了V$TEMPSEG_USAGE的使用方法。 ... [详细]

蜡笔小新 2023-12-12 17:57:15

adfa3sd5f6a

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章