Python學習筆記21：資料庫操作(sqlite3)，pythonsqlite3

Python學習筆記21：資料庫操作(sqlite3)，pythonsqlite3
Python內建一個輕量級的關係型資料庫SQLite。這一資料庫使用SQL語言。
SQLite作為後端資料庫，可以搭配Python建網站，或者製作有資料存放區需求的工具。
SQLite還在其它領域有廣泛的應用，比如HTML5和移動端。Python標準庫中的sqlite3提供該資料庫的介面。


一 資料庫設計我將建立一個簡單的關係型資料庫，為一個書店儲存書的分類和價格。
資料庫中包含兩個表：category用於記錄分類，book用於記錄某個書的資訊。
一本書歸屬於某一個分類，因此book有一個外鍵(foreign key)，指向catogory表的主鍵id。

【category】id int,sort int,name text【book】id int,sort int,name text,price real,category int

二 建立資料庫首先建立資料庫，以及資料庫中的表。
在使用connect()連結資料庫後，可以通過定位指標cursor，來執行SQL語句。

import sqlite3# data.db is a db file in the working directory.conn = sqlite3.connect("data.db")c = conn.cursor()# create tablesc.execute("'CREATE TABLE category      (id int primary key,        sort int,        name text)'")c.execute("'CREATE TABLE book      (id int primary key,        sort int,        name text,        price real,        category int,       FOREIGN KEY (category) REFERENCES category(id))'")# save the changesconn.commit()# close the connection with the databaseconn.close()

SQLite的資料庫是一個磁碟上的檔案，如上面的data.db，因此整個資料庫可以方便的移動或複製。
data.db一開始不存在，所以SQLite將自動建立一個新檔案。
利用execute()命令，我執行了兩個SQL命令，建立資料庫中的兩個表。建立完成後，儲存並斷開資料庫連接。


三 插入資料上面建立了資料庫和表，確立了資料庫的抽象結構。下面將在同一資料庫中插入資料：

import sqlite3conn = sqlite3.connect("data.db")c = conn.cursor()books = [(1, 1, 'Cook Recipe', 3.12, 1),         (2, 3, 'Python Intro', 17.5, 2),         (3, 2, 'OS Intro', 13.6, 2),        ]# execute "INSERT" c.execute("INSERT INTO category VALUES (1, 1, 'kitchen')")# using the placeholderc.execute("INSERT INTO category VALUES (?, ?, ?)", (2, 2, 'computer')) # 第二個參數是元組，非列表# execute multiple commandsc.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)conn.commit()conn.close()

插入資料同樣可以使用execute()來執行完整的SQL語句。
SQL語句中的參數，使用"?"作為替代符號，並在後面的參數中給出具體值。
這裡不能用Python的格式化字串，如"%s"，因為這一用法容易受到SQL注入攻擊。
我也可以用executemany()的方法來執行多次插入，增加多個記錄。
每個記錄是表中的一個元素，如上面的books表中的元素。


四 查詢在執行查詢語句後，Python將返回一個迴圈器，包含有查詢獲得的多個記錄。
迴圈讀取，也可以使用sqlite3提供的fetchone()和fetchall()方法讀取記錄：

import sqlite3conn = sqlite3.connect('test.db')c = conn.cursor()# retrieve one recordc.execute('SELECT name FROM category ORDER BY sort')print(c.fetchone())print(c.fetchone())# retrieve all records as a listc.execute('SELECT * FROM book WHERE book.category=1')print(c.fetchall())# iterate through the recordsfor row in c.execute('SELECT name, price FROM book ORDER BY sort'):    print(row)

五 更新和刪除記錄

可以更新某個記錄，或者刪除記錄：

import sqlite3conn = sqlite3.connect("test.db")c = conn.cursor()c.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))c.execute('DELETE FROM book WHERE id=2')conn.commit()conn.close()

也可以直接刪除整張表：

c.execute('DROP TABLE book')

如果刪除data.db，那麼整個資料庫會被刪除。


sqlite3 怎選中要操作的資料庫?

sqlite3隻能操作資料庫檔案。它沒有像mysql那樣的資料庫的概念，它一個檔案就是一個資料庫，一個資料庫就是一個檔案
所以你開啟資料庫檔案之後，接下去就是操作“表”了，沒有“資料庫”了
 
怎在Linux下用C/C++語言操作資料庫sqlite3

1. 說明 這裡我們假設你已經編譯好了sqlite的庫檔案 : libsqlite3.a libsqlite3.la libsqlite3.so libsqlite3.so.0 libsqlite3.so.0.8.6 pkgconfig 和可執行檔 : sqlite3 我們再假設你的sqlite3的安裝目錄在 /usr/local/sqlite3 目錄下。 如果不是，我們可以這樣做，將你的安裝檔案複製到 /usr/local/sqlite3 這個目錄， 這樣我們好在下面的操作中更加統一，從而減少出錯的機率 例如：[root@localhost home]# cp -rf sqlite-3.3.8-ix86/ /usr/local/sqlite3 這裡假設 /home/sqlite-3.3.8-ix86/ 是你的安裝目錄，也就是說你的sqlite原來就是安裝在這裡 這樣之後，我們的sqlite3的庫檔案目錄是：/usr/local/sqlite3/lib 可執行檔 sqlite3 的目錄是： /usr/local/sqlite3/bin 標頭檔 sqlite3.h 的目錄是： /usr/local/sqlite3/include 好拉，現在開始我們的Linux下sqlite3編程之旅。 2. 開始 這裡我們現在進行一個測試。 現在我們來寫個C/C++程式，調用 sqlite 的 API 介面函數。 下面是一個C程式的例子，顯示怎麼使用 sqlite 的 C/C++ 介面. 資料庫的名字由第一個參數取得且第二個參數或更多的參數是 SQL 執行語句. 這個函數調用sqlite3_open() 在 16 行開啟資料庫,並且sqlite3_close() 在 25 行關閉資料庫連接。 [root@localhost temp]# vi opendbsqlite.c 按下i 鍵切換到輸入模式，輸入下列代碼:// name： opendbsqlite.c // This prog is used to test C/C++ API for sqlite3.It is very simple,ha! // Author : zieckey All rights reserved. // data : 2006/11/13 #include <stdio.h> #include <sqlite3.h> int main( void ) { sqlite3 *db=NULL; char *zErrMsg = 0; int rc; //開啟指定的資料庫檔案,如果不存在將建立一個同名的資料庫檔案 rc = sqlite3_open("zieckey.db", &db); if( rc ) { fprintf(stderr, "Can't open database: %s ", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } else printf("You have opened a sqlite3 database......餘下全文>>