3.2.1 数据库---pymysql模块

# pip3 install pymysql

pycharm project Interpreter:  添加包 PyMySQL

 

 

import sys
import pymysql

print(sys.path)


def login_db(user, pwd):


    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='!QAZxsw2',
        db='db1',
        charset='utf8'
    )

    cur = conn.cursor()

    sql = "select username,pwd from user where username='%s' and pwd = '%s'" % (user, pwd)
    rows = cur.execute(sql)

    cur.close()

    conn.close()

    if rows:
        print('success')
    else:
        print('failed')


if __name__ == '__main__':
    user = input('user>>').strip()
    passwd = input('password>>').strip()

    login_db(user, passwd)

 注意：这种方法有sql注入的风险。

运行结果：

user>>zdaf" or 1=1 -- adfa 
password>>asf
select username,pwd from user where username="zdaf" or 1=1 -- adfa" and pwd = "asf" 
success

 

防范sql注入的方式：

import sys
import pymysql

print(sys.path)


def login_db(user, pwd):


    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='!QAZxsw2',
        db='db1',
        charset='utf8'
    )

    cur = conn.cursor()

    # sql = 'select username,pwd from user where username="%s" and pwd = "%s" ' % (user, pwd)
    # rows = cur.execute(sql)

    sql = 'select username,pwd from user where username=%s and pwd = %s'
    rows = cur.execute(sql, (user, pwd))
    print(sql)

    cur.close()
    conn.close()

    if rows:
        print('success')
    else:
        print('failed')


if __name__ == '__main__':
    user = input('user>>').strip()
    passwd = input('password>>').strip()

    login_db(user, passwd)

运行结果：

user>>lbx
password>>lbx
select username,pwd from user where username=%s and pwd = %s
success

user>>zdaf" or 1=1 -- adfa 
password>>
select username,pwd from user where username=%s and pwd = %s
failed

 

pymysql 的增删改查的应用

import sys
import pymysql

print(sys.path)


def db_conn():
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='!QAZxsw2',
        db='db1',
        charset='utf8'
    )
    return conn


def login_db():

    user = input('user>>').strip()
    pwd = input('password>>').strip()


    conn = db_conn()
    cur = conn.cursor()
    # sql = 'select username,pwd from user where username="%s" and pwd = "%s" ' % (user, pwd)
    # rows = cur.execute(sql)

    sql = 'select username,pwd from user where username=%s and pwd = %s'
    rows = cur.execute(sql, (user, pwd))
    print(sql)

    cur.close()
    conn.close()

    if rows:
        print('success')
    else:
        print('failed')


def insert():
    user = input('user>>').strip()
    passwd = input('password>>').strip()

    conn = db_conn()
    cur = conn.cursor()
    sql = 'insert into user(username, pwd) values (%s, %s)'
    cur.execute(sql, (user, passwd))   # 单行插入
    # cur.executemany(sql, [('lbx12', 'lbx12'), ('lbx13', 'lbx13')])    # 多行插入
    print(cur.lastrowid)   # 当前插入的记录的第一条的自增id

    conn.commit()  # 增，删，改一定要记得提交commit
    cur.close()
    conn.close()


def delete():
    user = input('请输入要删除的用户名：').strip()
    
    conn = db_conn()
    cur = conn.cursor()
    
    sql = 'delete from user where username= %s'
    cur.execute(sql, user)
    conn.commit()
    cur.close()
    conn.close()


def update():
    pass


def select():
    conn = db_conn()
    # cur = conn.cursor()  # 游标以元组的形式返回
    cur = conn.cursor(pymysql.cursors.DictCursor)   # 游标以字典的形式返回

    sql = 'select username, pwd from user'

    rows = cur.execute(sql)
    # print('rows=', rows)   # 影响的行数

    # res = cur.fetchall()   # 取出全部结果集
    # res = cur.fetchmany(3)     # 取前n行

    print(cur.fetchone())
    # print(cur.fetchone())

    # cur.scroll(1, mode='absolute')   # 光标移动多少行，绝对位置
    cur.scroll(0, mode='relative')   # 光标从当前移动多少行，相对位置
    print(cur.fetchone())

    cur.close()
    conn.close()


if __name__ == '__main__':

    # login_db()
    insert()
    # delete()

    # select()

 

调用存储过程：

　　Mysql 调用：

　　　　call 存储过程名();

　　pymysql调用存储过程：

　　　　cur.callproc(存储过程名,('参数'))

　　　　或：cursor.execute('call add_teacher(%s)',(10))

重点

如何python调用 callproc 进行调用储存过程

1.创建完整的Mysql数据库连接

2.使用cursor()初始化数据库游标

3.使用游标来调用callproc函数 里面添加 需要传入的变量 例如 callproc(name,args) name="proc_user",args=['21',syh];

3.cursor可以传递出一系列的结果集，使用storeresult来获取一系列的iterator指向结果集

4.用fetchall方法获取结果

callproc 无法直接获得out和INOUT变量 ，但是变量存在server中，可以通过@_procname_n 来获取变量值，可以按照传入参数的位置获取，如第1个 SELECT @_procname_0

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
def call_find_all_sp():
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()
 
        cursor.callproc('find_all')
 
        # print out the result
        for result in cursor.stored_results():
            print(result.fetchall())
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()
 
if __name__ == '__main__':
    call_find_all_sp()