Python MySQL database interaction

The introduction

This article describes how to interact with the MySQL database in Python

Using PyMySQL database driver to achieve MySQL database add, delete, change, check and transaction processing

Introduction of MySQL

MySQL is a relational database management system developed by MySQL AB, a Swedish company. It was later acquired by Sun, which was later acquired by Oracle.

The characteristics of

Open source free free use of a wide range of cross-platform support, provides a variety of languages to call the API.

Is the first choice to learn database development.

The environment

Name of the environment	version
Python	3.7.9
PyMySQL	1.0.2
MySql-Server	5.7.32

First we need to install the PyMySQL database driver

pip install PyMySQL
Copy the code

If you want to specify a version

pip install PyMySQL==1.02.
Copy the code

The default source may be a bit slow to install the third library, you can configure other mirror sources. Pip installation of third-party libraries slow (Solution)

If you just want to temporarily install the third library faster, you can temporarily use another image source.

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple PyMySQL==1.02.
Copy the code

PyMySQL is a library used to connect to MySQL servers in the Python3.x version, while mysqlDB is used in Python2.

To prepare data

Create a database to prepare data

Create testDB database
create databases testdb charset=utf8;

use testdb;

Create table EMPLOYEE
create table employee (
	id int unsigned primary key auto_increment not null,
    emp_num varchar(10) not null,
    name varchar(10) not null,
    age int not null,
    sex varchar(6) not null,
    salary float not null
);

Insert employee data
insert into employee (emp_num, name, age, sex, salary) values 
	('M001'.'Joe'.56.'male'.10000),
    ('F002'.'bill'.50.'woman'.9000),
    ('M003'.'Cathy'.47.'male'.8000), 
    ('M004'.'Daisy'.46.'male'.7000),
    ('F005'.'seven sun'.36.'woman'.6000),
    ('M006'.'eight weeks'.28.'male'.5000),
    ('M007'.'wu nine'.26.'male'.4000),
    ('M008'.'zheng ten'.22.'male'.3000);
Copy the code

Python database access process

The introduction of the module

• inPy filesThe introduction ofpymysqlThe module

from pymysql import *
Copy the code

Connection object

• Used to establish a connection to the database
• Create an object: call the connect() method

Conn = connect(parameter list)Copy the code

• parameterhostConnecting:mysqlHost, if the machine islocalhost
• parameterportConnecting:mysqlPort of the host. The default port is3306
• Parameter database: indicates the name of the database
• Parameter user: indicates the user name of the connection
• Parameter password: indicates the connection password
• parametercharset: Encoding mode used for communication. Recommendedutf8

Object methods

• Close () Closes the connection
• Commit ()
• Cursor () returns a cursor object that is used to execute SQL statements and get results

Cursor objects

• Used to execute SQL statements. The most frequently used statements are SELECT, INSERT, UPDATE, and DELETE
• Get Cursor object: Call the Cursor () method of the Connection object

cursor = conn.cursor()
Copy the code

Object methods

• Close () closed
• execute(operation [, parameters ])Execute SQL statement, return the number of affected rows, mainly for executionInsert, update, deleteStatement, which can also be executedCreate, ALTER, dropSuch statements
• Fetchone () fetchone() fetches the first row of the query result set and returns a tuple
• Fetchall () the query is executed, get all the rows of the result set, a line constitutes a tuple, then insert the yuan assembly back into a tuple

Object properties

• rowcountRead-only: indicates the last timeexecute()Number of rows affected after execution
• connectionGets the current connection object

Python operates the MySQL database

The queryMySQLThe service version

""" Python and MySQL database interaction exercises ""
import pymysql


def mysql_version() :
	"" query MySQL version information ""

	Get database connection
	conn = pymysql.connect(
		host='localhost'.Mysql server host localhost
		user='root', 
		password='123456', 
		database='testdb',
		port=3306
	)

	Create cursor object cursor
	cursor = conn.cursor()

	SQL > select * from mysql
	sql = 'select version(); '

	Execute SQL statement
	cursor.execute(sql)

	data = cursor.fetchone()
 
	print ("Database version : %s " % data)
 
	Close the database connection
	conn.close()


def main() :

	mysql_version()


if __name__ == '__main__':
	main()
Copy the code

Add, delete and modify the EMPLOYEE data table

To make it easier to manipulate the database, I have extracted the database connection and cursor object into a function

import pymysql


def get_conn(dbname) :
	""" Get the database connection and cursor object for the local DBname """
	conn = pymysql.connect(
		host = 'localhost', 
		user = 'root',
		password = '123456',
		database = dbname,
		port = 3306
	)
	return conn, conn.cursor()
Copy the code

Add employee information to the EMPLOYEE data table

def emp_insert() :
	""" New Employee Information """
	conn, cursor = get_conn(dbname='testdb')

	# insert data
	insert_sql = Insert into EMPLOYEE values (NULL, 'M009', 'hui', 21, 'male ', 6000); ""

	Execute SQL statement, return the number of rows in response
	count = cursor.execute(insert_sql)
	print(count)

	# use placeholders
	emp_info = ('M010'.'wang'.22.'male'.7000)
	insert_sql = """insert into employee values (NULL, %s, %s, %s, %s, %s);" ""
	count = cursor.execute(sql, emp_info)
	print(count)

	# Update the data (give every employee a 10% raise)
	update_sql = ""update employee set salary=salary * 1.1; ""
	cursor.execute(update_sql)

	# delete data (delete employee named Wang)
	delete_sql = """delete from employee where name='wang';" ""
	cursor.execute(delete_sql)

	Remember to commit to database for execution
	conn.commit()

	cursor.close()
	conn.close()
Copy the code

Update employee data table information

def emp_update() :
	"" Update employee database table data ""
	conn, cursor = get_conn(dbname='testdb')

	# Update the data (give every employee a 10% raise)
	update_sql = ""update employee set salary=salary * 1.1; ""
	cursor.execute(update_sql)

	Remember to commit to database for execution
	conn.commit()

	cursor.close()
	conn.close()
Copy the code

Delete the EMPLOYEE table information

def emp_delete() :
	"" Delete table data from employee database ""
	conn, cursor = get_conn(dbname='testdb')

	# delete data (delete employee named Wang)
	delete_sql = """delete from employee where name='wang';" ""
	cursor.execute(delete_sql)

	Remember to commit to database for execution
	conn.commit()

	cursor.close()
	conn.close()
Copy the code

The original Employee data table

mysql> select * from employee;
+----+---------+------+-----+-----+--------+
| id | emp_num | name | age | sex | salary |
+----+---------+------+-----+-----+--------+
|  1| | M001 | zhang SAN56Male | |10000 |
|  2| | F002 |, dick, and harry50Female | |9000 |
|  3| M003 | fifty |47Male | |8000 |
|  4Six | | M004 | zhao46Male | |7000 |
|  5Seven | | F005 | sun36Female | |6000 |
|  6| M006 | | 8 weeks28Male | |5000 |
|  79 | | M007 | wu26Male | |4000 |
|  8Ten | | M008 | zheng22Male | |3000 |
+----+---------+------+-----+-----+--------+
8 rows in set (0.00 sec)
Copy the code

After the operation

Mysql > select *from employee;
+----+---------+------+-----+-----+--------+
| id | emp_num | name | age | sex | salary |
+----+---------+------+-----+-----+--------+
|  1| | M001 | zhang SAN56Male | |10000 |
|  2| | F002 |, dick, and harry50Female | |9000 |
|  3| M003 | fifty |47Male | |8000 |
|  4Six | | M004 | zhao46Male | |7000 |
|  5Seven | | F005 | sun36Female | |6000 |
|  6| M006 | | 8 weeks28Male | |5000 |
|  79 | | M007 | wu26Male | |4000 |
|  8Ten | | M008 | zheng22Male | |3000 |
| 16 | M009    | hui  |  21Male | |6000 |
| 17 | M010    | wang |  22Male | |7000 |
+----+---------+------+-----+-----+--------+
10 rows in set (0.00Mysql > select * from mysqlfrom employee;
+----+---------+------+-----+-----+--------+
| id | emp_num | name | age | sex | salary |
+----+---------+------+-----+-----+--------+
|  1| | M001 | zhang SAN56Male | |11000 |
|  2| | F002 |, dick, and harry50Female | |9900 |
|  3| M003 | fifty |47Male | |8800 |
|  4Six | | M004 | zhao46Male | |7700 |
|  5Seven | | F005 | sun36Female | |6600 |
|  6| M006 | | 8 weeks28Male | |5500 |
|  79 | | M007 | wu26Male | |4400 |
|  8Ten | | M008 | zheng22Male | |3300 |
| 16 | M009    | hui  |  21Male | |6600 |
+----+---------+------+-----+-----+--------+
9 rows in set (0.00 sec)
Copy the code

Employee data table query

• Fetchone () retrieves the first row of the query result set, returning a tuple
• Fetchall () fetchall() fetchall() fetchall() fetchall() fetchall() fetchall() fetchall() fetchall() fetchall() fetchall() fetchall() fetchall

A single row query retrieves data

def emp_fetchone() :
	SQL > select EMPLOYEE from employee;

	Single row query
	conn, cursor = get_conn('testdb')
	sql = """select * from employee where id > 3;" ""
	count = cursor.execute(sql)
    print("Query %d data :" % count)

	for i in range(count):
		Get the query result line by line
		result = cursor.fetchone()
		print(result)

	cursor.close()
	conn.close()
Copy the code

Multi-row query retrieves data

def emp_fetchall() :
	Select * from employee; select * from EMPLOYEE;

	# multi-line query
	conn, cursor = get_conn('testdb')
	sql = """select * from employee;" ""
	count = cursor.execute(sql)
        print("Query %d data :" % count)
    
	results = cursor.fetchall()
	for ret in results:
		print(ret)

	cursor.close()
	conn.close()
Copy the code

Transaction processing

Why should there be transactions

Transaction is widely used in order system, banking system and other scenarios

Such as:

User A and user B are depositors of the bank. Now, user A wants to transfer 500 yuan to user B, so the following things need to be done:

1. Check the account balance of A >500 yuan;
2. 500 yuan is deducted from account A;
3. Add 500 yuan to account B;

The normal process goes, account A gets $500, account B gets $500, everyone’s happy.

What if the system breaks down after the money is withdrawn from account A? A lost 500 for nothing, and B did not receive his 500.

In the above case, there is A hidden premise: A deducts money and B increases money, and either succeeds or fails simultaneously. This is where the transaction needs to be

The so-called transaction, it is a sequence of operations, these operations are either performed or not performed, it is an inseparable unit of work.

For example, a bank transfer job: deduct money from one account and add money to another account, and either do both or do neither. Therefore, they should be treated as a transaction. A transaction is the unit of data consistency maintained by a database, and data consistency is maintained at the end of each transaction

The transaction mechanism ensures data consistency.

Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four properties are commonly referred to as ACID properties.

• Atomicity. A transaction is an indivisible unit of work in which all or none of the operations involved are performed.
• Consistency. Transactions must change the database from one consistent state to another. Consistency is closely related to atomicity.
• Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.
• They are persistent. Permanence, also known as permanence, means that once a transaction is committed, its changes to data in the database should be permanent. Subsequent operations or failures should not affect it in any way.

Transactions in Python DB API 2.0 provide two methods commit() or rollback().

Small instance

def transaction_test() :
	""" Transaction demo """
	conn, cursor = get_conn('testdb')
	sql = "delete from employee where age > %s" % (20)
	try:
	   Execute SQL statement
	   cursor.execute(sql)

	   # here to divide by 0 exception, to demonstrate the real situation
	   n = 1 / 0

	   Commit to database
	   conn.commit()

	except:
	   Rollback if an error occurs
	   print('Transaction rollback')
	   conn.rollback()

	cursor.close()
	conn.close()
Copy the code

SQL > delete from employee where age > 20

The source code

The source code has been uploaded to Gitee PythonKnowledge: PythonKnowledge Repository.

✍ code word is not easy, but also hope you heroes support ❤️.

The public,

Create a new folder X

Nature took tens of billions of years to create our real world, while programmers took hundreds of years to create a completely different virtual world. We knock out brick by brick with a keyboard and build everything with our brains. People see 1000 as authority. We defend 1024. We are not keyboard warriors, we are just extraordinary builders of ordinary world.