Pymysql
Need to install the Pymysql module to use MySQL in Python
Installation
PIP3 Install Pymysql
If the installation fails, you need to upgrade the PIP version
python-m pip Install--upgrade pip
Use
1. Execute MySQL
1 Import Pymysql2 3 # Create a connection4Conn=Pymysql.connect (Host='127.0.0.1', Port=3306,User='Root', passwd='123'Db='T1')5 # Create Cursors6 cursor =Conn.cursor()7 8 # Executes SQL and returns the number of affected rows Effect_row printable9Effect_row= cursor.Execute("UpdateHostsSetHost= '1.1.1.2'")Ten One # Executes SQL and returns the number of rows affected A# Effect_row= cursor.Execute("UpdateHostsSetHost= '1.1.1.2' whereNid> %S ", (1,)) - - # Executes SQL and returns the number of rows affected the# Effect_row= cursor. Executemany ("Insert intoHosts (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 A cursor.Close() at # Close Connection -Conn.Close()
2. Get the newly created data self-increment ID
Add multiple Data using Executemany () data should be a tuple in a list/tuple, such as [(), (), ()]
Self-increment ID:cursor.lastrowid
1 Import Pymysql2 3Conn=Pymysql.connect (Host='127.0.0.1', Port=3306,User='Root', passwd='123'Db='T1')4 cursor =Conn.cursor()5 cursor. Executemany ("Insert intoHosts (HOST,COLOR_ID)Values(%S%s) ",[("1.1.1.11", 1), ("1.1.1.11", 2)])6Conn.Commit()7 cursor.Close()8Conn.Close()9 Ten # Get the latest self-increment ID Onenew_id= cursor. lastrowid
3. Get Query data
1 Import Pymysql2 3Conn=Pymysql.connect (Host='127.0.0.1', Port=3306,User='Root', passwd='123'Db='T1')4 cursor =Conn.cursor()5 cursor.Execute("Select * fromhosts ")6 7 # Get the first row of data8Row_1= cursor. Fetchone ()9 Ten # Get top N rows of data One# row_2= cursor. Fetchmany (3) A # Get all the data -# Row_3= cursor. Fetchall () - theConn.Commit() - cursor.Close() -Conn.Close()
Note: In order to fetch data, you can use Cursor.scroll (num, mode) to move the cursor position
- Cursor.scroll (1,mode= ' relative ') # moves relative to the current position
- Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement
4. Fench Data type
The data obtained by default is a tuple type, and the cursor is set to the dictionary type if you want the dictionary type
1 Import Pymysql2 3Conn=Pymysql.connect (Host='127.0.0.1', Port=3306,User='Root', passwd='123'Db='T1')4 5 # Cursor set to dictionary type6 cursor =Conn.cursor(cursor=pymysql.cursors.DictCursor)7R= cursor.Execute("Call P1 ()")8 9Result= cursor. Fetchone ()Ten OneConn.Commit() A cursor.Close() -Conn.Close()
Python Operation MySQL