​

Catalog

1. pymysql module

2. Instructions for using pymysql modules

2.1 pymysql operation flowchart

2.2 Creating Connection Objects

2.3 Getting Cursor Objects

2.4 Usage Example

2.4.1 Getting a Data

6.4.2 Get multiple pieces of data:

6.4.3 Add or delete data

3. Instructions for using pymssql modules

Activity address: CSDN 21-day Learning Challenge

1. pymysql module

The pymysql module is a Python-written MySQL driver that allows us to manipulate the MySQL Library in Python. Since it is a third-party library, it needs to be installed manually. Different compilation environments use different methods. Use Pycharm to install third-party libraries to view future links specifically: Pycharm Installation Tutorial

2. Instructions for using pymysql modules

2.1 pymysql operation flowchart

2.2 Creating Connection Objects

Call the connect() function in the pymysql module to create the connection object.

Functional syntax: conn = pymysql.connect (parameter list)

Parameter description:

• Host: Connect mysql host if local is'localhost'
• Port: The port of the mysql host to which you are connecting, defaulting to 3306
• Database: The name of the database
• User: the user name of the connection
• Password: the password for the connection
• charset: the encoding method used for communication, utf8 is recommended, note: utf8 instead of utf-8.

Connection object operation instructions:

• conn.close(): Close the connection
• conn.commit(): Submit data
• conn.rollback(): Undo data

2.3 Getting Cursor Objects

The goal of acquiring cursor objects is to execute sql statements to add, delete, alter and check the database.

Functional syntax: cur = conn.cursor()

Cursor operation instructions:

• execute(operation[parameters]): Use cursors to execute SQL statements, return the number of rows affected, mainly for insert, update, delete, select and other statements, or creat e, alter, drop and other statements
• cur.fetchone(): Gets a piece of data from the query result set and returns a tuple, such as (1,'Zhang San')
• cur.fetchall(): Gets all the data in the query result set and returns an array, such as ((1,'Zhang San'), (2,'Li Si')
• cur.close(): Close the cursor to indicate that the database operation is complete

2.4 Usage Example

2.4.1 Getting a Data

Use cur.fetchone() takes a piece of data from the query result set and returns a tuple, such as (1,'Zhang San').

#1. Import pymysql module
import pymysql
#2. Create Connection Objects
# Host: Connect mysql host if local is'localhost'
# Port: The port of the mysql host to which you are connecting, defaulting to 3306
# Database: The name of the database
# User: the user name of the connection
# Password: the password for the connection
# charset: the encoding method used for communication, utf8 is recommended
conn = pymysql.connect(host='localhost',
                       port=3306,
                       user='root',
                       password='mysql',
                       database='python41',
                       charset='utf8'
                       )
#3. Get the cursor to execute the sql statement
cursor = conn.cursor()
#Prepare sql, how to write SQL in mysql client before, how to write SQL in Python program
sql = 'select * from students;'
#4. Execute sql statements
cursor.execute(sql)
#Get the query result, the data type returned is a tuple: (1,'Zhang San')
row = cursor.fetchone()
print(row)
#5. Close cursors
cursor.close()
#6. Close the connection
conn.close()

Analysis: If an error occurs pymysql.err. OperationalError: (2003,'Can't connect to MySQL server on'localhost'([WinError 10061] Unable to connect due to active rejection by the target computer.) Download link because MySQL is not open or downloaded: MySQL Download Address

6.4.2 Get multiple data:

Use cur.fetchall() takes all the data in the query result set and returns an array, such as ((1,'Zhang San'), (2,'Li Si').

#1. Import pymysql module
import pymysql
#2. Create Connection Objects
# Host: Connect mysql host if local is'localhost'
# Port: The port of the mysql host to which you are connecting, defaulting to 3306
# Database: The name of the database
# User: the user name of the connection
# Password: the password for the connection
# charset: the encoding method used for communication, utf8 is recommended
conn = pymysql.connect(host='localhost',
                       port=3306,
                       user='root',
                       password='mysql',
                       database='python41',
                       charset='utf8'
                       )
#3. Get the cursor to execute the sql statement
cursor = conn.cursor()
#Prepare sql, how to write SQL in mysql client before, how to write SQL in Python program
sql = 'select * from students;'
#4. Execute sql statements
cursor.execute(sql)
#Get the query result, the data type returned is a tuple: ((1,'Zhang San'), (2,'Li Si'))
rows = cursor.fetchall()
for row in rows:
    print(row)
#5. Close cursors
cursor.close()
#6. Close the connection
conn.close()

6.4.3 Add or delete data

Submit data using conn.commit(); conn.rollback() undoes the addition or deletion of data to the database.

#1. Import pymysql module
import pymysql
#2. Create Connection Objects
# Host: Connect mysql host if local is'localhost'
# Port: The port of the mysql host to which you are connecting, defaulting to 3306
# Database: The name of the database
# User: the user name of the connection
# Password: the password for the connection
# charset: the encoding method used for communication, utf8 is recommended
conn = pymysql.connect(host='localhost',
                       port=3309,
                       user='root',
                       password='mysql',
                       database='python41',
                       charset='utf8'
                       )
#3. Get the cursor to execute the sql statement
cursor = conn.cursor()

#Add Operation
sql = "insert into classes(name) value('Xiao Ming')"
#Modify Action
# sql = "update classes set name ='little red'where id = 2"
#Delete operation
# sql = "delete from classes where id=2"

try:
    #4. Execute sql statements
    cursor.execute(sql)
    #Data must be submitted for addition or deletion
    cursor.commit()
except:
    #If an error occurs, the modified data is undone, indicating that the data is back.
    conn.rollback()

#5. Close cursors
cursor.close()
#6. Close the connection
conn.close()

3. Instructions for using pymssql modules

The difference between the pymssql module and the pymyql module is that:

1. Import module: import pymssql
2. Create a linked object: conn = pymssql.connect (parameter list)

Other syntax is the same as the pymyql module.