A novice's guide to using pymysql in python to operate MySQL database
- 2021-12-04 10:49:07
- OfStack
Directory 1. Introduction to pymysql
2. Complete process of connecting to database
1. Introducing the pymysql module
2. Create a connection object
3. Create a cursor object using a connection object
4. Prepare the sql statements you need to use
5. Execute an sql statement using a cursor object (if it is a data modification operation, the number of rows affected will be returned)
6. If the execution statement is a query operation, you need to use a cursor object to get the query results
7. Close the cursor object
8. Close the connection object
3. Complete and simple source code Summarize
1. Introduction to pymysql
2. Complete process of connecting to database
1. Introducing pymysql module
2. Create a connection object
3. Create a cursor object using a connection object
4. Prepare the sql statements you need to use
5. Execute an sql statement using a cursor object (if it is a data modification operation, the number of rows affected will be returned)
6. If the execution statement is a query operation, you need to use a cursor object to get the query results
7. Close the cursor object
8. Close the connection object
3. Complete and simple source code
2. Create a connection object
3. Create a cursor object using a connection object
4. Prepare the sql statements you need to use
5. Execute an sql statement using a cursor object (if it is a data modification operation, the number of rows affected will be returned)
6. If the execution statement is a query operation, you need to use a cursor object to get the query results
7. Close the cursor object
8. Close the connection object
3. Complete and simple source code Summarize
1. Introduction to pymysql
pymysql is a library for connecting to and operating with MySQL servers in Python3. x.
Introduction method:
pip install pymysql
The pip tool is a module of the python, so before installing the pymysql module with the pip tool, make sure that both the python and pip tools work properly
2. Complete process of connecting to database
1. Introducing pymysql module
import pymysql # Introduce the 3 Square library pymysql
2. Create a connection object
import pymysql
cnn = pymysql.connect(
user="user", # User name
password="password", # Password
port= Port number , # Default to 3306, And this is an integer type
database=" Database name ",
host=" Connect IP Address ",
charset="utf8")
3. Create a cursor object using a connection object
1. Cursor objects are created by linking objects
2. The cursor displays the data 4 A kind of way :
1. Cursor ---> Use by default , Tuple set tuple
2. SSCursor ---> Generator of the way tuples are nested ( Elegant iterator )
3. Dictcursor ---> The way to list dictionaries
4. SSDictcursor ---> Generator of the way of list set dictionary
3. Create syntax ( Example in the form of the most commonly used list set dictionary )
cursor = database.cursor(cursor=pymysql.cursors.DictCursor
# cursor Is the cursor object name , You can customize your name according to your habits
4. Prepare the sql statements you need to use
Prepare sql statements according to their own needs. Unfamiliar students can click on the link below to review the basic introduction course of MySQL written by me last time.
Basic knowledge of MySQL
5. Execute an sql statement using a cursor object (if it is a data modification operation, the number of rows affected will be returned)
# Executing statements is relatively simple , That's all I wrote 1 A
cursor.execute(SQL Statement )
6. If the execution statement is a query operation, you need to use a cursor object to get the query results
1. Syntax for getting results :
1. Cursor object .fetchall() ---> Returns all the queried data
2. Cursor object .fetchone() ---> Returns the queried 1 A record
3. Cursor object .fetchmany( Number of records ) ---> Returns the specified number of records queried
2. Toggle database syntax :
1. Switch database by link object fetch operation
2. Linked object .select_db(" Database name )
3. Specific statement ( Use 1 Variable data Accept stored data )
data = cursor.fetchall()
7. Close the cursor object
Cursor object .close()
8. Close the connection object
Connection object .close()
3. Complete and simple source code
# 1. Introducing module
import pymysql
# 2. Connect to a database
cnn = pymysql.connect(
user="root",
password="******",
port=3306,
database="mytest",
host="localhost",
charset="utf8"
)
# 3. Create a cursor object
my_cursor = cnn.cursor(cursor=pymysql.cursors.DictCursor)
# 4. SQL Statement
my_sql = "select * from student"
# 5. Execute SQL ---> Executed through cursor objects
my_cursor.execute(my_sql)
# 6. View the results
data = my_cursor.fetchall()
# 7. Close the connection
my_cursor.close()
database.close()
# Print the obtained data , Check whether the execution is correct
print(data)