1. pymysql module
Before using pymysql module, you need to learn database MySQL: MySQL Foundation.
1.1 download and use of pymysql
After reading the MySQL foundation, we all operate the database through mysql, the command-line client tool of MySQL. How to operate the database in the python program? This uses the pymysql module, which is essentially a socket client software, which needs to be installed before use.
1.1.1 download pymysql module
pip3 install pymysql
1.1.2 use of pymysql
Database and data already exist
# Implementation: Using Python Implement user login. If the user exists, the login succeeds (assuming that the user is already in the database) import pymysql user = input('Please enter the user name:') pwd = input('Please input a password:') # 1.Connect conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='db8', charset='utf8') # 2.Create cursors cursor = conn.cursor() #Be careful%s Quotes required sql = "select * from userinfo where username='%s' and pwd='%s'" %(user, pwd) print(sql) # 3.implement sql Sentence cursor.execute(sql) result=cursor.execute(sql) #implement sql Statements, returning sql Number of records successfully queried print(result) # Close the connection. Both the cursor and the connection should be closed cursor.close() conn.close() if result: print('Landing successfully') else: print('Login failed')
1.2 sql injection of execute()
For the last space, if you encounter select * from userinfo where username ='mjj '-- asadasdas' and PWD =' 'in an sql statement, the condition after -- is commented out (note -- there is a space after) #1. sql injection: user exists, bypass password xhh '-- any character #2. sql injection: the user does not exist, bypassing the user and password Xxx 'or 1 = 1 -- any character
Solution:
# It turned out that we were right sql String splicing # sql="select * from userinfo where name='%s' and password='%s'" %(username,pwd) # print(sql) # result=cursor.execute(sql) #Rewrite as ( execute Do string splicing for us. We don't need to and can't do it any more%s Quoted) sql="select * from userinfo where name=%s and password=%s" #!!!Be careful%s Quotes need to be removed because pymysql Will automatically add result=cursor.execute(sql,[user,pwd]) #pymysql Modules help us solve it automatically sql The problem of injection, as long as we follow pymysql Come on.
1.3 add, delete and change: conn.commit()
commit() method: when adding, deleting, or modifying in the database, you must submit, otherwise the inserted data will not take effect.
import pymysql username = input('Please enter the user name:') pwd = input('Please input a password:') # 1.Connect conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.Create cursors cursor = conn.cursor() # operation # increase # sql = "insert into userinfo(username,pwd) values (%s,%s)" # effect_row = cursor.execute(sql,(username,pwd)) #Insert multiple data at the same time #cursor.executemany(sql,[('Li Si'，'110'),('Wang Wu'，'119')]) # print(effect_row)# # change # sql = "update userinfo set username = %s where id = 2" # effect_row = cursor.execute(sql,username) # print(effect_row) # Delete sql = "delete from userinfo where id = 2" effect_row = cursor.execute(sql) print(effect_row) #Must remember commit conn.commit() # 4.Close cursor cursor.close() # 5.Close connection conn.close()
1.4 check: fetchone, fetchmany, fetchall
fetchone(): get the next row of data, the first row is the first row; fetchall(): get all row data sources fetchmany(4): get 4 rows of data
Check the following table:
mysql> select * from userinfo; +----+----------+-----+ | id | username | pwd | +----+----------+-----+ | 1 | mjj | 123 | | 3 | Zhang San | 110 | | 4 | Li Si | 119 | +----+----------+-----+ rows in set (0.00 sec)
Use fetchone():
import pymysql # 1.Connect conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.Create cursors cursor = conn.cursor() sql = 'select * from userinfo' cursor.execute(sql) # Query the data in the first row row = cursor.fetchone() print(row) # (1, 'mjj', '123') # Query the second row of data row = cursor.fetchone() print(row) # (3, 'Zhang San', '110') # 4.Close cursor cursor.close() # 5.Close connection conn.close()
Use fetchall():
import pymysql # 1.Connect conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.Create cursors cursor = conn.cursor() sql = 'select * from userinfo' cursor.execute(sql) # Get all the data rows = cursor.fetchall() print(rows) # 4.Close cursor cursor.close() # 5.Close connection conn.close() #Operation result ((1, 'mjj', '123'), (3, 'Zhang San', '110'), (4, 'Li Si', '119'))
By default, the return value we get is a tuple. We can only see the data of each row, but we don't know what each column represents. At this time, we can use the following methods to return the dictionary, and the data of each row will generate a dictionary:
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #When instantiating, attribute cursor Set to pymysql.cursors.DictCursor
In the fetchone example, when acquiring row data, it can be understood that at the beginning, there is a row pointer pointing to the top of the first row, acquiring a row, and it moves down a row, so when the row pointer reaches the last row, it can no longer obtain the content of the row, so we can use the following methods to move the row pointer:
cursor.scroll(1,mode='relative ') moves relative to the current position cursor.scroll(2,mode='absolute ') (relative absolute position movement) The first value is the number of rows moved, the integer is moved down, and the negative number is moved up. mode specifies whether to move relative to the current position or the first row
# 1.Python Implement user login # 2.Mysql Save data import pymysql # 1.Connect conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.Create cursors cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = 'select * from userinfo' cursor.execute(sql) # Query the data in the first row row = cursor.fetchone() print(row) # (1, 'mjj', '123') # Query the second row of data row = cursor.fetchone() # (3, 'Zhang San', '110') print(row) cursor.scroll(-1,mode='relative') #After setting, the cursor moves forward one line relative to the current position, so the printed result is the data of the second line row = cursor.fetchone() print(row) cursor.scroll(0,mode='absolute') #After setting, the cursor does not change with respect to the first row, so the printed result is the first row of data row = cursor.fetchone() print(row) # 4.Close cursor cursor.close() # 5.Close connection conn.close() #give the result as follows {'id': 1, 'username': 'mjj', 'pwd': '123'} {'id': 3, 'username': 'Zhang San', 'pwd': '110'} {'id': 3, 'username': 'Zhang San', 'pwd': '110'} {'id': 1, 'username': 'mjj', 'pwd': '123'}
fetchmany():
import pymysql # 1.Connect conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.Create cursors cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = 'select * from userinfo' cursor.execute(sql) # Get 2 pieces of data rows = cursor.fetchmany(2) print(rows) # 4.Close cursor # rows = cursor.fetchall() # print(rows) cursor.close() # 5.Close connection conn.close() #The results are as follows: [{'id': 1, 'username': 'mjj', 'pwd': '123'}, {'id': 3, 'username': 'Zhang San', 'pwd': '110'}]