Python SQLite 快速指南

Python SQLite - 簡介

SQLite3 可以使用 sqlite3 模組與 Python 整合，該模組由 Gerhard Haring 編寫。它提供了一個符合 PEP 249 中描述的 DB-API 2.0 規範的 SQL 介面。您無需單獨安裝此模組，因為它預設隨 Python 2.5.x 及更高版本一起提供。

要使用 sqlite3 模組，您必須首先建立一個代表資料庫的連線物件，然後您可以選擇建立一個遊標物件，這將幫助您執行所有 SQL 語句。

Python SQLite3 模組 API

以下是重要的 sqlite3 模組例程，足以滿足您從 Python 程式中使用 SQLite 資料庫的要求。如果您正在尋找更復雜的應用程式，則可以檢視 Python sqlite3 模組的官方文件。

序號	API & 描述
1	sqlite3.connect(database [,timeout ,其他可選引數]) 此 API 開啟與 SQLite 資料庫檔案的連線。您可以使用“:memory:”開啟與駐留在 RAM 中而不是磁碟上的資料庫的資料庫連線。如果資料庫成功開啟，它將返回一個連線物件。
2	connection.cursor([cursorClass]) 此例程建立一個遊標，它將在您使用 Python 進行資料庫程式設計的過程中使用。此方法接受單個可選引數 cursorClass。如果提供，則必須是擴充套件 sqlite3.Cursor 的自定義遊標類。
3	cursor.execute(sql [, 可選引數]) 此例程執行 SQL 語句。SQL 語句可以是引數化的（即使用佔位符而不是 SQL 字面量）。sqlite3 模組支援兩種型別的佔位符：問號和命名佔位符（命名樣式）。例如 − cursor.execute("insert into people values (?, ?)", (who, age))
4	connection.execute(sql [, 可選引數]) 此例程是遊標物件提供的上述 execute 方法的快捷方式，它透過呼叫 cursor 方法建立一箇中間遊標物件，然後使用給定的引數呼叫遊標的 execute 方法。
5	cursor.executemany(sql, seq_of_parameters) 此例程針對序列 sql 中找到的所有引數序列或對映執行 SQL 命令。
6	connection.executemany(sql[, parameters]) 此例程是一個快捷方式，它透過呼叫 cursor 方法建立一箇中間遊標物件，然後使用給定的引數呼叫 cursor.s executemany 方法。
7	cursor.executescript(sql_script) 此例程以指令碼形式一次執行多個 SQL 語句。它首先發出 COMMIT 語句，然後執行它作為引數獲得的 SQL 指令碼。所有 SQL 語句都應以分號 (;) 分隔。
8	connection.executescript(sql_script) 此例程是一個快捷方式，它透過呼叫 cursor 方法建立一箇中間遊標物件，然後使用給定的引數呼叫遊標的 executescript 方法。
9	connection.total_changes() 此例程返回自資料庫連線開啟以來已修改、插入或刪除的資料庫行的總數。
10	connection.commit() 此方法提交當前事務。如果您不呼叫此方法，則自上次呼叫 commit() 以來所做的任何操作都無法從其他資料庫連線中看到。
11	connection.rollback() 此方法回滾自上次呼叫 commit() 以來對資料庫的任何更改。
12	connection.close() 此方法關閉資料庫連線。請注意，這不會自動呼叫 commit()。如果您在首先呼叫 commit() 之前關閉資料庫連線，您的更改將會丟失！
13	cursor.fetchone() 此方法獲取查詢結果集的下一行，返回單個序列，或者當沒有更多資料可用時返回 None。
14	cursor.fetchmany([size = cursor.arraysize]) 此例程獲取查詢結果的下一組行，返回一個列表。當沒有更多行可用時，將返回一個空列表。該方法嘗試獲取 size 引數指示的儘可能多的行。
15	cursor.fetchall() 此例程獲取查詢結果的所有（剩餘）行，返回一個列表。當沒有行可用時，將返回一個空列表。

Python SQLite - 建立連線

要與 SQLite 建立連線，請開啟命令提示符，瀏覽到您安裝 SQLite 的位置，然後執行命令sqlite3，如下所示：

使用 Python 建立連線

您可以使用 SQLite3 Python 模組與 SQLite2 資料庫進行通訊。為此，首先需要建立連線（建立連線物件）。

要使用 Python 與 SQLite3 資料庫建立連線，您需要：

使用 import 語句匯入 sqlite3 模組。
connect() 方法接受您需要連線到的資料庫的名稱作為引數，並返回一個 Connection 物件。

示例

import sqlite3
conn = sqlite3.connect('example.db')

輸出

print("Connection established ..........")

Python SQLite - 建立表

使用 SQLite CREATE TABLE 語句，您可以在資料庫中建立表。

語法

以下是建立 SQLite 資料庫中表的語法：

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

示例

以下 SQLite 查詢/語句在 SQLite 資料庫中建立名為CRICKETERS的表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

讓我們再建立一個名為 OdiStats 的表，用於描述 CRICKETERS 表中每個球員的一日板球統計資料。

sqlite> CREATE TABLE ODIStats (
   First_Name VARCHAR(255),
   Matches INT,
   Runs INT,
   AVG FLOAT,
   Centuries INT,
   HalfCenturies INT 
);
sqlite>

您可以使用.tables命令在 SQLite 資料庫中獲取資料庫中表的列表。建立表後，如果您可以驗證表列表，您可以在其中觀察新建立的表，如下所示：

sqlite> . tables
CRICKETERS ODIStats
sqlite>

使用 Python 建立表

遊標物件包含執行查詢和提取資料等的所有方法。connection 類的 cursor 方法返回一個遊標物件。

因此，要使用 Python 在 SQLite 資料庫中建立表：

使用 connect() 方法與資料庫建立連線。
透過在上面建立的連線物件上呼叫 cursor() 方法來建立一個遊標物件。
現在使用 Cursor 類的 execute() 方法執行 CREATE TABLE 語句。

示例

以下 Python 程式在 SQLite3 中建立一個名為 Employee 的表：

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)
print("Table created successfully........")

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

Table created successfully........

Python SQLite - 插入資料

您可以使用 INSERT INTO 語句向 SQLite 的現有表中新增新行。在這裡，您需要指定表的名稱、列名和值（與列名的順序相同）。

語法

以下是 INSERT 語句的推薦語法：

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
   VALUES (value1, value2, value3,...valueN);

其中，column1、column2、column3……是表的列名，value1、value2、value3……是您需要插入到表中的值。

示例

假設我們已經使用 CREATE TABLE 語句建立了一個名為 CRICKETERS 的表，如下所示：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

以下 PostgreSQL 語句在上面建立的表中插入一行。

sqlite> insert into CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) 
   values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite>

使用INSERT INTO語句插入記錄時，如果您跳過任何列名，則此記錄將被插入，在您跳過的列中留下空位。

sqlite> insert into CRICKETERS (First_Name, Last_Name, Country) 
   values ('Jonathan', 'Trott', 'SouthAfrica');
sqlite>

如果您傳遞的值的順序與其在表中的相應列名相同，您也可以在不指定列名的情況下將記錄插入表中。

sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>

將記錄插入表後，您可以使用 SELECT 語句驗證其內容，如下所示：

sqlite> select * from cricketers;
Shikhar  |Dhawan     | 33 | Delhi | India
Jonathan |Trott      |    |       | SouthAfrica
Kumara   |Sangakkara | 41 | Matale| Srilanka
Virat    |Kohli      | 30 | Delhi | India
Rohit    |Sharma     | 32 | Nagpur| India
sqlite>

使用 Python 插入資料

要向 SQLite 資料庫中的現有表新增記錄：

匯入 sqlite3 包。
使用 connect() 方法建立一個連線物件，並將資料庫的名稱作為引數傳遞給它。
cursor()方法返回一個遊標物件，您可以使用它與 SQLite3 通訊。透過在（上面建立的）Connection 物件上呼叫 cursor() 物件來建立一個遊標物件。
然後，透過將 INSERT 語句作為引數傳遞給它，在遊標物件上呼叫 execute() 方法。

示例

以下 Python 示例將記錄插入到名為 EMPLOYEE 的表中：

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Preparing SQL queries to INSERT a record into the database.
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Ramya', 'Rama Priya', 27, 'F', 9000)'''
)
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Vinay', 'Battacharya', 20, 'M', 6000)''')

cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Sharukh', 'Sheik', 25, 'M', 8300)''')

cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Sarmista', 'Sharma', 26, 'F', 10000)''')

cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Tripthi', 'Mishra', 24, 'F', 6000)''')

# Commit your changes in the database
conn.commit()

print("Records inserted........")

# Closing the connection
conn.close()

輸出

Records inserted........

Python SQLite - 查詢資料

您可以使用 SELECT 查詢從 SQLite 表中檢索資料。此查詢/語句以表格形式返回指定關係（表）的內容，稱為結果集。

語法

以下是 SQLite 中 SELECT 語句的語法：

SELECT column1, column2, columnN FROM table_name;

示例

假設我們已經使用以下查詢建立了一個名為 CRICKETERS 的表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

如果我們使用 INSERT 語句向其中插入了 5 條記錄，如下所示：

sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>

以下 SELECT 查詢從 CRICKETERS 表中檢索列 FIRST_NAME、LAST_NAME 和 COUNTRY 的值。

sqlite> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
Shikhar   |Dhawan       |India
Jonathan  |Trott        |SouthAfrica
Kumara    |Sangakkara   |Srilanka
Virat     |Kohli        |India
Rohit     |Sharma       |India
sqlite>

正如您所看到的，SQLite 資料庫的 SELECT 語句只返回指定表的記錄。要獲得格式化的輸出，您需要在 SELECT 語句之前使用各自的命令設定header和mode，如下所示：

sqlite> .header on
sqlite> .mode column
sqlite> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
First_Name   Last_Name    Country
----------   ----------   ----------
Shikhar      Dhawan       India
Jonathan     Trott        SouthAfric
Kumara       Sangakkara   rilanka
Virat        Kohli        India
Rohit        Sharma       India

如果您想檢索每個記錄的所有列，則需要將列名替換為“*”，如下所示：

sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM CRICKETERS;
First_Name   Last_Name    Age        Place_Of_Birth   Country
----------   ----------   -------    --------------   ----------
Shikhar      Dhawan       33         Delhi            India
Jonathan     Trott        38         CapeTown         SouthAfric
Kumara       Sangakkara   41         Matale           Srilanka
Virat        Kohli        30         Delhi            India
Rohit        Sharma       32         Nagpur           India
sqlite>

在SQLite中，列的預設寬度為 10 個值，超過此寬度的值將被截斷（觀察上面表格中第 2 行的國家/地區列）。您可以使用.width命令在檢索表內容之前將每列的寬度設定為所需的值，如下所示：

sqlite> .width 10, 10, 4, 10, 13
sqlite> SELECT * FROM CRICKETERS;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   --------
Shikhar      Dhawan       33     Delhi        India
Jonathan     Trott        38     CapeTown     SouthAfrica
Kumara       Sangakkara   41     Matale       Srilanka
Virat        Kohli        30     Delhi        India
Rohit        Sharma       32     Nagpur       India
sqlite>

使用 Python 檢索資料

對任何資料庫的讀取操作都意味著從資料庫中提取一些有用的資訊。您可以使用 sqlite python 模組提供的 fetch() 方法從 MYSQL 中提取資料。

sqlite3.Cursor 類提供了三種方法，即 fetchall()、fetchmany() 和 fetchone()，其中：

fetchall() 方法檢索查詢結果集中的所有行，並將它們作為元組列表返回。（如果我們在檢索幾行後執行此操作，它將返回剩餘的行）。
fetchone() 方法獲取查詢結果中的下一行，並將其作為元組返回。
fetchmany() 方法類似於 fetchone()，但是它檢索查詢結果集中的下一組行，而不是單行。

注意 - 結果集是在使用遊標物件查詢表時返回的物件。

示例

以下示例使用 SELECT 查詢獲取 EMPLOYEE 表的所有行，並從獲得的結果集中首先使用 fetchone() 方法檢索第一行，然後使用 fetchall() 方法檢索剩餘的行。

以下 Python 程式演示瞭如何從上面示例中建立的 COMPANY 表中獲取和顯示記錄。

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving data
cursor.execute('''SELECT * from EMPLOYEE''')

#Fetching 1st row from the table
result = cursor.fetchone();
print(result)

#Fetching 1st row from the table
result = cursor.fetchall();
print(result)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

('Ramya', 'Rama priya', 27, 'F', 9000.0)
[
   ('Vinay', 'Battacharya', 20, 'M', 6000.0),
   ('Sharukh', 'Sheik', 25, 'M', 8300.0),
   ('Sarmista', 'Sharma', 26, 'F', 10000.0),
   ('Tripthi', 'Mishra', 24, 'F', 6000.0)
]

Python SQLite - WHERE 子句

如果要在 SQLite 中獲取、刪除或更新表的特定行，需要使用 where 子句指定條件來過濾表中的行以便進行操作。

例如，如果有一個帶有 where 子句的 SELECT 語句，則只有滿足指定條件的行才會被檢索。

語法

以下是 SQLite 中 WHERE 子句的語法：

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

可以使用比較運算子或邏輯運算子來指定 search_condition，例如 >、<、=、LIKE、NOT 等。下面的示例將使這個概念更清晰。

示例

假設我們已經使用以下查詢建立了一個名為 CRICKETERS 的表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

如果我們使用 INSERT 語句向其中插入了 5 條記錄，如下所示：

sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>

下面的 SELECT 語句檢索年齡大於 35 的記錄：

sqlite> SELECT * FROM CRICKETERS WHERE AGE > 35;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   -----------
Jonathan     Trott        38     CapeTown     SouthAfrica
Kumara       Sangakkara   41     Matale       Srilanka
sqlite>

使用 Python 的 Where 子句

遊標物件/類包含執行查詢和獲取資料等的所有方法。連線類的遊標方法返回一個遊標物件。

因此，要使用 Python 在 SQLite 資料庫中建立表：

使用 connect() 方法與資料庫建立連線。
透過在上面建立的連線物件上呼叫 cursor() 方法來建立一個遊標物件。
現在使用 Cursor 類的 execute() 方法執行 CREATE TABLE 語句。

示例

下面的示例建立一個名為 Employee 的表並填充它。然後使用 where 子句檢索年齡值小於 23 的記錄。

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)

#Populating the table
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Ramya', 'Rama priya', 27, 'F', 9000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Vinay', 'Battacharya', 20, 'M', 6000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Sharukh', 'Sheik', 25, 'M', 8300)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Sarmista', 'Sharma', 26, 'F', 10000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Tripthi', 'Mishra', 24, 'F', 6000)''')

#Retrieving specific records using the where clause
cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23")

print(cursor.fetchall())

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

[('Vinay', 'Battacharya', 20, 'M', 6000.0)]

Python SQLite - ORDER BY 子句

使用 SELECT 查詢獲取資料時，將按照插入資料的順序獲得記錄。

可以使用 **_Order By_** 子句按所需順序（升序或降序）對結果進行排序。預設情況下，此子句按升序對結果進行排序，如果需要按降序排列，則需要顯式使用“DESC”。

語法

以下是 SQLite 中 ORDER BY 子句的語法：

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

示例

假設我們已經使用以下查詢建立了一個名為 CRICKETERS 的表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

如果我們使用 INSERT 語句向其中插入了 5 條記錄，如下所示：

sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>

下面的 SELECT 語句按 CRICKETERS 表中記錄的年齡升序檢索行：

sqlite> SELECT * FROM CRICKETERS ORDER BY AGE;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   -----------
Virat        Kohli        30     Delhi        India
Rohit        Sharma       32     Nagpur       India
Shikhar      Dhawan       33     Delhi        India
Jonathan     Trott        38     CapeTown     SouthAfrica
Kumara       Sangakkara   41     Matale       Srilanka
sqlite>

可以使用多個列對錶的記錄進行排序。下面的 SELECT 語句根據 _AGE_ 和 _FIRST_NAME_ 列對 CRICKETERS 表的記錄進行排序。

sqlite> SELECT * FROM CRICKETERS ORDER BY AGE, FIRST_NAME;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   -------------
Virat        Kohli        30     Delhi        India
Rohit        Sharma       32     Nagpur       India
Shikhar      Dhawan       33     Delhi        India
Jonathan     Trott        38     CapeTown     SouthAfrica
Kumara       Sangakkara   41     Matale       Srilanka
sqlite>

預設情況下，**ORDER BY** 子句按升序對錶中的記錄進行排序，可以使用 DESC 將結果排列為降序，如下所示：

sqlite> SELECT * FROM CRICKETERS ORDER BY AGE DESC;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   -------------
Kumara       Sangakkara   41     Matale       Srilanka
Jonathan     Trott        38     CapeTown     SouthAfrica
Shikhar      Dhawan       33     Delhi        India
Rohit        Sharma       32     Nagpur       India
Virat        Kohli        30     Delhi        India
sqlite>

使用 Python 的 ORDER BY 子句

要按特定順序檢索表的內容，請在遊標物件上呼叫 execute() 方法，並將 SELECT 語句以及 ORDER BY 子句作為引數傳遞給它。

示例

在下面的示例中，我們建立一個名為 Employee 的表，向其中插入記錄，然後使用 ORDER BY 子句按年齡的（升序）順序檢索其記錄。

import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#Creating a table
sql = '''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT, SEX CHAR(1),
   INCOME INT,
   CONTACT INT
)'''
cursor.execute(sql)
#Populating the table
#Populating the table
cursor.execute(
   '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Ramya', 'Rama priya', 27, 'F', 9000),
   ('Vinay', 'Battacharya', 20, 'M', 6000), 
   ('Sharukh', 'Sheik', 25, 'M', 8300), 
   ('Sarmista', 'Sharma', 26, 'F', 10000),
   ('Tripthi', 'Mishra', 24, 'F', 6000)''')
conn.commit()

#Retrieving specific records using the ORDER BY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")

print(cursor.fetchall())

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

[
   ('Vinay', 'Battacharya', 20, 'M', 6000, None),
   ('Tripthi', 'Mishra', 24, 'F', 6000, None),
   ('Sharukh', 'Sheik', 25, 'M', 8300, None),
   ('Sarmista', 'Sharma', 26, 'F', 10000, None),
   ('Ramya', 'Rama priya', 27, 'F', 9000, None)
]

Python SQLite - 更新表

資料庫上的 UPDATE 操作意味著修改表中已存在的一個或多個記錄的值。可以使用 UPDATE 語句更新 SQLite 中現有記錄的值。

要更新特定行，需要結合使用 WHERE 子句。

語法

以下是 SQLite 中 UPDATE 語句的語法：

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

示例

假設我們已經使用以下查詢建立了一個名為 CRICKETERS 的表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

如果我們使用 INSERT 語句向其中插入了 5 條記錄，如下所示：

sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>

下面的語句修改名為 **Shikhar** 的板球運動員的年齡：

sqlite> UPDATE CRICKETERS SET AGE = 45 WHERE FIRST_NAME = 'Shikhar' ;
sqlite>

如果檢索 FIRST_NAME 為 Shikhar 的記錄，你會發現年齡值已更改為 45：

sqlite> SELECT * FROM CRICKETERS WHERE FIRST_NAME = 'Shikhar';
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   --------
Shikhar      Dhawan       45     Delhi        India
sqlite>

如果沒有使用 WHERE 子句，所有記錄的值都將被更新。下面的 UPDATE 語句將 CRICKETERS 表中所有記錄的年齡增加 1：

sqlite> UPDATE CRICKETERS SET AGE = AGE+1;
sqlite>

如果使用 SELECT 命令檢索表的內容，則可以看到更新後的值，如下所示：

sqlite> SELECT * FROM CRICKETERS;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   -------------
Shikhar      Dhawan       46     Delhi        India
Jonathan     Trott        39     CapeTown     SouthAfrica
Kumara       Sangakkara   42     Matale       Srilanka
Virat        Kohli        31     Delhi        India
Rohit        Sharma       33     Nagpur       India
sqlite>

使用 Python 更新現有記錄

要向 SQLite 資料庫中的現有表新增記錄：

匯入 sqlite3 包。
使用 connect() 方法建立一個連線物件，並將資料庫的名稱作為引數傳遞給它。
cursor()方法返回一個遊標物件，您可以使用它與 SQLite3 通訊。透過在（上面建立的）Connection 物件上呼叫 cursor() 物件來建立一個遊標物件。
然後，透過將 UPDATE 語句作為引數傳遞給它，在遊標物件上呼叫 execute() 方法。

示例

下面的 Python 示例建立一個名為 EMPLOYEE 的表，向其中插入 5 條記錄，並將所有男性員工的年齡增加 1：

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)

#Inserting data
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
VALUES ('Ramya', 'Rama priya', 27, 'F', 9000),
   ('Vinay', 'Battacharya', 20, 'M', 6000), 
   ('Sharukh', 'Sheik', 25, 'M', 8300), 
   ('Sarmista', 'Sharma', 26, 'F', 10000),
   ('Tripthi', 'Mishra', 24, 'F', 6000)''')
conn.commit()

#Fetching all the rows before the update
print("Contents of the Employee table: ")
cursor.execute('''SELECT * from EMPLOYEE''')
print(cursor.fetchall())

#Updating the records
sql = '''UPDATE EMPLOYEE SET AGE=AGE+1 WHERE SEX = 'M' '''
cursor.execute(sql)
print("Table updated...... ")

#Fetching all the rows after the update
print("Contents of the Employee table after the update operation: ")
cursor.execute('''SELECT * from EMPLOYEE''')
print(cursor.fetchall())

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

Contents of the Employee table:
[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0), 
   ('Vinay', 'Battacharya', 20, 'M', 6000.0), 
   ('Sharukh', 'Sheik', 25, 'M', 8300.0), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0), 
   ('Tripthi', 'Mishra', 24, 'F', 6000.0)
]
Table updated......
Contents of the Employee table after the update operation:
[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0), 
   ('Vinay', 'Battacharya', 21, 'M', 6000.0), 
   ('Sharukh', 'Sheik', 26, 'M', 8300.0), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0), 
   ('Tripthi', 'Mishra', 24, 'F', 6000.0)
]

Python SQLite - 刪除資料

要從 SQLite 表中刪除記錄，需要使用 DELETE FROM 語句。要刪除特定記錄，需要結合使用 WHERE 子句。

語法

以下是 SQLite 中 DELETE 查詢的語法：

DELETE FROM table_name [WHERE Clause]

示例

假設我們已經使用以下查詢建立了一個名為 CRICKETERS 的表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

如果我們使用 INSERT 語句向其中插入了 5 條記錄，如下所示：

sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>

下面的語句刪除姓氏為“Sangakkara”的板球運動員的記錄。

sqlite> DELETE FROM CRICKETERS WHERE LAST_NAME = 'Sangakkara';
sqlite>

如果使用 SELECT 語句檢索表的內容，則可以看到只有 4 條記錄，因為我們刪除了一條。

sqlite> SELECT * FROM CRICKETERS;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   --------
Shikhar      Dhawan       46     Delhi        India
Jonathan     Trott        39     CapeTown     SouthAfrica
Virat        Kohli        31     Delhi        India
Rohit        Sharma       33     Nagpur       India
sqlite>

如果在沒有 WHERE 子句的情況下執行 DELETE FROM 語句，則將刪除指定表中的所有記錄。

sqlite> DELETE FROM CRICKETERS;
sqlite>

由於已刪除所有記錄，如果嘗試使用 SELECT 語句檢索 CRICKETERS 表的內容，則將獲得一個空的結果集，如下所示：

sqlite> SELECT * FROM CRICKETERS;
sqlite>

使用 Python 刪除資料

要向 SQLite 資料庫中的現有表新增記錄：

匯入 sqlite3 包。
使用 _connect()_ 方法建立一個連線物件，並將資料庫的名稱作為引數傳遞給它。
_**cursor()**_ 方法返回一個遊標物件，可以使用它與 SQLite3 通訊。透過在（上面建立的）連線物件上呼叫 cursor() 物件來建立一個遊標物件。
然後，透過將 **DELETE** 語句作為引數傳遞給它，在遊標物件上呼叫 execute() 方法。

示例

下面的 python 示例刪除 EMPLOYEE 表中年齡值大於 25 的記錄。

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving contents of the table
print("Contents of the table: ")
cursor.execute('''SELECT * from EMPLOYEE''')
print(cursor.fetchall())

#Deleting records
cursor.execute('''DELETE FROM EMPLOYEE WHERE AGE > 25''')

#Retrieving data after delete
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

Contents of the table:
[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0), 
   ('Vinay', 'Battacharya', 21, 'M', 6000.0), 
   ('Sharukh', 'Sheik', 26, 'M', 8300.0), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0), 
   ('Tripthi', 'Mishra', 24, 'F', 6000.0)
]
Contents of the table after delete operation
[
   ('Vinay', 'Battacharya', 21, 'M', 6000.0), 
   ('Tripthi', 'Mishra', 24, 'F', 6000.0)
]

Python SQLite - 刪除表

可以使用 DROP TABLE 語句刪除整個表。只需要指定要刪除的表的名稱。

語法

以下是 PostgreSQL 中 DROP TABLE 語句的語法：

DROP TABLE table_name;

示例

假設我們已經使用以下查詢建立了名為 CRICKETERS 和 EMPLOYEES 的兩個表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
sqlite> CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, 
   SEX CHAR(1), INCOME FLOAT
);
sqlite>

現在，如果使用 **.tables** 命令驗證表列表，則可以在其中（列表）看到上面建立的表，如下所示：

sqlite> .tables
CRICKETERS EMPLOYEE
sqlite>

下面的語句從資料庫中刪除名為 Employee 的表：

sqlite> DROP table employee;
sqlite>

由於已刪除 Employee 表，如果再次檢索表列表，則只會看到一個表。

sqlite> .tables
CRICKETERS
sqlite>

如果再次嘗試刪除 Employee 表，由於已刪除它，則會收到一條錯誤訊息，提示“no such table”，如下所示：

sqlite> DROP table employee;
Error: no such table: employee
sqlite>

要解決此問題，可以使用 IF EXISTS 子句以及 DELETE 語句。如果表存在，則此語句會刪除表；否則，會跳過 DELETE 操作。

sqlite> DROP table IF EXISTS employee;
sqlite>

使用 Python 刪除表

可以使用 MYSQL 的 DROP 語句隨時刪除表，但在刪除任何現有表時需要非常小心，因為刪除表後資料將無法恢復。

示例

要使用 python 從 SQLite3 資料庫中刪除表，請在遊標物件上呼叫 _**execute()**_ 方法，並將 drop 語句作為引數傳遞給它。

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists
cursor.execute("DROP TABLE emp")
print("Table dropped... ")

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

Table dropped...

Python SQLite - LIMIT 子句

如果在獲取記錄時想要將它們限制為特定數量，可以使用 SQLite 的 LIMIT 子句。

語法

以下是 SQLite 中 LIMIT 子句的語法：

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

示例

假設我們已經使用以下查詢建立了一個名為 CRICKETERS 的表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

如果我們使用 INSERT 語句向其中插入了 5 條記錄，如下所示：

sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>

下面的語句使用 LIMIT 子句檢索 Cricketers 表的前 3 條記錄：

sqlite> SELECT * FROM CRICKETERS LIMIT 3;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   -------------
Shikhar      Dhawan       33     Delhi        India
Jonathan     Trott        38     CapeTown     SouthAfrica
Kumara       Sangakkara   41     Matale       Srilanka
sqlite>

如果需要從第 n 條記錄（不是第 1 條）開始限制記錄，則可以使用 OFFSET 和 LIMIT。

sqlite> SELECT * FROM CRICKETERS LIMIT 3 OFFSET 2;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   --------
Kumara       Sangakkara   41     Matale       Srilanka
Virat        Kohli        30     Delhi        India
Rohit        Sharma       32     Nagpur       India
sqlite>

使用 Python 的 LIMIT 子句

如果透過傳遞帶有 LIMIT 子句的 SELECT 查詢來在遊標物件上呼叫 execute() 方法，則可以檢索所需數量的記錄。

示例

下面的 python 示例使用 LIMIT 子句檢索 EMPLOYEE 表的前兩條記錄。

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
sql = '''SELECT * from EMPLOYEE LIMIT 3'''

#Executing the query
cursor.execute(sql)

#Fetching the data
result = cursor.fetchall();
print(result)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0), 
   ('Vinay', 'Battacharya', 20, 'M', 6000.0), 
   ('Sharukh', 'Sheik', 25, 'M', 8300.0)
]

Python SQLite - JOIN 操作

當資料分成兩個表時，可以使用聯接從這兩個表中獲取組合記錄。

示例

假設我們已經使用以下查詢建立了一個名為 CRICKETERS 的表：

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

讓我們再建立一個名為 OdiStats 的表，用於描述 CRICKETERS 表中每個球員的一日板球統計資料。

sqlite> CREATE TABLE ODIStats (
   First_Name VARCHAR(255),
   Matches INT,
   Runs INT,
   AVG FLOAT,
   Centuries INT,
   HalfCenturies INT
);
sqlite>

下面的語句檢索這兩個表的組合值資料：

sqlite> SELECT
   Cricketers.First_Name, Cricketers.Last_Name, Cricketers.Country,
   OdiStats.matches, OdiStats.runs, OdiStats.centuries, OdiStats.halfcenturies
   from Cricketers INNER JOIN OdiStats ON Cricketers.First_Name = OdiStats.First_Name;
First_Name  Last_Name   Country  Matches  Runs   Centuries   HalfCenturies
----------  ----------  -------  -------  ----   ---------   -------------- 
Shikhar     Dhawan      Indi     133      5518   17          27
Jonathan    Trott       Sout     68       2819   4           22
Kumara      Sangakkara  Sril     404      14234  25          93
Virat       Kohli       Indi     239      11520  43          54
Rohit       Sharma      Indi     218      8686   24          42
sqlite>

使用 Python 的 JOIN 子句

下面的 SQLite 示例演示了使用 python 的 JOIN 子句：

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving data
sql = '''SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID'''

#Executing the query
cursor.execute(sql)

#Fetching 1st row from the table
result = cursor.fetchall();

print(result)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

輸出

[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0, 101, 101, 'Krishna@mymail.com', 'Hyderabad'), 
   ('Vinay', 'Battacharya', 20, 'M', 6000.0, 102, 102,'Raja@mymail.com', 'Vishakhapatnam'), 
   ('Sharukh', 'Sheik', 25, 'M', 8300.0, 103, 103, 'Krishna@mymail.com', 'Pune'), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0, 104, 104, 'Raja@mymail.com', 'Mumbai')
]

Python SQLite - 遊標物件

sqlite3.Cursor 類是一個例項，可以使用它來呼叫執行 SQLite 語句、從查詢的結果集中獲取資料的方法。可以使用連線物件/類的 cursor() 方法建立遊標物件。

示例

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

方法

以下是遊標類/物件提供的各種方法。

方法	描述
execute()	此例程執行 SQL 語句。SQL 語句可以是引數化的（即，使用佔位符而不是 SQL 字面量）。psycopg2 模組支援使用 %s 符號進行佔位符。例如：cursor.execute("insert into people values (%s, %s)", (who, age))
executemany()	此例程針對序列 sql 中找到的所有引數序列或對映執行 SQL 命令。
fetchone()	此方法獲取查詢結果集的下一行，返回單個序列，或者當沒有更多資料可用時返回 None。
fetchmany()	此例程獲取查詢結果的下一組行，返回一個列表。當沒有更多行可用時，將返回一個空列表。該方法嘗試獲取 size 引數指示的儘可能多的行。
fetchall()	此例程獲取查詢結果的所有（剩餘）行，返回一個列表。當沒有行可用時，將返回一個空列表。

屬性

以下是遊標類的屬性：

方法	描述
arraySize	這是一個讀/寫屬性，可以設定 fetchmany() 方法返回的行數。
description	這是一個只讀屬性，它返回一個列表，其中包含結果集中列的描述。
lastrowid	這是一個只讀屬性，如果表中存在任何自動遞增列，則返回上次 INSERT 或 UPDATE 操作為該列生成的值。
rowcount	對於 SELECT 和 UPDATE 操作，這將返回返回/更新的行數。
connection	此只讀屬性提供遊標物件使用的 SQLite 資料庫連線。

列印頁面