一验烧、訪問SQLite數(shù)據(jù)庫
SQLite是一個開源關(guān)系型數(shù)據(jù)庫，具有零配置（Zero Configuration）帅矗、自我包含（Self-contained）和便于傳輸（Easy Transfer）等優(yōu)點基协，由于其高度便攜，使用方便波势、結(jié)構(gòu)緊湊、高效和可靠，因此被廣泛用于移動設備嵌入式數(shù)據(jù)庫作為前端數(shù)據(jù)存儲清寇。SQLite支持規(guī)范的SQL(Structured Query Language,結(jié)構(gòu)化查詢語言)，可方便地支持數(shù)據(jù)庫系統(tǒng)原型研發(fā)和移植护蝶。
SQLite將整個數(shù)據(jù)庫的表华烟、索引、數(shù)據(jù)都存儲在一個單一的.db文件中持灰，不需要網(wǎng)絡配置和管理垦江，沒有用戶賬戶和密碼，數(shù)據(jù)庫的訪問權(quán)限依賴于文件所在的操作系統(tǒng)搅方。這個小型的數(shù)據(jù)庫系統(tǒng)能夠支持事務比吭，具有原子性、一致性姨涡、隔離性和持久性衩藤，還能支持觸發(fā)器、復雜查詢涛漂，以及多進程并發(fā)訪問赏表。
關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)存放于多個二維表中检诗，在表中，行稱為記錄(record),列稱為字段(field)瓢剿。
一個數(shù)據(jù)庫中可以包含多個表逢慌。例如，在student.db數(shù)據(jù)庫中除包含基本情況表base外间狂，還包含成績表score等數(shù)據(jù)表攻泼。各表中都包含一個學號字段，通過學號可以建立兩個表乃至多個表之間的關(guān)聯(lián)關(guān)系鉴象，作為一個邏輯整體提供查詢應用忙菠。這樣，既避免了單個表之間龐大復雜纺弊，又增加了引用數(shù)據(jù)的靈活性牛欢，減少了數(shù)據(jù)的冗余。
-
1.1淆游、SQLite數(shù)據(jù)庫連接對象及表的SQL操作
SQLite3是Python的內(nèi)置庫傍睹，用import sqlite3 引用后，訪問SQLite數(shù)據(jù)庫通常需要經(jīng)歷如下的步驟：- 用
connect()創(chuàng)建數(shù)據(jù)庫連接對象conn犹菱。 - 若需要對表進行創(chuàng)建新表焰望、插入數(shù)據(jù)、修改或刪除數(shù)據(jù)操作已亥，可使用
conn.execute()方法熊赖，并使用conn.commit()提交事務。 - 若需要查詢操作虑椎，應先使用
conn.cursor()方法返回游標對象cur,然后通過執(zhí)行cur.execute()進行查詢震鹉。 - 調(diào)用
cur.fetchone()、cur.fetchmany()或cur.fetchall()方法返回查詢結(jié)果捆姜。 - 最后關(guān)閉
cur和conn對象传趾。
用 connect()函數(shù)可建立對已有數(shù)據(jù)庫文件的鏈接對象（下例中的 conn）,若不存在該數(shù)據(jù)庫文件，則新建該數(shù)據(jù)庫泥技。例如：在桌面建立一個空數(shù)據(jù)庫：test.db浆兰。
屏幕快照 2018-08-29 下午9.54.29.pngimport sqlite3 conn = sqlite3.connect('/Users/wangchong/Desktop/test.db')由于sqlite3不是可視化呈現(xiàn)的，因此可使用 Navicat for SQLite 珊豹、SQLite Expert簸呈、SQLite Studio、SQLiteTool等第三方工具協(xié)助管理數(shù)據(jù)庫店茶。
建立數(shù)據(jù)庫對象后蜕便，用數(shù)據(jù)庫連接對象的 execute （SQL 語句）方法可執(zhí)行SQL語句，對數(shù)據(jù)庫及表實現(xiàn)創(chuàng)建贩幻、插入轿腺、修改两嘴、刪除和查詢操作。SQL語句大小寫不敏感族壳，可分行憔辫，關(guān)鍵字之間可使用空格。在Python字符串的三重引號定符
'''的支持下仿荆，可將SQL語句分行呈現(xiàn)贰您，增加可讀性。成功創(chuàng)建數(shù)據(jù)庫后赖歌，應在其中合理的創(chuàng)建表枉圃。表結(jié)構(gòu)的設計是否合理功茴，對程序的運行效率至關(guān)重要庐冯。設計和創(chuàng)建表，主要應關(guān)注表中應包含哪些字段坎穿，每個字段的名字展父、數(shù)據(jù)類型和寬度。
-
SQLite3的表支持以下4種類型玲昧。
- 整數(shù)型（INTEGEER）: 有符號整數(shù)栖茉，按實際存儲大小自動存儲為1、2孵延、3吕漂、4、6或8字節(jié)尘应，通常不需要指定位數(shù)惶凝。
- 實數(shù)型（REAL）: 浮點數(shù)，以8字節(jié)指數(shù)形式存儲犬钢，可指定總位數(shù)和小數(shù)位數(shù)苍鲜。
- 文本型（TEXT）: 字符串，以數(shù)據(jù)庫編碼方式存儲（以UTF-8 支持漢字）玷犹。
- BLOB型： 二進制對象存儲混滔，通常用來保存圖片、視頻歹颓、XML等數(shù)據(jù)坯屿。
創(chuàng)建表的語句的通式為：
CREATE TABLE <表>(<字段元組>)SQL語句大小寫不敏感，但此處為與Python語句相區(qū)別巍扛，以大寫表示愿伴。設計表結(jié)構(gòu)時作為一種數(shù)據(jù)完整性約束，可指定某字段是否允許空电湘，若不允許為空隔节，可用 NOT NULL 關(guān)鍵字加以限制鹅经。在大多數(shù)表中，往往會指定一個非空且唯一的字段作為關(guān)鍵字（PRIMARY KRY,如學號）怎诫。為了便于快速檢索瘾晃，通常將表按主關(guān)鍵字建立索引。
-
例如1幻妓、在桌面建立一個如下的數(shù)據(jù)庫 test.db
學生基本情況表 base的數(shù)據(jù)結(jié)構(gòu)import sqlite3 conn = sqlite3.connect('/Users/wangchong/Desktop/test.db') c = conn.cursor() c.execute('''CREATE TABLE base \ (學號 TEXT(10) PRIMARY KEY NOT NULL, \ 姓名 TEXT(10) NOT NULL,\ 性別 TEXT(1) NOT NULL,\ 專業(yè) TEXT(6),\ 生源 TEXT(6),\ 身高 INTEGER,\ 電話 TEXT(6) )''') conn.commit() conn.close()與數(shù)據(jù)庫鏈接對象
conn.execute()方法相關(guān)的常用SQL語句通式如下：-
添加：
INSERT INTO <表>(<字段元組>) VALUES (<數(shù)據(jù)元組>) 如下： import sqlite3 conn = sqlite3.connect('/Users/wangchong/Desktop/test.db') c = conn.cursor() print ("Opened database successfully") c.execute("INSERT INTO base (學號,姓名,性別,專業(yè),生源,身高,電話) \ VALUES ('20','王','男','電氣工程及其自動化','本科',178,'18500652890')") conn.commit() print ("Records created successfully") conn.close() -
修改：
UPDATE <表> SET <字段>=<值> -
刪除：
DELETE FROM <表> WHERE <條件表達式>
-
-
例如2蹦误、編寫Python 程序為例1中創(chuàng)建的student 庫的base表添加新學生學號、姓名和性別三項非空數(shù)據(jù)肉津。
base表中添加數(shù)據(jù)import sqlite3 # 鏈接數(shù)據(jù)庫 conn = sqlite3.connect('/Users/wangchong/Desktop/test.db') while True: id = input('請輸入新生學號：（輸入0退出程序）\n') if id == '0': break name = input('請輸入新生的姓名：\n') gender = input('請輸入新生的性別：\n') # 格式化構(gòu)建 SQL 字符串 SQL = '''insert into base \ (學號,姓名,性別) values ('%s','%s','%s')''' % (id,name,gender) # 插入數(shù)據(jù) conn.execute(SQL) # 提交事務 conn.commit() conn.close()運行結(jié)果如下：
請輸入新生學號：（輸入0退出程序） 21 請輸入新生的姓名： 邵豪杰 請輸入新生的性別： 男 請輸入新生學號：（輸入0退出程序） 22 請輸入新生的姓名： 馬也 請輸入新生的性別： 女 請輸入新生學號：（輸入0退出程序）在格式化構(gòu)建SQL字符串時應該注意强胰，values 后面的數(shù)據(jù)元組應與前面的表達字段元組順序一直，且TEXT類型的數(shù)據(jù)要加單引號界定符妹沙。
- 用
-
1.2偶洋、游標對象和SQL查詢
與游標對象 cur.execute() 方法相關(guān)的SQL語句通式為：
SELECT [DISTINCT] <目標列表表達式> [AS <列名>] [,<目標列表達式> [AS <列名> ...] FROM <表名> [,<表名>...] [WHERE <條件表達式> [AND | OR <條件表達式>...] [GROUP BY 列名 [HAVING <條件表達式>> [ORDER BY 列名 [ASC | DESC>其中DISTINCT表示不包括重復行；
<目標列表達式>包含對目標列的 AVG距糖、COUNT玄窝、SUM、MIN悍引、MAX等聚合函數(shù)恩脂；
<GROUP BY 列名> 為對聚合函數(shù)查詢的分組；
[HAVING<條件表達式>] 為分組篩選的條件趣斤；
[ORDER BY 列名 [ASC | DESC>表示對查詢結(jié)果的排序俩块，ASC 為升序（默認），DESC為降序浓领。
執(zhí)行游標對象 cur.execute(<SELECT 查詢SQL語句>)后玉凯，用 cur.fetchall()或cur.fetchone() 可接收查詢結(jié)果。其中镊逝，cur.fetchall() 返回的是每條記錄為 一個元組作為列表元素的數(shù)據(jù)集列表壮啊，而cur.fetchone()則只返回第一條記錄的元組類型結(jié)果。-
例如3撑蒜、創(chuàng)建Python 程序?qū)η懊胬觿?chuàng)建的student 庫中根據(jù)所輸入的專業(yè)查詢學生學號歹啼、姓名和性別。
base表import sqlite3 conn = sqlite3.connect('/Users/wangchong/Desktop/test.db') while True: major = input('請輸入查詢專業(yè)：（輸入0退出程序）\n') if major == '0': break SQL="SELECT * from base where 專業(yè)='%s'" % major cur = conn.execute(SQL) list1=cur.fetchall() print('學號 ',' 姓名 ',' 性別 ',' 專業(yè) ') for rec in list1: print(rec[0],rec[1],rec[2],rec[3]) conn.close()運行的結(jié)果如下：
請輸入查詢專業(yè)：（輸入0退出程序） 電氣工程及其自動化 學號 姓名 性別 專業(yè) 20 王沖 男 電氣工程及其自動化 4 李云東 女 電氣工程及其自動化 請輸入查詢專業(yè)：（輸入0退出程序）
-
二座菠、訪問Access狸眼、MySQL 和 SQLServer數(shù)據(jù)庫
Python 可支持訪問不同的數(shù)據(jù)庫。但由于不同的數(shù)據(jù)庫及其服務的通信協(xié)議各有不同浴滴，早期版本訪問不同數(shù)據(jù)庫需要不同的代碼支持拓萌，二Python DB-API作為 Python標準數(shù)據(jù)庫接口的誕生，為Python數(shù)據(jù)庫應用提供了標準的編程接口升略，支持MySQL 微王、PostgreSQL屡限、Microsoft SQL Server 、Oracle 炕倘、Sybase 等常用數(shù)據(jù)庫钧大。即使所需鏈接的數(shù)據(jù)庫底層服務協(xié)議不同，也能夠標準的DB-API 接口實現(xiàn)訪問罩旋。
利用微軟操作系統(tǒng)對各種數(shù)據(jù)庫驅(qū)動的開放數(shù)據(jù)庫連接接口（ODBC啊央，Open Database Connecttivity） 也可以實現(xiàn)對數(shù)據(jù)庫的標準訪問。通過標準的 DB-API 訪問各類數(shù)據(jù)庫通常如上節(jié)訪問 SQLite 一樣涨醋，也需經(jīng)歷如下步驟：
1瓜饥、用connect創(chuàng)建數(shù)據(jù)庫連接對象 conn;
2、如果需要對表進行創(chuàng)建浴骂、插入數(shù)據(jù)乓土、修改或刪除數(shù)據(jù)、可使用 conn.execute()方法靠闭，并使用conn.commit()提交事務帐我。
3坎炼、如果需要查詢操作愧膀，應先使用 conn.cursor()方法返回游標對象 cur,然后通過執(zhí)行 cur.execute()進行查詢。
4谣光、調(diào)用 cur.fetchone()檩淋、cur.fetchmany()或cur.fetchall()方法返回查詢結(jié)果。
其中萄金，對不同類型的數(shù)據(jù)庫需要引用不同的標準庫蟀悦，用不同的語句創(chuàng)建鏈接對象。
-
2.1氧敢、用 ODBC 創(chuàng)建鏈接對象
對 Access 數(shù)據(jù)庫文件的訪問日戈，利用 Windows開放數(shù)據(jù)庫連接接口 ODBC 對文件、dBase孙乖、Foxpro浙炼、SQL Server 等微軟數(shù)據(jù)庫的訪問，均可預先在控制面板中建立 ODBC 數(shù)據(jù)源唯袄，如下：
ODBC數(shù)據(jù)源預先安裝 pyodbc 庫 弯屈，然后用下列語句建立數(shù)據(jù)庫連接對象：
import pyodbc conn= pyodbc.connect('DNS=student') -
2.2、對 MySQL 創(chuàng)建鏈接對象
MySQL 數(shù)據(jù)庫是近來流行的開源關(guān)系型數(shù)據(jù)庫服務恋拷，對其建立連接對象可預先安裝 PyMySQL庫资厉，然后用下列語句創(chuàng)建：import pymysql conn= pymysql.connect(host=服務器地址或域名,port=3306,user='root',passwd=密碼,db=數(shù)據(jù)庫名字) -
2.3、對 MS SQL Server 創(chuàng)建鏈接對象
MS SQL Server 數(shù)據(jù)庫是微軟主流的大型關(guān)系型數(shù)據(jù)庫服務蔬顾，對其建立連接對象可預先安裝 pymssql 庫 宴偿，然后用下列語句創(chuàng)建：import pymssql conn= pymssql.connect(host=<服務器地址或域名>,database=<數(shù)據(jù)庫名>,user=<用戶名>,password=<密碼>)數(shù)據(jù)庫連接對象建立后湘捎，對各類數(shù)據(jù)庫的訪問操作方法均與前面介紹的對SQLite的訪問操作類似。這里不再重復敘述窄刘。值的注意的是消痛，由于目前各類數(shù)據(jù)庫的編碼不統(tǒng)一，因此對中文查詢的支持尚不夠理想都哭。
-
例如4秩伞、用Python 程序分別在 ODBC 鏈接的 student.db、MySQL數(shù)據(jù)庫 student(數(shù)據(jù)庫地址 192.168.145.253欺矫，root 密碼為test) 和 MS SQL Server 數(shù)據(jù)庫 student （數(shù)據(jù)庫地址 192.168.145.253纱新，sa 密碼為 123456）的base表中，查詢身高（height）181cm以上的學生學號穆趴、姓名和性別脸爱。
# 1、用 obdc 連接數(shù)據(jù)庫（Access 等） # import pyodbc # conn=pyodbc.connect('DNS=student') # 2未妹、連接 MySQL 數(shù)據(jù)庫 import pymysql conn=pymysql.connect(host='192.168.145.253',port=3306,user='root',passwd='test',db='base') # 3簿废、連接 MS SQL Server 數(shù)據(jù)庫 # import pymssql # conn=pymssql.connect(host=".",database="student",user='sa',password='123456') cur = conn.cursor() cur.execute("SELECT * FROM BASE WHERE HEIGHT>181") list1=cur.fetchall() print('學號 ',' 姓名 ',' 性別 ',' 專業(yè)') for rec in list1: print(rec[0],rec[1],rec[2],rec[3]) conn.close()
