作者 | CDA數(shù)據(jù)分析師

來(lái)源 | CDA數(shù)據(jù)分析研究院

本文涉及到的開(kāi)發(fā)環(huán)境：

• 操作系統(tǒng) Windows 10
• 數(shù)據(jù)庫(kù) MySQL 8.0
• Python 3.7.2
• pip 19.0.3

兩種方法進(jìn)行數(shù)據(jù)庫(kù)的連接分別是PyMySQL和mysql.connector

步驟：

1. 連接數(shù)據(jù)庫(kù)
2. 生成游標(biāo)對(duì)象
3. 執(zhí)行SQL語(yǔ)句
4. 關(guān)閉游標(biāo)
5. 關(guān)閉連接

PyMySQL

PyMySQL ： 是封裝了MySQL驅(qū)動(dòng)的Python驅(qū)動(dòng)，一個(gè)能使Python連接到MySQL的庫(kù)

環(huán)境要求：Python version >= 3.4

PyMySQL安裝

安裝

Win鍵+X鍵再按I鍵，調(diào)出Windows PowerShell窗口，輸入

pip install PyMySQL

回車(chē)

運(yùn)行結(jié)果如下則安裝成功

pip version ===19.0.3

查看版本

查看PyMySQL的版本，輸入

pip show PyMySQL

回車(chē)

利用PyMySQL連接數(shù)據(jù)庫(kù)

首先我們的MySQL數(shù)據(jù)庫(kù)已安裝，且已建好名為test的數(shù)據(jù)庫(kù)，其中有名為student的表

import pymysql

#連接數(shù)據(jù)庫(kù)

conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認(rèn)127.0.0.1

,user = 'root' # 用戶名

,passwd='password' # 密碼

,port= 3306 # 端口，默認(rèn)為3306

,db='test' # 數(shù)據(jù)庫(kù)名稱

,charset='utf8' # 字符編碼

)

cur = conn.cursor() # 生成游標(biāo)對(duì)象

sql="select * from `student` " # SQL語(yǔ)句

cur.execute(sql) # 執(zhí)行SQL語(yǔ)句

data = cur.fetchall() # 通過(guò)fetchall方法獲得數(shù)據(jù)

for i in data[:2]: # 打印輸出前2條數(shù)據(jù)

print (i)

cur.close() # 關(guān)閉游標(biāo)

conn.close() # 關(guān)閉連接

上述代碼中，實(shí)現(xiàn)了通過(guò)Python連接MySQL查詢所有的數(shù)據(jù)，并輸出前2條數(shù)據(jù)的功能。執(zhí)行結(jié)果如下：

('a', '趙大', '16')

('b', '錢(qián)二', '16')

mysql.connector

mysql-connector-python：是MySQL官方的純Python驅(qū)動(dòng)；

mysql.connector安裝

安裝

pip install mysql

查看版本

pip show mysql

利用 mysql.connector連接數(shù)據(jù)庫(kù)

首先我們的MySQL數(shù)據(jù)庫(kù)已安裝，且已建好名為test的數(shù)據(jù)庫(kù)，其中有名為student的表

import mysql.connector

conn=mysql.connector.connect(host = '127.0.0.1' # 連接名稱，默認(rèn)127.0.0.1

,user = 'root' # 用戶名

,passwd='password' # 密碼

,port= 3306 # 端口，默認(rèn)為3306

,db='test' # 數(shù)據(jù)庫(kù)名稱

,charset='utf8' # 字符編碼

)

cur = conn.cursor() # 生成游標(biāo)對(duì)象

sql="select * from `student` " # SQL語(yǔ)句

cur.execute(sql) # 執(zhí)行SQL語(yǔ)句

data = cur.fetchall() # 通過(guò)fetchall方法獲得數(shù)據(jù)

for i in data[:2]: # 打印輸出前2條數(shù)據(jù)

print (i)

cur.close() # 關(guān)閉游標(biāo)

conn.close() # 關(guān)閉連接

上述代碼中，實(shí)現(xiàn)了通過(guò)Python連接MySQL查詢所有的數(shù)據(jù)，并輸出前2條數(shù)據(jù)的功能。執(zhí)行結(jié)果如下：

('a', '趙大', '16')

('b', '錢(qián)二', '16')

Python對(duì)MySql數(shù)據(jù)庫(kù)實(shí)現(xiàn)增刪改查

接下來(lái)我們以用pymysql包為例，介紹一下如何用Python對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行增刪改查 。

增

import pymysql

#連接數(shù)據(jù)庫(kù)

conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認(rèn)127.0.0.1

,user = 'root' # 用戶名

,passwd='password' # 密碼

,port= 3306 # 端口，默認(rèn)為3306

,db='test' # 數(shù)據(jù)庫(kù)名稱

,charset='utf8' # 字符編碼

)

cur = conn.cursor() # 生成游標(biāo)對(duì)象

#=============插入語(yǔ)句===============================

sql= "INSERT INTO student VALUES ('p','魏六','17')"

#===================================================

try:

cur.execute(sql1) # 執(zhí)行插入的sql語(yǔ)句

conn.commit() # 提交到數(shù)據(jù)庫(kù)執(zhí)行

except:

coon.rollback()# 如果發(fā)生錯(cuò)誤則回滾

conn.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接

然后我們?cè)龠\(yùn)行查詢語(yǔ)句

import mysql.connector

conn=mysql.connector.connect(host = '127.0.0.1' # 連接名稱，默認(rèn)127.0.0.1

,user = 'root' # 用戶名

,passwd='password' # 密碼

,port= 3306 # 端口，默認(rèn)為3306

,db='test' # 數(shù)據(jù)庫(kù)名稱

,charset='utf8' # 字符編碼

)

cur = conn.cursor() # 生成游標(biāo)對(duì)象

sql="select * from `student` " # SQL語(yǔ)句

cur.execute(sql) # 執(zhí)行SQL語(yǔ)句

data = cur.fetchall() # 通過(guò)fetchall方法獲得數(shù)據(jù)

for i in data[:]: # 打印輸出所有數(shù)據(jù)

print (i)

cur.close() # 關(guān)閉游標(biāo)

conn.close() # 關(guān)閉連接

執(zhí)行結(jié)果就是

('b', '錢(qián)二', '16')

('c', '張三', '17')

('d', '李四', '17')

('e', '王五', '16')

('a', '趙大', '16')

('p', '魏六', '17')

刪

import pymysql

#連接數(shù)據(jù)庫(kù)

conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認(rèn)127.0.0.1

,user = 'root' # 用戶名

,passwd='password' # 密碼

,port= 3306 # 端口，默認(rèn)為3306

,db='test' # 數(shù)據(jù)庫(kù)名稱

,charset='utf8' # 字符編碼

)

cur = conn.cursor() # 生成游標(biāo)對(duì)象

#=============刪除語(yǔ)句===============================

sql = "DELETE FROM student WHERE `學(xué)號(hào)` = "a"

#===================================================

try:

cur.execute(sql) # 執(zhí)行插入的sql語(yǔ)句

conn.commit() # 提交到數(shù)據(jù)庫(kù)執(zhí)行

except:

coon.rollback()# 如果發(fā)生錯(cuò)誤則回滾

conn.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接

改

import pymysql

#連接數(shù)據(jù)庫(kù)

conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認(rèn)127.0.0.1

,user = 'root' # 用戶名

,passwd='password' # 密碼

,port= 3306 # 端口，默認(rèn)為3306

,db='test' # 數(shù)據(jù)庫(kù)名稱

,charset='utf8' # 字符編碼

)

cur = conn.cursor() # 生成游標(biāo)對(duì)象

#=============刪除語(yǔ)句===============================

sql ="UPDATE student SET `學(xué)員姓名` = '歐陽(yáng)' WHERE `學(xué)號(hào)` = 'b' "

#===================================================

try:

cur.execute(sql) # 執(zhí)行插入的sql語(yǔ)句

conn.commit() # 提交到數(shù)據(jù)庫(kù)執(zhí)行

except:

coon.rollback()# 如果發(fā)生錯(cuò)誤則回滾

conn.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接

查

import pymysql

#連接數(shù)據(jù)庫(kù)

conn=pymysql.connect(host = '127.0.0.1' # 連接名稱，默認(rèn)127.0.0.1

,user = 'root' # 用戶名

,passwd='password' # 密碼

,port= 3306 # 端口，默認(rèn)為3306

,db='test' # 數(shù)據(jù)庫(kù)名稱

,charset='utf8' # 字符編碼

)

cur = conn.cursor() # 生成游標(biāo)對(duì)象

#=============刪除語(yǔ)句===============================

sql="select * from `student` " # SQL語(yǔ)句

#====================================================

try:

cur.execute(sql) # 執(zhí)行插入的sql語(yǔ)句

data = cur.fetchall()

for i in data[:]:

print (i)

conn.commit() # 提交到數(shù)據(jù)庫(kù)執(zhí)行

except:

coon.rollback()# 如果發(fā)生錯(cuò)誤則回滾

conn.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接

小型案例

import pymysql

config = {

'host': '127.0.0.1',

'port': 3306,

'user': 'root',

'passwd': 'password',

'charset':'utf8',

}

conn = pymysql.connect(**config)

cursor = conn.cursor()

try:

# 創(chuàng)建數(shù)據(jù)庫(kù)

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)

#創(chuàng)建表

TABLE_NAME = 'bankData'

cursor.execute('CREATE TABLE %s(id int primary key,money int(30))' %TABLE_NAME)

# 批量插入紀(jì)錄

values = []

for i in range(20):

values.append((int(i),int(156*i)))

cursor.executemany('INSERT INTO bankData values(%s,%s)',values)

conn.commit()

# 查詢數(shù)據(jù)條目

count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)

print ('total records:{}'.format(cursor.rowcount))

# 獲取表名信息

desc = cursor.description

print ("%s %3s" % (desc[0][0], desc[1][0]))

cursor.scroll(10,mode='absolute')

results = cursor.fetchall()

for result in results:

print (result)

except:

import traceback

traceback.print_exc()

# 發(fā)生錯(cuò)誤時(shí)會(huì)滾

conn.rollback()

finally:

# 關(guān)閉游標(biāo)連接

cursor.close()

# 關(guān)閉數(shù)據(jù)庫(kù)連接

conn.close()

綜合案例

FIFA球員信息系統(tǒng)

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 = host

self.user = user

self.password = password

self.port = port

self.database = database

self.charset = charset

# 數(shù)據(jù)庫(kù)連接方法:

def open(self):

self.db = connect(host=self.host, user=self.user,

password=self.password, port=self.port,

database=self.database,

charset=self.charset)

# 游標(biāo)對(duì)象

self.cur = self.db.cursor()

# 數(shù)據(jù)庫(kù)關(guān)閉方法:

def close(self):

self.cur.close()

self.db.close()

# 數(shù)據(jù)庫(kù)執(zhí)行操作方法:

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()

# 數(shù)據(jù)庫(kù)查詢所有操作方法:

def Search(self, sql):

try:

self.open()

self.cur.execute(sql)

result = self.cur.fetchall()

return result

except Exception as e:

print("Failed", e)

self.close()

def Insert():#如何從外面將數(shù)據(jù)錄入到sql語(yǔ)句中

ID = int(input("請(qǐng)輸入球員編號(hào)："))

people_name = input("請(qǐng)輸入球員名字：")

PAC = int(input("請(qǐng)輸入速度評(píng)分："))

DRI = int(input("請(qǐng)輸入盤(pán)帶評(píng)分："))

SHO = int(input("請(qǐng)輸入射門(mén)評(píng)分："))

DEF = int(input("請(qǐng)輸入防守評(píng)分："))

PAS = int(input("請(qǐng)輸入傳球評(píng)分："))

PHY = int(input("請(qǐng)輸入身體評(píng)分："))

score =(PAC+DRI+SHO+DEF+PAS+PHY)/6

sql_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_insert

def Project():

print("球員的能力評(píng)分有：")

list=['速度','盤(pán)帶','射門(mén)','防守','傳球','身體','綜合']

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.查找數(shù)據(jù),其中order by 是為了按什么順序輸出，asc 是升序輸出，desc降序輸出

input_date=input("請(qǐng)選擇您想要以什么格式輸出：默認(rèn)升序排列1.球員編號(hào)，2.速度，3.盤(pán)帶，4.射門(mén)， 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("請(qǐng)重新輸入！")

result = date.Search(sql_search)

print(" 編號(hào) 姓名 速度 盤(pán)帶 射門(mén) 防守 傳球 身體 綜合 ")

for str in result:

print(str)

Start()

elif num=="3":

Project()

Start()

elif num=="4":

del_num=input("請(qǐng)輸入您要?jiǎng)h除球員的編號(hào)：")

sql_delete="delete from FIFA where id=%s"%del_num

date.Operation(sql_delete)

print("刪除成功！")

Start()

elif num=="5":