Of Stack

The pymysql module that operates mysql in Python

  • 2020-05-10 18:28:17
  • OfStack

preface

pymsql is the module in Python that operates MySQL, and it is used in almost the same way as MySQLdb. Currently, pymysql supports python3.x, while the latter does not support the 3.x version.

This article tested python version 2.7.11. mysql version: 5.6.24

1. Install 


pip3 install pymysql

2. Operation

1. Execute SQL 


#!/usr/bin/env pytho
# -*- coding:utf-8 -*-
import pymysql
 
#  Create a connection 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1', charset='utf8')
#  Create a cursor 
cursor = conn.cursor()
 
#  perform SQL , and returns the number of indented rows 
effect_row = cursor.execute("select * from tb7")
 
#  perform SQL , and returns the number of affected rows 
#effect_row = cursor.execute("update tb7 set pass = '123' where nid = %s", (11,))
 
#  perform SQL , and returns the number of affected rows , Many times 
#effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])
 
 
#  Commit, otherwise you cannot save new or modified data 
conn.commit()
 
#  Close the cursor 
cursor.close()
#  Close the connection 
conn.close()

Note: when Chinese exists, you need to add charset='utf8' to the connection, otherwise the Chinese will show scrambled code.

2. Obtain query data 


#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor()
cursor.execute("select * from tb7")

#  Gets the order of the remaining results 1 Rows of data 
row_1 = cursor.fetchone()
print row_1
#  Before getting the rest of the results n Rows of data 
# row_2 = cursor.fetchmany(3)

#  Get all the data for the remaining results 
# row_3 = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()

3. Get the newly created data and add ID

You can get the latest self-incrementing ID, which is the last data inserted, ID 


#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor()
effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u3","u3pass","11113"),("u4","u4pass","22224")])
conn.commit()
cursor.close()
conn.close()
# Get on the id
new_id = cursor.lastrowid      
print new_id

4. Move the cursor

Operations are done with the cursor, and control of the cursor is required 


 Note: in fetch The data is in order and can be used cursor.scroll(num,mode) To move the cursor position, such as: 

cursor.scroll(1,mode='relative') #  Move relative to current position 
cursor.scroll(2,mode='absolute') #  Relative to the absolute position

 

5. fetch data type

The data acquired by default is of meta-ancestor type. If you want data of dictionary type, namely: 


#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
# The cursor is set to the dictionary type 
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute("select * from tb7")

row_1 = cursor.fetchone()
print row_1　　#{u'licnese': 213, u'user': '123', u'nid': 10, u'pass': '213'}

conn.commit()
cursor.close()
conn.close()

6. Call the stored procedure

a, calling a no-argument stored procedure 


#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
# The cursor is set to the dictionary type 
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# Parameterless stored procedure 
cursor.callproc('p2')  # Is equivalent to cursor.execute("call p2()")

row_1 = cursor.fetchone()
print row_1


conn.commit()
cursor.close()
conn.close()

b, call the parameter stored procedure 


#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.callproc('p1', args=(1, 22, 3, 4))
# Gets the parameters that are stored after execution , parameter @ At the beginning 
cursor.execute("select @p1,@_p1_1,@_p1_2,@_p1_3")  #{u'@_p1_1': 22, u'@p1': None, u'@_p1_2': 103, u'@_p1_3': 24}
row_1 = cursor.fetchone()
print row_1


conn.commit()
cursor.close()
conn.close()

3. About pymysql anti-injection

  1, string concatenation query, resulting in injection

Normal query statement: 


#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor()
user="u1"
passwd="u1pass"
# The condition of a normally constructed statement 
sql="select user,pass from tb7 where user='%s' and pass='%s'" % (user,passwd)
#sql=select user,pass from tb7 where user='u1' and pass='u1pass'
row_count=cursor.execute(sql) row_1 = cursor.fetchone()
print row_count,row_1

conn.commit()
cursor.close()
conn.close()

Construct the injection statement: 


#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor()

user="u1' or '1'-- "
passwd="u1pass"
sql="select user,pass from tb7 where user='%s' and pass='%s'" % (user,passwd)

# The concatenation statement is constructed into the following, the never true condition, at which point the injection is successful. So to avoid this situation you need to use pymysql Parameterized query provided. 
#select user,pass from tb7 where user='u1' or '1'-- ' and pass='u1pass'

row_count=cursor.execute(sql)
row_1 = cursor.fetchone()
print row_count,row_1


conn.commit()
cursor.close()
conn.close()

 

  2, avoid injection, use the parameterized statements provided by pymysql

Normal parameterized query 


#!/usr/bin/env pytho
# -*- coding:utf-8 -*-
import pymysql
 
#  Create a connection 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1', charset='utf8')
#  Create a cursor 
cursor = conn.cursor()
 
#  perform SQL , and returns the number of indented rows 
effect_row = cursor.execute("select * from tb7")
 
#  perform SQL , and returns the number of affected rows 
#effect_row = cursor.execute("update tb7 set pass = '123' where nid = %s", (11,))
 
#  perform SQL , and returns the number of affected rows , Many times 
#effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])
 
 
#  Commit, otherwise you cannot save new or modified data 
conn.commit()
 
#  Close the cursor 
cursor.close()
#  Close the connection 
conn.close()
0

Construct injection, parameterized query injection failed. 


#!/usr/bin/env pytho
# -*- coding:utf-8 -*-
import pymysql
 
#  Create a connection 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1', charset='utf8')
#  Create a cursor 
cursor = conn.cursor()
 
#  perform SQL , and returns the number of indented rows 
effect_row = cursor.execute("select * from tb7")
 
#  perform SQL , and returns the number of affected rows 
#effect_row = cursor.execute("update tb7 set pass = '123' where nid = %s", (11,))
 
#  perform SQL , and returns the number of affected rows , Many times 
#effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])
 
 
#  Commit, otherwise you cannot save new or modified data 
conn.commit()
 
#  Close the cursor 
cursor.close()
#  Close the connection 
conn.close()
1

Conclusion: when excute executes SQL statement, it must be parameterized. Otherwise, SQL injection vulnerability will be generated.

3. Use mysql storage process to dynamically execute SQL anti-injection

Anti-injection is automatically provided using MYSQL stored procedures and SQL is dynamically passed into stored procedure execution statements. 


#!/usr/bin/env pytho
# -*- coding:utf-8 -*-
import pymysql
 
#  Create a connection 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1', charset='utf8')
#  Create a cursor 
cursor = conn.cursor()
 
#  perform SQL , and returns the number of indented rows 
effect_row = cursor.execute("select * from tb7")
 
#  perform SQL , and returns the number of affected rows 
#effect_row = cursor.execute("update tb7 set pass = '123' where nid = %s", (11,))
 
#  perform SQL , and returns the number of affected rows , Many times 
#effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])
 
 
#  Commit, otherwise you cannot save new or modified data 
conn.commit()
 
#  Close the cursor 
cursor.close()
#  Close the connection 
conn.close()
2 

set @nid1=12;
set @nid2=15;
set @callsql = 'select * from tb7 where nid>? and nid<?';
CALL proc_sql(@nid1,@nid2,@callsql)

pymsql call 


#!/usr/bin/env pytho
# -*- coding:utf-8 -*-
import pymysql
 
#  Create a connection 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1', charset='utf8')
#  Create a cursor 
cursor = conn.cursor()
 
#  perform SQL , and returns the number of indented rows 
effect_row = cursor.execute("select * from tb7")
 
#  perform SQL , and returns the number of affected rows 
#effect_row = cursor.execute("update tb7 set pass = '123' where nid = %s", (11,))
 
#  perform SQL , and returns the number of affected rows , Many times 
#effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])
 
 
#  Commit, otherwise you cannot save new or modified data 
conn.commit()
 
#  Close the cursor 
cursor.close()
#  Close the connection 
conn.close()
4

4. Simplify the connection process with with

Closing the connection every time is cumbersome, and using context management simplifies the connection process 


#!/usr/bin/env pytho
# -*- coding:utf-8 -*-
import pymysql
 
#  Create a connection 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1', charset='utf8')
#  Create a cursor 
cursor = conn.cursor()
 
#  perform SQL , and returns the number of indented rows 
effect_row = cursor.execute("select * from tb7")
 
#  perform SQL , and returns the number of affected rows 
#effect_row = cursor.execute("update tb7 set pass = '123' where nid = %s", (11,))
 
#  perform SQL , and returns the number of affected rows , Many times 
#effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])
 
 
#  Commit, otherwise you cannot save new or modified data 
conn.commit()
 
#  Close the cursor 
cursor.close()
#  Close the connection 
conn.close()
5

conclusion

The above is the whole content of pymysql module in Python. I hope it will be helpful for you to learn or use python. If you have any questions, please leave a message to communicate.

Related articles: