3.21 study database design of python operation database authority system

  1. pip3 download pymysql Python operation Mysql
#pypi inside
pip3 install pymysql

2. Use pymysql

def yanga11ang():
#------------------1. Execute SQL---------------------------
import pymysql
# Create connection
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='654321', db='t1',charset='utf8')
# Create cursors
cursor = conn.cursor()
# Execute SQL and recycle the number of affected rows
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
# implement SQL,And return the number of affected rows     #! use parameters instead of string splicing to prevent sql injection, (database security,)
effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# Execute SQL and insert multiple affected rows
effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# Submit, otherwise the new or modified data cannot be saved
conn.commit()
# Close cursor
cursor.close()
# Close connection
conn.close()
#----------2. Get the auto increment ID of newly created data-----------------
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()

# Get the latest auto increment ID
new_id = cursor.lastrowid
def yanga11ang():
#-------------3. Get query data-------------
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")

# Getting the first row of data is equivalent to the readline in the file
row_1 = cursor.fetchone()
# Get the first n rows of data
row_2 = cursor.fetchmany(3)
# Get all the data and return the ancestor of a tuple (record)
row_3 = cursor.fetchall()
#Note: the data is fetch ed in order. You can use cursor.scroll(num,mode) to move the cursor position, such as:
cursor.scroll(1,mode='relative')  # Move relative to current position
cursor.scroll(2,mode='absolute') # Relative absolute position movement

conn.commit()
cursor.close()
conn.close()
def yanga11ang():
#-------------4.fetch data type-----------
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# The cursor is set to dictionary type, and each row is a dictionary type
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
result = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()

3.sql injection

def yanga111ang():
import pymysql
conn=pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='654321', db='t1')
cursor=conn.cursor()
#Transfer parameters
cursor.execute('select username,password from userinfo where username=%s and password=%s',('yanga11ang',654321))
#If the string splicing password is wrong, you can also get the data
sql='select username,password from userinfo where username=%s and password=%s'
sql=sql %('yanga11ang" or 1=1 -- ',123456)
sql=sql %('yanga11ang" -- ',123456) #--In sql, it means annotation. You can annotate the password so that you can find the information

4. Authority system design

1. login
 2. Get menu
 3. Print menu
 4. Function of menu selection
 -------Preliminary design-------
Create user table
 Establish authority table
 Establish user authority relation table
 Disadvantages: if there are many employees with similar rights, management will not change
 How to view user permissions sql statement:

-------Preliminary improvement-----------
Create user table + Role
 Establish authority table
 Create role table
 Establish role permission relation table
 How to view user permissions sql statement:

Tags: SQL Python MySQL Database

Posted on Tue, 31 Mar 2020 07:04:26 -0700 by Errant_Shadow