Python Sqlite 简明教程

Python SQLite - Quick Guide

Python SQLite - Introduction

SQLite3 可以使用 sqlite3 模块与 Python 集成，该模块由 Gerhard Haring 编写。它提供与 PEP 249 描述的 DB-API 2.0 规范兼容的 SQL 接口。您无需单独安装此模块，因为它默认随 Python 2.5.x 及更高版本一起提供。

要使用 sqlite3 模块，您必须首先创建一个表示数据库的连接对象，然后可以选择创建一个游标对象，它将帮助您执行所有 SQL 语句。

Python SQLite3 Module APIs

以下是重要的 sqlite3 模块例程，它可以满足您使用 Python 程序处理 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，则可以查看 Python sqlite3 模块的官方文档。

Sr.No.	API & Description
1	sqlite3.connect(database [,timeout ,other optional arguments]) 此 API 打开与 SQLite 数据库文件的连接。您可以使用 ":memory:" 打开与驻留在 RAM 中而不是磁盘上的数据库的数据库连接。如果数据库打开成功，它将返回一个连接对象。
2	connection.cursor([cursorClass]) 此例程将创建一个光标，用于你使用 Python 编写的数据库程序。此方法接受一个可选参数 cursorClass。如果提供此参数，则它必须是扩展 sqlite3.Cursor 的自定义光标类。
3	cursor.execute(sql [, optional parameters]) 此例程执行 SQL 语句。SQL 语句可以是参数化的（即占位符而不是 SQL 文字）。sqlite3 模块支持两种类型的占位符：问号和命名占位符（命名样式）。 For example − cursor.execute("insert into people values (?, ?)", (who, age))
4	connection.execute(sql [, optional parameters]) 此例程是游标对象提供的上述 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 - Establishing Connection

要建立与 SQLite 的连接，打开命令提示符，浏览到你安装了 SQLite 的位置，然后执行 @[s2] 命令，如下所示：

Establishing Connection Using Python

你可以使用 SQLite3 python 模块与 SQLite2 数据库进行通信。要做到这一点，首先你需要建立一个连接（创建一个连接对象）。

要使用 python 建立与 SQLite3 数据库的连接，你需要：

使用 import 语句导入 sqlite3 模块。
connect() 方法接受您需要连接的数据库的名称作为参数，并返回一个 Connection 对象。

Example

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

Output

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

Python SQLite - Create Table

使用 SQLite CREATE TABLE 语句，可以在数据库中创建一个表。

Syntax

下面是创建 SQLite 数据库表的语法：

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

Example

以下 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>

Creating a Table Using Python

游标对象包含用于执行查询和获取数据等的所有方法。连接类的游标方法返回一个游标对象。

因此，要在 Python 中创建 SQLite 数据库中的表：

使用 connect() 方法与数据库建立连接。
通过在上述创建的连接对象上调用 cursor() 方法来创建一个 cursor 对象。
现在使用 Cursor 类的 execute() 方法执行 CREATE TABLE 语句。

Example

以下 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()

Output

Table created successfully........

Python SQLite - Insert Data

您可以使用 INSERT INTO 语句将新行添加到 SQLite 的现有表中。在此，您需要按顺序指定表名、列名和值（与列名相同）。

Syntax

以下是 INSERT 语句的建议语法 -

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

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

Example

假设我们使用 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>

Inserting Data Using Python

要在 SQLite 数据库的现有表中添加记录：

Import sqlite3 package.
使用 connect() 方法创建一个连接对象，并将数据库名称作为参数传递给该对象。
cursor() 方法返回一个光标对象，您可以使用该光标对象与 SQLite3 进行通信。通过在（上述创建的）连接对象上调用 cursor() 对象来创建光标对象。
然后，通过将 INSERT 语句作为参数传递给光标对象，调用光标对象的 execute() 方法。

Example

以下 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()

Output

Records inserted........

Python SQLite - Select Data

你可以使用 SELECT 查询从 SQLite 表中检索数据。此查询/语句以表格形式返回指定关系（表）的内容，并称之为结果集。

Syntax

以下是 SQLite 中 SELECT 语句的语法 -

SELECT column1, column2, columnN FROM table_name;

Example

假设我们使用以下查询创建了一个名为 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 行 country 列）。可以在检索表格内容之前，使用 .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>

Retrieving Data Using Python

对任何数据库执行 READ 操作意味着从中获取一些有用的信息。可以使用 sqlite python 模块提供的 fetch() 方法从 MYSQL 中获取数据。

sqlite3.Cursor 类提供 fetchall()、fetchmany() 和 fetchone() 三个方法，其中：

fetchall() 方法检索查询结果集中的所有行，然后将它们作为元组列表返回。（如果执行此操作之后检索出几行，它将返回剩余的行）。
fetchone() 方法获取查询结果中的下一行，并将其作为元组返回。
fetchmany() 方法类似于 fetchone()，但是它检索结果集中的下一组行，而不是单行。

Note - 结果集是在使用光标对象查询表格时返回的对象。

Example

下面的示例使用 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()

Output

('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 Clause

如果要获取、删除或更新 SQLite 中表的特定行，您需要使用 where 子句来指定条件，以便筛选表的行以进行操作。

例如，如果您有带 where 从句的 SELECT 语句，则只有满足指定条件的行才会被检索。

Syntax

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

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

您可以使用比较或逻辑运算符（如>、<、=、LIKE、NOT等）指定search_condition。以下示例将阐明此概念。

Example

假设我们使用以下查询创建了一个名为 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>

Where Clause Using Python

Cursor 对象/类包含执行查询和获取数据等的所有方法。连接类的 cursor 方法返回一个 cursor 对象。

因此，要在 Python 中创建 SQLite 数据库中的表：

使用 connect() 方法与数据库建立连接。
通过在上述创建的连接对象上调用 cursor() 方法来创建一个 cursor 对象。
现在使用 Cursor 类的 execute() 方法执行 CREATE TABLE 语句。

Example

以下示例创建一个名为 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()

Output

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

Python SQLite - Order By

使用 SELECT 查询获取数据时，您将按照插入记录的顺序获取记录。

您可以使用 Order By 子句按所需顺序（升序或降序）对结果进行排序。默认情况下，此子句按升序对结果进行排序。如果您需要按降序对结果进行排序，您需要显式使用 “DESC”。

Syntax

以下是 SQLite 中 ORDER BY 子句的语法。

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

Example

假设我们使用以下查询创建了一个名为 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>

选择语句检索 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>

ORDER BY Clause Using Python

要以特定顺序检索表的全表，请对光标对象调用 execute() 方法，并将 SELECT 语句连同 ORDER BY 子句作为参数传递给它。

Example

在以下示例中，我们使用 ORDER BY 子句创建了一个名为 Employee 的表，对其进行填充，然后按其年龄的（升序）顺序检索其记录。

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()

Output

[
   ('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 Table

对任何数据库的 UPDATE 操作意味着修改表中一个或多个已经存在于数据库中的记录的值。您可以使用 UPDATE 语句更新 SQLite 中现有记录的值。

要更新特定行，需要同时使用 WHERE 子句。

Syntax

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

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

Example

假设我们使用以下查询创建了一个名为 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 的记录，您会发现其 age 值已更改为 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>

Updating Existing Records Using Python

要在 SQLite 数据库的现有表中添加记录：

Import sqlite3 package.
使用 connect() 方法创建一个连接对象，并将数据库名称作为参数传递给该对象。
cursor() 方法返回一个光标对象，您可以使用该光标对象与 SQLite3 进行通信。通过在（上述创建的）连接对象上调用 cursor() 对象来创建光标对象。
然后，通过将 UPDATE 语句作为参数传递给 cursor 对象，调用 execute() 方法。

Example

以下 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()

Output

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 - Delete Data

要从 SQLite 表格中删除记录，需要使用 DELETE FROM 语句。要移除特定记录，需要同时使用 WHERE 子句。

Syntax

以下是 SQLite 中 DELETE 查询的语法：

DELETE FROM table_name [WHERE Clause]

Example

假设我们使用以下查询创建了一个名为 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>

Deleting Data Using Python

要在 SQLite 数据库的现有表中添加记录：

Import sqlite3 package.
使用 connect() 方法创建一个连接对象，并将数据库名称作为参数传递给该对象。
cursor() 方法返回一个光标对象，您可以使用该光标对象与 SQLite3 进行通信。通过在（上述创建的）连接对象上调用 cursor() 对象来创建光标对象。
然后，通过将 DELETE 语句作为参数传递给 cursor 对象，调用该对象的 execute() 方法。

Example

以下 Python 示例从 EMPLOYEE 表中删除 age 值大于 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()

Output

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

你可以使用 DROP TABLE 语句删除整个表。你只需指定要删除的表的名称即可。

Syntax

以下是 PostgreSQL 中 DROP TABLE 语句的语法 −

DROP TABLE table_name;

Example

假设我们已经使用以下查询创建了两个名为 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 表格，由于你已经删除了它，你将收到一个错误信息，显示“没有此表格”，如下所示：

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

若要解决此问题，您可将 IF EXISTS 子句与 DELETE 语句一起使用。如果表存在，此方法会删除表，否则会跳过 DELETE 操作。

sqlite> DROP table IF EXISTS employee;
sqlite>

Dropping a Table Using Python

您可以根据需要使用 MYSQL 的 DROP 语句删除表，但您在删除任何现有表时需要非常小心，因为删除表后丢失的数据将无法恢复。

Example

要使用 Python 从 SQLite3 数据库中删除表，请在游标对象上调用 execute() 方法，并将删除语句作为参数传递给它。

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()

Output

Table dropped...

Python SQLite - Limit

在获取记录时，如果你想通过某个特定的数字来限制它们，你可以使用 SQLite 的 LIMIT 子句来做到。

Syntax

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

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

Example

假设我们使用以下查询创建了一个名为 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 个记录开始限制记录（不是第一个），你可以使用 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>

LIMIT Clause Using Python

如果你通过将 SELECT 查询与 LIMIT 子句一起传递给光标对象来调用 execute() 方法，则可以检索所需数量的记录。

Example

以下 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()

Output

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

Python SQLite - Join

当你在两张表中划分数据时，你可以使用连接从这两张表中获取组合记录。

Example

假设我们使用以下查询创建了一个名为 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>

Join Clause Using Python

以下 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()

Output

[
   ('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 - Cursor Object

sqlite3.Cursor 类是一个实例，使用它可以调用执行 SQLite 语句、从查询结果集中获取数据的函数。可以使用 Connection 对象/类的 cursor() 方法创建 Cursor 对象。

Example

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

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

Methods

以下是 Cursor 类/对象提供的各种方法。

Method

Description

execute()

此例程执行一个 SQL 语句。该 SQL 语句可能带参数（即占位符，而不是 SQL 字面值）。psycopg2 模块使用占位符 %s，例如：cursor.execute("insert into people values (%s, %s)", (who, age))

executemany()

此例程对在序列 sql 中找到的所有参数序列或映射执行 SQL 命令。

fetchone()

此方法提取查询结果集的下一行，返回单个序列，或在没有更多数据可用时返回 None。

fetchmany()

此例程提取查询结果的下一组行，返回一个列表。如果没有更多行时，会返回一个空列表。此方法尝试提取尽可能多的行，如由大小参数指示。

fetchall()

此例程提取查询结果的所有（剩余）行，返回一个列表。如果没有行时，会返回一个空列表。

Properties

以下为 Cursor 类的属性：

Method

Description

arraySize

这是一个读/写属性，可以设置 fetchmany() 方法返回的行数。

description

这是一个只读属性，它返回一个包含结果集中列描述的列表。

lastrowid

这是一个只读属性，如果表中存在任何自动增量列，它将返回在最近的 INSERT 或 UPDATE 操作中为此列生成的值。

rowcount

在 SELECT 和 UPDATE 操作时，它将返回返回/更新的行数。

connection

此只读属性提供 Cursor 对象使用的 SQLite 数据库连接。