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