pip install PyMySQL

import pymysql
# 簡單的查找
# 連接數(shù)據(jù)庫
	conn = pymysql.connect(host='127.0.0.1', user='root', password='******', database='authoritydb')
# cursor=pymysql.cursors.DictCursor,是為了將數(shù)據(jù)作為一個字典返回
	cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
	sql = 'select id,name from userinfo where id = %s'
# row返回獲取到數(shù)據(jù)的行數(shù)
# 不能將查詢的某個表作為一個參數(shù)傳入到SQL語句中，否則會報錯
# eg：sql = 'select id,name from %s'
# eg：row = cursor.excute(sql, 'userinfo') # 備注：userinfo是一個表名
# 像上面這樣做就會報SQL語法錯誤,正確做法如下：
	row = cursor.execute(sql, 1)
# fetchall()（獲取所有的數(shù)據(jù)），fetchmany(size)（size指定獲取多少條數(shù)據(jù)），fetchone()（獲取一條數(shù)據(jù)）
	result = cursor.fetchall()
	cursor.close()
	conn.close()
# 最后打印獲取到的數(shù)據(jù)
	print(result)

# 補充
# 傳入多個數(shù)據(jù)時
	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)

# 調(diào)用函數(shù)
import pymysql
# 函數(shù)已經(jīng)在mysql數(shù)據(jù)庫中創(chuàng)建，這里只調(diào)用
# 函數(shù)的創(chuàng)建請訪問后面的網(wǎng)址(https://blog.csdn.net/qq_43102443/article/details/107349451).
# in （指在創(chuàng)建函數(shù)時指定的參數(shù)只能輸入）
	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()
# 這個函數(shù)返回兩個結果集
# 換到另一個結果集，在進行獲取值
	cursor.nextset()
	result_2 = cursor.fetchall()
	cursor.close()
	connect.close()
	
	print('學生:', result_1, '\n老師:', result_2)

# out （指在創(chuàng)建函數(shù)時指定的參數(shù)只能輸出）
	connect = pymysql.connect(host='localhost', user='root', password='*****', database='schooldb')
	cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
# 調(diào)用函數(shù)
	r = cursor.callproc('p3', (8, 0))
	result_1 = cursor.fetchall()
# 查詢函數(shù)的第二個參數(shù)的值（從零開始計數(shù)）
	cursor.execute('select @_p3_1')
	result_2 = cursor.fetchall()
	cursor.close()
	connect.close()
	
	print(result_1, '\n', result_2)

# inout（指在創(chuàng)建函數(shù)時指定的參數(shù)既能輸入，又能輸出）
	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()