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, '[email protected]', 'Hyderabad'), 
   ('Vinay', 'Battacharya', 20, 'M', 6000.0, 102, 102,'[email protected]', 'Vishakhapatnam'), 
   ('Sharukh', 'Sheik', 25, 'M', 8300.0, 103, 103, '[email protected]', 'Pune'), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0, 104, 104, '[email protected]', '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 数据库连接。

打印页面