亲宝软件园·资讯

展开

Python中PyMySQL的基本操作

尛刀石 人气:0

简介

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库

PyMySQL 遵循 Python 数据库 API v2.0 规范,并包含了 pure-Python MySQL 客户端库。

如果还未安装,我们可以使用以下命令安装最新版的 PyMySQL:

pip install PyMySQL

pymysql github地址

下面看下PyMySQL的基本操作,

1、查找数据

import pymysql
# 简单的查找
# 连接数据库
	conn = pymysql.connect(host='127.0.0.1', user='root', password='******', database='authoritydb')
# cursor=pymysql.cursors.DictCursor,是为了将数据作为一个字典返回
	cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
	sql = 'select id,name from userinfo where id = %s'
# row返回获取到数据的行数
# 不能将查询的某个表作为一个参数传入到SQL语句中,否则会报错
# eg:sql = 'select id,name from %s'
# eg:row = cursor.excute(sql, 'userinfo') # 备注:userinfo是一个表名
# 像上面这样做就会报SQL语法错误,正确做法如下:
	row = cursor.execute(sql, 1)
# fetchall()(获取所有的数据),fetchmany(size)(size指定获取多少条数据),fetchone()(获取一条数据)
	result = cursor.fetchall()
	cursor.close()
	conn.close()
# 最后打印获取到的数据
	print(result)

# 补充
# 传入多个数据时
	sql = 'select id,name from userinfo where id>=%s and id<=%s'
	row = cursor.executemany(sql, [(1, 3)])
# 以字典方式传值
	sql = 'select id,name from userinfo where id>=%(id)s or name=%(name)s'
	rows = cursor.execute(sql, {'id': id, 'name': name})
	
# -------------------------------------------------
	import pymysql
# 复杂一点的查找,与MySQL的语句格式一样
	connect = pymysql.connect(host='localhost', user='root', password='****', database='authoritydb')
	cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
	username = input('username: ')
	sql = 'select A.name,authorityName from (select name,aid from userauth left join userinfo on' \
	      ' uid=userinfo.id where name=%s) as A left join authority on authority.id=A.aid'
	row = cursor.execute(sql, username)
	result = cursor.fetchmany(row)
	cursor.close()
	connect.close()
	print(result)

# 调用函数
import pymysql
# 函数已经在mysql数据库中创建,这里只调用
# 函数的创建请访问后面的网址(https://blog.csdn.net/qq_43102443/article/details/107349451).
# in (指在创建函数时指定的参数只能输入)
	connect = pymysql.connect(host='localhost', user='root', password='******', database='schooldb')
	cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
	r = cursor.callproc('p2', (10, 5))
	result_1 = cursor.fetchall()
# 这个函数返回两个结果集
# 换到另一个结果集,在进行获取值
	cursor.nextset()
	result_2 = cursor.fetchall()
	cursor.close()
	connect.close()
	
	print('学生:', result_1, '\n老师:', result_2)

# out (指在创建函数时指定的参数只能输出)
	connect = pymysql.connect(host='localhost', user='root', password='*****', database='schooldb')
	cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
# 调用函数
	r = cursor.callproc('p3', (8, 0))
	result_1 = cursor.fetchall()
# 查询函数的第二个参数的值(从零开始计数)
	cursor.execute('select @_p3_1')
	result_2 = cursor.fetchall()
	cursor.close()
	connect.close()
	
	print(result_1, '\n', result_2)

# inout(指在创建函数时指定的参数既能输入,又能输出)
	connect = pymysql.connect(host='localhost', user='root', password='l@l19981019', database='schooldb')
	cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
	r = cursor.callproc('p4', (10, 0, 2))
	result_1 = cursor.fetchall()
	cursor.execute('select @_p4_1,@_p4_2')
	result_2 = cursor.fetchall()
	cursor.close()
	connect.close()
	print(result_1, '\n', result_2)

2、添加数据

用PyMySQL进行数据的增、删、改时,记得最后要commit()进行提交,这样才能保存到数据库,否则不能

	connect = pymysql.Connect(host='localhost', user='root', password='******', database='schooldb')
	cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
	student_id, course_id, number = input('student_id,course_id,number[eg:1 2 43]: ').split()
	sql = 'insert into score(student_id,course_id,number) values(%(student_id)s,%(course_id)s,%(number)s)'
	rows = cursor.execute(sql,{'student_id': student_id, 'course_id': course_id, 'number': number})
# 这里一定要提交
	cursor.commit()
	cursor.close()
	connect.close()

3、删除数据

connect = pymysql.Connect(host='localhost', user='root', password='******', database='schooldb')
	cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
	student_id = input('student_id: ')
	sql = 'delete from student where sid=%s'
	rows = cursor.execute(sql, student_id)
# 这里一定要提交
	cursor.commit()
	cursor.close()
	connect.close()

4、更改数据

connect = pymysql.Connect(host='localhost', user='root', password='******', database='schooldb')
	cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
	id, name = input('id, name: ').split()
	sql = "update userinfo set name=%s where id=%s"
	rows = cursor.executemany(sql, [(name, id)])
# 这里一定要提交
	cursor.commit()
	cursor.close()
	connect.close()

加载全部内容

相关教程
猜你喜欢
用户评论