最近需要用Python寫一個(gè)簡(jiǎn)易通訊錄，但是對(duì)于數(shù)據(jù)存儲(chǔ)很發(fā)愁。大家都知道，使用 Python 中的列表和字典進(jìn)行存儲(chǔ)數(shù)據(jù)是很不靠譜的，所以就想到Python有沒(méi)有內(nèi)置的數(shù)據(jù)庫(kù)模塊。
SQLite3簡(jiǎn)介
SQLite3 可使用 sqlite3 模塊與 Python 進(jìn)行集成。sqlite3 模塊是由 Gerhard Haring 編寫的。它提供了一個(gè)與 PEP 249 描述的 DB-API 2.0 規(guī)范兼容的 SQL 接口。您不需要單獨(dú)安裝該模塊，因?yàn)?Python 2.5.x 以上版本默認(rèn)自帶了該模塊。
為了使用 sqlite3 模塊，您首先必須創(chuàng)建一個(gè)表示數(shù)據(jù)庫(kù)的連接對(duì)象，然后您可以有選擇地創(chuàng)建光標(biāo)對(duì)象，這將幫助您執(zhí)行所有的 SQL 語(yǔ)句。
怎么樣，聽(tīng)起來(lái)不錯(cuò)吧！那就快來(lái)學(xué)習(xí)使用吧。
連接數(shù)據(jù)庫(kù)
下面的 Python 代碼顯示了如何連接到一個(gè)現(xiàn)有的數(shù)據(jù)庫(kù)。如果數(shù)據(jù)庫(kù)不存在，那么它就會(huì)被創(chuàng)建，最后將返回一個(gè)數(shù)據(jù)庫(kù)對(duì)象。
#-*- coding:utf-8 -*-import sqlite3conn = sqlite3.connect('mysql_person.db')print "Opened database successfully";在這里，您也可以把數(shù)據(jù)庫(kù)名稱復(fù)制為特定的名稱 :memory:，這樣就會(huì)在 RAM 中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)。現(xiàn)在，讓我們來(lái)運(yùn)行上面的程序，在當(dāng)前目錄中創(chuàng)建我們的數(shù)據(jù)庫(kù) mysql_person.db。您可以根據(jù)需要改變路徑。保存上面代碼到 sqlite.py 文件中，并按如下所示執(zhí)行。如果數(shù)據(jù)庫(kù)成功創(chuàng)建，那么會(huì)顯示下面所示的消息：
$chmod +x sqlite.py$./sqlite.pyOpen database successfully
創(chuàng)建表
下面的 Python 代碼段將用于在先前創(chuàng)建的數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)表：
#-*- coding:utf-8 -*-import sqlite3conn = sqlite3.connect('mysql_person.db')print "Opened database successfully";conn.execute('''CREATE TABLE MT (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);''')print "Table created successfully";conn.close()上述程序執(zhí)行時(shí)，它會(huì)在 test.db 中創(chuàng)建 MT 表，并顯示下面所示的消息：
Opened database successfullyTable created successfully
INSERT 操作
下面的 Python 程序顯示了如何在上面創(chuàng)建的 MT 表中創(chuàng)建記錄：
#-*- coding:utf-8 -*-import sqlite3conn = sqlite3.connect('mysql_person.db')print "Opened database successfully";conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) / VALUES (1, 'Paul', 32, 'California', 20000.00 )");conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) / VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) / VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) / VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");conn.commit()print "Records created successfully";conn.close()上述程序執(zhí)行時(shí)，它會(huì)在 MT 表中創(chuàng)建給定記錄，并會(huì)顯示以下兩行：
Opened database successfullyRecords created successfully
SELECT 操作
下面的 Python 程序顯示了如何從前面創(chuàng)建的 MT 表中獲取并顯示記錄：
#-*- coding:utf-8 -*-import sqlite3conn = sqlite3.connect('mysql_person.db')print "Opened database successfully";cursor = conn.execute("SELECT id, name, address, salary from MT")for row in cursor: print "ID = ", row[0] print "NAME = ", row[1] print "ADDRESS = ", row[2] print "SALARY = ", row[3], "/n"print "Operation done successfully";conn.close()上述程序執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：
Opened database successfullyID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000.0ID = 2NAME = AllenADDRESS = TexasSALARY = 15000.0ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000.0Operation done successfully
UPDATE 操作
下面的 Python 代碼顯示了如何使用 UPDATE 語(yǔ)句來(lái)更新任何記錄，然后從 COMPANY 表中獲取并顯示更新的記錄：
#-*- coding:utf-8 -*-import sqlite3conn = sqlite3.connect('mysql_person.db')print "Opened database successfully";conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")conn.commit()print "Total number of rows updated :", conn.total_changescursor = conn.execute("SELECT id, name, address, salary from MT")for row in cursor: print "ID = ", row[0] print "NAME = ", row[1] print "ADDRESS = ", row[2] print "SALARY = ", row[3], "/n"print "Operation done successfully";conn.close()上述程序執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：
Opened database successfullyTotal number of rows updated : 1ID = 1NAME = PaulADDRESS = CaliforniaSALARY = 25000.0ID = 2NAME = AllenADDRESS = TexasSALARY = 15000.0ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000.0Operation done successfully
DELETE 操作
下面的 Python 代碼顯示了如何使用 DELETE 語(yǔ)句刪除任何記錄，然后從 COMPANY 表中獲取并顯示剩余的記錄：
#-*- coding:utf-8 -*-import sqlite3conn = sqlite3.connect('mysql_person.db')print "Opened database successfully";conn.execute("DELETE from COMPANY where ID=2;")conn.commit()print "Total number of rows deleted :", conn.total_changescursor = conn.execute("SELECT id, name, address, salary from MT")for row in cursor: print "ID = ", row[0] print "NAME = ", row[1] print "ADDRESS = ", row[2] print "SALARY = ", row[3], "/n"print "Operation done successfully";conn.close()上述程序執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：
Opened database successfullyTotal number of rows deleted : 1ID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000.0ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000.0Operation done successfully
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持VEVB武林網(wǎng)。
新聞熱點(diǎn)
疑難解答
圖片精選
