PostgreSQL Python: Insert Data Into a Table
Summary: in this tutorial, you will learn to insert one or more rows into a PostgreSQL table from Python.
This tutorial picks up from where theCreating Tables Tutorial left off.
Steps for inserting one row into a table from Python
To insert one or more rows into a table from Python, you follow these steps:
- First,connect to the PostgreSQL server.
- Next, create a
cursorobject from theconnectionobject. - Then, execute theINSERT statement with values by calling the
execute()orexecutemany()method of thecursorobject. - After that, commit the transaction by calling the
commit()method of theconnectionobject. - Finally, obtain the inserted ID by calling the
fetchone()method of thecursorobject.
Inserting data into a table example
We will use thevendors table in thesuppliers database for the demonstration purposes:
1) Inserting one row into a table
First, create a new file named insert.py.
Second, define ainsert_vendor() function in the insert.py file:
import psycopg2from config import load_configdef insert_vendor(vendor_name): """ Insert a new vendor into the vendors table """ sql = """INSERT INTO vendors(vendor_name) VALUES(%s) RETURNING vendor_id;""" vendor_id = None config = load_config() try: with psycopg2.connect(**config)as conn: with conn.cursor()as cur: # execute the INSERT statement cur.execute(sql, (vendor_name,)) # get the generated id back rows = cur.fetchone() if rows: vendor_id = rows[0] # commit the changes to the database conn.commit() except (Exception,psycopg2.DatabaseError) as error: print(error) finally: return vendor_idif __name__ == '__main__': insert_vendor("3M Co.")Theinsert_vendor() function will insert a new row into thevendors table and return the insertedvendor_id.
2) Inserting multiple rows into a table
The steps for inserting multiple rows into a table are similar to the steps for inserting one row.
The key difference is in the third step: instead of calling theexecute() method of thecursor object, you use the executemany() method.
For example, the following definesinsert_many_vendors() function that inserts multiple rows into thevendors table:
def insert_many_vendors(vendor_list): """ Insert multiple vendors into the vendors table """ sql= "INSERT INTO vendors(vendor_name) VALUES(%s) RETURNING *" config= load_config() try: with psycopg2.connect(**config) as conn: with conn.cursor() as cur: # execute the INSERT statement cur.executemany(sql, vendor_list) # commit the changes to the database conn.commit() except (Exception, psycopg2.DatabaseError)as error: print(error)The following uses theinsert_vendor() andinsert_many_vendors() functions to insert one and multiple rows into thevendors table:
if __name__ == '__main__': insert_vendor("3M Co.") insert_many_vendors([ ('AKM Semiconductor Inc.',), ('Asahi Glass Co Ltd.',), ('Daikin Industries Ltd.',), ('Dynacast International Inc.',), ('Foster Electric Co. Ltd.',), ('Murata Manufacturing Co. Ltd.',) ])3) Execute the insert.py module
First, open the Command Prompt on Windows or Terminal on Unix-like systems.
Second, run the following command to execute theinsert.py module:
python insert.py4) Verify the inserts
First,connect to the PostgreSQL server using thepsql:
psql -U postgresSecond, change the current database tosuppliers:
\c suppliersThird, retrieve data from thevendors table:
SELECT * FROM vendors;Output:
vendor_id | vendor_name-----------+------------------------------- 1 | 3M Co. 2 | AKM Semiconductor Inc. 3 | Asahi Glass Co Ltd. 4 | Daikin Industries Ltd. 5 | Dynacast International Inc. 6 | Foster Electric Co. Ltd. 7 | Murata Manufacturing Co. Ltd.(7 rows)Download the project source code
Summary
- Use the
execute()orexecutemany()method of thecursorobject to insert one or more rows into a table from Python.
Last updated on