Python operates MySQL database connection (pymysql)

Table of contents

1: Python operates MySQL
- - 1.How does python operate MySQL?
  - 2. What is PyMySQL?
2: PyMySQL installation
- - 1.Method 1:
  - 2.Method 2:
Three: Basic use of pyMySQL module
- - 1.Basic use of pyMySQL module
Three: pymysql main methods
- - 1.pymysql main methods
  - 2. Content analysis:
Four: fetchall has the concept of cursor on data
- - 1. Verify the existence of the cursor
  - 2. Reasons why fetchone returns abnormal results
  - 3. Solve the problem of cursor movement
  - 4. Relative movement (move relative to current position)
Five: SQL injection problem
- - 1. Verify that the injection problem exists
  - 2. Vulnerability 1
  - 3. Vulnerability 2
  - 4. Based on the above vulnerabilities
  - 5. Causes of sql statement vulnerabilities (sql injection problem)
Six: execute method (solve sql injection problem)
- - 1.execute method
  - 2. Solve the injection problem
Seven: conn.commit() secondary confirmation
- - 1. Second confirmation function
  - 2. Verify that secondary confirmation is not used
  - 3. Querying data in the table has no impact
  - 2. The insert statement can be executed but does not affect the data.
  - 3. The update statement can be executed, but it does not affect the data in the table
4. The delete statement can be executed, but it does not affect the data.
- - 5. Summary:
  - 6. Reasons why additions, deletions and modifications cannot be realized
  - 7.conn.commit() secondary confirmation
  - 8. Verify secondary confirmation (manual testing)
  - 9. Solve manual operation secondary confirmation (automated secondary confirmation)
Eight: executemany() inserts multiple data at the same time
- - 1. Insert multiple data at the same time

One: python operation MySQL

1. How does python operate MySQL?

In order for python to connect to the database, you need a driver, which is a library for interacting with the database.

PyMySQL: This is a library that enables Python to connect to MySQL, it is a pure Python library (folder).

PyMySQL is a MySQL client operation library implemented in pure Python, supporting transactions, access processes, batch execution, and implementation of additions, deletions, modifications, etc.

2. What is PyMySQL?

PyMySQL is a library used to connect to the MySQL server in Python3.x version, and mysqldb is used in Python2.

PyMySQL follows the Python Database API v2.0 specification and includes the pure-Python MySQL client library.

Second: PyMySQL installation

1.Method 1:

1. Command line input (download module pymysql)
pip3 install pymysql

2.Method 2:

Switch download source (warehouse)
pip3 install pymysql -i source address

How to download third-party modules

Detailed tutorial on downloading modules and switching sources: https://www.cnblogs.com/goOJBK/p/15621209.html

Three: Basic usage of pyMySQL module

1.Basic use of pyMySQL module

import pymysql

# Create link
conn = pymysql.connect( # Assign value to conn connection object
    host='127.0.0.1', # local loopback address
    port=3306, #Default port
    user='root', # username
    password='123', # Password
    database='db_01', #Connect database name
    charset='utf8' # Encoding cannot write utf-8
)

# Generate a cursor object (equivalent to cmd opening mysql> in mysql)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # Let the data be automatically organized into a dictionary
#Define SQL statement
sql = 'select * from egg'
#Execute SQL statement
cursor.execute(sql)
# Get the returned results
res = cursor.fetchall()
print(res)

Three: pymysql main methods

1.pymysql main method

import pymysql

# Create link
conn = pymysql.connect( # Assign value to conn connection object
    host='127.0.0.1', # local loopback address
    port=3306, #Default port
    user='root', # username
    password='123', # Password
    database='db_01', #Connect database name
    charset='utf8' # Encoding cannot write utf-8
)

# Generate a cursor object (equivalent to cmd opening mysql> in mysql)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # Let the data be automatically organized into a dictionary
#Define SQL statement
sql = 'select * from egg'
#Execute SQL statement
ret = cursor.execute(sql)

print(ret) #The return value is the number of rows affected after executing the SQL statement

# Get the returned results
# res = cursor.fetchall() # List within dictionary
# res = cursor.fetchone() # Data dictionary
# res = cursor.fetchmany(3) # List within dictionary
print(res)

2. Content Analysis:

1.execute return value is the number of rows affected after executing the SQL statement

2.fetchall() gets all the results
fetchone() gets the first result of the result set
fetchmany() can specify how many result sets to obtain within the brackets

Four: fetchall has the concept of cursor on data

1. Verify the existence of the cursor

import pymysql

# Create link
conn = pymysql.connect( # Assign value to conn connection object
    host='127.0.0.1', # local loopback address
    port=3306, #Default port
    user='root', # username
    password='123', # Password
    database='db_01', #Connect database name
    charset='utf8' # Encoding cannot write utf-8
)

# Generate a cursor object (equivalent to cmd opening mysql> in mysql)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # Let the data be automatically organized into a dictionary
#Define SQL statement
sql = 'select * from egg'
#Execute SQL statement
ret = cursor.execute(sql)
print(ret) #The return value is the number of rows affected after executing the SQL statement
# Get the returned results
res = cursor.fetchall()
res1 = cursor.fetchall()
print(res)
print(res1)

2.fetchone abnormal return result reason

Analysis:
The above method also has the concept of cursor for data acquisition.

fetchall() gets all results

3. Solving the cursor movement problem

4. Relative movement (move relative to the current position)

import pymysql

# Create link
conn = pymysql.connect( # Assign value to conn connection object
    host='127.0.0.1', # local loopback address
    port=3306, #Default port
    user='root', # username
    password='123', # Password
    database='db_01', #Connect database name
    charset='utf8' # Encoding cannot write utf-8
)

# Generate a cursor object (equivalent to cmd opening mysql> in mysql)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # Let the data be automatically organized into a dictionary
#Define SQL statement
sql = 'select * from egg'
#Execute SQL statement
ret = cursor.execute(sql)
print(ret) #The return value is the number of rows affected after executing the SQL statement
# Get the returned results
res = cursor.fetchone() # Get the first result of the result set
# cursor.scroll(1, 'relative') # Move relative to the current position (relative movement)
# cursor.scroll(0, 'absolute') # Move relative to the beginning of the data (absolute movement)
res1 = cursor.fetchall() # Get all results
print(res)
print(res1)

Relative movement $image\$ Absolute move

Five: SQL injection problems

1. Verify that the injection problem exists

import pymysql

# Create link
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_01',
    charset='utf8'
)

# Generate a cursor object
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # Let the data be automatically organized into a dictionary

# Get username and password
username = input('username>>>:').strip()
password = input('password>>>:').strip()

#Construct SQL statement
sql = "select * from egg where name='%s' and password='%s'"%(username,password)
print(sql)
#Execute sql statement
cursor.execute(sql)
# Get all returned results
res = cursor.fetchall()
# if judgment
if res:
    print(res)
    print('Login successful')
else:
    print('Username or password is incorrect')

Is the current pymysq code user login safe?
Let's test the pymysql code (security)

2. Vulnerability 1

3. Vulnerability 2

4. Comprehensive Vulnerabilities

1. The username is incorrect. You can log in without a password.
2. You can log in without a username or password

5. Causes of SQL Statement Vulnerabilities (SQL Injection Problem)

The occurrence of SQL injection problems
It is to achieve certain specific effects through a combination of special symbols to avoid conventional logic.
How to solve SQL injection problem
    The execute method automatically helps you solve the problem

Six: execute methods (to solve sql injection problems)

1.execute method

The

execute method automatically places the username and password in the corresponding %s, and automatically verifies the username and password with special symbols before placing them to ensure security.

2. Solving the injection problem

import pymysql

# Create link
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_01',
    charset='utf8'
)

# Generate a cursor object
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # Let the data be automatically organized into a dictionary

Login function
1. Get username and password
2. Direct and accurate search based on username and password
username = input('username>>>:').strip()
password = input('password>>>:').strip()

#Construct SQL statement
sql = "select * from egg where name=%s and password=%s" # For core data, do not splice it yourself. Let the execute method help you filter and splice it.
print(sql)
#Execute sql statement
cursor.execute(sql,(username, password))
# Get all returned results
res = cursor.fetchall()
# if judgment
if res:
    print(res)
    print('Login successful')
else:
    print('Username or password is incorrect')

Seven:conn.commit() second confirmation

1. Second confirmation function

Second confirmation function:
For addition, deletion and modification operations, a second confirmation is required before they can take effect

2. Verification without using secondary confirmation

import pymysql

# Create link
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_01',
    charset='utf8'
)

# Generate a cursor object
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # Let the data be automatically organized into a dictionary
sql1 = 'select * from egg' # Query the data in the table
sql2 = 'insert into egg(name,password) values(%s,%s)' # Insert data
sql3 = 'update egg set name=jasonNB where id=1' # Modify data
sql4 = 'delete from egg where id=2' # Delete data

3. Query the data in the table no impact

cursor.execute(sql1)
print(cursor.fetchall())

2. The insert statement can be executed but it does not affect the data

cursor.execute(sql2,('objk',123))

3. The update statement can be executed but it does not affect the data in the table

res = cursor.execute(sql3)
print(res)

4. The delete statement can be executed but it does not affect the data

res = cursor.execute(sql4)
print(res)

5. Summary:

1. The query statement can be executed normally and the results obtained
cursor.execute(sql1)
2. The insert statement can be executed but does not affect the table data.
cursor.execute(sql2,('jackson',666))
3. The update statement can be executed but does not affect the table data.
res = cursor.execute(sql3)
print(res)
4. The delete statement can be executed but does not affect the table data.
res = cursor.execute(sql4)
print(res)

6. Reasons why additions, deletions and modifications cannot be realized

Because the importance of the four operations is different, and the levels of addition, deletion, modification and query are different. The query level is the lowest, and all can be operated. However, for the addition, deletion and modification, which is the highest level, pymysq does not have permission to operate, so you need to use Addition, deletion, and modification operations cannot be performed until the second confirmation.

'''For addition, deletion and modification operations, a second confirmation is required before they can take effect '''

7.conn.commit() Second Confirmation

Second confirmation keyword:
commit()
effect:
For addition, deletion and modification operations, a second confirmation is required before they can take effect

8. Verification and Second Confirmation (Manual Test)

'''For addition, deletion and modification operations, a second confirmation is required before they can take effect'''
# 1. Insert data
cursor.execute(sql2,('tony',123))
#Second confirmation
conn.commit()

# 2. Modify data
cursor.execute(sql3)
#Second confirmation
conn.commit()

# 3. Delete data
cursor.execute(sql4)
#Second confirmation
conn.commit()

9. Solve manual operation secondary confirmation (automated secondary confirmation)

Automatic secondary confirmation:
autocommmit=True # Involves automatic secondary confirmation of additions, deletions, modifications and checks

import pymysql

# Create link
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_01',
    charset='utf8',
    autocommit=True # Automatic second confirmation involving additions, deletions and modifications
)

# Generate a cursor object
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # Let the data be automatically organized into a dictionary
sql1 = 'select * from egg'
sql2 = 'insert into egg(name,password) values(%s,%s)'
sql3 = 'update egg set name="jaonNB" where id=2'
sql4 = 'delete from egg where id=1'

'''For addition, deletion and modification operations, a second confirmation is required before they can take effect'''
# 1. Insert data
cursor.execute(sql2,('laike',123))

# 2. Modify data
cursor.execute(sql3)

# 3. Delete data
cursor.execute(sql4)


Actively close the link and release resources
conn.close()

Eight: executemany() inserts multiple data at the same time

1. Insert multiple data at the same time

# Insert multiple data at the same time
cursor.executemany(sql2,[('jason1',1),('jason2',2),('jason3',3)])