The Pymysql module in Python that operates MySQL

Objective

Pymsql is a module that operates MySQL in Python and is used almost the same way as MySQLdb. However, currently Pymysql supports python3.x and the 3.x version is not supported by the latter.

This article tests the Python version: 2.7.11. MySQL version: 5.6.24

First, installation

PIP3 Install Pymysql

Second, use the operation

1. Execute SQL

#!/usr/bin/env pytho#-*-coding:utf-8-*-import pymysql  # Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, User= ' root ', passwd= ', db= ' tkq1 ', charset= ' UTF8 ') # create cursor cursor = Conn.cursor ()  # Execute SQL and return the number of affected rows Effect_row = Cursor.execute ("SELECT * from Tb7")  # executes SQL and returns the number of affected rows #effect_row = Cursor.execute ("update tb7 set pass = ' 123 ' Where NI D =%s ", (one,))  # Executes SQL and returns the number of rows affected, executing multiple #effect_row = Cursor.executemany (" INSERT into TB7 (User,pass,licnese) VALUES (%s ,%s,%s) ", [(" U1 "," U1pass "," 11111 "), (" U2 "," U2pass "," 22222 ")]    # commits, or cannot save new or modified Data conn.commit ()  # Close Cursor cursor.close () # Close connection Conn.close ()

Note: In the presence of Chinese, the connection needs to add charset= ' UTF8 ', otherwise the Chinese display garbled.

2. Get 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 first row of data for the remaining results row_1 = CU Rsor.fetchone () print row_1# get the remaining results before n rows of data # row_2 = Cursor.fetchmany (3) # Get the remaining results all data # Row_3 = Cursor.fetchall () conn.commit ( ) Cursor.close () Conn.close ()

3. Get the newly created data self-increment ID

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

#! /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 () #获取自增idnew_id = cursor.lastrowid     print new_id

4. Moving the cursor

Operations are done by cursors, which are also necessary for the control of cursors.

Note: In order to fetch data, you can use Cursor.scroll (Num,mode) to move the cursor position, such as: Cursor.scroll (1,mode= ' relative ') # move Cursor.scroll relative to the current position ( 2,mode= ' absolute ') # relative absolute position movement

5. Fetch data type

The data that is obtained by default is the Ganso type, if desired or the dictionary type of data, i.e.:

#! /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.execute ("SELECT * from Tb7") Row_1 = Cursor.fetchone () print row_1 #{u ' Licnese ': 213, U ' user ': ' 123 ', U ' nid ': Ten, U ' Pass ': ' 213 '} conn.commit () Cursor.close () Conn.close ()

6. Call the stored procedure

A. Call the non-parametric 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) # No parameter stored procedure cursor.callproc (' P2 ')  #等价于cursor. Execute ("Call P2 ()") Row_1 = Cursor.fetchone () print Row_1  Conn.commit () Cursor.close () Conn.close ()

B. Call a stored procedure with parameters

#! /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, 3, 4)) #获取执行完存储的参数, parameter @ begins with Cursor.execute ("Select @p1, @_p1_1,@_p1_2,@_p1_3")  #{u ' @_p1_1 ': $, U ' @ P1 ': None, U ' @_p1_2 ': 103, U ' @_p1_3 ': 24}row_1 = Cursor.fetchone () print row_1  conn.commit () cursor.close () Conn.close ()

Third, about Pymysql anti-injection

1, string splicing 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" #正常构造语句的情况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= ' U1pas S ' 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 F Rom tb7 where user= '%s ' and pass= '%s ' "% (USER,PASSWD) #拼接语句被构造成下面这样, the eternal condition, at which time it was injected successfully. Therefore, to avoid this situation, you need to use the parameterized query provided by Pymysql. #select User,pass from Tb7 where user= ' U1 ' or ' 1 '--' and pass= ' U1pass ' Row_count=cursor.execute (sql) Row_1 = Cursor.fetcho NE () print row_count,row_1  conn.commit () cursor.close () Conn.close ()

2. Avoid injection, using parameterized statements provided by Pymysql

Normal parameterized queries

#! /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" #执行参数化查询row_count = Cursor.execute ("Select User,pass from Tb7 where user=%s and pass=%s", (user,passwd)) Row_1 = Cursor.fetchone () print Row_ Count,row_1 Conn.commit () cursor.close () Conn.close ()

Construction injection, parameterized query injection failed.

#! /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" #执行参数化查询row_count = Cursor.execute ("Select User,pass from Tb7 where user=%s and pass=%s", (USER,PASSWD)) #内部执行参数化生成的SQL语句, adds \ Escapes for special characters, Avoid injection statement generation.  # 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 ' are escaped statements. Row_1 = Cursor.fetchone () print row_count,row_1 conn.commit () cursor.close () Conn.close ()

Conclusion: When executing SQL statements, Excute must use parameterized methods, otherwise SQL injection vulnerability will inevitably occur.

3. Dynamically execute SQL anti-injection using stored MySQL stored process

Use MySQL stored procedures to automatically provide anti-injection, dynamic incoming SQL to the stored procedure execution statement.

Delimiter \\DROP PROCEDURE IF EXISTS proc_sql \\CREATE PROCEDURE proc_sql (  in Nid1 int, in Nid2  int., in  calls QL 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 value passed in is a string,? As placeholders--   with @p1, and @p2 fill 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)

Called in Pymsql

#! /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 () mysql= "SELECT * from Tb7 where nid>? and nid<? " Cursor.callproc (' Proc_sql ', args= (one, all, mysql)) rows = Cursor.fetchall () Print Rows # ((A, ' U1 ', ' U1pass ', 11111), (13, ' U2 ', ' U2pass ', 22222), (+, ' U3 ', ' U3pass ', 11113)) Conn.commit () Cursor.close () Conn.close ()

Iv. using with simplifies the connection process

Connection closure is cumbersome every time, using context management to simplify the connection process

#! /usr/bin/env python#-*-coding:utf-8-*-# __author__ = "TKQ" Import pymysqlimport contextlib# define context Manager, Connect automatically after connection @contextlib.contextmanagerdef MySQL (host= ' 127.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 () # executes Sqlwith MySQL () as cursor:  print (cursor)  Row_count = Cursor.execute ( "SELECT * from Tb7")  row_1 = Cursor.fetchone ()  print Row_count, row_1

Summarize

The above is about the Pymysql module in Python All the content, I hope to learn from you or use Python can have a certain help, if there are questions you can message exchange.