Python basic article-python operation MySQL

There are two main ways to use MySQL for Python operations:

• Native Module Pymsql
• ORM Framework Sqlachemy

Pymysql

Download installation

PIP3 Install Pymysql   #PIP3 command path: Scripts directory under installation path #  download pymysql to local #  #  python2, default no PIP command #  Python3, default comes with pip3 command python3-m pip install--upgrade pip Update pip#https://pypi.python.org/pypi  module Source

Use action

1. Execute SQL statements

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 ImportPymysql4   5 #Create a connection6conn = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1' , charset= ' UTF8 ')7 #Create a cursor (create a connection just open the database and want to fetch the data through a cursor)8cursor =conn.cursor ()9   Ten #executes SQL and returns the number of rows affected (that is, there is a return value) OneEffect_row = Cursor.execute (" update hosts set host = ' 1.1.1.2 '") ACursor.execute (Insert into Class (caption) values (' Full stack Class two ') '  -  - # executes SQL and returns the number of rows affected, inserting multiple rows using Executemany  the #Effect_row = Cursor.executemany ("INSERT into hosts (host,color_id) VALUES (%s,%s)", [("1.1.1.11", 1), (" 1.1.1.11 ", 2)])   - Effect_row = Cursor.execute("DeleteFrom score where SID = 3 ") -  -INP = input ("Please enter an IP address") cursor.execute ("INSERT into hosts values (%s)", INP)
20
Cursor.execute ("Select* FROM student ") #查询的数据从数据库中取出保存在内存中
RESULT1 = Cursor.fetchall ()
Print (RESULT1) #输出查询的结果
RESULT2 = Cursor.fetchone ()
Print (RESULT2) #输出查询的第一条结果
RESULT3 = Cursor.fetchmany (3)
Print (RESULT3) #输出查询的前n条结果
28 A #Commit, or you cannot save the newly created or modified data at Conn.commit () -    - #Close Cursors - cursor.close () - #Close Connection -Conn.close ()

2. Get Query data

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 ImportPymysql4   5conn = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1')6cursor =conn.cursor ()7Cursor.execute ("SELECT * from hosts")8   9 #get the first row of dataTenRow_1 =Cursor.fetchone () One    A #get top N rows of data - #row_2 = Cursor.fetchmany (3) - #Get all data the #row_3 = Cursor.fetchall () -    - Conn.commit () - cursor.close () +Conn.close ()

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 ') # moves relative to the current position i.e. the pointer goes down one relative to the current position and a negative number means go up
  • Cursor.scroll (2,mode= ' absolute ') # moves the pointer back to the 2nd position relative to the absolute position

3.sql Injection

Insert operations on the database without a username and password

1conn = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1')2 #normal should be in the following way, no security issues3Cursor.execute ('Select Username,password from UserInfo where username=%s and password=%s',('Alex', 123))4result =Cursor.fetchone ()5 Print(Result)6 7 #string stitching can cause security issues8 #to perform normally:9sql ='Select Username,password from UserInfo where username= "%s" and password= "%s"'Tensql = SQL% ('Alex', 123) One cursor.execute (SQL) Aresult =Cursor.fetchone () - Print(Result) -  the #cannot execute -sql ='select Username,password from userinfo where username= '%s ' and password= '%s '
 -sql = SQL% ('Alex', 1236) - cursor.execute (SQL) +result =Cursor.fetchone () - Print(Result) +  A #Normal execution: Password error can also be taken to the data atsql ='select Username,password from userinfo where username= '%s ' and password= '%s '
 -sql = SQL% (' Alex" -- ', 1236# ' Select Username,password from userinfo where Username= 'Alex' --    "and password= Comment out after "%s"
   #sql = sql% ('Alex' or 1=1 - ', 1236) can execute even if the username is not present as an immediate command    
           - cursor.execute (SQL) -result =Cursor.fetchone () - Print(Result)

4.fetch Data types

About the data obtained by default is the Ganso type, if you want to get the data of the dictionary type, that is:

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 ImportPymysql4   5conn = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1')6   7 #cursor set to dictionary type8cursor = Conn.cursor (cursor=pymysql.cursors.DictCursor)9 
Ten    Oneresult =Cursor.fetchone () A    - Conn.commit () - cursor.close () theConn.close ()

5. Get the newly created data self-increment ID

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 ImportPymysql4   5conn = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1')6cursor =conn.cursor ()7Cursor.executemany ("INSERT INTO hosts (host,color_id) VALUES (%s,%s)", [("1.1.1.11", 1), ("1.1.1.11", 2)])8 Conn.commit ()9 cursor.close ()Ten conn.close () One    A #get the latest self-increment ID -new_id = Cursor.lastrowid

Python basic article-python operation MySQL