• mysql之pymysql模块


    一、pymysql的基本使用

    import pymysql
    
    user = input('user>>>:').strip()
    pwd = input('password>>>:').strip()
    
    # 建立连接
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='112233',
        db='db8',
        charset='utf8'
    )
    
    # 拿到游标
    
    cursor = conn.cursor()
    
    # 执行sql语句
    
    sql = 'select * from userinfo where user = "%s" and pwd="%s"' % (user, pwd)
    
    rows = cursor.execute(sql)
    
    cursor.close()
    conn.close()
    
    # 进行判断
    if rows:
        print('登陆成功')
    else:
        print('登陆失败')

    、解决sql注入问题

    import pymysql
    
    user = input('user>>>:').strip()
    pwd = input('password>>>:').strip()
    
    # 建立连接
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='112233',
        db='db8',
        charset='utf8'
    )
    
    # 拿到游标
    
    cursor = conn.cursor()
    
    # 执行sql语句
    
    # sql = 'select * from userinfo where user = "%s" and pwd="%s"' % (user, pwd)
    # print(sql)
    '''
    方式一
    # select * from userinfo where user = "egon" -- xxxxxx" and pwd=""
    # 在sql中 --之后的都代表注释掉。所以等于只查 where user ="egon"
    
    方式二
    # select * from userinfo where user = "xxx" or 1=1 -- hahahaha" and pwd=""
    # select * from userinfo where user = "xxx" or 1=1 其实是执行了这个,1=1永远成立
    '''
    
    # 防止sql注入的方法,过滤到非法操作
    
    sql = 'select * from userinfo where user = %s and pwd= %s '
    rows = cursor.execute(sql, (user, pwd))  # 将用户密码在这里传入,pymysql内置的功能就能帮助你过滤
    
    cursor.close()
    conn.close()
    
    # 进行判断
    if rows:
        print('登陆成功')
    else:
        print('登陆失败')

    三、pymysql之增删改查

    # 1、增删改
    import pymysql
    
    # 建立链接
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='112233',
        db='db8',
        charset='utf8'
    )
    # 拿游标
    cursor = conn.cursor()
    
    # 执行sql
    # 增、删、改
    sql = 'insert userinfo(user,pwd) values(%s,%s)'  # 删改把insert换成相应的关键字就行
    # cursor.execute(sql,('wxx','123'))
    
    # rows = cursor.executemany(sql, [('yxx', '123'), ('egon1', '123'), ('egon2', '123')])  # 插入多条记录
    # print(rows) # 3
    
    rows = cursor.executemany(sql,[('egon3','123'),('egon4','123'),('egon5','123')])
    print(cursor.lastrowid)  # 7 插入之前id走到哪了,在插入三条就是7、8、9
    
    conn.commit()  # 执行commit才会对数据库进行操作
    
    # 关闭游标和链接
    cursor.close()
    conn.close()
    
    
    # 2、查询
    
    import pymysql
    
    # 建立链接
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='112233',
        db='db8',
        charset='utf8'
    )
    # 拿游标
    cursor = conn.cursor(pymysql.cursors.DictCursor)  # 以字典的形式取出来
    
    # 执行sql
    # 查询
    rows = cursor.execute('select * from userinfo;')
    # print(rows)  6
    
    # print(cursor.fetchone())  # 取一行,直到取到没有就显示None
    # print(cursor.fetchone())
    # print(cursor.fetchmany(5))  # 指定取几行
    
    # print(cursor.fetchall())  # 取全部
    
    
    # cursor.scroll(3,'absolute') # 绝对位置
    # print(cursor.fetchone())  # 第四条
    
    print(cursor.fetchone())
    cursor.scroll(2,mode='relative')  # 基于当前的位置往后在跳两个
    print(cursor.fetchone()) # 4 ,如果用absolute就是3
    
    
    # 关闭游标和链接
    cursor.close()
    conn.close()

  • 相关阅读:
    条件变量:为什么要与互斥锁配套使用?为什么要使用while来避免虚假唤醒?
    【转】高性能IO之Reactor模式
    LeetCode127:单词接龙
    CF1245F: Daniel and Spring Cleaning
    权值线段树学习笔记
    luogu_4317: 花神的数论题
    luogu_2605: 基站选址
    入门平衡树: Treap
    CF1244C: The Football Season
    luogu_1156: 垃圾陷阱
  • 原文地址:https://www.cnblogs.com/lshedward/p/10254690.html
Copyright © 2020-2023  润新知