第七章：Python之數據庫編程

copy_left 2021-02-02

展開全文

第一節:數據庫API與全局變量及核心類基本流程

數據庫API

python DB API 2.0

通過全局變量查看 DB API特性

全局變量用于判斷該數據庫模塊所支持的功能，通常有以下3個全局變量

apilevel：顯示數據庫模塊的API版本號
threadsafety：指定該數據庫模塊的線程安全等級
paramstyle：指定當SQL語句需要參數時，可以使用哪種風格（qmark、numeric、named）的參數

核心API

connect()函數：鏈接數據庫，返回數據庫鏈接
數據庫鏈接：用于打開游標，開啟或提交事務
游標：用于執行SQL語句，獲取執行結果

操作數據庫的流程

第二節：案例實操-動態創建數據表

導入sqlite3模塊

python自帶了sqlite數據庫和sqlite數據庫的API模塊，無需再安裝，如果是導入其他sqlite自身未帶有的模塊，就需要大家手動去安裝了
導入sqlite3模塊，通過全局變量可了解該模塊支持的特性

執行DDL創建數據庫

按照前面的步驟操作SQLite數據庫，只要用游標執行DDL語句即可

import sqlite3# 1 打開數據庫鏈接# SQLite是一個沒有后臺進程的數據庫，磁盤上的一個文件就可以對應SQLite數據庫conn = sqlite3.connect('test.db')# 2 打開游標c = conn.cursor()# 3 使用游標的execute方法執行任意的SQL語句（DDL）c.execute('''    craete table user_tb(        _id integer primary key autoincrement,         name text,        pass text,        age interger)''')c.execute('''    craete table order_tb(        _id integer primary key autoincrement,         item_name text,        item_price real,        item_number integer,        user_id integer,        foreign key(user_id) references user_tb(_id))''')# 4 關閉游標c.close()# 5 關閉數據庫鏈接conn.colse()

SQLite數據庫特性

SQLite 內部只支持NULL 、INTEGER 、REAL(浮點型)、TEXT(文本)和BLOB(大二進制對象)這五種數據類型
SQLite允許輸入數據時忽略底層數據列實際的數據類型，因此在編寫建表語句時可以省略數據列后面的類型聲明

import sqlite3# 1 打開數據庫鏈接# SQLite是一個沒有后臺進程的數據庫，磁盤上的一個文件就可以對應SQLite數據庫conn = sqlite3.connect('test.db')# 2 打開游標c = conn.cursor()# 3 使用游標的execute方法執行任意的SQL語句（DDL）# 省略數據列后面的類型聲明c.execute('''    craete table user_tb(        _id integer primary key autoincrement,         name,        pass,        age)''')# 4 關閉游標c.close()# 5 關閉數據庫鏈接conn.colse()

第三節：使用SQLite Expert

下載安裝SQLite EXpert

登錄http://www./download.html下載軟件

安裝：跟安裝普通軟件相同，按照步驟安裝即可

使用SQLite EXpert創建數據庫

主界面左上角的第一個和第二個按鈕（創建內存中的數據庫）都可以創建數據庫
創建數據庫之后就可以創建數據表：選擇工具欄中的SQL->New SQL Tab

使用SQLite EXpert打開數據庫

單擊主界面工具條上第三個按鈕即可打開數據庫，打開數據庫之后，可查看該數據庫包含的數據表以及表中包含的數據

第四節：執行DML語句

執行DML語句

使用游標的execute()方法也可以執行語句的insert、update、delete語句
SQLite數據 API默認就是開啟事務，因此必須提交事務，否則程序對數據所做的修改(包括插入數據、刪除數據，整理數據)不會生效

import sqlite3# 創建數據庫conn = sqlite3.connect('test.db')# 獲取游標c = conn.cursor()# 執行SQL語句# 插入insert into tabnamec.execute('insert into user_tb values(null, ?, ?, ?)', ('fkjava', '33445', 23))c.execute('insert into user_tb values(null, ?, ?, ?)', ('crazyit', '35555', 25))c.execute('insert into order_tb values(null, ?, ?, ?, ?)', ('鼠標', 33, 3, 1))# 更新 update tabnamec.execute('update user_tb set pass=?', ('98765',))# 執行完DML語句之后，如果程序獲取被DML語句修改的記錄條數，可通過游標的rowcount來獲取print('受影響的記錄條數：' , c.rowcount)# 提交事務，使修改生效conn.commit()# 關閉資源c.close()conn.close()

重復執行多次DML語句

使用executemany()方法則可以將同一條DML語句重復執行多次
該方法的第二個參數包含幾個元組，該DML語句就會被執行幾次

import sqlite3# 創建數據庫conn = sqlite3.connect('test.db')# 獲取游標c = conn.cursor()# 執行SQL語句# 此處每個元組就代表一行數據c.executemany('insert into user_tb values(null, ?, ?, ?)',     (('悟空', '4444', 20),    ('八戒', '5555', 30),    ('沙僧', '6666', 40),    ('唐僧', '7777', 50)))# 提交事務，使修改生效conn.commit()# 關閉資源c.close()conn.close()

第五節：執行查詢

使用execute執行查詢語句

此時改為執行select語句，由于select語句執行完成后可以得到查詢結果，因此程序可通過游標的fetchone()、fetchmany(n)、fetchall()來獲取查詢結果，也可以直接將游標當成可迭代對象來獲取查詢結果

fetchone():返回一個元組，該元組代表一行數據
fetchmany(n)：返回一個長度小于等于n的列表，列表每個元素都是一個元組（每個元組代表一行數據）
fetchall()：盡量避免使用fetchall()來獲取查詢返回的全部記錄，原因是可能導致內存開銷過大，嚴重時可能導致系統崩潰

import sqlite3# 創建數據庫conn = sqlite3.connect('test.db')# 獲取游標c = conn.cursor()# 執行SQL語句c.execute('select * from user_td where _id > ?', (2,))# 所有查詢結果都通過游標來獲取# description屬性（元組）返回列信息# 如果要獲取查詢數據，fetchxxx或者直接迭代游標for col in c.description:    print(col[0], end='\t')print() #--------------------fetchone方法------------------   while True:     # 用fetchone每次獲取一條記錄    row = c.fetchone()    # 如果row為空，說明沒有數據    if not row:        break     else:         # 輸出該行內各個單元格的數據         for d in row:             print(col[0], end='\t')         print()#--------------------游標當成可迭代對象------------------ for row in c:    # 輸出該行內各個單元格的數據    for d in row:        print(col[0], end='\t')    print()# 關閉資源c.close()conn.close()

第六節：案例實操-使用事務控制數據庫操作

事務：事務由一步或者幾步數據庫操作序列組成的邏輯執行單元
事務具備的4個特性：原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)、持續性(Durability)，簡稱ACID
事務回滾兩種方式：顯示回滾和自動回滾

顯示回滾：調用數據庫連接對象的rollback
自動回滾：系統錯誤或者強行退出（退出之前沒有提交）

import sqlite3# 創建數據庫conn = sqlite3.connect('test.db')# 獲取游標c = conn.cursor()# 如果游標只是執行DDL語句，程序不需要顯示提交事務，程序所做的修改會自動生效# 如果程序先執行DML語句# 執行DML語句，事務開啟了，該游標后面所執行ddl語句也不會自動生效c.execute('insert into user_tb values(null, ?, ?, ?)', ('aaa', 'bbb', 23))# 因此這條DDL語句也不會自動生效c.execute('create table haha(_id integer primary key)')# 提交事務，上面的語句才能生效# conn.commit()# 顯示回滾：回滾事務，如果程序不提交事務，默認就會回滾,上面的語句不會生效# 自動回滾：沒有提交事務conn.rollback()# 關閉資源c.close()conn.close()

第七節：案例實操-用程序執行SQL腳本

編寫SQL腳本

多條SQL語句組成SQL腳本

insert into user_tb values(null, '張三', '11111', 23)insert into user_tb values(null, '李四', '22222', 24)insert into user_tb values(null, '小吳', '33333', 25)creat table test_td(_id integer primary key autoincrement,name text,pass text,description);creat table emp_td(_id integer primary key autoincrement,emp_name,emp_pass,emp_title);

執行SQL腳本

游標對象還包含一個executescript()方法，可執行一段SQL腳本，它并不是一個標準的API，但是大部分的數據庫API模塊中都有這個方法

import sqlite3# 創建數據庫conn = sqlite3.connect('test.db')# 獲取游標c = conn.cursor()# 打卡SQL腳本所在的文件with open('a.sql', 'r', True, 'UTF-8') as f:    # 讀取文件中的SQL語句    sql = f.read()    # 使用游標來執行SQL腳本，用executescript方法    # SQL腳本中的所有語句都會被執行    c.executescript(sql)    # 提交事務conn.commit()  # 關閉資源c.close()conn.close()

便捷方法：

execute(sql[, parameters]):執行一條SQL語句
executemany(sql[, parameters]):根據序列重復執行SQL語句
executescript(sql_script)：執行SQL腳本

import sqlite3# 創建數據庫conn = sqlite3.connect('test.db')# 打卡SQL腳本所在的文件with open('a.sql', 'r', True, 'UTF-8') as f:    # 讀取文件中的SQL語句    sql = f.read()    # 直接用數據庫連接對象來執行SQL腳本    c.executescript(sql)    # 提交事務conn.commit()  # 關閉資源conn.close()