1. Connect to mysql database

To connect to mysql in python, you need to import the pymysql module first

import pymysql

# Connect to mysql database---
# User name, password, host name, port number
# host = None, con specifies the host name, ip address, 127.0.0.1 and localhost represents the host
# user = None, specify user name
# "Password =" ", specify password"
# database = None, specify the connected database
# port = 0, the specified database port number is 3306 by default
# charset = '', specify encoding
connect = pymysql.Connect(host="10.20.130.20",user="xxxx",password = "123456",database="xxx",charset='utf8')

Use connect to receive the return value of the connect function

Note: there are too many parameters in Connect. Here are some commonly used ones

# Execute sql statement
# Get the cursor object, which is used to execute sql statements
cursor = connect.cursor()
# Execute sql statement
cursor.execute("select version();")

Use the execute function to execute instructions

Close connection after function execution

# Close connection
cursor.close()
connect.close()

2. Create a table

import pymysql


connect = pymysql.Connect(host="10.31.160.116",user="pyrans",password = "pp",database="school",charset='utf8')

# Get cursor object
cursor = connect.cursor()

# If the table already exists, delete the user
sqlFood = "drop table if EXISTS  food"

cursor.execute(sqlFood)

# Create a table
createSql = "CREATE TABLE food(id INT PRIMARY  KEY  auto_increment,name VARCHAR(20),price INT)"

cursor.execute(createSql)

# close resource
cursor.close()
connect.close()

3. Adding data

import  pymysql
connect = pymysql.Connect(host="10.31.160.116",user="pyrans",password = "pp",database="school",charset='utf8')
cursor = connect.cursor()

# Rollback is required for addition, deletion and modification 
try:
    sqlInsert = "insert into food values(0,'Braised big elbow',91);"
    sqlInsert2 = "insert into food values(0,'Spicy Hot pot',66);"
    cursor.execute(sqlInsert)
    cursor.execute(sqlInsert2)
#     commit submission
    connect.commit()

except:  #Rollback sql whenever an exception occurs
    # When using sql for data operation, it is usually necessary to ensure the integrity of data. When multiple sql statements need to be executed at the same time, any one of them is executed incorrectly, which may cause all data errors,
    #   Therefore, when one of the other sql statements fails, it will be rolled back to the state before these sql statements are executed
    connect.rollback()


cursor.close()
connect.close()

Note: data deletion and modification are similar to data addition

4. Data query

Example: selectSql = "select * from food"

       cursor.execute(selectSql)

Code reception is required when inputting the above code to query data

cursor.fetchone() gets a result

cursor.fetchall() gets all the results

cursor.fetchmany(size) gets a specified number of results

cursor.rowcount the number of rows to get the result

Note: the above return values are all values of array type

Usage similar to next in iterator

import  pymysql

connect = pymysql.Connect(host="10.31.160.116",user="pyrans",password = "pp",database="school",charset='utf8')

cursor = connect.cursor()

# --------------Query
selectSql = "select * from food"
cursor.execute(selectSql)

# Query results in cursor
# cursor.fetchone() gets a result
# cursor.fetchall() gets all the results
# cursor.fetchmany(size) gets a specified number of results
# cursor.rowcount the number of rows to get the result

# res = cursor.fetchone()
# print(res)                      # (1, 'braised big elbow', 91)
# res = cursor.fetchone()
# print(res)                      # (2, 'spicy hot pot', 66)

# res = cursor.fetchall()
# print(res)                      # ((1, 'braised big elbow', 91), (2, 'spicy hot pot', 66))

allres = cursor.fetchmany(1)      # ((1, 'braised big elbow', 91),)
print(allres)

res = cursor.rowcount
print(res)                        # 2

# Close connection
cursor.close()
connect.close()