Environment: Win10 x64 + Python 3.7 + PyMySQL 1.0.2 + MySQL 8.0.27
1 installation
pip install pymysql
2 addresses
https://pypi.org/project/pymysql/
3.1 Database version query (search_version.py)
import pymysql # Open the database connection try: db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306) print('The connection is successful!') except: print('something wrong!') # Use the cursor() method to create a cursor object cursor cursor = db. cursor() # Execute SQL query using execute() method cursor. execute("SELECT VERSION()") # Use the fetchone() method to get a single piece of data. fetchone only returns a one-dimensional tuple data = cursor. fetchone() print(type(data)) print(data) print("Database version : %s " % data) # Close the database connection db. close() ''' connection succeeded! <class 'tuple'> ('8.0.27',) Database version: 8.0.27 '''
3.2 Create a table (create_table.py)
import pymysql # Open the database connection try: db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='db_jupiter') print('The connection is successful!') except: print('something wrong!') # Use the cursor() method to create a cursor object cursor cursor = db. cursor() # Execute SQL using the execute() method, and delete the table if it exists cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # create table using prepared statement sql = """CREATE TABLE EMPLOYEE ( ID INT NOT NULL AUTO_INCREMENT, FIRST_NAME CHAR(20) NOT NULL COMMENT 'first name', LAST_NAME CHAR(20), AGE INT COMMENT 'age', SEX CHAR(1) COMMENT 'sex', INCOME FLOAT NOT NULL COMMENT 'income', PRIMARY KEY (ID) )""" cursor. execute(sql) print('Table created successfully!') # Close the database connection db. close() ''' connection succeeded! Create table successfully! '''
3.3 Insert data (insert_update_delete_table.py)
import pymysql # Open the database connection try: db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='db_jupiter') print('The connection is successful!') except: print('something wrong!') # Use the cursor() method to create a cursor object cursor cursor = db. cursor() # SQL insert statement # sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('Qin', 'DeXu', 20, 'M', 2000)""" # sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('Li', 'Xiaofei', 20, 'F', 3000)""" sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('Wang', 'HanYu', 20, 'M', 5000)""" # SQL update statement # sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') # SQL delete statement # sql = "DELETE FROM EMPLOYEE WHERE AGE > %d" % (20) try: # execute sql statement cursor. execute(sql) # Submit to the database for execution db. commit() print('Data inserted successfully!') except: # rollback if an error occurs db. rollback() print('Data insertion error!') # Close the database connection db. close() ''' connection succeeded! Data inserted successfully! '''
3.4 Insert data in batches (insert_table_many.py)
import pymysql # Open the database connection try: db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='db_jupiter') print('The connection is successful!') except: print('something wrong!') # Use the cursor() method to create a cursor object cursor cursor = db. cursor() # SQL insert statement data = [ ('DongFang', 'YuXiao', 25, 'M', 2000.30), ('OuYang', 'HeHe', 22, 'F', 2000), ('GongSun', 'Sheng', 23, 'M', 2000) ] stmt = "INSERT INTO EMPLOYEE (FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s)" try: # execute sql statement cursor. executemany(stmt, data) # Submit to the database for execution db. commit() print('Data inserted successfully!') except: # rollback if an error occurs db. rollback() print('Data insertion error!') # Close the database connection db. close() ''' connection succeeded! Data inserted successfully! ''' ''' illustrate: The placeholder %s is changed to %d, and an error is reported. '''
3.5 Query data (search_table.py)
import pymysql # Open the database connection try: db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='db_jupiter') print('The connection is successful!') except: print('something wrong!') # Use the cursor() method to create a cursor object cursor cursor = db. cursor() # SQL query statement sql = "SELECT * FROM EMPLOYEE WHERE INCOME >= %s" % (3000) try: # Execute the SQL statement cursor. execute(sql) # rowcount: This is a read-only property and returns the number of rows affected after executing the execute() method. row_count = cursor.rowcount print(type(row_count)) print(row_count) # fetchall(): Receive all returned result rows, get all record lists fetchall returns two-dimensional tuple (tuple contains tuple) results = cursor. fetchall() print(type(results)) print(results) for row in results: id = row[0] fname = row[1] lname = row[2] age = row[3] sex = row[4] income = row[5] # print the result print('Data query succeeded!') print("id=%s, fname=%s, lname=%s, age=%s, sex=%s, income=%s" % (id, fname, lname, age, sex, income)) except: print("Error: unable to fetch data") # Close the database connection db. close() ''' connection succeeded! <class 'int'> 2 <class 'tuple'> ((2, 'Li', 'Xiaofei', 20, 'F', 3000.0), (3, 'Wang', 'HanYu', 20, 'M', 5000.0)) Data query succeeded! id=2, fname=Li, lname=Xiaofei, age=20, sex=F, income=3000.0 Data query succeeded! id=3, fname=Wang, lname=HanYu, age=20, sex=M, income=5000.0 '''
Other cases:
The executemany() method requires that the incoming parameter is a list of nested tuples, each tuple in the list represents a set of data, and each value in the tuple represents a field value of a data.
######################################
Batch insert 1:
data = [
(‘Jane’, date(2005, 2, 12)),
(‘Joe’, date(2006, 5, 23)),
(‘John’, date(2010, 10, 3)),
]stmt = “INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)”
cursor. executemany(stmt, data)INSERT INTO employees (first_name, hire_date)
VALUES (‘Jane’, ‘2005-02-12’), (‘Joe’, ‘2006-05-23’), (‘John’, ‘2010-10-03’)
######################################
Batch insert 2:
# Define the SQL statement
insert_sql = “INSERT INTO person VALUES (%s, %s, %s)”# define data
data = [
(‘1’, ‘Zhang San’, 18),
(‘2’, ‘Lisi’, 19),
(‘3’, ‘Wang Wu’, 20)
]
# Batch insert data
cursor. executemany(insert_sql, data)Batch delete 3:
# Define the SQL statement
delete_sql = “DELETE FROM WHERE id = %s”
# define data
ids = [(1,), (2,), (3,)]
# Batch delete data
cursor.executemany(delete_sql, ids)
Reference link:
https://zhuanlan.zhihu.com/p/397765212
http://www.xoxxoo.com/index/index/article/id/288
https://blog.csdn.net/m0_48300767/article/details/131063781
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html