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

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)

Summarize


Related articles: