您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“如何使用pyMySql連接mysql”的有關(guān)知識,在實(shí)際案例的操作過程中,不少人都會遇到這樣的困境,接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧!希望大家仔細(xì)閱讀,能夠?qū)W有所成!
安裝
pip3 install pymysql |
常用函數(shù):
execute() 執(zhí)行語句并返回收影響的行數(shù)
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) effect_row = cursor.execute("insert into t2 VALUES(1,'ray')") # 執(zhí)行SQL,并返回受影響行數(shù) # effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 執(zhí)行SQL,并返回受影響行數(shù) # effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) # 提交,不然無法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) truple_str='ray' effect_row = cursor.execute("insert into t2 VALUES(3,%s)",truple_str) print(effect_row) truple_str='suen' effect_row = cursor.execute("insert into t2 VALUES(4,%s)",truple_str) print(effect_row) # 提交,不然無法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) truple_str=('lalala',1) cursor.execute('update t2 set t_name=%s where t_id=%s',truple_str) # 提交,不然無法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) # truple_str=('lalala',1) tid=1 cursor.execute('delete from t2 where t_id=%s',tid) # 提交,不然無法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
executemany()執(zhí)行條語句,以元組的形式傳入
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) truple_str=[ (5,'aaa'), (6,'bbb'), (7,'ccc'), (8,'ddd') ] cursor.executemany("insert into t2 VALUES(%s,%s)",truple_str) # 提交,不然無法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
fecheone()
fechemany()
fecheall()
注:在fetch數(shù)據(jù)時按照順序進(jìn)行,可以使用cursor.scroll(num,mode)來移動游標(biāo)位置,如:
cursor.scroll(1,mode='relative') # 相對當(dāng)前位置移動,正數(shù)為向下移動,負(fù)數(shù)為向上移動
cursor.scroll(2,mode='absolute') # 相對絕對位置移動
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) # truple_str=('lalala',1) cursor.execute('select * from t2') #fech 是去內(nèi)存中獲取 res = cursor.fetchone() print(res) res = cursor.fetchmany(7) #指定獲取的條目數(shù) print(res) res = cursor.fetchall() print(res) # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) # truple_str=('lalala',1) cursor.execute('select * from t2') #fech 是去內(nèi)存中獲取 res = cursor.fetchone() print(res) res = cursor.fetchone() print(res) cursor.scroll(0,mode='absolute') res = cursor.fetchall() print(res) cursor.scroll(-1,mode='relative') res = cursor.fetchall() print(res) # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) # truple_str=('lalala',1) cursor.execute('select * from t2') #fech 是去內(nèi)存中獲取 while 1: res = cursor.fetchone() if res == None: break print(res) # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
cursor修改,改變獲取后的結(jié)果為字典集合
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 執(zhí)行SQL,并返回收影響行數(shù) # truple_str=('lalala',1) cursor.execute('select * from t2') #fech 是去內(nèi)存中獲取 while 1: res = cursor.fetchone() if res == None: break print(res) # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
lastrowid 獲取最后的自增序列號
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset="utf8" ) # 創(chuàng)建游標(biāo) cursor = conn.cursor() cursor.executemany('insert into t3(t_name) VALUES (%s)',[('aaa07'),('aaa08'),('aaa09')]) conn.commit() tid=cursor.lastrowid print(tid) # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close() |
“如何使用pyMySql連接mysql”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實(shí)用文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。