Python operation MySQL

Pymsql

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

Download installation

PIP3 Install Pymysql

Using action 1, Execute SQL statement

Import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ', db= ' DB2 ')  # Parameters: Database IP, database user name , database password, database name # Create a cursor with a type of dictionary cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) # Execute SQL and return the affected number of rows # Effect_row = Cursor.execute ("INSERT into User (Username,password) VALUES (' Alex ', ' 8888 ')")   # Write the SQL statement in quotation marks, and Python executes # print (effect _row) # Commit, otherwise unable to save new or modified Data conn.commit () # Close Cursor cursor.close () # Close connection Conn.close ()

　　

2. Self-increment ID

Import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ', db= ' DB2 ')  # Parameters: Database IP, database user name , database password, database name # Create cursor cursor = conn.cursor (cursor=pymysql.cursors.dictcursor)  # in parentheses is the type # of its dictionary that executes SQL and returns the number of rows affected effect_ row = Cursor.execute ("INSERT into User (Username,password) VALUES (%s,%s)", ("even", "345"))  # Cursor.executemany represents multiple #%s placeholders, regardless of what type is used with the s% placeholder, separated by a comma ",". # Commit, otherwise unable to save new or modified data Conn.commit () print (Cursor.lastrowid)  #获取自增的ID值 # Close Cursor cursor.close () # Close connection Conn.close ()

　　

3. Get Query data

Import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ', db= ' DB2 ')  # Parameters: Database IP, database user name , database password, database name # Create cursor cursor = conn.cursor (cursor=pymysql.cursors.dictcursor)  # # # Cursor set to dictionary type # Execute SQL, and return the affected number of rows effect_ row = Cursor.execute ("SELECT * from user where nid>%s order by nid desc", (3,)) # result = Cursor.fetchall ()  # get all numbers According to # print (result) # result = Cursor.fetchone ()   # Gets the first Data # print (result) # result = Cursor.fetchone ()   # Get rid of the first piece of data (second article) # Print (result) # result = Cursor.fetchone ()   # The first piece of data (third) (iteration; and so on) # print (result) # result = Cursor.fetchmany (3)  # get multiple # print (result) # result = Cursor.fetchone () # Cursor.scroll ( -1, mode= "relative") #  relative relative movement; Cursor.scroll (0, mode= "Absolute")    # Absolute Absolute Move result = Cursor.fetchone () print (Result) # Close Cursor cursor.close () # Close connection 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 database type

The data obtained by default is the Ganso type, if you want to transform the tuple type into a dictionary-type data, namely:

#!/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 ()

　　

See more: http://www.cnblogs.com/wupeiqi/articles/5713330.html

Python operation MySQL