• Python(PyMySQL模块)


    day60

    参考:http://www.cnblogs.com/wupeiqi/articles/5713330.html

    查询(登录)s1.py

     1 import pymysql
     2 
     3 user = input("username:")
     4 pwd = input("password:")
     5 
     6 #连接数据库
     7 
     8 #打开
     9 conn = pymysql.connect(host= "localhost", user = 'root', password='112358', database = 'db4')
    10 #
    11 cursor = conn.cursor()
    12 
    13 sql = "select * from userinfo where username = '%s' and password = '%s'" %(user, pwd)
    14 # select * from userinfo where username='uu' or 1=1 -- ' and password='%s'
    15 #其中 1=1百分百成立( -- )将后半部分注释
    16 #uu' or 1=1 -- 
    17 # 倪志鹏' -- 也行
    18 cursor.execute(sql)
    19 result = cursor.fetchone() #
    20 
    21 #关闭数据库
    22 cursor.close()
    23 conn.close()
    24 
    25 if result:
    26     print('登录成功')
    27 else:
    28     print('登录失败')

    执行结果:

    username:古丽
    password:123456
    登录成功
    
    Process finished with exit code 0

    但是以上方式存在一个问题:

    不用用户名和密码也能登录。

    是因为SQL注入。

    sql = "select * from userinfo where username = '%s' and password = '%s'" %(user, pwd)
    # select * from userinfo where username='uu' or 1=1 -- ' and password='%s'
    #其中 1=1百分百成立( -- )将后半部分注释
    #uu' or 1=1 -- 
    # 倪志鹏' -- 也行

    如果用拼接方式存在漏洞,or 1 = 1,百分百处理, -- 会将后半部分注释,所以无所谓输入的密码是什么。

    应改为以下方式s2.py:

     1 import pymysql
     2 
     3 user = input("username:")
     4 pwd = input("password:")
     5 
     6 #连接数据库
     7 
     8 #打开
     9 conn = pymysql.connect(host= "localhost", user = 'root', password='112358', database = 'db4')
    10 #
    11 cursor = conn.cursor()
    12 
    13 #不要字符串拼接
    14 sql = "select * from userinfo where username = %s and password = %s"
    15 
    16 #cursor.execute(sql, user, pwd)  linux下报错
    17 
    18 cursor.execute(sql, [user, pwd])
    19 
    20 result = cursor.fetchone() #
    21 
    22 #关闭数据库
    23 cursor.close()
    24 conn.close()
    25 
    26 if result:
    27     print('登录成功')
    28 else:
    29     print('登录失败')

    其中第16行在linux下可能出错。

    执行结果

    username:asc
    password:12313
    登录成功
    
    Process finished with exit code 0

    插入 s3.py

     1 import pymysql
     2 
     3 user = "呱呱"
     4 pwd = "asca"
     5 
     6 #连接数据库
     7 #打开
     8 conn = pymysql.connect(host= "localhost", user = 'root', password='112358', database = 'db4')
     9 #
    10 cursor = conn.cursor()
    11 #
    12 # sql = "insert into userinfo(username, password) values(%s,%s)"
    13 # cursor.execute(sql,[user,pwd])
    14 
    15 #增加多个
    16 sql = "insert into userinfo(username, password) values(%s,%s)"
    17 r = cursor.executemany(sql,[('dssdsa', '21131'), ('asc', '12313')])
    18 #其中r为受影响的行数
    19 conn.commit()#提交
    20 
    21 
    22 #关闭数据库
    23 cursor.close()
    24 conn.close()

    16~17为插入多对数据,其中r为受影响的行数。

    19行不要忘了提交。

    取fetchone、fetchmany等 s4.py

     1 import pymysql
     2 
     3 #打开
     4 conn = pymysql.connect(host= "localhost", user = 'root', password='112358', database = 'db4')
     5 #
     6 cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)#带其中参数,可使结果为列表带字典
     7 
     8 sql = "select * from userinfo;"
     9 
    10 cursor.execute(sql)
    11 result = cursor.fetchone() #每次拿一个
    12 print(result)
    13 result = cursor.fetchone() #
    14 print(result)
    15 
    16 #按顺序拿
    17 result = cursor.fetchmany(2) #
    18 print(result)#元组
    19 
    20 #关闭数据库
    21 cursor.close()
    22 conn.close()

    按顺序拿取,其中第6行带参数,可以输出列表带字典,不带参数输出元组。

    执行结果:

    {'username': '古丽', 'id': 2, 'password': '123456'}
    {'username': 'dssdsa', 'id': 8, 'password': '21131'}
    [{'username': 'asc', 'id': 9, 'password': '12313'}, {'username': 'dssdsa', 'id': 10, 'password': '21131'}]
    
    Process finished with exit code 0
    新插入数据的自增id s5.py 
     1 #新插入数据的自增id
     2 
     3 #文章表:id title hobby_count
     4 #       1  assda     0
     5 
     6 import pymysql
     7 
     8 #连接数据库
     9 #打开
    10 conn = pymysql.connect(host= "localhost", user = 'root', password='112358', database = 'db4')
    11 #
    12 cursor = conn.cursor()
    13 #
    14 sql = "insert into userinfo(username, password) values('哈哈','000000')"
    15 cursor.execute(sql)  #linux下报错
    16 conn.commit()#提交
    17 print(cursor.lastrowid)#输出插入语句的自增id
    18 
    19 #关闭数据库
    20 cursor.close()
    21 conn.close()

    第17行输出插入语句的自增id。

    输出结果:

    16
    
    Process finished with exit code 0

  • 相关阅读:
    1219 总结
    1206 冲刺三
    1130 冲刺2
    1128 主页面
    1123 冲刺3
    1121 冲刺2
    1118 冲刺1
    1117 新冲刺
    0622 软件工程总结
    0617 实验四 主存空间的分配和回收
  • 原文地址:https://www.cnblogs.com/112358nizhipeng/p/9961109.html
Copyright © 2020-2023  润新知