Examples: Common Operations

import pymysql

# Create a connection object (non-SSL connection). The character sets of the client and server must be the same. Otherwise, an error is reported.
conn=pymysql.connect(database="database",user="user",password="********",host="localhost",port=port,charset="utf8")
# Create a connection object (SSL connection). The character sets of the client and server must be the same. Otherwise, an error is reported.
conn=pymysql.connect(database="database_name", 
                      user="user", 
                      password="********", 
                      host="IP_address", 
                      port=port,
                      ssl_disabled=False,
                      ssl_ca="file1",
                      ssl_key="file2",
                      ssl_cert="file3"
)
# Note: When using SSL to remotely connect to a database node, you need to use the SHA-256 authentication mode and valid CA root certificates, server public and private keys, and client public and private keys.

cur=conn.cursor() # Create a pointer object.
conn.set_character_set('utf8', collation=None) # Set the character set. The character sets of the client and server must be the same. Otherwise, an error is reported.

# Start a transaction.
conn.begin()

# Create a table.
cur.execute("CREATE TABLE student(id integer,name varchar(256),gender varchar(256));")

# Insert data.
cur.execute("INSERT INTO student(id,name,gender) VALUES(%s,%s,%s);",(1,'Aspirin','M'))
cur.execute("INSERT INTO student(id,name,gender) VALUES(%s,%s,%s);",(2,'Taxol','F'))
cur.execute("INSERT INTO student(id,name,gender) VALUES(%s,%s,%s);",(3,'Dixheral','M'))

# Insert data in batches.
stus = ((4,'John','M'),(5,'Alice','F'),(6,'Peter','M'))
cur.executemany("INSERT INTO student(id,name,gender) VALUES(%s,%s,%s);",stus)

# Obtain the result.
cur.execute("SELECT * FROM student;")
results=cur.fetchall()
print(results)

cur.execute("SELECT * FROM student;")
results=cur.fetchmany()
print(results)

# Perform a commit.
conn.commit()
# Insert a data record.
cur.execute("INSERT INTO student(id,name,gender) VALUES(%s,%s,%s);",(7,'Lucy','F'))

# Perform a rollback.
conn.rollback()

conn.select_db(dbname)

# Attempt to reconnect.
conn.ping()

# Close the connection.
cur.close()
conn.close()

# Common connection modes of PyMySQL
# Common RSA connection modes
conn = pymysql.connect(dbname="dbname", user="user", password="********", host="localhost", port=port)
# Common SSL connection modes
conn = pymysql.connect(dbname="dbname", user="user", password="********", host="localhost", port=port,'ssl_disabled':False,'ssl_ca': './ca.pem','ssl_key': './client-key.pem','ssl_cert': './client-cert.pem')