Python3 MySQL 資料庫操作

什麼是 PyMySQL？

PyMySQL 是在 Python3.x 版本中用於連線 MySQL 伺服器的一個庫，Python2中則使用mysqldb。
PyMySQL 遵循 Python 資料庫 API v2.0 規範，幷包含了 pure-Python MySQL 客戶端庫。

PyMySQL 安裝

在使用 PyMySQL 之前，我們需要確保 PyMySQL 已安裝。
PyMySQL 下載地址：https://github.com/PyMySQL/PyMySQL。
如果還未安裝，我們可以使用以下命令安裝最新版的 PyMySQL：

pip install PyMySQL

程式碼連結：

https://github.com/TracyMcgrady6/pymsql_Operation

我的部落格連結：

http://mcgrady.cn/2017/11/07/Python3%20MySQL%20%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5/

資料庫連線

• 您已經建立了資料庫 database_name.
• 在TESTDB資料庫中您已經建立了表 EMPLOYEE
• EMPLOYEE表欄位為 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。
• 連線資料庫TESTDB使用的使用者名稱為 "username" ，密碼為 "password"。當然可以自己更改

例項：

connect.py

# coding:utf-8
import pymysql

# 開啟資料庫連線
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# 使用 cursor() 方法建立一個遊標物件 cursor
cursor = db.cursor()

# 使用 execute()  方法執行 SQL 查詢
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法獲取單條資料.
data = cursor.fetchone()

print("Database version : %s " % data)

# 關閉遊標
cursor.close()
# 關閉資料庫連線
db.close()

輸出結果：

Database version : 5.5.20-log

建立資料庫表

例項

create_table.py

# coding:utf-8
import pymysql

# 開啟資料庫連線
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# 使用 cursor() 方法建立一個遊標物件 cursor
cursor = db.cursor()

# 使用 execute() 方法執行 SQL，如果表存在則刪除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 使用預處理語句建立表
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)
# 關閉遊標
cursor.close()
# 關閉資料庫連線
db.close()

資料庫查詢

例項

query.py

# coding:utf-8
import pymysql

# 開啟資料庫連線
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# 使用cursor()方法獲取操作遊標
cursor = db.cursor()

# SQL 查詢語句
sql1 = "SELECT * FROM EMPLOYEE WHERE NAME = 'xiaoming'"
# SQL 查詢語句
sql2 = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % 1000

try:
    # 執行SQL語句
    cursor.execute(sql1)
    # results是個元組物件
    results = cursor.fetchone()
    # 先判斷是否為空
    if results is None:
        print("查詢為空")
    else:
        print(results)

    # 執行SQL語句
    cursor.execute(sql2)
    # 獲取所有記錄列表
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # 列印結果
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")
# 關閉遊標
cursor.close()
# 關閉資料庫連線
db.close()

資料庫插入

例項

insert.py

# coding:utf-8
import pymysql

# 開啟資料庫連線
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# 使用cursor()方法獲取操作遊標
cursor = db.cursor()

# SQL兩種插入格式
sql = "INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)"
sql2 = "INSERT INTO EMPLOYEE(FIRST_NAME, AGE, SEX) VALUES (%s,%s,%s)" % ('Mac', 20, 'boy')

try:
    # 執行sql語句
    cursor.execute(sql)
    # 提交到資料庫執行
    db.commit()
except:
    # 如果發生錯誤則回滾
    db.rollback()
# 關閉遊標
cursor.close()
# 關閉資料庫連線
db.close()

資料庫批量插入

有一個很cooooooooooool的功能就是批量操作executemany，可以進行多行插入
先寫sql語句。要注意的是裡面的引數，不管什麼型別，統一使用%s作為佔位符
例如，向user表(username,salt,pwd)插入資料

sql = 'INSERT INTO 表名 VALUES(%s,%s,%s)'

對應的param是一個tuple或者list

param = ((username1, salt1, pwd1), (username2, salt2, pwd2), (username3, salt3, pwd3))

這樣就包含了三條資料，通過executemany插入

n=cursor.executemany(sql,param)

例項

insert_batch.py

# coding:utf-8
import pymysql

# 開啟資料庫連線
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# 使用cursor()方法獲取操作遊標
cursor = db.cursor()

# SQL 插入語句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, AGE, SEX) VALUES (%s,%s,%s)"
# 一個tuple或者list
T = (('xiaoming', 31, 'boy'), ('hong', 22, 'girl'), ('wang', 90, 'man'))

try:
    # 執行sql語句
    cursor.executemany(sql, T)
    # 提交到資料庫執行
    db.commit()
except :
    # 如果發生錯誤則回滾
    db.rollback()
# 關閉遊標
cursor.close()
# 關閉資料庫連線
db.close()

資料庫修改更新

例項

# coding:utf-8
import pymysql

# 開啟資料庫連線
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# 使用cursor()方法獲取操作遊標
cursor = db.cursor()

# SQL 更新語句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % 'M'
try:
    # 執行SQL語句
    cursor.execute(sql)
    # 提交到資料庫執行
    db.commit()
except:
    # 發生錯誤時回滾
    db.rollback()

# 關閉遊標
cursor.close()
# 關閉資料庫連線
db.close()

資料庫刪除

例項

# coding:utf-8
import pymysql

# 開啟資料庫連線
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')
# 使用cursor()方法獲取操作遊標
cursor = db.cursor()

# SQL 刪除語句
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % 20
try:
    # 執行SQL語句
    cursor.execute(sql)
    # 提交修改
    db.commit()
except:
    # 發生錯誤時回滾
    db.rollback()

# 關閉遊標
cursor.close()
# 關閉資料庫連線
db.close()