Python標準庫14 資料庫 (sqlite3)

ii_chengzi發表於2019-10-20
Python資料庫SQLite

作者：Vamei 出處：http://www.cnblogs.com/vamei 歡迎轉載，也請保留這段宣告。謝謝！

 

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

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

 

建立資料庫

我首先來建立資料庫，以及資料庫中的表。在使用connect()連線資料庫後，我就可以通過定位指標cursor，來執行SQL命令：

# By Vamei
import sqlite3

# test.db is a file in the working directory.
conn = sqlite3.connect("test.db")

c = conn.cursor()

# create tables
c.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 changes
conn.commit()

# close the connection with the database
conn.close()

SQLite的資料庫是一個磁碟上的檔案，如上面的test.db，因此整個資料庫可以方便的移動或複製。test.db一開始不存在，所以SQLite將自動建立一個新檔案。

利用execute()命令，我執行了兩個SQL命令，建立資料庫中的兩個表。建立完成後，儲存並斷開資料庫連線。

 

插入資料

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

# By Vamei

import sqlite3

conn = sqlite3.connect("test.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 placeholder
c.execute("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])

# execute multiple commands
c.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)

conn.commit()
conn.close()

插入資料同樣可以使用execute()來執行完整的SQL語句。SQL語句中的引數，使用"?"作為替代符號，並在後面的引數中給出具體值。這裡不能用Python的格式化字串，如"%s"，因為這一用法容易受到SQL隱碼攻擊。

我也可以用executemany()的方法來執行多次插入，增加多個記錄。每個記錄是表中的一個元素，如上面的books表中的元素。

 

查詢

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

# By Vamei

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()

# retrieve one record
c.execute('SELECT name FROM category ORDER BY sort')
print(c.fetchone())
print(c.fetchone())

# retrieve all records as a list
c.execute('SELECT * FROM book WHERE book.category=1')
print(c.fetchall())

# iterate through the records
for row in c.execute('SELECT name, price FROM book ORDER BY sort'):
    print(row)

 

更新與刪除

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

# By Vamei

conn = 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')

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

 

總結

sqlite3只是一個SQLite的介面。想要熟練的使用SQLite資料庫，還需要學習更多的關係型資料庫的知識。

 

歡迎繼續閱讀“Python快速教程”

相關文章