天天看點
傳回

Python 深入淺出 - PyMySQL 操作 MySQL 資料庫

不知不覺 2017 就已經結束了，2018 元旦快樂。

回顧 2017 ，真是碌碌無為；希望 2018 勿忘初心，好好努力，早日實作新年願望：

  • 提升自身技術，堅持把 Java 後端技術學好；
  • 輕松購買 MBP，而不會覺得價格貴；
  • 努力賺錢，買車；
  • 妹子；

祝自己好運~~~~

Python 的資料庫接口标準時 Python DB-API，大多數 Python 資料庫接口都遵循這個标準。為了相容 Python 3，本文使用 PyMySQL 庫進行操作 MySQL 資料庫。

Python DB-API 使用流程

  1. 引入 API 子產品
  2. 擷取與資料庫的連接配接
  3. 執行 SQL 語句和存儲過程。
  4. 關閉資料庫連接配接

PyMySQL 介紹

PyMySQL 是 Python 連接配接 MySQL 資料庫伺服器的接口，他實作了 Python 資料庫 API v2.0,并包含了一個純 Python 的 MySQL 用戶端庫。

PyMySQL參考文檔：http://pymysql.readthedocs.io

安裝 PyMySQL

C:\Users\Administrator> pip install PyMySQL

資料庫簡單操作

連接配接資料庫之前，先使用 MySQL 用戶端 SQLyog 手動建立好資料庫和表：

# 建立資料庫
CREATE DATABASE db_study

# 建立表
CREATE TABLE tb_student
(
    id INT NOT NULL AUTO_INCREMENT,
    `name` CHAR() NOT NULL,
    gender CHAR() DEFAULT NULL,
    age INT DEFAULT NULL,
    score INT DEFAULT NULL,
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8

此例查詢資料庫版本号：

# 0. import db
import pymysql
# 1. open database connection
db = pymysql.connect('localhost','root','root','db_study')

# 2. prepare a cursor object using cursor() method
cursor  = db.cursor()

# 3. execute SQL query using execute() method
cursor.execute('SELECT VERSION()')

# 4. fetch a single row using fetchone() method
version = cursor.fetchone()

print('database version : %s' % version)

# 5. disconnect from db server
db.close()

輸出結果：

database version :

insert 操作 

# 0. import db
import pymysql
# 1. open database connection
db = pymysql.connect('localhost','root','root','db_study')

# 2. prepare a cursor object using cursor() method
cursor  = db.cursor()

# 3. execute SQL command
sql = "insert into tb_student(name,gender,age,score) values('mike','F',21,90 )"
print('sql = %s' % sql)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

print('insert sucess')

# 4. disconnect from db server
db.close()

輸出結果：

sql = insert into tb_student(name,gender,age,score) values('mike','F',, )
insert sucess

select 操作

遊标 Cursor 對象的屬性和方法 描述
fetchone() 它用來擷取使用遊标對象來查詢表時，傳回的結果集對象中的下一行資料
fetchall() 它用來擷取使用遊标對象來查詢表時，傳回的結果集對象中的所有行資料
rowcount 隻讀屬性，傳回 execute() 方法影響的行數 
# 0. import db
import pymysql
# 1. open database connection
db = pymysql.connect('localhost','root','root','db_study')

# 2. prepare a cursor object using cursor() method
cursor  = db.cursor()

# 3. execute SQL command
sql_select = 'SELECT * FROM tb_student'
print(sql_select)
try:
    cursor.execute(sql_select)
    res  = cursor.fetchone()
    # cursor.scroll(0,'absolute')  # scroll 移動遊标的位置,mode = 'relative' 或 'absolute'
    print('fetchone ===> id = %s,name = %s,gender = %s,age = %d,score = %d ' % (res[],res[],res[],res[],res[]))
    print('rowcount = %d' % cursor.rowcount)
    result = cursor.fetchall()
    for row in result:
        id = row[]
        name = row[]
        gender = row[]
        age = row[]
        score = row[]
        print('id = %s,name = %s,gender = %s,age = %d,score = %d' % (id,name,gender,age,score))
except:
    import traceback
    traceback.print_exc()
    print('execute error')

# 4. disconnect from db server
db.close()

輸出結果：

SELECT * FROM tb_student
fetchone ===> id = ,name = mike,gender = F,age = ,score =  
rowcount = 
id = ,name = jane,gender = M,age = ,score =

資料庫表中是有兩條資料，但是上面代碼中先使用了 cursor.fetchone() 擷取一個資料，再使用 cursor.fetchall() 擷取所有行資料，導緻擷取所有行時，隻擷取了剩下行的資料，這是因為在 fetchone() 之後，遊标已經處于第 1 行的位置上，而不是第 0 行，要想 fetchall() 擷取所有行資料，則需要将遊标重新移動到第 0 行位置上，即使用 cursor.scroll(0,’absolute’) ,放開上面代碼注釋即可。

輸出結果：

SELECT * FROM tb_student
fetchone ===> id = ,name = mike,gender = F,age = ,score =  
rowcount = 
id = ,name = mike,gender = F,age = ,score = 
id = ,name = jane,gender = M,age = ,score =

update 操作

将 age = 24 的記錄的 score 增加 5 分

# 0. import db
import pymysql
# 1. open database connection
db = pymysql.connect('localhost','root','root','db_study')

# 2. prepare a cursor object using cursor() method
cursor  = db.cursor()
# 3. execute SQL command
sql_update = 'UPDATE tb_student SET score = score + 5 WHERE age = 24 '
print('sql_update = %s' % sql_update)
try:
    cursor.execute(sql_update)
    db.commit()
    print('update success')
except:
    db.rollback()
    print('update error')
# 4. disconnect from db server
db.close()

操作後的結果：

delete 操作 

# 0. import db
import pymysql

# 1. open database connection
db = pymysql.connect('localhost', 'root', 'root', 'db_study')

# 2. prepare a cursor object using cursor() method
cursor = db.cursor()

# 3. execute SQL command
# delete row
sql_delete = 'DELETE FROM tb_student WHERE age < 24'
print('sql_delete = %s' % sql_delete)
try:
    cursor.execute(sql_delete)
    db.commit()
    print('delete success')
except:
    db.rollback()
    print('delete error')
print('delete rowcount = %d' % cursor.rowcount)

# query all row
sql_select = 'SELECT * FROM tb_student'
print('sql_select = %s' % sql_select)
try:
    cursor.execute(sql_select)
    print('select rowcount = %d' % cursor.rowcount)
    result = cursor.fetchall()
    for row in result:
        id = row[]
        name = row[]
        gender = row[]
        age = row[]
        score = row[]
        print('id = %s,name = %s,gender = %s,age = %d,score = %d' % (id, name, gender, age, score))
except:
    import traceback
    traceback.print_exc()
    print('execute error')

# 4. disconnect from db server
db.close()

輸出結果：

sql_delete = DELETE FROM tb_student WHERE age < 
delete success
delete rowcount = 
sql_select = SELECT * FROM tb_student
select rowcount = 
id = ,name = jane,gender = M,age = ,score =

執行事務

事務是確定資料一緻性的一種機制，事務具有四大屬性：

  • 原子性： 指事務包含的所有操作要麼全部成功，要麼全部失敗復原。
  • 一緻性： 事務應確定資料庫的狀态從一個一緻狀态轉變成另一個一緻狀态，一緻狀态的含義是資料庫中的資料應滿足完整性限制。
  • 隔離性 : 多個事務并發執行時，一個事務的執行不應影響其他事務的執行。
  • 持久性： 一個事務一旦送出，它對資料庫的修改應該永久儲存在資料庫中。
操作 描述
db.commit() 送出事務
db.rollback() 復原事務

移動遊标

移動遊标操作 描述
cursor.scroll(1,mode = ‘relative’) 相對于目前位置移動
cursor.scroll(2,mode = ‘absolute’) 相對于絕對位置移動
深入淺出 Python Python
上一篇: java讀取中文亂碼解決方法
下一篇: pymysql執行有參數的in語句

繼續閱讀