快捷導(dǎo)航

Python SQLite3簡介

更新時間：2018年02月22日 11:40:51 作者：MTbaby

這篇文章主要為大家詳細介紹了Python SQLite3的簡單介紹以及使用方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下

最近需要用Python寫一個簡易通訊錄，但是對于數(shù)據(jù)存儲很發(fā)愁。大家都知道，使用 Python 中的列表和字典進行存儲數(shù)據(jù)是很不靠譜的，所以就想到Python有沒有內(nèi)置的數(shù)據(jù)庫模塊。

SQLite3簡介

SQLite3 可使用 sqlite3 模塊與 Python 進行集成。sqlite3 模塊是由 Gerhard Haring 編寫的。它提供了一個與 PEP 249 描述的 DB-API 2.0 規(guī)范兼容的 SQL 接口。您不需要單獨安裝該模塊，因為 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ù)庫。現(xià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í)行時，它會在 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í)行時，它會在 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í)行時，它會產(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í)行時，它會產(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í)行時，它會產(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

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: