安科网

Python操作SQLite/MySQL/LMDB

zluxingzhe

zluxingzhe

2019-11-07

关注 关注

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")

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

Python操作SQLite/MySQL/LMDB

 　　可以看到当前目录下多了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获取数据。

sqlite python连接mysql 数据库 python mysql

zluxingzhe

zluxingzhe

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

你知道Python有内置数据库吗？Python内置库SQlite3使用指南

如果你是软件开发人员，相信你一定知道或者曾经使用过一个非常轻量级的数据库——SQLite。它具有作为关系数据库所需的几乎所有功能，但是这个数据库把所有功能都保存在一个文件中。在其官方网站上，SQLite的主要应用场景包括：。最重要的是，SQLite实际上是

specialbrian 2020-07-31

sqlite expert professional中文版

教程：1、下载解压，运行对应操作系统安装包，点击；

zhoubingyi 2020-07-30

【译】Announcing Entity Framework Core 5.0 Preview 5

　　今天我们宣布EF Core 5.0发布第五个预览版。　　EF Core 5.0 的预览版要求 .NET Standard 2.1。EF Core 5.0 在 .NET Core 3.1 上运行，不需要 .NET 5。根据 .NET 5 计划的改变，这可

loveandroid0 2020-06-18

C#中使用SQLite

最近要做一个单机小程序，但涉及到一些数据的存取和增删改查，研究了一番，觉得还是轻量级数据库sqlite最合适，于是先做了个样例，方便进一步开发。我选择了其中的Precompiled Binaries for 64-bit Windows . 不过貌似需要t

DAV数据库 2020-06-17

Android开发中的SQLite事务处理

如初始8000条记录也就是要8000次读写磁盘操作。同时也是为了保证数据的一致性，避免出现数据缺失等情况。

loveandroid0 2020-06-14

自己设计大学排名-数据库实践

　　SQLite数据库是一款非常小巧的嵌入式开源数据库软件，它使用一个文件存储整个数据库，优点是使用方便，但是功能相比于其它大型数据库来说，确实有点差距。由于此次数据库实践所需要求不多，所以就使用SQLite来完成。值得一提的是，Python内置SQLit

URML 2020-06-13

C#使用SQLite出错：无法加载 DLL“SQLite.Interop.dll”，找不到指定的模块

在SQLite官方下载了System.Data.SQLite，编写如下测试代码：复制内容到剪贴板程序代码using { using { DataTable table1 = new DataTable(); ada

Dlanguage 2020-06-12

用makefile文件执行sqlite3

OBJ=test.o zhan.o duilie.o ditu.o welcome.o init.o menu.o time.o user.o data.o

Plant 2020-06-07

大型Electron应用本地数据库技术选型

由于没有索引机制，关联查询、条件查询等操作效率不高，更新了某项数据之后，要持久化更新操作，又要重写整个文件。Cookies存储容量太小，只能存4kb的内容，而且每次与服务端交互，同域下的Cookie还会被携带到服务端，也没有关联查询、条件查询的机制。现在可

疯狂老司机 2020-06-07

python数据库之sqlite3

cursor.execute(‘create table MainTab (id int(10) primary key, name varchar(50))‘). #cursor.execute(‘create table MainTab (id int

chibangyuxun 2020-06-07

Python 之 Sqlite3数据库

　　SQLite数据库是一款非常小巧的嵌入式开源数据库软件，它使用一个文件存储整个数据库，优点是使用方便，但是功能相比于其它大型数据库来说，确实有点差距。由于此次数据库实践所需要求不多，所以就使用SQLite来完成。值得一提的是，Python内置SQLit

sdwylry 2020-06-04

sqlite遇到database is locked问题的完美解决

使用sqlite提供的两个busy handler函数，但对于一个连接来说，只能有一个busy handle，两个函数会相互影响，设置一个的同时会清除另一个，应根据需要来选择。

airfling 2020-05-31

一分钟实现，一个RN持久数据管理器

在React Native开发过程中，总避免不了需要存储一些数据在本地。对于大多数应用只需要存储一些结构简单的数据，如标记位，用户信息等。这时候我们首选择的存储方式就是AsyncStorage，那我们先来看下AsyncStorage给我提供了哪些基本方法：

Plant 2020-05-31

Android SQLite轻量级数据库（简单介绍）

SQLite它是相当于嵌入到安卓里的一个小数据库吧，它也可以使用SQL语句进行数据库的增删改查操作，但是是SQL1992的语句。然后SQLite也有自己的语句，但是学过SQL的应该都会发现，它比较鸡肋，首先冗长代码不说，操作还花里胡哨的。SqliteDat

zbcaicai 2020-05-26

当Excel遇到大数据问题，是时候用Python来拯救了

Excel能做很多事情；当涉及到更大的数据集时，这简直是一种痛苦。数据需要很长时间才能加载，在你意识到机器的内存耗尽之前，整个事情就变得无法管理了。更不用说Excel最多只能支持1,048,576行。如果有一种简单的方法，那就是将数据传输到SQL数据库中进

chaochao 2020-05-19

安卓开发之SQLite(数据的增删改查)

以下关于一些布局的按钮就不贴代码了.Cursor相当于游标,是每行的集合,使用moveTofirst()定位第一行,你必须知道每一列的数据类型,

Plant 2020-05-17

Django 升级sqlite3

## 可以看到sqlite3的版本还是旧版本，那么需要更新一下。# 设置开机自启动执行，可以将下面的export语句写入 ~/.bashrc 文件中，如果如果你想立即生效，可以执行source ?/.bashrc 将在每次启动终端时执行。Type ‘cop

小火车 2020-05-14

C# Web API操作Sqlite数据库

<add name="Access-Control-Allow-Methods" value="GET, POST, Options, PUT, DELETE" />

beibeijia 2020-04-25

记一次C#使用SQLite的坑

准备做一个数据查询小工具，由于数据都是固定的，所以其实就是做一个Windows的数据界面展示软件。决定用SQLite和C#搞定，数据库方面比较熟也没遇上什么问题，就略过不表，记几个C#的坑吧。最后还是VS2019立功，在显示修补方案有一个“System.D

Rain 2020-04-16

Python + SQLite + ST_Geometry

SQLite是嵌入式的轻量级数据库，ArcMap可以建立SQLite数据库，并配合ST_Geometry直接对数据库中的空间数据进行操作，为了可以更加灵活的编写SQL语言，使用Python调用SQLite。为了将空间属性字段加入到数据库中，还需要调用Cre

Plant 2020-04-08
zluxingzhe

zluxingzhe

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号