Python連接MySQL資料庫方法介紹(超詳細!手把手項目案例操作)

2020-12-05 CDA數據分析師

作者 | CDA數據分析師 來源 | CDA數據分析研究院

本文涉及到的開發環境：

作業系統 Windows 10資料庫 MySQL 8.0Python 3.7.2pip 19.0.3兩種方法進行資料庫的連接分別是PyMySQL和mysql.connector

步驟：

連接資料庫生成遊標對象執行SQL語句關閉遊標關閉連接PyMySQL

PyMySQL ： 是封裝了MySQL驅動的Python驅動，一個能使Python連接到MySQL的庫

環境要求：Python version >= 3.4

PyMySQL安裝

安裝

Win鍵+X鍵再按I鍵，調出Windows PowerShell窗口，輸入

pip install PyMySQL

回車

運行結果如下則安裝成功

pip version ===19.0.3

查看版本

查看PyMySQL的版本，輸入

pip show PyMySQL

回車

利用PyMySQL連接資料庫

首先我們的MySQL資料庫已安裝，且已建好名為test的資料庫，其中有名為student的表

import pymysql#連接資料庫conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認127.0.0.1 ,user = 'root' # 用戶名,passwd='password' # 密碼,port= 3306 # 埠，默認為3306,db='test' # 資料庫名稱,charset='utf8' # 字符編碼)cur = conn.cursor() # 生成遊標對象sql="select * from `student` " # SQL語句cur.execute(sql) # 執行SQL語句data = cur.fetchall() # 通過fetchall方法獲得數據for i in data[:2]: # 列印輸出前2條數據print (i)cur.close() # 關閉遊標conn.close() # 關閉連接

上述代碼中，實現了通過Python連接MySQL查詢所有的數據，並輸出前2條數據的功能。執行結果如下：

('a', '趙大', '16')('b', '錢二', '16')

mysql.connector

mysql-connector-python：是MySQL官方的純Python驅動；

mysql.connector安裝

安裝

pip install mysql

查看版本

pip show mysql

利用 mysql.connector連接資料庫

首先我們的MySQL資料庫已安裝，且已建好名為test的資料庫，其中有名為student的表

import mysql.connectorconn=mysql.connector.connect(host = '127.0.0.1' # 連接名稱，默認127.0.0.1 ,user = 'root' # 用戶名,passwd='password' # 密碼,port= 3306 # 埠，默認為3306,db='test' # 資料庫名稱,charset='utf8' # 字符編碼)cur = conn.cursor() # 生成遊標對象sql="select * from `student` " # SQL語句cur.execute(sql) # 執行SQL語句data = cur.fetchall() # 通過fetchall方法獲得數據for i in data[:2]: # 列印輸出前2條數據print (i)cur.close() # 關閉遊標conn.close() # 關閉連接

上述代碼中，實現了通過Python連接MySQL查詢所有的數據，並輸出前2條數據的功能。執行結果如下：

('a', '趙大', '16')('b', '錢二', '16')

Python對MySql資料庫實現增刪改查

接下來我們以用pymysql包為例，介紹一下如何用Python對資料庫中的數據進行增刪改查 。

增

import pymysql#連接資料庫conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認127.0.0.1 ,user = 'root' # 用戶名,passwd='password' # 密碼,port= 3306 # 埠，默認為3306,db='test' # 資料庫名稱,charset='utf8' # 字符編碼)cur = conn.cursor() # 生成遊標對象 #=============插入語句===============================sql= "INSERT INTO student VALUES ('p','魏六','17')"#===================================================try:cur.execute(sql1) # 執行插入的sql語句conn.commit() # 提交到資料庫執行except:coon.rollback()# 如果發生錯誤則回滾conn.close() # 關閉資料庫連接

然後我們再運行查詢語句

import mysql.connectorconn=mysql.connector.connect(host = '127.0.0.1' # 連接名稱，默認127.0.0.1 ,user = 'root' # 用戶名,passwd='password' # 密碼,port= 3306 # 埠，默認為3306,db='test' # 資料庫名稱,charset='utf8' # 字符編碼)cur = conn.cursor() # 生成遊標對象sql="select * from `student` " # SQL語句cur.execute(sql) # 執行SQL語句data = cur.fetchall() # 通過fetchall方法獲得數據for i in data[:]: # 列印輸出所有數據print (i)cur.close() # 關閉遊標conn.close() # 關閉連接

執行結果就是

('b', '錢二', '16')('c', '張三', '17')('d', '李四', '17')('e', '王五', '16')('a', '趙大', '16')('p', '魏六', '17')

刪

import pymysql#連接資料庫conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認127.0.0.1 ,user = 'root' # 用戶名,passwd='password' # 密碼,port= 3306 # 埠，默認為3306,db='test' # 資料庫名稱,charset='utf8' # 字符編碼)cur = conn.cursor() # 生成遊標對象 #=============刪除語句===============================sql = "DELETE FROM student WHERE `學號` = "a"#===================================================try:cur.execute(sql) # 執行插入的sql語句conn.commit() # 提交到資料庫執行except:coon.rollback()# 如果發生錯誤則回滾conn.close() # 關閉資料庫連接

改

import pymysql#連接資料庫conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認127.0.0.1 ,user = 'root' # 用戶名,passwd='password' # 密碼,port= 3306 # 埠，默認為3306,db='test' # 資料庫名稱,charset='utf8' # 字符編碼)cur = conn.cursor() # 生成遊標對象 #=============刪除語句===============================sql ="UPDATE student SET `學員姓名` = '歐陽' WHERE `學號` = 'b' "#===================================================try:cur.execute(sql) # 執行插入的sql語句conn.commit() # 提交到資料庫執行except:coon.rollback()# 如果發生錯誤則回滾conn.close() # 關閉資料庫連接

查

import pymysql#連接資料庫conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認127.0.0.1 ,user = 'root' # 用戶名,passwd='password' # 密碼,port= 3306 # 埠，默認為3306,db='test' # 資料庫名稱,charset='utf8' # 字符編碼)cur = conn.cursor() # 生成遊標對象 #=============刪除語句===============================sql="select * from `student` " # SQL語句#====================================================try:cur.execute(sql) # 執行插入的sql語句data = cur.fetchall()for i in data[:]: print (i)conn.commit() # 提交到資料庫執行except:coon.rollback()# 如果發生錯誤則回滾conn.close() # 關閉資料庫連接

小型案例

import pymysqlconfig = {'host': '127.0.0.1','port': 3306,'user': 'root','passwd': 'password','charset':'utf8',}conn = pymysql.connect(**config)cursor = conn.cursor()try:# 創建資料庫DB_NAME = 'test_3'cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)conn.select_db(DB_NAME)#創建表TABLE_NAME = 'bankData'cursor.execute('CREATE TABLE %s(id int primary key,money int(30))' %TABLE_NAME)# 批量插入紀錄values = []for i in range(20):values.append((int(i),int(156*i)))cursor.executemany('INSERT INTO bankData values(%s,%s)',values)conn.commit()# 查詢數據條目count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)print ('total records:{}'.format(cursor.rowcount))# 獲取表名信息desc = cursor.descriptionprint ("%s %3s" % (desc[0][0], desc[1][0]))cursor.scroll(10,mode='absolute')results = cursor.fetchall()for result in results:print (result)except:import tracebacktraceback.print_exc()# 發生錯誤時會滾conn.rollback()finally:# 關閉遊標連接cursor.close()# 關閉資料庫連接conn.close()

綜合案例

FIFA球員信息系統

from pymysql import *class Mysqlpython:def __init__(self, database='test', host='127.0.0.1', user="root",password='password', port=3306, charset="utf8"):self.host = hostself.user = userself.password = passwordself.port = portself.database = database self.charset = charset# 資料庫連接方法:def open(self):self.db = connect(host=self.host, user=self.user,password=self.password, port=self.port,database=self.database,charset=self.charset)# 遊標對象self.cur = self.db.cursor()# 資料庫關閉方法:def close(self):self.cur.close()self.db.close()# 資料庫執行操作方法:def Operation(self, sql):try:self.open()self.cur.execute(sql)self.db.commit()print("ok")except Exception as e:self.db.rollback()print("Failed", e)self.close()# 資料庫查詢所有操作方法:def Search(self, sql):try:self.open()self.cur.execute(sql)result = self.cur.fetchall()return resultexcept Exception as e:print("Failed", e)self.close()def Insert():#如何從外面將數據錄入到sql語句中ID = int(input("請輸入球員編號："))people_name = input("請輸入球員名字：")PAC = int(input("請輸入速度評分："))DRI = int(input("請輸入盤帶評分："))SHO = int(input("請輸入射門評分："))DEF = int(input("請輸入防守評分："))PAS = int(input("請輸入傳球評分："))PHY = int(input("請輸入身體評分："))score =(PAC+DRI+SHO+DEF+PAS+PHY)/6sql_insert = "insert into FIFA(ID, people_name, PAC,DRI,SHO,DEF, PAS, PHY, score) values(%d,'%s',%d,%d,%d,%d,%d,%d,%d)"%(ID, people_name, PAC,DRI,SHO,DEF, PAS, PHY, score)print(people_name)return sql_insertdef Project():print("球員的能力評分有：")list=['速度','盤帶','射門','防守','傳球','身體','綜合']print(list)def Exit():print("歡迎下次使用!!!")exit()def Search_choice(num):date = Mysqlpython()date.open()if num=="2":# 1.增加操作sql_insert = Insert()date.Operation(sql_insert)print("添加成功！")Start()elif num=="1":# 2.查找數據,其中order by 是為了按什麼順序輸出，asc 是升序輸出，desc降序輸出input_date=input("請選擇您想要以什麼格式輸出：默認升序排列1.球員編號，2.速度，3.盤帶，4.射門， 5.防守， 6.傳球， 7.身體 ， 8.綜合 ")if input_date=="1":sql_search = "select * from FIFA order by ID asc"elif input_date=="2":sql_search = "select * from FIFA order by PAC asc"elif input_date=="3":sql_search = "select * from FIFA order by DRI asc"elif input_date=="4":sql_search = "select * from FIFA order by SHO asc"elif input_date=="5":sql_search = "select * from FIFA order by DEF asc"elif input_date=="6":sql_search = "select * from FIFA order by PAS asc"elif input_date=="7":sql_search = "select * from FIFA order by PHY asc" elif input_date=="8":sql_search = "select * from FIFA order by PHY score" else:print("請重新輸入！")result = date.Search(sql_search)print(" 編號 姓名 速度 盤帶 射門 防守 傳球 身體 綜合 ")for str in result:print(str)Start()elif num=="3":Project()Start()elif num=="4":del_num=input("請輸入您要刪除球員的編號：")sql_delete="delete from FIFA where id=%s"%del_numdate.Operation(sql_delete)print("刪除成功！")Start()elif num=="5":Exit()else:print("輸入有誤，請重新輸入！")def Start():print("********************************************************")print("* 歡迎來到FIFA球員信息系統 *")print("*1.查看球員信息 2.球員信息錄入 *")print("*3.球員能力 4.刪除球員信息 *")print("*5.退出系統 *")print("********************************************************")choice = input("請輸入您的選擇：")Search_choice(choice)if __name__=="__main__":Start()

銀行轉帳系統

先建立資料庫test_3和表bankdata

import pymysqlconfig = {'host': '127.0.0.1','port': 3306,'user': 'root','passwd': 'password','charset':'utf8',}conn = pymysql.connect(**config)cursor = conn.cursor()try:# 創建資料庫DB_NAME = 'test_3'cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)conn.select_db(DB_NAME)#創建表TABLE_NAME = 'bankData'cursor.execute('CREATE TABLE %s(id int primary key,money int(30))' %TABLE_NAME)# 批量插入紀錄values = []for i in range(20):values.append((int(i),int(156*i)))cursor.executemany('INSERT INTO bankData values(%s,%s)',values)conn.commit()# 查詢數據條目count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)print ('total records:{}'.format(cursor.rowcount))# 獲取表名信息desc = cursor.descriptionprint ("%s %3s" % (desc[0][0], desc[1][0]))cursor.scroll(10,mode='absolute')results = cursor.fetchall()for result in results:print (result)except:import tracebacktraceback.print_exc()# 發生錯誤時會滾conn.rollback()finally:# 關閉遊標連接cursor.close()# 關閉資料庫連接conn.close()

構建系統

import pymysqlclass TransferMoney(object):# 構造方法def __init__(self, conn):self.conn = connself.cur = conn.cursor()def transfer(self, source_id, target_id, money):if not self.check_account_avaialbe(source_id):raise Exception("帳戶不存在")if not self.check_account_avaialbe(target_id):raise Exception("帳戶不存在")if self.has_enough_money(source_id, money):try:self.reduce_money(source_id, money)self.add_money(target_id, money)except Exception as e:print("轉帳失敗:", e)self.conn.rollback()else:self.conn.commit()print("%s給%s轉帳%s金額成功" % (source_id, target_id, money))def check_account_avaialbe(self, acc_id):"""判斷帳號是否存在， 傳遞的參數是銀行卡號的id"""select_sqli = "select * from bankData where id=%d;" % (acc_id)print("execute sql:", select_sqli)res_count = self.cur.execute(select_sqli)if res_count == 1:return Trueelse:# raise Exception("帳戶%s不存在" %(acc_id))return Falsedef has_enough_money(self, acc_id, money):"""判斷acc_id帳戶上金額> money"""# 查找acc_id存儲金額?select_sqli = "select money from bankData where id=%d;" % (acc_id)print("execute sql:", select_sqli)self.cur.execute(select_sqli) # ((1, 500), )# 獲取查詢到的金額錢數;acc_money = self.cur.fetchone()[0]# 判斷if acc_money >= money:return Trueelse:return Falsedef add_money(self, acc_id, money):update_sqli = "update bankData set money=money+%d where id=%d" % (money, acc_id)print("add money:", update_sqli)self.cur.execute(update_sqli)def reduce_money(self, acc_id, money):update_sqli = "update bankData set money=money-%d where id=%d" % (money, acc_id)print("reduce money:", update_sqli)self.cur.execute(update_sqli)# 析構方法def __del__(self):self.cur.close()self.conn.close()if __name__ == '__main__':# 1. 連接資料庫，conn = pymysql.connect(host = '127.0.0.1' # 連接名稱，默認127.0.0.1 ,user = 'root' # 用戶名,passwd='password' # 密碼,port= 3306 # 埠，默認為3306,db='test_3' # 資料庫名稱,charset='utf8',autocommit=True, # 如果插入數據，自動提交給資料庫 )trans = TransferMoney(conn) trans.transfer(15, 12, 200)

相關焦點

  • python3.8操作(插入,刪除)mysql/MariaDB資料庫
    01主題大家好,我是義縣遊學電子科技.今天來跟大家說一個工作中常用到的操作,python3.8操作MariaDB資料庫.因為MariaDB屬於mysql分支因此資料庫命令語句都是通用的非常方便.mysql/Mariadb的資料庫驅動包mysql-connector,使用命令pip install mysql-connector即可.要在管理員權限下打開cmd窗口進行哦!
  • eclipse如何使用JDBC連接mysql資料庫
    eclipse如何使用JDBC連接mysql資料庫1.在新建的Project中右鍵新建Floder2.創建名為lib的包3.創建完畢之後的工程目錄4.接下來解壓你下載的mysql的jar包，拷貝其中的.jar文件5.在工程lib
  • Python視頻教程網課編程零基礎入門數據分析網絡爬蟲全套Python...
    基於liunx系統的python教程,課程裡也有liunx操作的詳細教學,不用擔心學習時不會操作liunx系統。 9發射子彈以及碰撞檢測 4部分視頻課程 1網絡編程 2多任務 3web伺服器v3.1 4python高級語法v3.1 5mysql資料庫v3.1 6mini-web框架v3.1 7html和css 8首頁布局案例和移動布局 9javascrtpt
  • 「技術文章」《Python 資料庫 GUI CGI編程》
    1.寫在前邊上一次，我們介紹了Python的入門的文章，今天我們就來介紹下Python的資料庫，GUI, CGI的編程，以及Python 2.x 與Python 3.x的區別。2.連接資料庫 Python 標準資料庫接口為 Python DB-API，MySQLdb 是用於Python連結Mysql資料庫的接口。
  • 基於MySQL資料庫應用開發實現嵌入式數控系統的設計
    本文就嵌入式數控系統中資料庫應用開發的具體實現過程，以及如何將資料庫應用程式向嵌入式數控系統硬體平臺的移植方法和對資料庫進行最小化裁剪的技巧上做了詳細介紹。 由於本課題開發的人機界面是利用GTK+圖形庫，因此系統中採用的基於MySQL資料庫應用開發，需要解決MySQL和GTK+相互融合的問題。
  • Mysql(Mariadb)資料庫主從複製
    Mysql主從複製的實現原理圖大致如下：MySQL之間數據複製的基礎是以二進位日誌文件（binary log file）來實現的，一臺MySQL資料庫一旦啟用二進位日誌後，其作為master，它資料庫中所有操作都會以「事件」的方式記錄在二進位日誌中，其他資料庫作為slave通過一個I/O線程與主伺服器保持通信，並監控master的二進位日誌文件的變化，如果發現
  • 實踐分享:ACCESS資料庫導入mysql資料庫
    作為程式設計師很多時候要進行不同資料庫之間的導入導出工作，這樣是為了節省數據錄入的時間。下面我就和大家分享一下如何把數據從ACCESS資料庫導入mysql資料庫。首先說明一下思路，可以導入mysql的方法有很多，但不包過access直接導入，網上有很多介紹CSV格式導入mysql的，但操作很不方便，經常發生錯誤而無法導入。sql語句導入是最簡單的。那有沒有辦法把access資料庫轉換成sql語句呢。
  • MySQL資料庫常見的出錯代碼及出錯信息
    本文介紹的MySQL資料庫的出錯代碼表，依據MySQL資料庫頭文件mysql/include/mysqld_error.h整理而成。詳細內容請大家參考下文： 1005：創建表失敗 1006：創建資料庫失敗 1007：資料庫已存在，創建資料庫失敗 1008：資料庫不存在，刪除資料庫失敗 1009：不能刪除資料庫文件導致刪除資料庫失敗 1010：不能刪除數據目錄導致刪除資料庫失敗 1011：刪除資料庫文件失敗 1012：不能讀取系統表中的記錄
  • mysql/mariadb資料庫在查詢結果中再次查詢篩選的操作方法
    大家好，這是由義縣遊學電子科技提供的關於編程開發和嵌入式方面的實用教程，其中有一些技術方案和一些處理問題的解決方法，希望可以為您提供參考依據.今天是2020年4月9日，我跟大家分享一個二次操作mysql資料庫查詢結果的方法.我以資料庫 mariadb為例進行說明.因為它有個heidiSQL圖形管理工具，比較好操作.資料庫 mariadb為例進行說明.因為它有個heidiSQL圖形管理工具，比較好操作.
  • python海量數據快速查詢的技巧
    在實際工作中，經常會遇到查詢的任務，比如根據某些rs號，檢索dbsnp資料庫，提取這些snp位點的信息，對於這樣的任務，最基本的操作方法是將資料庫的內容存為字典，然後檢索特定的key即可。對於小文件而言，這樣的操作編碼簡單，運行速度也比較滿意，但是對於大型資料庫而言，將資料庫存為字典這個動作是非常耗費時間的，而且每次運行代碼都要執行這樣的操作，導致效率大大降低。想要改善這一狀況，有以下兩種解決辦法1.
  • Python開發簡單爬蟲【學習資料總結】
    語言基礎：（帶你熟悉python語言的特性，學會使用python開發環境，使用python開發一些簡單的案例） （1）Python3入門，數據類型，字符串 （2）判斷/循環語句，函數， （3）類與對象，繼承，多態 （4）tkinter界面編程
  • 大數據挖掘——(六):MySql資料庫
    mysql資料庫MySQL 是最流行的關係型資料庫管理系統，在WEB應用方面 MySQL 是最好的RDBMS(Relational Database Management System：關係資料庫管理系統)應用軟體之一,MySQL 是開源的，並且支持多種語言，包括C、C++、Python、Java、
  • 0064 關係資料庫的概念和安裝使用MySQL
    MySQL資料庫介紹MySQL是一種關係資料庫管理系統，關係資料庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並提高了靈活性。MySQL所使用的 SQL 語言是用於訪問資料庫的最常用標準化語言。
  • 如何在ubuntu20.04安裝MySQL並修改資料庫密碼
    sudo apt install mysql-client-core-83、再次輸入mysql命令，提示不能連接上mysql server，由提示可知mysql服務端沒有安裝；輸入命令sudo apt-get
  • 簡單介紹自動化運維工具clip
    下面來詳細介紹以下clip這款自動換運維工具。首先，傳統伺服器管理方式與String管理方式的相比，String管理方式的3點優勢：1) 傳統為IP管理方式，IP由4組無意義的數字組成，比較難記憶。最後，我們再來看一下應用案例 ，來比較一下傳統方式vs clip管理方式差異：傳統方式：在 A 模塊的100臺伺服器上，執行uptime命令，具體的操作步驟如下：1) 找到要同步的A模塊ip列表；2) 編寫腳本與ip列表中的伺服器建立連接；3) 連接伺服器時輸入帳號密碼；4) 帳號密碼認證成功後拷貝文件；
  • mysql 矩陣類型專題及常見問題 - CSDN
    mysql資料庫是為了方便對數據進行存儲以及管理的。失敗回滾的操作事務，將不能對事務有任何影響。一致性是指事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態，也就是說一個事務執行之前和執行之後都必須處於一致性狀態。隔離性是指當多個用戶並發訪問資料庫時，比如同時訪問一張表，資料庫每一個用戶開啟的事務，不能被其他事務所做的操作幹擾(也就是事務之間的隔離)，多個並發事務之間，應當相互隔離。
  • 多表組合查詢——Python操作Mysql資料庫
    前面我們介紹了單張表的查詢，包括模糊查詢、分組、排序、各種篩選條件等等操作，在實際應用中，查詢的數據往往不止局限在一張表裡，通常需要多張表在一起進行組合查詢，今天我們將會對Mysql當中的多張有關聯的表進行組合查詢，本節內容涉及到的幾個關鍵點有：等值和非等值連接查詢；
  • mysql 版本號解釋_mysql workbench查詢mysql版本號 - CSDN
    下面分別介紹一下這些表的結構和內容：user權限表：記錄允許連接到伺服器的用戶帳號信息，裡面的權限是全局級的。db權限表：記錄各個帳號在各個資料庫上的操作權限。table_priv權限表：記錄數據表級的操作權限。columns_priv權限表：記錄數據列級的操作權限。
  • DTCC:MySQl核心代碼開發經驗揭示
    【IT168資訊】由IT168(ITPUB、IXPUB、ChinaUnix)主辦的2012中國資料庫技術大會(DTCC)於(以下簡稱大會)2012年4月13日~15日在北京永泰福朋喜來登大酒店隆重召開。大會將針對大數據架構設計、資料庫安全、分布式資料庫、商業智能、NoSQL、Hadoop等多個重點話題進行深入探討。
  • MySQL、SqlServer、Oracle三種資料庫區別在哪裡?
    ，需要技術含量較高；三、MySql優點：體積小、速度快、總體擁有成本低，開源； 支持多種作業系統； 是開源資料庫，提供的接口支持多種語言連接操作當客戶與MySql伺服器連接時，他們之間所有的口令傳送被加密，而且MySql支持主機認證； 支持ODBC for Windows， 支持所有的ODBC 2.5函數和其他許多函數， 可以用Access連接MySql伺服器， 使得應用被擴展； 支持大型的資料庫， 可以方便地支持上千萬條記錄的資料庫。