您好,登錄后才能下訂單哦!
小編給大家分享一下Python中SQLite3的示例分析,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
SQLite3簡介
SQLite3 可使用 sqlite3 模塊與 Python 進(jìn)行集成。sqlite3 模塊是由 Gerhard Haring 編寫的。它提供了一個與 PEP 249 描述的 DB-API 2.0 規(guī)范兼容的 SQL 接口。您不需要單獨(dú)安裝該模塊,因?yàn)?Python 2.5.x 以上版本默認(rèn)自帶了該模塊。
為了使用 sqlite3 模塊,您首先必須創(chuàng)建一個表示數(shù)據(jù)庫的連接對象,然后您可以有選擇地創(chuàng)建光標(biāo)對象,這將幫助您執(zhí)行所有的 SQL 語句。
怎么樣,聽起來不錯吧!那就快來學(xué)習(xí)使用吧。
連接數(shù)據(jù)庫
下面的 Python 代碼顯示了如何連接到一個現(xiàn)有的數(shù)據(jù)庫。如果數(shù)據(jù)庫不存在,那么它就會被創(chuàng)建,最后將返回一個數(shù)據(jù)庫對象。
#-*- coding:utf-8 -*- import sqlite3 conn = sqlite3.connect('mysql_person.db') print "Opened database successfully";
在這里,您也可以把數(shù)據(jù)庫名稱復(fù)制為特定的名稱 :memory:,這樣就會在 RAM 中創(chuàng)建一個數(shù)據(jù)庫?,F(xiàn)在,讓我們來運(yùn)行上面的程序,在當(dāng)前目錄中創(chuàng)建我們的數(shù)據(jù)庫 mysql_person.db。您可以根據(jù)需要改變路徑。保存上面代碼到 sqlite.py 文件中,并按如下所示執(zhí)行。如果數(shù)據(jù)庫成功創(chuàng)建,那么會顯示下面所示的消息:
$chmod +x sqlite.py $./sqlite.py Open database successfully
創(chuàng)建表
下面的 Python 代碼段將用于在先前創(chuàng)建的數(shù)據(jù)庫中創(chuàng)建一個表:
#-*- coding:utf-8 -*- import sqlite3 conn = 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í),它會在 test.db 中創(chuàng)建 MT 表,并顯示下面所示的消息:
Opened database successfully Table created successfully
INSERT 操作
下面的 Python 程序顯示了如何在上面創(chuàng)建的 MT 表中創(chuàng)建記錄:
#-*- coding:utf-8 -*- import sqlite3 conn = 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í),它會在 MT 表中創(chuàng)建給定記錄,并會顯示以下兩行:
Opened database successfully Records created successfully
SELECT 操作
下面的 Python 程序顯示了如何從前面創(chuàng)建的 MT 表中獲取并顯示記錄:
#-*- coding:utf-8 -*- import sqlite3 conn = 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í),它會產(chǎn)生以下結(jié)果:
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
UPDATE 操作
下面的 Python 代碼顯示了如何使用 UPDATE 語句來更新任何記錄,然后從 COMPANY 表中獲取并顯示更新的記錄:
#-*- coding:utf-8 -*- import sqlite3 conn = 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_changes 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í),它會產(chǎn)生以下結(jié)果:
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
DELETE 操作
下面的 Python 代碼顯示了如何使用 DELETE 語句刪除任何記錄,然后從 COMPANY 表中獲取并顯示剩余的記錄:
#-*- coding:utf-8 -*- import sqlite3 conn = 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_changes 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í),它會產(chǎn)生以下結(jié)果:
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
以上是“Python中SQLite3的示例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對大家有所幫助,如果還想學(xué)習(xí)更多知識,歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。