Python Data Access 简明教程

Python PostgreSQL - Insert Data

你可以使用 INSERT INTO 语句将记录插入到 PostgreSQL 中现有的表中。执行此操作时，你需要指定表的名称以及其中的列值。

You can insert record into an existing table in PostgreSQL using the INSERT INTO statement. While executing this, you need to specify the name of the table, and values for the columns in it.

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 −

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age INT,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
CREATE TABLE
postgres=#

以下 PostgreSQL 语句在上述创建的表中插入一行：

Following PostgreSQL statement inserts a row in the above created table −

postgres=# insert into CRICKETERS (
   First_Name, Last_Name, Age, Place_Of_Birth, Country)
   values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=#

使用 INSERT INTO 语句插入记录时，如果你跳过任何列名，则会插入记录，并在你跳过的列中留出空白。

While inserting records using the INSERT INTO statement, if you skip any columns names Record will be inserted leaving empty spaces at columns which you have skipped.

postgres=# insert into CRICKETERS (First_Name, Last_Name, Country)
   values('Jonathan', 'Trott', 'SouthAfrica');
INSERT 0 1

如果您传递的值的顺序与表中的相应列名称相同，您还可以不指定列名将记录插入到表中。

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.

postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1
postgres=#

在将记录插入到表中后，您可以使用 SELECT 语句验证其内容，如下所示 -

After inserting the records into a table you can verify its contents using the SELECT statement as shown below −

postgres=# SELECT * from CRICKETERS;
first_name  | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      |     |                | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
(5 rows)

Inserting data using python

psycopg2 的 cursor 类提供了一个名为 execute() 的方法。此方法接受查询作为参数并执行该查询。

The cursor class of psycopg2 provides a method with name execute() method. This method accepts the query as a parameter and executes it.

因此，要使用 python 将数据插入到 PostgreSQL 中的表中 -

Therefore, to insert data into a table in PostgreSQL using python −

Import psycopg2 package.
Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
Turn off the auto-commit mode by setting false as value to the attribute autocommit.
The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method.
Then, execute the INSERT statement(s) by passing it/them as a parameter to the execute() method.

Example

以下 Python 程序在 PostgreSQL 数据库中创建一个名为 EMPLOYEE 的表，并使用 execute() 方法向其中插入记录：

Following Python program creates a table with name EMPLOYEE in PostgreSQL database and inserts records into it using the execute() method −

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

# 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........