Directory
basic function
simple package
Source code and other data acquisition methods
Basic functions
import pymysql
from pymysql.cursors import DictCursor # import dictionary type cursor object
# Connect to the database
db = pymysql.connect(host='192.168.3.109', # database IP address
port=3306, # database port number
user='root', # database user name
password='123456', # database user password
db='test') # connected database name
# The cursor() method can create a cursor object
# If the data returned by the cursor is not specified, the returned data defaults to a tuple type
# cursor = db. cursor()
#Specified as a dict type cursor, the returned data is a dictionary type
cursor = db. cursor(DictCursor)
# The execute() method can execute all native SQL, and the result will return a number if executed successfully
# For example: create a table, return 0
ret = cursor.execute("CREATE TABLE testsheet (name VARCHAR(20), gender CHAR(1),age int(2))") # create table
print(F"Create form: {ret}")
# For example: insert data, return the number of inserted records
ret = cursor.execute("INSERT INTO testsheet (name,gender,age) VALUES('Zhang San', 'male', 22),('three two one', 'female', 22)")
print(F"Insert data: {ret}")
# For example: query data, return the number of records
ret = cursor. execute("select * from testsheet")
print(F"Number of records: {ret}")
# For example: delete the table, return 0
ret = cursor. execute("DROP TABLE testsheet")
print(F"Delete form: {ret}")
# Use fetchall() and fetchone() to extract the query data, and delete the data after extraction
# fetchall() fetches all data, if the queried data is empty, it will return empty, such as (), []; once query, multiple fetches, it will return empty
cursor. execute("select * from info")
ret = cursor. fetchall()
print(F"fetchall extracts data: {ret}")
ret = cursor. fetchall()
print(F"fetchall fetches data again: {ret}")
# fetchone() fetches the first piece of data, if the queried data is empty, returns None; if one query, multiple extractions, returns the first piece of data after extraction, until the extraction is complete, returns None.
cursor. execute("select * from info")
ret = cursor. fetchone()
print(ret)
ret = cursor. fetchone()
print(ret)
# Emphasis: When using execute() to execute a sql statement with parameters, if the sql statement is directly initialized with a string, there is a risk of SQL injection; it needs to be executed in the way of execute(sql, parameter) to prevent SQL injection
# For example, query the data with ID 1
id = 1
sql = "select * from info where id='%s'" % (id,)
cursor. execute(sql)
ret = cursor. fetchall()
print(F"query the data result with ID 1: {ret}")
# If the value passed in is maliciously modified to the following value, then the data has a security problem of being out of the library
id = "1' or '1=1'
sql = "select * from info where id='%s'" % (id,)
cursor. execute(sql)
ret = cursor. fetchall()
print(F"SQL injection query result: {ret}")
# The execution method of preventing SQL injection, if the above id parameter is used, an SQL statement error will be reported
sql = "select * from info where id='%s'"
try:
cursor. execute(sql, (id,))
ret = cursor. fetchall()
except:
ret = "execution error"
print(F"Query result against SQL injection: {ret}")
# rollback() Roll back data. As long as there is no commit, you can use rollback to roll back all operations that have modified the database (addition, deletion, modification)
# db.rollback()
# Submit the cursor operation to the database.
db. commit()
# close the cursor
cursor. close()
# Close the database connection
db. close()
Simple packaging
import pymysql
from pymysql.cursors import DictCursor # import cursor type object
class Mysql(object):
def __init__(self, host, user, password, port, db):
# Connect to the database
self.database = pymysql.connect(host=host, user=user, password=password, port=port, db=db)
print("connect to database")
# Get the cursor object
self.cursor = self.database.cursor() # The data returned by this cursor is a tuple type
print(self. cursor)
self.cursor = self.database.cursor(DictCursor) # The data returned by this cursor is a dictionary type
print(self. cursor)
def __del__(self):
"""Destroy the object for resource recovery"""
# close the cursor
self. cursor. close()
# Close the database connection
self. database. close()
print("__del__ is executed")
def execute(self, query, args=None, force=None):
"""
Execute SQL
:param query: sql statement
:param args: parameters in the sql statement
:param force: Whether to skip the check of the sql statement "where"
:return: return cursor object
"""
# Check whether "where" is included in the sql statement passed in the query, if it is included, and args does not pass parameters, an exception will be thrown. The purpose of verification is to prevent SQL injection
if force is None:
query = query. lower()
if "where" in query and args is None:
raise("Checked that the SQL contains conditional statements, but the args parameter is empty, there is a risk of SQL injection, if you still want to execute, please change the value of the parameter force to True")
self. cursor. execute(query, args)
# Return the cursor object and you can use execute().fetchone() to get the value
return self. cursor
def commit(self):
"""Submit the data, rollback if the submission fails"""
try:
self. database. commit()
return 0
except Exception as e:
self. database. rollback()
return -1
if __name__ == '__main__':
db = Mysql(host="192.168.0.58", user='root', password='123456', port=3306, db="test")
_id = "1 or 1=1"
code = "000036"
# SQL injection execution method
sql = "select * from info WHERE id=%s or code=%s" % (_id, code)
# ret = db.execute(sql).fetchall()
ret = db.execute(sql, force=True).fetchall()
print(ret)
print(F"The number of data acquired by SQL injection: {len(ret)}")
# Execution method to prevent SQL injection
sql = "select * from info where id=%s or code=%s"
ret = db. execute(sql, (_id, code)). fetchall()
print(ret)
print(F"Number of data obtained by preventing SQL injection: {len(ret)}")
print("="*50)
# update data
_id = 1
code = "000011"
sql = "UPDATE `d`.`info` SET `code` = %s WHERE `id` = %s"
db. execute(sql, (code, _id))
ret = db.commit()
if ret == 0:
print("data updated successfully")
else:
print("Data update failed")
# Get the specified field value
_id = 1
sql = "select * from info where id=%s"
ret = db.execute(sql, (_id,)).fetchone()
print(f"Get the value of the code field whose id is {_id}: {ret.get('code')}")
Results of the
How to obtain source code and other data
Everyone who wants to get the source code and other tutorial materials, pleaselike + comment + favorite, triple!
After Three Links, I will send you private messages one by one in the comment area~