For details about how to operate mysql in Python, pythonpymysql

For details about how to operate mysql in Python, pythonpymysql

Preface

Pymsql is a module used to operate MySQL in Python. Its usage is almost the same as that of MySQLdb. However, pymysql currently supports python3.x, while the latter does not support version 3.x.

This article tests python version 2.7.11. Mysql version: 5.6.24

I. Installation

pip3 install pymysql

Ii. Operation

1. Execute SQL

#! /Usr/bin/env pytho #-*-coding: UTF-8-*-import pymysql # create connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1', charset = 'utf8') # create a cursor = conn. cursor () # execute the SQL statement and return the number of rows affected by the receipt. Export t_row = cursor.exe cute ("select * from tb7") # execute the SQL statement, and returns the number of affected rows # effect_row = cursor.exe cute ("update tb7 set pass = '000000' where nid = % s", (11,) # executes the SQL statement and returns the number of affected rows, run multiple times # effect_row = cursor.exe cute.pdf ("insert into tb7 (user, pass, licnese) values (% s, % s, % s)", [("u1 ", "u1pass", "11111"), ("u2", "u2pass", "22222")]) # submit. Otherwise, the new or modified data conn cannot be saved. commit () # Close the cursor. close () # close the connection conn. close ()

NOTE: If Chinese characters exist, add charset = 'utf8' to the connection; otherwise, Chinese characters are garbled.

2. Obtain query data

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1') cursor = conn.cursor()cursor.exe cute (" select * from tb7 ") # obtain the first row of Data row_1 = cursor for the remaining results. fetchone () print row_1 # obtain n rows of data before the remaining results # row_2 = cursor. fetchmany (3) # obtain all data from the remaining results # row_3 = cursor. fetchall () conn. commit () cursor. close () conn. close ()

3. Get the auto-increment ID of the newly created data

You can get the latest auto-increment ID, that is, the last inserted data ID.

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1') cursor = conn. cursor () effect_row = cursor.exe cute.pdf ("insert into tb7 (user, pass, licnese) values (% s, % s, % s)", [("u3 ", "u3pass", "11113"), ("u4", "u4pass", "22224")]) conn. commit () cursor. close () conn. close () # Get auto-increment idnew_id = cursor. lastrowid print new_id

4. move the cursor

The operation depends on the cursor, so the control of the cursor is also necessary.

Note: When fetch data is performed in order, you can use cursor. scroll (num, mode) to move the cursor position, for example, cursor. scroll (1, mode = 'relative ') # move cursor relative to the current position. scroll (2, mode = 'absolute ') # Move relative to absolute position

 

5. fetch Data Type

The data obtained by default is of the Ancestor Type. If you want data of the dictionary type, that is:

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1 ') # Set the cursor to the dictionary type cursor = conn.cursor(cursorpypymysql.cursors.dictcursor)cursor.exe cute ("select * from tb7") row_1 = cursor. fetchone () print row_1 # {u 'licnese': 213, u 'user': '000000', u 'nid': 10, u 'pass ': '000000'} conn. commit () cursor. close () conn. close ()

6. Call the Stored Procedure

A. Call a stored procedure without Parameters

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1') # Set the cursor to the dictionary type cursor = conn. cursor (cursor = pymysql. cursors. dictCursor) # No parameter Stored Procedure cursor. callproc ('p2 ') equals to cursor.exe cute ("call p2 ()") row_1 = cursor. fetchone () print row_1conn.commit () cursor. close () conn. close ()

B. Call the stored procedure with Parameters

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 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 rows) obtain the region where the storage is complete, then @ start cursor.exe cute ("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_1conn.commit () cursor. close () conn. close ()

Iii. Anti-injection for pymysql

1. String concatenation query, resulting in Injection

Normal query statement:

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1') cursor = conn. cursor () user = "u1" passwd = "u1pass" # SQL = "select user, pass from tb7 where user = '% s' and pass =' % S' "% (user, passwd) # SQL = select user, pass from tb7 where user = 'u1' and password'u1pass'row_count+cursor.exe cute (SQL) row_1 = cursor. fetchone () print row_count, row_1conn.commit () cursor. close () conn. close ()

Construct the injection statement:

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 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 as follows. permanent conditions, the injection is successful. Therefore, to avoid this situation, use the parameter query provided by pymysql. # Select user, pass from tb7 where user = 'u1' or '1' -- 'and pass+'u1pass'row_count#cursor.exe cute (SQL) row_1 = cursor. fetchone () print row_count, row_1conn.commit () cursor. close () conn. close ()

 

2. Avoid injection and use the parameterized statements provided by pymysql.

Normal parameterized Query

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1') cursor = conn. cursor () user = "u1" passwd = "u1pass" condition executes the normalized query row_count#cursor.exe cute ("select user, pass from tb7 where user = % s and pass = % s", (user, passwd) row_1 = cursor. fetchone () print row_count, row_1conn.commit () cursor. close () conn. close ()

Construction injection, parameterized query injection failed.

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1') cursor = conn. cursor () user = "u1 'or '1' --" passwd = "u1pass" condition executes the normalized query row_count1_cursor.exe cute ("select user, pass from tb7 where user = % s and pass = % s ", (user, passwd) # execute the parameterized SQL statement internally and add \ escape to special characters, avoid generating injection statements. # SQL = cursor. mogrify ("select user, pass from tb7 where user = % s and pass = % s", (user, passwd) # print SQL # select user, pass from tb7 where user = 'u1 \ 'or \ '1 \' -- 'and pass = 'u1pass' is escaped. Row_1 = cursor. fetchone () print row_count, row_1conn.commit () cursor. close () conn. close ()

Conclusion: parameters must be used when excute executes SQL statements. Otherwise, the SQL injection vulnerability is inevitable.

3. dynamically Execute SQL Injection prevention during mysql Storage

MYSQL stored procedures automatically provide anti-injection and dynamically pass SQL statements into stored procedure execution statements.

Delimiter \ drop procedure if exists proc_ SQL \ create procedure proc_ SQL (in nid1 INT, in nid2 INT, in callsql VARCHAR (255) BEGIN set @ nid1 = nid1; set @ nid2 = nid2; set @ callsql = callsql; PREPARE myprod FROM @ callsql; -- PREPARE prod FROM 'select * from tb2 where nid>? And nid <? '; The input value is a string ,? Use @ p1 and @ p2 to fill the placeholder EXECUTE myprod USING @ nid1, @ nid2; DEALLOCATE prepare myprod; END \ delimiter;

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

Call in pymsql

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')cursor = conn.cursor()mysql="select * from tb7 where nid>? and nid<?"cursor.callproc('proc_sql', args=(11, 15, mysql))rows = cursor.fetchall()print rows #((12, 'u1', 'u1pass', 11111), (13, 'u2', 'u2pass', 22222), (14, 'u3', 'u3pass', 11113))conn.commit()cursor.close()conn.close()

4. Use with to simplify the connection process

It is troublesome to close each connection. Use context management to simplify the connection process.

#! /Usr/bin/env python #-*-coding: UTF-8-*-# _ author _ = "TKQ" import pymysqlimport contextlib # define the context manager, the connection is automatically closed after the connection @ contextlib. contextmanagerdef mysql (host = '20180101. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'tkq1', charset = 'utf8'): conn = pymysql. connect (host = host, port = port, user = user, passwd = passwd, db = db, charset = charset) cursor = conn. cursor (cursor = pymysql. cursors. dictCursor) try: yield cursor finally: conn. commit () cursor. close () conn. close () # Run sqlwith mysql () as cursor: print (cursor) row_count = cursor.exe cute ("select * from tb7") row_1 = cursor. fetchone () print row_count, row_1

Summary

The above is all about the pymysql module in Python. I hope to help you learn or use python. If you have any questions, please leave a message.