import pymysql # Connect to the database and call the Connect function (the Connect function returns an object of Connection) conn = pymysql.Connect(host='localhost', #Host ip port=3306, #port user='user', #User name password='passwd', #Password database='db', #Database name charset='utf8', #character set )2. Operating the database (adding, deleting, modifying and checking)
mysql> select * from userinfo; +----+-------+------+ | id | name | pwd | +----+-------+------+ | 1 | sssss | 123 | | 2 | abc | 233 | +----+-------+------+1. Query (simulated user login)
username = input('Please enter a user name:') pwd = input('Please input a password:') #Write the SQL statement to be executed (string form, pay attention to the use of single and double quotation marks) sql = 'select * from userinfo where name="%s" and pwd="%s"'%(username,pwd) cur = conn.cursor() #Call the cursor method, which returns a Cursor object cur.execute(sql) #Call the execute method to execute the sql statement (if executed successfully, the number of query results will be returned here) cur.close() #Close conn.close() #The above sql statement has a "vulnerability". If username input: XX "or 1 = 1 -- xxxx, such a Sao operation can use the wrong username and skip the password, because it is equivalent to executing a select * from userinfo where name=" XX "or 1 = 1 - xx" and PWD = "here at will" statement, 1 = 1 is always valid, and 1 = 1 is commented out later. username = input('Please enter a user name:') pwd = input('Please input a password:') sql = 'select * from userinfo where name=%(name)s and pwd=%(pwd)s' cur.execute(sql,{'name':username,'pwd':pwd}) #Anti-annotation Processing cur.close() conn.close()2. additions and deletions
mysql> select * from userinfo; +----+--------+------+ | id | name | pwd | +----+--------+------+ | 1 | Sroxi | 123 | | 2 | abc | 233 | | 3 | QaQ | 306 | | 4 | Violet | 520 | +----+--------+------+
#increase cur = conn.cursor() sql = 'insert into userinfo(username,pwd) valuses(%s,%s)' cur.execute(sql,(username,pwd)) #cur.executemany(sql,[(username1,pwd1),(username2,pwd2)]) #Insert multiple data at a time #change sql = 'update userinfo set name = %s where id = 1' cur.execute(sql,username) #Delete sql = 'delete from userinfo where id = 2' cursor.execute(sql) conn.commit() #Make sure to commit after adding, deleting and modifying, otherwise the operation is invalid. cursor.close() conn.close()3. Check: fetchone(), fetchmany(), fetchall() and scroll()
Before using these three methods, you must first use execute(), otherwise raise err. Programming Error ("execute() first") and persist select statements, which can only be found in the temporary table obtained from the select query.
- fetchone() queries a record and moves the cursor to the next row
- fetchall() queries all data
- fetchmany(N) queries N pieces of data
- scroll(value,mode) moves cursor, negative mode; it can be'relative'or'absolute'
#By default, the value we get is tuple. We can only see the data of each row, but do not know what each column represents. At this time, we can return to the dictionary in the following way. cur = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = 'select * from userinfo ' cur.execute(sql) row = cur.fetchone() print(row) row1 = cur.fetchone() print(row1) row2 = cur.fetchall() print(row2) //Print results: {'id': 1, 'name': 'Sroxi', 'pwd': '123'} {'id': 2, 'name': 'abc', 'pwd': '233'} [{'id': 3, 'name': 'QaQ', 'pwd': '306'}, {'id': 4, 'name': 'Violet', 'pwd': '520'}]3.2 fetchmany () and scroll()
cur.scroll(-2,'relative') #Negative values move up,'relative'indicates the current position and absolute() indicates the starting position. row3 = cur.fetchmany(2) #Read two records print(row3)