Python Development --- operate MySQL database, pythonmysql

Python Development --- operate MySQL database, pythonmysql

Core Blog content:

Reference blog: https://www.cnblogs.com/anpengapple/p/7127580.html
1. initial trial operation
2. the user logs on to the MySQL database for the first time and causes SQL Injection problems.
3. How to Solve MySQL injection Problems
4. The pymysql module adds, deletes, modifies, and queries databases.
5. Use pymysql to obtain the auto-incrementing ID of new data: Use the lastrowid method.
6. Obtain the fetch data type

(1) The pymysql module initially operates the MySQL database
Pymysql is a module used to operate MySQL in Python. Its usage is almost the same as that of MySQLdb.
How python operates MySQL: connect to the MySQL server through the Socket Client, send SQL statements after the connection, and close the connection.
Sample Code:

#! /Usr/bin/python #-*-coding: UTF-8-*-import pymysql # connect to the MySQL server through the Socket Client and create the corresponding connection conn = pymysql. connect (host = "192.168.80.100", port = 3306, user = "root", password = "admin", database = "Test_mine1", charset = 'utf8 ') # create cursor_cursor = conn. cursor () "after the cursor is created, you can perform corresponding operations on the database MySQL. Note: The cursor comes with the moving feature that moves each time, obtain the next data "" # execute the SQL statement and return the affected number of rows v1 = cursor_oper.execute ('select * from student ') print ("the number of rows in the student table is: % d "% v1) # Use the fetch method in the cursor to obtain data in the database result1 = cursor_oper.fetchall () print (result1) # Execute SQL, and return the affected row v2 = cursor_oper.execute ('select * from student where name = "lidong" ') print ("% d" % v2) result2 = cursor_oper.fetchone () print (result2) # execute the SQL statement and return the affected rows v3 = cursor_oper.execute ('select * from student where id> = 2') print ("% d" % v3) result3 = cursor_oper.fetchmany (1) print (result3) # Close the cursor and link cursor_oper.close () conn. close ()

Running result:

The number of data rows in the student table is: 4 (1, 'hangsan', 'nan '), (2, 'mongog', 'nan'), (3, 'hangout ', 'nv '), (4, 'zhang ming', 'nan') 1 (2, 'lidong', 'nan ') 3 (2, 'lidong ', 'nan '),)

(2) The first time the user logs on to the MySQL database for verification and the problem caused by SQL Injection
Related Knowledge points:

User Login: socket connects to the database, and customizes SQL statements. Database-based login verification causes of SQL Injection problems: If you concatenate SQL statements through string formatting, this will cause SQL Injection problems. Solution to the SQL Injection problem: cute (SQL, [user, pass_wd]) reminds me of the solution in C language.

Comparison chart:

Sample program: Preliminary login verification program

#! /Usr/bin/python #-*-coding: UTF-8-*-"User Logon: socket-connected database, and customize SQL statements based on database login to verify the cause of SQL Injection problems: If you splice SQL statements through string formatting, it will cause SQL Injection problems. Solution to the SQL Injection problem: cute (SQL, [user, pass_wd]) reminds me of the solution in C language. "import pymysqluser = input (" Enter the User name: ") pass_wd = input (" enter the password: ") # establish a connection with the database before user authentication conn = pymysql. connect (host = "192.168.80.100", port = 3306, user = "root", password = "admin", database = "Test_mine1", charset = 'utf8') cursor_records = conn. after the cursor () "client is connected to the database, you can perform the corresponding operations. Note: the corresponding fields in the SQL statement must be enclosed by quotation marks "SQL =" select * from userinfo where username = '% s' and pass_wd =' % S' "% (user, pass_wd) print (SQL) v1 = cursor_oper.execute (SQL) print (v1) result = cursor_oper.fetchall () print (result) cursor_oper.close () conn. close ()

Example of running result 1:

Enter username: alex enter password: 841807 select * from userinfo where username = 'Alex 'and pass_wd = '000000' 1 (1, 'Alex ', '123 '),)

Example of running result 2: (SQL Injection problem)

(3) how to solve MySQL injection Problems
Cause: the cause of the SQL Injection problem: If you concatenate an SQL statement through string formatting, it will cause the SQL Injection problem.
Method: concatenate a SQL sentence without passing through the serial format, and input the second parameter in cursor_oper.exe cute (SQL, [user, pass_wd.

cursor_oper.execute(sql,[user,pass_wd])

Example: how to solve the MySQL injection Problem

#! /Usr/bin/python #-*-coding: UTF-8-*-"User Logon: socket-connected database, and customize SQL statements based on database login to verify the cause of SQL Injection problems: If you splice SQL statements through string formatting, it will cause SQL Injection problems. "Import pymysqluser = input (" Enter the User name: ") pass_wd = input (" enter the password: ") # establish a connection with the database before user authentication conn = pymysql. connect (host = "192.168.80.100", port = 3306, user = "root", password = "admin", database = "Test_mine1", charset = 'utf8') cursor_records = conn. after the cursor () "client is connected to the database, you can perform the corresponding operations. Note: the corresponding fields in the SQL statement must be enclosed by quotation marks "SQL =" select * from userinfo where username = % s and pass_wd = % s "print (SQL) v1 = cursor_oper.execute (SQL, [user, pass_wd]) print (v1) result = cursor_oper.fetchall () print (result) cursor_oper.close () conn. close ()

Running result:

Enter username: alex enter password: 841807 select * from userinfo where username = % s and pass_wd = % s1 (1, 'Alex ', '123 '),)

(4) The pymysql module adds, deletes, modifies, and queries databases.
Core: Except the query operation, the other three operations must be submitted through conn. commit (). Otherwise, new or modified data cannot be saved.
① Query operation: omitted (fancy)
② Insert operation (sample code ):

#! /Usr/bin/python #-*-coding: UTF-8-*-"using pymysql to add, delete, and modify MySQL" import pymysql # establish a connection with the database before user authentication conn = pymysql. connect (host = "192.168.80.100", port = 3306, user = "root", password = "admin", database = "Test_mine1", charset = 'utf8') cursor_records = conn. cursor () "after the client establishes a connection with the database, you can perform the corresponding operation" SQL = "insert into userinfo (username, pass_wd) values (% s, % s) "print (SQL) v1 = cursor_oper.execute (SQL, ['hangzhou', '000000']) print (" the number of affected rows in the table is: % d "% v1) # all operations involving addition, deletion, modification, and query must be submitted to the database through the conn link before the conn takes effect. commit () cursor_oper.close () conn. close ()

Running result:

③ Delete operation (sample code ):

#! /Usr/bin/python #-*-coding: UTF-8-*-"using pymysql to add, delete, and modify MySQL" import pymysql # establish a connection with the database before user authentication conn = pymysql. connect (host = "192.168.80.100", port = 3306, user = "root", password = "admin", database = "Test_mine1", charset = 'utf8') cursor_records = conn. cursor () "after the client establishes a connection with the database, you can perform the corresponding operation "SQL =" delete from userinfo where username = % s "print (SQL) v1 = cursor_oper.execute (SQL, ['hangzhou']) print ("the number of affected rows in the table is % d" % v1) conn. commit () cursor_oper.close () conn. close ()

Running result:

④ Update operation: (sample code)

#! /Usr/bin/python #-*-coding: UTF-8-*-"using pymysql to add, delete, and modify MySQL" import pymysql # establish a connection with the database before user authentication conn = pymysql. connect (host = "192.168.80.100", port = 3306, user = "root", password = "admin", database = "Test_mine1", charset = 'utf8') cursor_records = conn. cursor () "after the client establishes a connection with the database, you can perform the following operations: "SQL =" update userinfo set pass_wd = % s where username = % s "print (SQL) v1 = cursor_oper.execute (SQL, ['100', 'Alex ']) print ("the number of affected rows in the table is % d" % v1) conn. commit () cursor_oper.close () conn. close ()

Running result:

(5). Use pymysql to obtain the auto-increment ID of new data: Use the lastrowid method.
Sample Code:

#! /Usr/bin/python #-*-coding: UTF-8-*-"Get the auto-increment ID of new data through pymysql: use the lastrowid method "import pymysql # establish a connection with the database before the user authentication conn = pymysql. connect (host = "192.168.80.100", port = 3306, user = "root", password = "admin", database = "Test_mine1", charset = 'utf8') cursor_records = conn. cursor () "after the client establishes a connection with the database, you can perform the corresponding operation" SQL = "insert into userinfo (username, pass_wd) values (% s, % s) "print (SQL) v1 = cursor_oper.execute (SQL, ['hangting ', '000000']) print (" the number of affected rows in the table is: % d "% v1) # all operations involving addition, deletion, modification, and query must be submitted to the database through the conn link before the conn takes effect. commit () # obtain the health print (type (cursor_oper.lastrowid) of the newly added data) print (cursor_oper.lastrowid) cursor_oper.close () conn. close ()

Running result:

The number of affected rows in the insert into userinfo (username, pass_wd) values (% s, % s) Table is: 1 <class 'int'> 10

Image Display:

(6) Obtaining fetch data types
Function: fetchall (), fetone (), and fetmanay:
Method: Set the cursor to the dictionary type.
Cursor = conn. cursor (cursor = pymysql. cursors. DictCursor)
Comparison:

conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor()conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor(cursor=pymysql.cursors.DictCursor)

Sample Code:

#! /Usr/bin/python #-*-coding: UTF-8-*-"function: the results obtained by fetchall (), fetone (), and fetmanay () are converted to a list, similar to the previous dict (zip) operation "import pymysql # establish a connection with the database before user authentication conn = pymysql. connect (host = "192.168.80.100", port = 3306, user = "root", password = "admin", database = "Test_mine1", charset = 'utf8') cursor_records = conn. cursor (cursor = pymysql. cursors. dictCursor) "after the client establishes a connection with the database, you can perform corresponding operations" SQL = "select * from userinfo" print (SQL) v1 = cursor_oper.execute (SQL) print ("the number of affected rows in the table is: % d" % v1) result = cursor_oper.fetchall () print (result) cursor_oper.close () conn. close ()

Running result:

The number of affected rows in the select * from userinfo table is: 6 [{'uid': 1, 'pass _ wd ': '000000', 'username': 'Alex '}, {'uid': 3, 'pass _ wd ': '000000', 'username': 'yunhao'}, {'uid': 5, 'pass _ wd ': '000000', 'username': 'Eric '}, {'uid': 7, 'pass _ wd': '000000', 'username': 'lihao '}, {'uid': 8, 'pass _ wd ': '000000', 'username': 'lihao'}, {'uid': 10, 'pass _ wd ': '123', 'username': 'hangting '}] Process finished with exit code 0

Top

0

Step on

0

View comments