第七章：Python之數(shù)據(jù)庫編程

copy_left 2021-02-02

展開全文

第一節(jié):數(shù)據(jù)庫API與全局變量及核心類基本流程

數(shù)據(jù)庫API

python DB API 2.0

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

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

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

核心API

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

操作數(shù)據(jù)庫的流程

第二節(jié)：案例實操-動態(tài)創(chuàng)建數(shù)據(jù)表

導入sqlite3模塊

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

執(zhí)行DDL創(chuàng)建數(shù)據(jù)庫

按照前面的步驟操作SQLite數(shù)據(jù)庫，只要用游標執(zhí)行DDL語句即可

import sqlite3# 1 打開數(shù)據(jù)庫鏈接# SQLite是一個沒有后臺進程的數(shù)據(jù)庫，磁盤上的一個文件就可以對應SQLite數(shù)據(jù)庫conn = sqlite3.connect('test.db')# 2 打開游標c = conn.cursor()# 3 使用游標的execute方法執(zhí)行任意的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 關閉數(shù)據(jù)庫鏈接conn.colse()

SQLite數(shù)據(jù)庫特性

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

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

第三節(jié)：使用SQLite Expert

下載安裝SQLite EXpert

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

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

使用SQLite EXpert創(chuàng)建數(shù)據(jù)庫

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

使用SQLite EXpert打開數(shù)據(jù)庫

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

第四節(jié)：執(zhí)行DML語句

執(zhí)行DML語句

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

import sqlite3# 創(chuàng)建數(shù)據(jù)庫conn = sqlite3.connect('test.db')# 獲取游標c = conn.cursor()# 執(zhí)行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',))# 執(zhí)行完DML語句之后，如果程序獲取被DML語句修改的記錄條數(shù)，可通過游標的rowcount來獲取print('受影響的記錄條數(shù)：' , c.rowcount)# 提交事務，使修改生效conn.commit()# 關閉資源c.close()conn.close()

重復執(zhí)行多次DML語句

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

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

第五節(jié)：執(zhí)行查詢

使用execute執(zhí)行查詢語句

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

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

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

第六節(jié)：案例實操-使用事務控制數(shù)據(jù)庫操作

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

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

import sqlite3# 創(chuàng)建數(shù)據(jù)庫conn = sqlite3.connect('test.db')# 獲取游標c = conn.cursor()# 如果游標只是執(zhí)行DDL語句，程序不需要顯示提交事務，程序所做的修改會自動生效# 如果程序先執(zhí)行DML語句# 執(zhí)行DML語句，事務開啟了，該游標后面所執(zhí)行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()

第七節(jié)：案例實操-用程序執(zhí)行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);

執(zhí)行SQL腳本

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

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

便捷方法：

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

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