Python中PyMySQL的基本操作

pip install PyMySQL

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) 

	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()

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()

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()