3.2.1 数据库---pymysql模块
# pip3 install pymysql
pycharm project Interpreter: 添加包 PyMySQL
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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注入的风险。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
运行结果: 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注入的方式:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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()