Python中PyMySQL的基本操作
簡介
PyMySQL 是在 Python3.x 版本中用于連接 MySQL 服務(wù)器的一個(gè)庫
PyMySQL 遵循 Python 數(shù)據(jù)庫 API v2.0 規(guī)范,并包含了 pure-Python MySQL 客戶端庫。
如果還未安裝,我們可以使用以下命令安裝最新版的 PyMySQL:
pip install PyMySQL
下面看下PyMySQL的基本操作,
1、查找數(shù)據(jù)
import pymysql # 簡單的查找 # 連接數(shù)據(jù)庫 conn = pymysql.connect(host='127.0.0.1', user='root', password='******', database='authoritydb') # cursor=pymysql.cursors.DictCursor,是為了將數(shù)據(jù)作為一個(gè)字典返回 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = 'select id,name from userinfo where id = %s' # row返回獲取到數(shù)據(jù)的行數(shù) # 不能將查詢的某個(gè)表作為一個(gè)參數(shù)傳入到SQL語句中,否則會(huì)報(bào)錯(cuò) # eg:sql = 'select id,name from %s' # eg:row = cursor.excute(sql, 'userinfo') # 備注:userinfo是一個(gè)表名 # 像上面這樣做就會(huì)報(bào)SQL語法錯(cuò)誤,正確做法如下: 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) # 補(bǔ)充 # 傳入多個(gè)數(shù)據(jù)時(shí) 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 # 復(fù)雜一點(diǎn)的查找,與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)建請(qǐng)?jiān)L問后面的網(wǎng)址(https://blog.csdn.net/qq_43102443/article/details/107349451). # in (指在創(chuàng)建函數(shù)時(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() # 這個(gè)函數(shù)返回兩個(gè)結(jié)果集 # 換到另一個(gè)結(jié)果集,在進(jìn)行獲取值 cursor.nextset() result_2 = cursor.fetchall() cursor.close() connect.close() print('學(xué)生:', result_1, '\n老師:', result_2) # out (指在創(chuàng)建函數(shù)時(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ù)的第二個(gè)參數(shù)的值(從零開始計(jì)數(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í)指定的參數(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)
2、添加數(shù)據(jù)
用PyMySQL進(jìn)行數(shù)據(jù)的增、刪、改時(shí),記得最后要commit()進(jìn)行提交,這樣才能保存到數(shù)據(jù)庫,否則不能
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、刪除數(shù)據(jù)
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、更改數(shù)據(jù)
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()
到此這篇關(guān)于PyMySQL的基本操作的文章就介紹到這了,更多相關(guān)PyMySQL操作內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
python實(shí)現(xiàn)手機(jī)通訊錄搜索功能
這篇文章主要介紹了python模仿手機(jī)通訊錄搜索功能,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2018-02-02Python數(shù)據(jù)分析基礎(chǔ)之異常值檢測(cè)和處理方式
這篇文章主要介紹了Python數(shù)據(jù)分析基礎(chǔ)之異常值檢測(cè)和處理方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-07-07Django傳遞數(shù)據(jù)給前端的3種方式小結(jié)
Django從后臺(tái)往前臺(tái)傳遞數(shù)據(jù)時(shí)有多種方法可以實(shí)現(xiàn),下面這篇文章主要給大家介紹了關(guān)于Django傳遞數(shù)據(jù)給前端的3種方式,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-01-01基于python不同開根號(hào)的速度對(duì)比分析
這篇文章主要介紹了基于python不同開根號(hào)的速度對(duì)比分析,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-03-03pandas中.loc和.iloc以及.at和.iat的區(qū)別說明
這篇文章主要介紹了pandas中.loc和.iloc以及.at和.iat的區(qū)別說明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-04-04