Python operation SQLite/MySQL/LMDB

1. overview

1.1 Preface

Recently, when storing font image sets, you need to learn LMDB, take this opportunity to review the use of SQLite and MySQL, and organize here together.

1.2 environment

Use win7, Python 3.5.2.

2.SQLite

2.1 preparation

SQLite is an embedded database whose database is a file. SQLite3 is built-in in Python 2.5x and above, and you can directly import sqlite3 when you use it.

2.2 operation process

Generally speaking, the process of SQLite operation is as follows:

· create connection object with database file through sqlite3.open()

· create cursor object cursor through connection.cursor()

· execute SQL statement through cursor.execute()

· submit the current transaction through connection.commit(), or obtain the query result through cursor.fetchall()

· close the connection with the database file through connection.close()

The detailed sqlite3 module API can be seen here: SQLite - Python

In summary, execute SQL statements with cursor.execute(), submit changes with connection.commit() when changing data (insert, delete, modify), and get query results with cursor.fetchall() when querying data.

2.3 operation examples

2.3.1 establish database and table

#! /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


# Create connection
conn = sqlite3.connect("test.db")
# create a cursor
cur = conn.cursor()
# Execution (of creating a data table) SQL Sentence
cur.execute("CREATE TABLE IF NOT EXISTS students (sid INTEGER PRIMARY KEY, name TEXT)")
# Submission
conn.commit()
# Close connection
conn.close()

Here, Conn is the connection object with the database file test.db, and cur is the cursor object of Conn. through cur.execute(), a simple student information table (student number, name) is created, submitted by conn.commit(), and finally, the connection is closed by conn.close().

When creating a connection, it will be automatically created when the database file does not exist. Here, the file "test.db" is used, or ": memory:" can be used to establish a memory database.

2.3.2 inserting, deleting and modifying

To facilitate multiple runs, the memory database is directly used:

#! /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


# create link
conn = sqlite3.connect(":memory:")
# create a cursor
cur = conn.cursor()
# Execution (of creating a data table) SQL Sentence
cur.execute("CREATE TABLE students (sid INTEGER PRIMARY KEY, name TEXT)")
# Execute (insert) SQL Sentence
cur.execute("INSERT INTO students VALUES(?, ?)", (1, "Alice"))
cur.execute("INSERT INTO students VALUES(?, ?)", (2, "Bob"))
cur.execute("INSERT INTO students VALUES(?, ?)", (3, "Peter"))
# Execute (query) SQL Sentence
cur.execute("SELECT * FROM students")
print(cur.fetchall())  # [(1, 'Alice'), (2, 'Bob'), (3, 'Peter')]
# Execute (delete) SQL Sentence
cur.execute("DELETE FROM students WHERE sid = ?", (1,))
# Execute (query) SQL Sentence
cur.execute("SELECT * FROM students")
print(cur.fetchall())  # [(2, 'Bob'), (3, 'Peter')]
# Execution (modification) SQL Sentence
cur.execute("UPDATE students SET name = ? WHERE sid = ?", ("Mark", 3))
# Execute (query) SQL Sentence
cur.execute("SELECT * FROM students")
print(cur.fetchall())  # [(2, 'Bob'), (3, 'Mark')]
# Close links
conn.close()

What we do is also very simple and easy to understand. Insert (1, Alice), (2, Bob), (3, Peter) three records into the student information table, delete (1, Alice), and change (3, Peter) to (3, Mark). After inserting, deleting and modifying, query the content in the database and print it out.

"?" is a placeholder in sqlite3. When executing, the elements in the second parameter tuple will be replaced in order. For security reasons, it is recommended in the official documents not to directly use Python for string splicing.

Also, note that commit is not required after each execute.

2.3.3 query

Use the above code directly:

# Execute (query) SQL Sentence
cur.execute("SELECT * FROM students")
print(cur.fetchall())  # [(2, 'Bob'), (3, 'Peter')]

fetchall() returns an array of records, which may be selected more carefully through the WHERE clause.

3.MySQL

3.1 preparation

Install MySQL: omitted (Baidu)

To install pymysql:

pip install PyMySQL

3.2 operation process

As a relational database, MySQL's operation method is similar to SQLite's. Establish the connection object and cursor object, execute the SQL statement with execute(), commit(), and fetch all() to get all the query results.

3.3 operation examples

3.3.1 establish connection with database and establish table

Preparation:

- - database TESTDB has been created

- the user name used to connect to TESTDB is "mayi" and the password is "test123". You can set or directly use the root user name and password.

· database connection

#! /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 module
import pymysql

# Create database connection
conn = pymysql.connect(
    host="localhost",
    port=3306,
    db="testdb",
    user="mayi",
    password="test123",
    charset="utf8")
# Create cursors
cur = conn.cursor()
# implement SQL Statement (query Mysql Version)
cur.execute("SELECT VERSION()")
# Get single data
data = cur.fetchone()
# Print: Database version: 5.7.17-log 
print("Database version: %s " % data)
# Close database connection
conn.close()

· establish a table

If the database connection exists, we can use the execute() method to create tables for the database, as shown below

#! /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 module
import pymysql

# Create database connection
conn = pymysql.connect(
    host="localhost",
    port=3306,
    db="testdb",
    user="mayi",
    password="test123",
    charset="utf8")
# Create cursors
cur = conn.cursor()
# implement SQL Statement, delete if table exists
cur.execute("DROP TABLE IF EXISTS students")
# Creating a table with preprocessing statements
cre_sql = """CREATE TABLE students (
        sid INT(4) PRIMARY KEY,
        name VARCHAR(10)
        )"""
# implement SQL Statement (table creation)
cur.execute(cre_sql)

# Close database connection
conn.close()

3.3.2 inserting, deleting and modifying

#! /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 module
import pymysql

# Create database connection
conn = pymysql.connect(
    host="localhost",
    port=3306,
    db="testdb",
    user="mayi",
    password="test123",
    charset="utf8")
# Create cursors
cur = conn.cursor()

# Execute (insert) SQL Sentence
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'))
# Execute (query) SQL Sentence
cur.execute("SELECT * FROM students")
print(cur.fetchall())  # ((1, 'Alice'), (2, 'Bob'), (3, 'Peter'))
# Execute (delete) SQL Sentence
cur.execute("DELETE FROM students WHERE sid = %s" % (1,))
# Execute (query) SQL Sentence
cur.execute("SELECT * FROM students")
print(cur.fetchall())  # ((2, 'Bob'), (3, 'Peter'))
# Execution (modification) SQL Sentence
cur.execute("UPDATE students SET name = '%s' WHERE sid = %s" % ('Mark', 3))
# # Execute (query) SQL statement
cur.execute("SELECT * FROM students")
print(cur.fetchall())  # ((2, 'Bob'), (3, 'Mark'))
# Submission
conn.commit()
# Close database connection
conn.close()

3.3.3 query

Python queries MySQL to get a single piece of data using the fetchone() method, and fetchall() method to get multiple pieces of data.

· fetchone(): this method gets the next query result set. The result set is an object.

· fetchall(): receive all returned result bars.

· rowcount: This is a read-only property and returns the number of rows affected after executing the execute() method.

#! /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 module
import pymysql

# Create database connection
conn = pymysql.connect(
    host="localhost",
    port=3306,
    db="testdb",
    user="mayi",
    password="test123",
    charset="utf8")
# Create cursors
cur = conn.cursor()

# Execute (query) SQL Sentence
cur.execute("SELECT * FROM students")
# Returns the number of rows affected
print(cur.rowcount)  # 2
# Take a piece of data
print(cur.fetchone())  # (2, 'Bob')
# Take all the remaining data
print(cur.fetchall())  # ((3, 'Mark'),)
# Close database connection
conn.close()

4.LMDB

4.1 preparation

Different from SQLite/MySQL and other relational databases, LMDB belongs to key value database (it is easy to understand LMDB as dict). Both key and value are strings.

Installation:

pip install lmdb

import lmdb when in use

4.2 operation process

Generally speaking, the process of LMDB operation is as follows:

· open the environment through env = lmdb.open()

· establish transaction through txn = env.begin()

· insert and modify through txn.put(key, value)

· delete through txn.delete(key)

· query through txn.get(key)

· traversal through txn.cursor()

· commit changes via txn.commit()

4.3 operation examples

4.3.1 establishment of environment

#! /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


# Open the environment
env = lmdb.open("students")

Run to see the changes in the current directory:

You can see that there are more students directories in the current directory, including data.mdb and lock.mdb files.

4.3.2 inserting, deleting and modifying

Insert and modify are implemented with put, delete is implemented with delete.

import lmdb


# Open the environment
env = lmdb.open("students")
# Set up affairs
txn = env.begin(write=True)
# Insert three records
txn.put(b"1", b"Alice")
txn.put(b"2", b"Bob")
txn.put(b"3", b"Peter")
# delete key="1"Records
txn.delete(b"1")
# modify key="3"The value is"Mark"
txn.put(b"3", b"Mark")
# Submit changes
txn.commit()

Note that when creating a transaction with txn=env.begin(), only write=True can write to the database.

4.3.3 query

get(key) is used to check the speaker record, and cursor is used to traverse the database.

import lmdb


# Open the environment
env = lmdb.open("students")
# Set up affairs
txn = env.begin()
# Query single record
print(txn.get(b"3"))  # b'Mark'
# b'2' b'Bob'
# b'3' b'Mark'
for key, value in txn.cursor():
    print(key, value)
# Submit changes
txn.commit()

5. Learning summary

Finally, it will be recalled that SQLite and MySQL are relational databases. During the operation, the connection object and cursor object cursor are created. The SQL statement is executed by execute, the change is submitted by commit, and the query result is obtained by fetch. LMDB is a key value database. During the operation, the connection with the database is established, the data is changed by put/delete, and the data is obtained by get.

Tags: Python Database SQL SQLite MySQL

Posted on Wed, 06 Nov 2019 23:44:33 -0500 by Muggin