Official website document link: https://pymysql.readthedocs.io/en/latest/
1.PyMySQL installation
1 pip install pymysql
2. Database connection
Note: MySQL database has created TESTDB. In TESTDB database, you have created the table EMPLOYEE
1 # -*- coding:utf-8 -*-
2 import pymysql
3
4 # Open database connection
5 connection = pymysql.connect(
6 host='localhost', # IP，MySQL database server IP address
7 port=3306, # Port, 3306 by default, optional
8 user='root', # Database user name
9 password='yxh1990', # Database login password
10 database='testdb', # Database to connect to
11 charset='utf8' # Character set, note not'utf-8'
12 )
13
14 # use cursor()Method to create a cursor object cursor
15 cursor = connection.cursor()
16
17 # use execute()Method execution SQL query
18 sql = 'SELECT VERSION()'
19 cursor.execute(sql)
20
21 # use fetchone()Get a single piece of data
22 data = cursor.fetchone()
23 print("Database version:%s" % data)
24
25 # close database
26 connection.close()
Results after execution:
Database version: 5.7.21-log
2. Create database table
1 # -*- coding:utf-8 -*-
2 import pymysql
3
4 # Open database connection
5 connection = pymysql.connect(
6 host='localhost', # IP，MySQL database server IP address
7 port=3306, # Port, 3306 by default, optional
8 user='root', # Database user name
9 password='yxh1990', # Database login password
10 database='testdb', # Database to connect to
11 charset='utf8' # Character set, note not'utf-8'
12 )
13
14 # use cursor()Method to create a cursor object cursor
15 cursor = connection.cursor()
16
17 # use execute()Method execution SQL,Delete if table exists
18 sql = 'DROP TABLE IF EXISTS EMPLOYEE'
19 cursor.execute(sql)
20
21 # Create table
22 create_table_sql = """CREATE TABLE EMPLOYEE (
23 ID INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
24 FIRST_NAME CHAR(20) NOT NULL,
25 LAST_NAME CHAR(20),
26 AGE INT,
27 SEX CHAR(1),
28 SALARY FLOAT)"""
29 cursor.execute(create_table_sql)
30
31 # close database
32 connection.close()
3. Database insertion
1 # -*- coding:utf-8 -*-
2 import pymysql
3
4 # Open database connection
5 connection = pymysql.connect(
6 host='localhost', # IP，MySQL database server IP address
7 port=3306, # Port, 3306 by default, optional
8 user='root', # Database user name
9 password='yxh1990', # Database login password
10 database='testdb', # Database to connect to
11 charset='utf8' # Character set, note not'utf-8'
12 )
13
14 # use cursor()Method to create a cursor object cursor
15 cursor = connection.cursor()
16
17 # SQL Insert statement
18 insert_sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, SALARY) \
19 VALUES ('%s','%s', %s, '%s', %s)" \
20 % ('Mac', 'Mohan', 20, 'M', 2000.00)
21 try:
22 # implement sql sentence
23 cursor.execute(insert_sql)
24 # Submit
25 connection.commit()
26 except:
27 # Rollback on error
28 connection.rollback()
29
30 # close database
31 connection.close()
Batch insert:
1 # -*- coding:utf-8 -*-
2 import pymysql
3
4 # Open database connection
5 connection = pymysql.connect(
6 host='localhost', # IP，MySQL database server IP address
7 port=3306, # Port, 3306 by default, optional
8 user='root', # Database user name
9 password='yxh1990', # Database login password
10 database='testdb', # Database to connect to
11 charset='utf8' # Character set, note not'utf-8'
12 )
13
14 # use cursor()Method to create a cursor object cursor
15 cursor = connection.cursor()
16
17 # Insert statement
18 insert_sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, SALARY) VALUES (%s, %s, %s, %s, %s)" # Be careful not to use it here'%s'，use s%Standing position
19 args_list = [('Mei', 'Han', 20, 'F', 3000.00), ('Obama', 'Qiao', 57, 'M', 2348.01), ('Jim', 'Cluse', 35, 'F', 2231.01), ('Macer', 'Jordan', 45, 'M', 2340.10)]
20
21 try:
22 # implement sql sentence
23 cursor.executemany(insert_sql, args_list)
24 # Submit
25 connection.commit()
26 except:
27 # Rollback on error
28 connection.rollback()
29
30 # close database
31 connection.close()
4. Database query operation
Python queries MySQL, using fetchone() to get a single piece of data, fetchmany(size=None) to get multiple pieces of data, and fetchall() to get all the data.
- fetchone(): get the next query result set. The result set is an object (tuple format)
- fetchmany(size=None): get size results from the result set. If size=None, get the first one
- fetchall(): get all the returned rows.
- rowcount: This is a read-only property and returns the number of rows affected after executing the execute() method.
1 # -*- coding:utf-8 -*-
2 import pymysql
3
4 # Open database connection
5 connection = pymysql.connect(
6 host='localhost', # IP，MySQL database server IP address
7 port=3306, # Port, 3306 by default, can be left blank
8 user='root', # Database user name
9 password='yxh1990', # Database login password
10 database='testdb', # Database to connect to
11 charset='utf8' # Character set, note not'utf-8'
12 )
13
14 # use cursor()Method to create a cursor object cursor
15 cursor = connection.cursor()
16
17 # Query statement
18 select_sql = "SELECT * FROM EMPLOYEE WHERE SALARY>2000"
19
20 # fetchone()method
21 try:
22 # implement sql sentence
23 cursor.execute(select_sql)
24 result_one = cursor.fetchone()
25 result_two = cursor.fetchone()
26 result_three = cursor.fetchone()
27 result_four = cursor.fetchone()
28 print(result_one)
29 print(result_two)
30 print(result_three)
31 print(result_four)
32 except:
33 print('fetchone Failed to get data')
34
35 # fetchmany(size=none)
36 try:
37 cursor.execute(select_sql)
38 result_many = cursor.fetchmany(size=None)
39 print(result_many)
40 except:
41 print('fetchmany Failed to get data')
42
43 # fetchall()and rowcount
44 try:
45 cursor.execute(select_sql)
46 result_all = cursor.fetchall()
47 row_count = cursor.rowcount
48 print(result_all)
49 print(row_count)
50 except:
51 print('fetchmany Failed to get data')
52
53 # close database
54 connection.close()
The execution results of the above script are as follows:
1 (2, 'Jim', 'Cluse', 32, 'M', 5000.78)
2 (3, 'Mary', 'Lily', 28, 'F', 3564.89)
3 (4, 'Tom', 'Kuke', 35, 'M', 4545.56)
4 None
5 ((2, 'Jim', 'Cluse', 32, 'M', 5000.78), (3, 'Mary', 'Lily', 28, 'F', 3564.89))
6 ((2, 'Jim', 'Cluse', 32, 'M', 5000.78), (3, 'Mary', 'Lily', 28, 'F', 3564.89), (4, 'Tom', 'Kuke', 35, 'M', 4545.56))
7 3
5. Database update operation
1 # -*- coding:utf-8 -*-
2 import pymysql
3
4 # Open database connection
5 connection = pymysql.connect(
6 host='localhost', # IP，MySQL database server IP address
7 port=3306, # Port, 3306 by default, optional
8 user='root', # Database user name
9 password='yxh1990', # Database login password
10 database='testdb', # Database to connect to
11 charset='utf8' # Character set, note not'utf-8'
12 )
13
14 # use cursor()Method to create a cursor object cursor
15 cursor = connection.cursor()
16
17 # UPDATE statement
18 update_sql = "UPDATE EMPLOYEE SET SALARY = SALARY + 1000 WHERE SEX='%c'" % ('M')
19
20 try:
21 # implement sql sentence
22 cursor.execute(update_sql)
23 connection.commit()
24 except:
25 # Rollback on error
26 connection.rollback()
27
28 # close database
29 connection.close()
6. Database deletion
1 # -*- coding:utf-8 -*-
2 import pymysql
3
4 # Open database connection
5 connection = pymysql.connect(
6 host='localhost', # IP，MySQL database server IP address
7 port=3306, # Port, 3306 by default, optional
8 user='root', # Database user name
9 password='yxh1990', # Database login password
10 database='testdb', # Database to connect to
11 charset='utf8' # Character set, note not'utf-8'
12 )
13
14 # use cursor()Method to create a cursor object cursor
15 cursor = connection.cursor()
16
17 # Delete statement
18 delete_sql = "DELETE FROM EMPLOYEE WHERE AGE>'%s'" % (30)
19
20 # fetchone()method
21 try:
22 # implement sql sentence
23 cursor.execute(delete_sql)
24 # Submit modification
25 connection.commit()
26 except:
27 # Rollback on error
28 connection.rollback()
29
30 # close database
31 connection.close()
7.Connection class and Cursor class
7.1 Connection class
class pymysql.connections.Connection(host=None, user=None, password='', database=None, port=0, unix_socket=None, charset='', sql_mode=None, read_default_file=None, conv=None, use_unicode=None, client_flag=0, cursorclass=<class 'pymysql.cursors.Cursor'>, init_command=None, connect_timeout=10, ssl=None, read_default_group=None, compress=None, named_pipe=None, autocommit=False, db=None, passwd=None, local_infile=False, max_allowed_packet=16777216, defer_connect=False, auth_plugin_map=None, read_timeout=None, write_timeout=None, bind_address=None, binary_prefix=False, program_name=None, server_public_key=None)
Parameters:
Host: the host where the database server is located
User: user name
Password: password
Database: database name, None to not use a particular one
Port: port number. The default value is 3306
charset: Code (Note: utf8 is not utf-8)
begin(): start transaction
close(): close the connection
Raise: if the connection is closed, it will cause an exception
commit(): commit a transaction
cursor(cursor=None): creates a cursor
Parameter: any one of Cursor, SSCursor, DictCursor and SSDictCursor. The default Cursor is None
Open: returns True if the connection is open
ping(reconnect=True): check whether the database service connection is normal
Parameter: reconnect reconnect if the connection is disconnected
Raise: if the connection has been closed and reconnect=False, an exception will be caused
rollback(): rollback transaction
select_db(): select database
Parameter: db database name
show_warnings(): send warning SQL command
7.2 Cursor class
Objects used for database interaction. Instead of using Corsor to create cursor instances, call connections Connect. cursor().
close(): closes the cursor when all data is used
execute(query, args=None): execute a query statement
Parameters:
query(str): Query to execute
args(tuple,list or dict) optional parameter
Return: number of rows affected (return type: int)
Note: if the parameter args is a list or tuple,% s can be used as a placeholder in the query. If the parameter args is a dictionary,% name)s can be used as a placeholder in the query.
Execute any (query, args): execute query statements in batches through parameters
Parameters:
query: Query to execute on the server
args: sequence of sequences or mapped sequences.
Return: number of rows affected (return type: int)
Note: this method improves the performance of multi line INSERT and REPLACE. Otherwise, it is equivalent to traversing all parameters with execute().
fetchall(): get all rows
fetchmany(size=None): get the number of rows according to the size value
fetchone(): get the next line
max_stmt_length=1024000: the maximum size of the statement generated by executenamy().
The maximum allowed statement size is max_allowed_packet - packet_header_size. max_ allowed_ The default value of packet is 1048576.
mogrify(query, args=None):
7.3 SSCursor, DictCursor and SSDictCursor
The methods in SSCursor include: close(), fetchall(), and fetchall()_ unbuffered(),fetchmany(size=None),fetchone(),read_next
SSCursor, a non buffered cursor, is mainly used for queries that return a large amount of data or to connect to a remote server when the network speed is slow.
Use SSCursor (streaming cursor) to prevent the client from occupying a large amount of memory. (the cursor doesn't actually cache any data. It won't read everything into memory. Its method is to read records from the storage block and return them to you one by one.), Note: because SSCursor is a cursor without cache, as long as the result set is not fetched, this conn cannot process other sql, including generating another cursor. If you need to do something else, please regenerate it into a connection object.
The returned result of DictCursor is dictionary type:
1 # -*- coding:utf-8 -*-
2 import pymysql
3
4 # Open database connection
5 connection = pymysql.connect(
6 host='localhost', # IP，MySQL database server IP address
7 port=3306, # Port, 3306 by default, optional
8 user='root', # Database user name
9 password='yxh1990', # Database login password
10 database='testdb', # Database to connect to
11 charset='utf8' # Character set, note not'utf-8'
12 )
13
14 # use cursor()Method to create a dictionary cursor object DictCursor
15 dict_cursor = connection.cursor(pymysql.cursors.DictCursor)
16
17 # Query statement
18 select_sql = "SELECT * FROM EMPLOYEE"
19
20 try:
21 # implement sql sentence
22 dict_cursor.execute(select_sql)
23 result_all = dict_cursor.fetchall()
24 print(result_all)
25 except:
26 print('Query error!')
27
28 # close database
29 connection.close()
Execution result:
[{'ID': 1, 'FIRST_NAME': 'Mac', 'LAST_NAME': 'Mohan', 'AGE': 20, 'SEX': 'M', 'SALARY': 3000.0},
{'ID': 3, 'FIRST_NAME': 'Mary', 'LAST_NAME': 'Lily', 'AGE': 28, 'SEX': 'F', 'SALARY': 3564.89},
{'ID': 4, 'FIRST_NAME': 'Mei', 'LAST_NAME': 'Han', 'AGE': 20, 'SEX': 'F','SALARY': 3000.0},
{'ID': 5, 'FIRST_NAME': 'Obama', 'LAST_NAME': 'Qiao', 'AGE': 57, 'SEX': 'M', 'SALARY': 2348.01},
{'ID': 6, 'FIRST_NAME': 'Jim', 'LAST_NAME': 'Cluse', 'AGE': 35, 'SEX': 'F', 'SALARY': 2231.01},
{'ID': 7, 'FIRST_NAME': 'Macer', 'LAST_NAME': 'Jordan', 'AGE': 45, 'SEX': 'M', 'SALARY': 2340.1}]