Python operation SQLite/MySQL/LMDB

1. overview

1.1 Preface

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

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

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.

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

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

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

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

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

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.

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.

Add new comment

0 comments