Python PostgreSQL: Insert Data into a table with psycopg3

Summary: In this tutorial, you’ll learn how to insert one or multiple rows into a table from Python using Psycopg 3.

Here are the steps for inserting one or more rows into a table from Python:

First, connect to PostgreSQL from Python.
Next, create a cursor object from the Connection object.
Third, execute the INSERT statement with values by calling the execute() or executemany() method of the cursor object.
After that, save data permanently using the commit() method of the Connection object.
Finally, retrieve the ID of the inserted row by calling the fetchone() method of the Cursor object.

Prerequisites #

This tutorial assumes you already have a products table like this in the pyinventory database:

CREATE TABLE IF NOT EXISTS products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    safety_stock INTEGER NOT NULL,
    gross_weight NUMERIC(10, 2)
);
Code language: SQL (Structured Query Language) (sql)

Insert one row into a table #

The following script inserts one row into the products table:

import psycopg
from config import config

sql = """
INSERT INTO products (name, price, safety_stock, gross_weight)
VALUES (%s, %s, %s, %s)
"""

data = ("iPhone 14 Pro", 999.99, 10, 0.24)

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, data)
            print("1 row inserted successfully.")
except Exception as e:
    print(f"Error: {e}")
Code language: Python (python)

How it works. #

Step 1: Import the psycopg library

import psycopgCode language: Python (python)

The psycopg is the module that allows Python to connect to a PostgreSQL database, send SQL statements, and receive results.

Step 2: Import the database configuration

from config import configCode language: Python (python)

This imports a variable called config from config.py. The config is a dictionary containing connection parameters like this:

{
    "host": "localhost",
    "dbname": "pyinventory",
    "user": "postgres",
    "password": ""
}
Code language: Python (python)

Step 3: Define the SQL INSERT statement to insert a new row into the products table:

sql = """
INSERT INTO products (name, price, safety_stock, gross_weight)
VALUES (%s, %s, %s, %s)"""
Code language: Python (python)

This defines a parameterized SQL statement:

products is the target table to insert data.
(name, price, safety_stock, gross_weight) are columns of the products table where we’ll insert the data.
%s are placeholders where the actual values will be supplied separately. It prevents SQL injection because psycopg will safely convert Python values to SQL values.

Note that the triple quotes (""") allow writing multi-line SQL cleanly.

Step 4: Prepare the data to insert

data = ("iPhone 14 Pro", 999.99, 10, 0.24)Code language: Python (python)

This is a tuple that matches the placeholders in the SQL. The order is very important:

name → "iPhone 14 Pro"
price → 999.99
safety_stock → 10
gross_weight → 0.24

psycopg will bind these values to %s in the SQL INSERT statement.

Step 5: Start error handling

try:Code language: Python (python)

This begins a try block. It catches any error that happens while connecting to the database and executing SQL.

Step 6: Open a database connection

with psycopg.connect(**config) as conn:Code language: Python (python)

The psycopg.connect(**config) creates a connection to PostgreSQL.

The **config expands the config dictionary into keyword arguments like this:

psycopg.connect(
    host="localhost",
    dbname="pyinventory",
    user="postgres",
    password=""
)Code language: Python (python)

The with ... as conn statement:

Automatically closes the connection when complete.
Automatically commits if everything succeeds and rolls back if an exception occurs.

Step 7: Create a cursor

with conn.cursor() as cur:
Code language: Python (python)

We use a cursor to execute SQL statements. We use the with statement to ensure that the cursor is closed automatically.

Step 8: Execute the INSERT statement

cur.execute(sql, data)
Code language: Python (python)

The execute() method sends the SQL INSERT statement to PostgreSQL and performs the insert.

Note that psycopg replaces each %s with the corresponding value in data and converts Python types to the correct PostgreSQL types before the insert.

Step 9: Confirm success

print("1 row inserted successfully.")
Code language: Python (python)

We display a confirmation message if no error occurred.

Step 10: Catch any errors

except Exception as e:Code language: Python (python)

This catches any exception raised in the try block. Typical errors include

Connection problems
Missing table
Constraint violations

Step 11: Display the error message

print(f"Error: {e}")Code language: Python (python)

This displays the actual error message returned by psycopg or PostgreSQL. We use an f-string to make the message more readable and informative.

Insert multiple rows into a table #

The following script inserts multiple rows into the products table:

import psycopg
from config import config

sql = """
INSERT INTO products (name, price, safety_stock, gross_weight)
VALUES (%s, %s, %s, %s)
"""

products = [
    ("Galaxy S23 Ultra", 1199.99, 15, 0.23),
    ("Pixel 7 Pro", 899.99, 8, 0.21),
]

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.executemany(sql, products)
            print("Multiple rows inserted successfully.")
except Exception as e:
    print(f"Error: {e}")
Code language: Python (python)

Output:

Multiple rows inserted successfully.
Code language: Python (python)

How it works:

Step 1. Import required modules

import psycopg
from config import config
Code language: Python (python)

import psycopg loads the PostgreSQL driver for Python.
from config import config imports the database connection settings.

Step 2. Define the SQL INSERT statement

sql = """
INSERT INTO products (name, price, safety_stock, gross_weight)
VALUES (%s, %s, %s, %s)
"""Code language: Python (python)

This SQL command inserts data into the products table.
%s are placeholders for values.
We’ll provide the values later from Python.

Step 3. Prepare the data

products = [
    ("Galaxy S23 Ultra", 1199.99, 15, 0.23),
    ("Pixel 7 Pro", 899.99, 8, 0.21),
]Code language: Python (python)

products stores a list of rows to insert.
Each tuple represents one row.
The values in the tuple match the column order in the INSERT statement:
- name
- price
- safety_stock
- gross_weight

Step 4. Start the try block

try:Code language: Python (python)

This tells Python to catch any errors if an exception occurs.

Step 5. Open a database connection

with psycopg.connect(**config) as conn:
Code language: Python (python)

This creates a connection to PostgreSQL using the settings in config.
**config passes the connection arguments as dictionary values.
with statement automatically closes the database connection when done.

Step 6. Create a cursor

with conn.cursor() as cur:Code language: Python (python)

Python uses a cursor to run SQL statements.
The cursor() returns a cur which is the cursor object.

Step 7. Insert multiple rows

cur.executemany(sql, products)Code language: Python (python)

The executemany() executes the same INSERT statement for each tuple in the products list, which inserts all rows in one step.

Step 8. Print a success message

print("Multiple rows inserted successfully.")Code language: Python (python)

Step 9. Handle errors

except Exception as e:
    print(f"Error: {e}")Code language: Python (python)

If any error happens, Python jumps to the except block. The e stores the error message.
This code prints the error instead of crashing.

Insert a row into a table and return it #

The following script inserts one row into a table and gets the inserted ID:

import psycopg
from config import config

sql = """
INSERT INTO products (name, price, safety_stock, gross_weight)
VALUES (%s, %s, %s, %s)
RETURNING product_id, name, price, safety_stock, gross_weight
"""

data = ("Xperia 1 IV", 1299.99, 7, 0.19)

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, data)
            row = cur.fetchone()
            print("Inserted row:")
            print(row)
except Exception as e:
    print(f"Error: {e}")
Code language: Python (python)

Output:

Inserted row:
(4, 'Xperia 1 IV', Decimal('1299.99'), 7, Decimal('0.19'))
Code language: Python (python)

How it works:

Step 1. Import the required modules

import psycopg
from config import configCode language: Python (python)

This imports the psycopg and database connection settings stored in the config variable.

Step 2. Define the SQL statement

sql = """
INSERT INTO products (name, price, safety_stock, gross_weight)
VALUES (%s, %s, %s, %s)
RETURNING product_id, name, price, safety_stock, gross_weight
"""Code language: Python (python)

This INSERT statement inserts a new row into the products table and returns the inserted record, including the generated product_id.

Step 3. Prepare the input data

data = ("Xperia 1 IV", 1299.99, 7, 0.19)Code language: Python (python)

These values match the placeholders in the INSERT statement and are passed safely as parameters, preventing SQL injection.

Step 4. Open a database connection

with psycopg.connect(**config) as conn:Code language: Python (python)

The with statement opens a database connection and ensures it is automatically closed when done.

Step 5. Create a cursor

with conn.cursor() as cur:Code language: Python (python)

We use a cursor to execute SQL commands and fetch results from the database.

Step 6. Execute the query

cur.execute(sql, data)Code language: Python (python)

The execute() sends the INSERT statement and its parameters to PostgreSQL for execution.

Step 7. Read the returned row

row = cur.fetchone()
print("Inserted row:")
print(row)Code language: Python (python)

The RETURNING clause allows you to fetch the inserted row immediately, which is useful for the insert verification.

Step 8. Handle errors

except Exception as e:
    print(f"Error: {e}")Code language: Python (python)

The try/except block catches any errors and prints the error message instead of crashing the program.

Summary #

Use psycopg.connect() to open a PostgreSQL connection.
Use the cursor() to execute SQL.
Use the %s placeholders and execute(sql, data) to safely insert data into a table.
Use the with blocks to manage resources automatically.
Use the try/except prevents your script from crashing and helps with debugging.

Was this tutorial helpful ?