Python Data Access 简明教程
Python SQLite - Insert Data
您可以使用 INSERT INTO 语句将新行添加到 SQLite 的现有表中。在此，您需要按顺序指定表名、列名和值（与列名相同）。
You can add new rows to an existing table of SQLite using the INSERT INTO statement. In this, you need to specify the name of the table, column names, and values (in the same order as column names).
Syntax
以下是 INSERT 语句的建议语法 -
Following is the recommended syntax of the INSERT statement −
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);其中，column1、column2、column3、… 是表中列的名称，value1、value2、value3、… 是您需要插入到表中的值。
Where, column1, column2, column3,.. are the names of the columns of a table and value1, value2, value3,… are the values you need to insert into the table.
Example
假设我们使用 CREATE TABLE 语句创建了一个名为 CRICKETERS 的表，如下所示 -
Assume we have created a table with name CRICKETERS using the CREATE TABLE statement as shown below −
sqlite> CREATE TABLE CRICKETERS (
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Age int,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255)
);
sqlite>以下 PostgreSQL 语句在上述创建的表中插入一行。
Following PostgreSQL statement inserts a row in the above created table.
sqlite> insert into CRICKETERS
(First_Name, Last_Name, Age, Place_Of_Birth, Country) values
('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite>在使用 INSERT INTO 语句插入记录时，如果您跳过了任何列名称，则此记录将被插入，在您跳过的列中留出空格。
While inserting records using the INSERT INTO statement, if you skip any columns names, this record will be inserted leaving empty spaces at columns which you have skipped.
sqlite> insert into CRICKETERS
(First_Name, Last_Name, Country) values
('Jonathan', 'Trott', 'SouthAfrica');
sqlite>如果您传递的值的顺序与表中的相应列名称相同，您还可以不指定列名将记录插入到表中。
You can also insert records into a table without specifying the column names, if the order of values you pass is same as their respective column names in the table.
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 语句验证其内容，如下所示 -
After inserting the records into a table you can verify its contents using the SELECT statement as shown below −
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 数据库的现有表中添加记录：
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 INSERT statement as a parameter to it.
Example
以下 python 示例将记录插入到名为 EMPLOYEE 的表中 -
Following python example inserts records into to a table named 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()