Python operation MySQL

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

• Native Module Pymsql
• ORM Framework Sqlachemy

Use action

1. Execute SQL

ImportPymysql#Create a connection, connect to the databaseconn = Pymysql.connect (host ='localhost', port = 3306, user ='Root', passwd ='123', db ='Sqlexample', CharSet ='UTF8')#Creating Cursorscursor =conn.cursor ()#inserting data into a tableCursor.execute ('INSERT into Class (caption) VALUES ("Four-year class")')#Commit, or you cannot save the created and modified dataConn.commit ()#Close Cursorscursor.close ()#To close a database connectionConn.close ()

Returns the number of rows affected, modifying multiple values

#returns the number of rows affected#Effect_row = Cursor.execute (' Insert INTO class ' (caption) VALUES ("Four-year Class II" )#insert multiple values with Executemany, placed in an iterative objectEffect_row = Cursor.executemany ('INSERT into student (name,pwd,age) VALUES (%s,%s,%s)',[('Wang','123', 12), ('Yang','123', 20), ('Qiang','123', 25)])Print(Effect_row)

Delete data

# Delete data Effect_row = Cursor.executemany ('delete from student where nid=%s', ( 3,4))

modifying data

Effect_row = Cursor.execute ("update student set name= ' Luxun ' where nid=2')

return results

Effect_row = Cursor.execute ('select * FROM student'  )print(effect_ Row)# get query Results (all)result = Cursor.fetchall ()# Gets the results of several items result = Cursor.fetchmany (2)# one item gets result = Cursor.fetchone ()print (Result)

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 ( -1,mode= ' relative ') # moves relative to the current position, up
• Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement

2. Fetch data type

About the data obtained by default is the Ganso type, if you want to get the data of the dictionary type, that is:

# cursor set to dictionary type cursor = conn.cursor (cursor=pymysql.cursors.dictcursor)

3. Prevent SQL injection:

If you use the following string concatenation, even if the user name password is incorrect, you can log in:

'   = sql% (', 1234) = =  Cursor.fetchone ()print(Result)

4. Get the newly created data self-increment ID

Use Cursor.lastrowid to get, multiple data, get last ID

Effect_row = Cursor.executemany ('INSERT into student (name,pwd,age) VALUES (%s,%s,%s)',[('Wang','123', 12), ('Yang','123', 20), ('Qiang','123', 25)]) new_id=Cursor.lastrowidPrint(new_id)

Python operation MySQL