Python's Path to Growth "13th": Python operation MySQL Pymysql

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

• Native Module Pymsql
• ORM Framework Sqlachemy

Pymsql

Pymsql is a module that operates MySQL in Python and is used almost the same way as MySQLdb.

Download installation

PIP3 Install Pymysql

Use action

1. Execute SQL

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysql  # Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, User= ' root ', passwd= ' 123 ', db= ' t1 ') # Create cursor cursor = Conn.cursor ()  # Execute SQL and return the number of affected rows Effect_row = Cursor.execute (" Update hosts Set host = ' 1.1.1.2 ')  # executes SQL and returns the number of affected rows #effect_row = Cursor.execute ("update hosts set host = ' 1.1.1.2 ' wh Ere nid >%s ", (1,))  # Executes SQL and returns the number of rows affected #effect_row = Cursor.executemany (" INSERT into hosts (HOST,COLOR_ID) values (%s ,%s) ", [(" 1.1.1.11 ", 1), (" 1.1.1.11 ", 2)])    # Commit, otherwise unable to save new or modified Data conn.commit ()  # Close Cursor cursor.close () # Close Connection Conn.close ()

2. Get the newly created data self-increment ID

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysql  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' Root ', passwd= ' 123 ', db= ' t1 ') cursor = Conn.cursor () cursor.executemany ("INSERT into hosts (host,color_id) VALUES (%s,%s ) ", [(" 1.1.1.11 ", 1), (" 1.1.1.11 ", 2)]) Conn.commit () Cursor.close () Conn.close ()  # Get latest self-increment idnew_id = Cursor.lastrowid

3. Get Query data

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysql  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' Root ', passwd= ' 123 ', db= ' t1 ') cursor = Conn.cursor () cursor.execute ("SELECT * from hosts")  # Gets the first row of data Row_1 = Cursor.fetchone ()  # Gets the first n rows of data # row_2 = Cursor.fetchmany (3) # Get all data # 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
• Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement

4. 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-*-import pymysql  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' Root ', passwd= ' 123 ', db= ' t1 ')  # Cursor set to dictionary type cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) R = Cursor.execute ("Call P1 ()")  result = Cursor.fetchone ()  conn.commit () cursor.close () Conn.close ()

Python's Path to Growth "13th": Python operation MySQL Pymysql