Python Data Access 简明教程

Python SQLite - Update Table

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

UPDATE Operation on any database implies modifying the values of one or more records of a table, which are already available in the database. You can update the values of existing records in SQLite using the UPDATE statement.

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

To update specific rows, you need to use the WHERE clause along with it.

Syntax

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

Following is the syntax of the UPDATE statement in SQLite −

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

Example

假设我们使用以下查询创建了一个名为 CRICKETERS 的表 -

Assume we have created a table with name CRICKETERS using the following query −

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 条记录，如下所示 -

And if we have inserted 5 records in to it using INSERT statements as −

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 的球员的年龄：

Following Statement modifies the age of the cricketer, whose first name is Shikhar −

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

如果您检索 FIRST_NAME 为 Shikhar 的记录，您会发现其 age 值已更改为 45：

If you retrieve the record whose FIRST_NAME is Shikhar you observe that the age value has been changed to 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：

If you haven’t used the WHERE clause values of all the records will be updated. Following UPDATE statement increases the age of all the records in the CRICKETERS table by 1 −

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

如果您使用 SELECT 命令检索表中的内容，您就能够看到更新后的值：

If you retrieve the contents of the table using SELECT command, you can see the updated values as −

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 数据库的现有表中添加记录：

To add records to an existing table in SQLite database −

Import sqlite3 package.
Create a connection object using the connect() method by passing the name of the database as a parameter to it.
The cursor() method returns a cursor object using which you can communicate with SQLite3 . Create a cursor object by invoking the cursor() object on the (above created) Connection object.
Then, invoke the execute() method on the cursor object, by passing an UPDATE statement as a parameter to it.

Example

以下 Python 示例创建一个名为 EMPLOYEE 的表，向其中插入 5 条记录，并将所有男性员工的年龄增加 1：

Following Python example, creates a table with name EMPLOYEE, inserts 5 records into it and, increases the age of all the male employees by 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)]