Python Foundation---Pymsql

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

First, download the installation

PIP3 Install Pymysql

Second, use

1. Execute SQL

#!/usr/bin/env python
#-*-Coding:utf-8-*-
Import Pymysql

# Create a connection
conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123 ', db= ' T1 ')
# Create Cursors
cursor = Conn.cursor ()

# executes SQL and returns the number of rows affected
Effect_row = Cursor.execute ("update hosts set host = ' 1.1.1.2 '")

# executes SQL and returns the number of rows affected
#effect_row = Cursor.execute ("update hosts set host = ' 1.1.1.2 ' where 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, or you cannot save new or modified data
Conn.commit ()

# Close Cursors
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 the latest self-increment ID
new_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")

# Get the first row of data
Row_1 = Cursor.fetchone ()

# get top N rows of data
# row_2 = Cursor.fetchmany (3)
# Get all the 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 Foundation---Pymsql