MySQL Learning---python operation MySQL 1231

Installing Pymysql

Install Pymysql: Py3 default comes with PIP3 installation, Py2 default no PIP command

cmd Enter pycharm installation directory to complete installation PIP3 install Pymysql

Installation Complete location : E:\PyCharm 2017.2.4\python3.2.5\lib\site-packages

Fault handling : Update the default Python installation

Operation MySQL under PY

Pymysql -designed to operate Mysqlpython modules, Py2 and Py3 are simultaneously compatible

-MySQLdb (Py3 temporarily does not support MYSQLDB)

Basic operations: Add information--insert

#-*-Coding:utf-8-*-import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' Test_python ', charset= ' UTF8 ') # create cursor cursor = conn.cursor () # Default is a tuple and can be changed to dictionary type # First: Insert # directly into # Execute SQL and return the number of affected rows insert_effect_ row = Cursor.execute ("INSERT into course (CNAME, teacher_id) VALUES (' Hhhh43hhkkhh ', ' 2 ')") INP = input (' Please enter name: ') INP2 = INP UT (' Please enter teacher ID: ') # Second: string concatenation # sql = ' INSERT INTO course (CNAME) VALUES ("%s") '%INP # cursor.execute (SQL) # Word string concatenation can be used, but it is easy to cause SQL injection is not recommended # Third: parameter passing, using%s to make placeholder symbols, passed in parameters, Pymysql internal help us to convert insert_effect_row_sec = Cursor.execute ("Insert    Into course (CNAME, teacher_id) VALUES (%s,%s) ", (INP, INP2)) # parameter passing # Fourth: inserting multiple messages li = [(' Wow haha 1 ', 1), (' Wow haha 2 ', 2), (' Wow haha 3 ', 3),]executmany = Cursor.executemany ("INSERT into course (CNAME, teacher_id) VALUES (%s,%s)", Li) # incoming iteration type pri NT (' Executmany: ', Executmany) # Executmany:3, modified successfully 3 # Commit, otherwise unable to save new or modified Data conn.commit () # Close Cursor cursor.close () # Close Connection Conn.close ()

Basic operations: Find information--select

#-*-Coding:utf-8-*-import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' Test_python ', charset= ' UTF8 ') # Create a cursor cursor = conn.cursor () ret = Cursor.execute (' SELECT * from student ') # only data is loaded into memory and requires FET CH value Print (ret) # Find the number of results # The first type: direct printing, large amount of data when the memory is easy to use (there is a pointer index) # r = Cursor.fetchall () # print (' Remove all values \ n ', r) # Prints the result, the result is a Tuple # The second type: 1 data is fetched from memory, at which time the data has been loaded into memory R1 = Cursor.fetchone () print (' Take out a \ n: ', R1) # The third type: 3 data is fetched from memory, at which time the data is loaded into memory R3 = Cursor.fetchmany (3) print (' Take out three \ n: ', R3) # Fourth: Operation pointer out of data # Cursor.scroll (0, mode= ' relative ') # relative position, pointer index regression 0, +1/-1 indicate up/ Down # R4 = Cursor.fetchmany (3) # print (' relative index takes out three \ n: ', R4) # starting from the 5th value: ((5, ' Female ', 1, ' Zhang Yi '), (6, ' Male ', 1, ' Zhang Si '), (7, ' Female ', 2, '  Hammer ')) cursor.scroll (0, mode= ' absolute ') # absolute position, pointer index regression 0R5 = Cursor.fetchmany (3) print (' Absolute index take out three \ n: ', R5) # starting from the No. 0 position to take a value: ((1, ' Male ', 1, ' Understanding '), (2, ' Female ', 1, ' Steel Egg '), (3, ' Male ', 1, ' Zhang San ')) # Close Cursor cursor.close () # Close connection Conn.close ()

Basic operation: Change information--update

#-*-Coding:utf-8-*-import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' Test_python ', charset= ' UTF8 ') # create cursor cursor = conn.cursor () inp  = input (' Please enter updated information: ') ret  = Cursor.execute (" Update course Set cname =%s where cname = ' wow haha 4 ' ", INP) Ret2 = Cursor.execute (" Update course set cname =%s where cname = ' Wow haha 1 ' ", INP) # Submit, otherwise unable to save new or modified data Conn.commit () print (' Not present and update result: ', ret, ' \ r \ n exists and update results: ', Ret2) # Close Cursor cursor.close () # Close Connection Conn.close ()

Basic Operation: Delete Information--delete

#-*-Coding:utf-8-*-import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' Test_python ', charset= ' UTF8 ') # create cursor cursor = conn.cursor () inp  = input (' Please enter updated information: ') ret  = Cursor.execute (" Update course Set cname =%s where cname = ' wow haha 4 ' ", INP) Ret2 = Cursor.execute (" Update course set cname =%s where cname = ' Wow haha 1 ' ", INP) # Submit, otherwise unable to save new or modified data Conn.commit () print (' Not present and update result: ', ret, ' \ r \ n exists and update results: ', Ret2) # Close Cursor cursor.close () # Close Connection Conn.close ()

Other actions: Change the cursor return value to Dictionary

#-*-Coding:utf-8-*-import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' Test_python ', charset= ' UTF8 ') # create cursors cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) cursor.execute (' Select CID as ID, CNAME as name from Course ')  # can change the principle dictionary of Key[cname] to Nameprint (Cursor.fetchall ())  # You can close the cursor by the dictionary value # # . Close () # Close connection Conn.close ()

Other actions: Get the self-increment ID

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysqlconn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' Test_python ', charset= ' UTF8 ') cursor = Conn.cursor () cursor.executemany ("INSERT into course (CNAME, TEACHER_ID) VALUES (%s,%s) ", [(" Pepsi ", 1), (" Coca-Cola ", 2)]) Conn.commit () # Get the latest auto-increment idnew_id = Cursor.lastrowidprint (new_id) Cursor.close () Conn.close ()

How to prevent SQL injection

How to prevent SQL injection:

1. Stored Procedures

2. Placeholder stitching remember to use string concatenation

SQL injection: Changed the original SQL statement, not recommended splicing, recommended parameter passing

#-*-Coding:utf-8-*-import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' Test_python ', charset= ' UTF8 ') # create cursors cursor = conn.cursor () sql = ' SELECT * FROM course WHERE cid = '%s ' and cname = '%s ' # sql = sql% (' 24 ', ' wow haha 3 ')        # normal sql = sql% (' 24 "--', ' wow haha 3 ')      # sql injected value, commented out the following content # sql = sql% ('" or 1=1--', ' Wow haha 3 ')   # SQL injection value, the following condition is constant, can query all results print (SQL) ret = cursor.execute (sql); r = Cursor.fetchall () print (' Execution result: ', R) # Close Cursor cursor.close () # Close connection Conn.close ()

MySQL Learning---python operation MySQL 1231