一、安裝第三方庫(kù)PyMySQL
1、在PyCharm中通過 【File】-【setting】-【Python Interpreter】搜索 PyMySQL進(jìn)行安裝
2、通過PyCharm中的 Terminal 命令行 輸入: pip install PyMySQL
注：通過pip安裝，可能會(huì)提示需要更新pip，這時(shí)可執(zhí)行：pip install --upgrade pip 進(jìn)行更新pip
二、mysql數(shù)據(jù)庫(kù)查詢（SELECT)
1、pymysql.connect（）連接數(shù)據(jù)庫(kù)
import pymysql
connect = pymysql.connect(host='101.37.246.001', # 數(shù)據(jù)庫(kù)地址
port= 3306, # 數(shù)據(jù)庫(kù)端口，mysql一般默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 登錄密碼
database='movie_cat', # 要連接的數(shù)據(jù)庫(kù)名
charset='utf8') # 數(shù)據(jù)庫(kù)編碼，一般默認(rèn)utf8
?2、使用 cursor( ) 創(chuàng)建游標(biāo)
import pymysql
connect = pymysql.connect(host='101.37.246.001', # 數(shù)據(jù)庫(kù)地址
port= 3306, # 數(shù)據(jù)庫(kù)端口，mysql一般默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 登錄密碼
database='movie_cat', # 要連接的數(shù)據(jù)庫(kù)名
charset='utf8') # 數(shù)據(jù)庫(kù)編碼，一般默認(rèn)utf8
with connect.cursor() as cursor: # 創(chuàng)建游標(biāo)
3、使用 execute( ) 執(zhí)行sql語(yǔ)句
?文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-821314.html
import pymysql
connect = pymysql.connect(host='101.37.246.001', # 數(shù)據(jù)庫(kù)地址
port= 3306, # 數(shù)據(jù)庫(kù)端口，mysql一般默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 登錄密碼
database='movie_cat', # 要連接的數(shù)據(jù)庫(kù)名
charset='utf8') # 數(shù)據(jù)庫(kù)編碼，一般默認(rèn)utf8
with connect.cursor() as cursor: # 創(chuàng)建游標(biāo) # 創(chuàng)建游標(biāo)
sql = 'SELECT * FROM movie2 '
cursor.execute(sql) # 執(zhí)行sql語(yǔ)句
4、執(zhí)行sql語(yǔ)句后，需要調(diào)用 fetchone() 或 fetchall() 方法來(lái)獲得查詢的返回結(jié)果
- fetchone(): 該方法獲取第一個(gè)查詢結(jié)果集。結(jié)果集是一個(gè)對(duì)象，連續(xù)多次執(zhí)行將依次取得下一條結(jié)果，直到為空；
- fetchall():?接收全部的返回結(jié)果行
import pymysql
connect = pymysql.connect(host='101.37.246.001', # 數(shù)據(jù)庫(kù)地址
port= 3306, # 數(shù)據(jù)庫(kù)端口，mysql一般默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 登錄密碼
database='movie_cat', # 要連接的數(shù)據(jù)庫(kù)名
charset='utf8') # 數(shù)據(jù)庫(kù)編碼，一般默認(rèn)utf8
# cursorclass=pymysql.cursors.DictCursor 設(shè)置游標(biāo)返回結(jié)果為字典
with connect.cursor() as cursor: # 創(chuàng)建游標(biāo)
sql = 'SELECT * FROM movie2 '
cursor.execute(sql) # 執(zhí)行sql語(yǔ)句
data = cursor.fetchall() # 接收全部返回的結(jié)果，返回一個(gè)元祖類型
print(f"數(shù)據(jù)庫(kù)查詢數(shù)據(jù)：{data}")
print(type(data))
connect.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接
數(shù)據(jù)庫(kù)查詢數(shù)據(jù)：((1, 'My Neighbor Totoro', '1988'), (2, 'Dead Poets Society', '1989'), (3, 'A Perfect World', '1993'), (4, 'Leon', '1994'), (5, 'Mahjong', '1996'), (6, 'Swallowtail Butterfly', '1996'), (7, 'King of Comedy', '1999'), (8, 'Devils on the Doorstep', '1999'), (9, 'WALL-E', '2008'), (10, 'The Pork of Music', '2012'), (12, 'huawei', '2020'))
<class 'tuple'>二、mysql數(shù)據(jù)庫(kù)更新數(shù)據(jù)（UPDATE）?
import pymysql
connect = pymysql.connect(host='101.37.246.001', # 數(shù)據(jù)庫(kù)地址
port= 3306, # 數(shù)據(jù)庫(kù)端口，mysql一般默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 登錄密碼
database='movie_cat', # 要連接的數(shù)據(jù)庫(kù)名
charset='utf8') # 數(shù)據(jù)庫(kù)編碼，一般默認(rèn)utf8
with connect.cursor() as cursor: # 創(chuàng)建游標(biāo)
sql = 'UPDATE movie2 SET year = 1998 WHERE id = 1 '
cursor.execute(sql) # 執(zhí)行sql語(yǔ)句
connect.commit() # 提交數(shù)據(jù)到數(shù)據(jù)庫(kù)
connect.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接在cursor( ) 創(chuàng)建游標(biāo)后通過execute( ) 執(zhí)行sql，需要通過connect實(shí)例調(diào)用commit( ) 進(jìn)行數(shù)據(jù)提交
三、mysql數(shù)據(jù)庫(kù)插入數(shù)據(jù)（INSERT)
import pymysql
connect = pymysql.connect(host='101.37.246.001', # 數(shù)據(jù)庫(kù)地址
port= 3306, # 數(shù)據(jù)庫(kù)端口，mysql一般默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 登錄密碼
database='movie_cat', # 要連接的數(shù)據(jù)庫(kù)名
charset='utf8') # 數(shù)據(jù)庫(kù)編碼，一般默認(rèn)utf8
with connect.cursor() as cursor: # 創(chuàng)建游標(biāo)
sql = "INSERT INTO movie2(title, year) VALUES ('firstday', '2021');"
cursor.execute(sql) # 執(zhí)行sql語(yǔ)句
connect.commit() # 提交數(shù)據(jù)到數(shù)據(jù)庫(kù)
connect.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接?四、mysql數(shù)據(jù)庫(kù)刪除數(shù)據(jù)（DELETE）
import pymysql
connect = pymysql.connect(host='101.37.246.001', # 數(shù)據(jù)庫(kù)地址
port= 3306, # 數(shù)據(jù)庫(kù)端口，mysql一般默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 登錄密碼
database='movie_cat', # 要連接的數(shù)據(jù)庫(kù)名
charset='utf8') # 數(shù)據(jù)庫(kù)編碼，一般默認(rèn)utf8
with connect.cursor() as cursor: # 創(chuàng)建游標(biāo)
sql = "DELETE FROM movie2 WHERE id = 13;"
cursor.execute(sql) # 執(zhí)行sql語(yǔ)句
connect.commit() # 提交數(shù)據(jù)到數(shù)據(jù)庫(kù)
connect.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接注：insert/delete/update后都需要調(diào)用commit( )提交數(shù)據(jù)到數(shù)據(jù)庫(kù)，完成事務(wù)提交
封裝一個(gè)數(shù)據(jù)庫(kù)操作類
class ConnectDB:
config = {
"host":'47.103.126.208',
"user":'siyuan',
"password":'123456',
"database":'mall',
"charset":'utf8',
#"cursorclass":"pymysql.cursors.DictCursor"
}
def __init__(self):
self.connect = pymysql.connect(**self.config)
def select_datas(self, sql):
with self.connect.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute(sql)
data = cur.fetchall()
print(data)
print(type(data))
return data
def charge_datas(self, sql):
pass
def connect_close(self):
self.connect.close()
def __call__(self, act=None, sql=None, connect=True):
if connect:
if act == 'select':
datas = self.select_datas(sql)
return datas
elif act in ['update', 'insert', 'delete']:
self.charge_datas(sql)
return self
else:
self.connect_close()
if __name__ == '__main__':
connect_db = ConnectDB()
sql = "SELECT * FROM ls_user WHERE nickname LIKE '%思源%';"
data1 = connect_db('select', sql)
data2 = connect_db('select', sql)
connect_db(connect=False)?文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-821314.html
?
?
?
?
?
?
?
?
?
到了這里，關(guān)于Python基礎(chǔ)之?dāng)?shù)據(jù)庫(kù)操作的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
