"Python Road" 19th--python operation MySQL

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

# Create a Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123456 ', db= ' DB1 ') # Create cursors cursor = Conn.cursor () # Executes SQL and returns the number of affected rows Effect_row = Cursor.execute ("update hosts set host = ' 1.1.1.2 ' where nid >%s", (1,))  # executes SQL and returns the number of affected rows 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 ()

increase, delete, change needs to be implementedconn.commit()

2. Get the newly created data self-increment ID

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 =  If you insert more than one, you can only get the last idnew_id = Cursor.lastrowid

3. Get Query data

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 line of data Row_1 = Cursor.fetchone () # = + Execute again: Cursor.fetchone ( To get the next data, no time for none# to get the first n rows of data # row_2 = Cursor.fetchmany (n) # ==> executed n times fetchone () # Get all data # Row_3 = Cursor.fetchall ()  C Onn.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.:

Import PYMYSQL  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123456 ', db= ' t1 ')  # Cursor set to dictionary type cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) row = Cursor.execute ("SELECT * from user")  result = Cursor.fetchone () print (result) Conn.commit () Cursor.close () Conn.close ()

　　

"Python Road" 19th--python operation MySQL