欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Python操作SQLite/MySQL/LMDB

程序员文章站 2023-11-02 15:44:22
1.概述 1.1前言 最近在存储字模图像集的时候,需要学习LMDB,趁此机会复习了SQLite和MySQL的使用,一起整理在此。 1.2环境 使用win7,Python 3.5.2。 2.SQLite 2.1准备 SQLite是一种嵌入式数据库,它的数据库就是一个文件。Python 2.5x以上版本 ......

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获取数据。