Calling all startups: Apply to the Neon Startup program and get up to 100k in credits

Connect a Python application to Neon Postgres

Learn how to run SQL queries in Neon from Python using psycopg2 or asyncpg

This guide describes how to create a Neon project and connect to it from a Python application using popular Postgres drivers like Psycopg (psycopg2), a synchronous database adapter, and asyncpg, an asynchronous adapter for use with asyncio.

You'll learn how to connect to your Neon database from a Python application and perform basic Create, Read, Update, and Delete (CRUD) operations.

Prerequisites

  • A Neon account. If you do not have one, see Sign up.
  • Python 3.8 or later. If you do not have Python installed, install it from the Python website.
  1. Create a Neon project

    If you do not have one already, create a Neon project.

    1. Navigate to the Projects page in the Neon Console.
    2. Click New Project.
    3. Specify your project settings and click Create Project.

    Your project is created with a ready-to-use database named neondb. In the following steps, you will connect to this database from your Python application.

  2. Create a Python project

    For your Python project, create a project directory, set up a virtual environment, and install the required libraries.

    1. Create a project directory and change into it.

      mkdir neon-python-quickstart
      cd neon-python-quickstart

      Open the directory in your preferred code editor (e.g., VS Code, PyCharm).

    2. Create and activate a Python virtual environment. This isolates your project's dependencies from your system's Python environment.

      MacOS / Linux / Windows Subsystem for Linux (WSL)
      Windows
      # Create a virtual environment
      python3 -m venv venv
      
      # Activate the virtual environment
      source venv/bin/activate
    3. Install the required libraries using pip.

      • psycopg2-binary: The synchronous database adapter for connecting to Postgres.
      • asyncpg: The asynchronous database adapter for connecting to Postgres.
      • python-dotenv: A helper library to manage environment variables.
      pip install psycopg2-binary asyncpg python-dotenv

      Install either psycopg2-binary or asyncpg, depending on whether you want to use synchronous or asynchronous code.

  3. Store your Neon connection string

    Create a file named .env in your project's root directory. This file will securely store your database connection string.

    1. In the Neon Console, select your project on the Dashboard.
    2. Click Connect on your Project Dashboard to open the Connect to your database modal. Connection modal
    3. Copy the connection string, which includes your password.
    4. Add the connection string to your .env file as shown below.
      DATABASE_URL="postgresql://[user]:[password]@[neon_hostname]/[dbname]?sslmode=require&channel_binding=require"

      Replace [user], [password], [neon_hostname], and [dbname] with your actual database credentials.

  4. Examples

    This section provides example Python scripts that demonstrate how to connect to your Neon database and perform basic operations such as creating a table, reading data, updating data, and deleting data.

    Create a table and insert data

    In your project directory, create a file named create_table.py and add the code for your preferred library. This script connects to your Neon database, creates a table named books, and inserts some sample data into it.

    psycopg2
    asyncpg
    import os
    
    import psycopg2
    from dotenv import load_dotenv
    
    # Load environment variables from .env file
    load_dotenv()
    
    # Get the connection string from the environment variable
    conn_string = os.getenv("DATABASE_URL")
    conn = None
    
    try:
        with psycopg2.connect(conn_string) as conn:
            print("Connection established")
    
            # Open a cursor to perform database operations
            with conn.cursor() as cur:
                # Drop the table if it already exists
                cur.execute("DROP TABLE IF EXISTS books;")
                print("Finished dropping table (if it existed).")
    
                # Create a new table
                cur.execute("""
                    CREATE TABLE books (
                        id SERIAL PRIMARY KEY,
                        title VARCHAR(255) NOT NULL,
                        author VARCHAR(255),
                        publication_year INT,
                        in_stock BOOLEAN DEFAULT TRUE
                    );
                """)
                print("Finished creating table.")
    
                # Insert a single book record
                cur.execute(
                    "INSERT INTO books (title, author, publication_year, in_stock) VALUES (%s, %s, %s, %s);",
                    ("The Catcher in the Rye", "J.D. Salinger", 1951, True),
                )
                print("Inserted a single book.")
    
                # Data to be inserted
                books_to_insert = [
                    ("The Hobbit", "J.R.R. Tolkien", 1937, True),
                    ("1984", "George Orwell", 1949, True),
                    ("Dune", "Frank Herbert", 1965, False),
                ]
    
                # Insert multiple books at once
                cur.executemany(
                    "INSERT INTO books (title, author, publication_year, in_stock) VALUES (%s, %s, %s, %s);",
                    books_to_insert,
                )
    
                print("Inserted 3 rows of data.")
    
                # Commit the changes to the database
                conn.commit()
    
    except Exception as e:
        print("Connection failed.")
        print(e)

    The above code does the following:

    • Load the connection string from the .env file.
    • Connect to the Neon database.
    • Drop the books table if it already exists to ensure a clean slate.
    • Create a table named books with columns for id, title, author, publication_year, and in_stock.
    • Insert a single book record.
    • Insert multiple book records.
    • Commit the changes to the database.

    Run the script using the following command:

    python create_table.py

    When the code runs successfully, it produces the following output:

    Connection established
    Finished dropping table (if it existed).
    Finished creating table.
    Inserted a single book.
    Inserted 3 rows of data.

    Read data

    In your project directory, create a file named read_data.py. This script connects to your Neon database and retrieves all rows from the books table.

    psycopg2
    asyncpg
    import os
    
    import psycopg2
    from dotenv import load_dotenv
    
    load_dotenv()
    
    conn_string = os.getenv("DATABASE_URL")
    conn = None
    
    try:
        with psycopg2.connect(conn_string) as conn:
            print("Connection established")
            with conn.cursor() as cur:
                # Fetch all rows from the books table
                cur.execute("SELECT * FROM books ORDER BY publication_year;")
                rows = cur.fetchall()
    
                print("\n--- Book Library ---")
                for row in rows:
                    print(
                        f"ID: {row[0]}, Title: {row[1]}, Author: {row[2]}, Year: {row[3]}, In Stock: {row[4]}"
                    )
                print("--------------------\n")
    
    except Exception as e:
        print("Connection failed.")
        print(e)

    The above code does the following:

    • Load the connection string from the .env file.
    • Connect to the Neon database.
    • Use a SQL SELECT statement to fetch all rows from the books table, ordered by publication_year.
    • Print each book's details in a formatted output.

    Run the script using the following command:

    python read_data.py

    When the code runs successfully, it produces the following output:

    Connection established
    
    --- Book Library ---
    ID: 2, Title: The Hobbit, Author: J.R.R. Tolkien, Year: 1937, In Stock: True
    ID: 3, Title: 1984, Author: George Orwell, Year: 1949, In Stock: True
    ID: 1, Title: The Catcher in the Rye, Author: J.D. Salinger, Year: 1951, In Stock: True
    ID: 4, Title: Dune, Author: Frank Herbert, Year: 1965, In Stock: False
    --------------------

    Update data

    In your project directory, create a file named update_data.py. This script connects to your Neon database and updates the stock status of the book 'Dune' to True.

    psycopg2
    asyncpg
    import os
    
    import psycopg2
    from dotenv import load_dotenv
    
    load_dotenv()
    conn_string = os.getenv("DATABASE_URL")
    conn = None
    
    try:
        with psycopg2.connect(conn_string) as conn:
            print("Connection established")
            with conn.cursor() as cur:
                # Update a data row in the table
                cur.execute(
                    "UPDATE books SET in_stock = %s WHERE title = %s;", (True, "Dune")
                )
                print("Updated stock status for 'Dune'.")
    
                # Commit the changes
                conn.commit()
    
    except Exception as e:
        print("Connection failed.")
        print(e)

    The above code does the following:

    • Load the connection string from the .env file.
    • Connect to the Neon database.
    • Use a SQL UPDATE statement to change the in_stock status of the book 'Dune' to True.
    • Commit the changes to the database.

    Run the script using the following command:

    python update_data.py

    After running this script, you can run read_data.py again to verify that the row was updated.

    python read_data.py

    When the code runs successfully, it produces the following output:

    Connection established
    
    --- Book Library ---
    ID: 2, Title: The Hobbit, Author: J.R.R. Tolkien, Year: 1937, In Stock: True
    ID: 3, Title: 1984, Author: George Orwell, Year: 1949, In Stock: True
    ID: 1, Title: The Catcher in the Rye, Author: J.D. Salinger, Year: 1951, In Stock: True
    ID: 4, Title: Dune, Author: Frank Herbert, Year: 1965, In Stock: True
    --------------------

    You can see that the stock status for 'Dune' has been updated to True.

    Delete data

    In your project directory, create a file named delete_data.py. This script connects to your Neon database and deletes the book '1984' from the books table.

    psycopg2
    asyncpg
    import os
    
    import psycopg2
    from dotenv import load_dotenv
    
    load_dotenv()
    conn_string = os.getenv("DATABASE_URL")
    conn = None
    
    try:
        with psycopg2.connect(conn_string) as conn:
            print("Connection established")
            with conn.cursor() as cur:
                # Delete a data row from the table
                cur.execute("DELETE FROM books WHERE title = %s;", ("1984",))
                print("Deleted the book '1984' from the table.")
    
                # Commit the changes
                conn.commit()
    
    except Exception as e:
        print("Connection failed.")
        print(e)

    The above code does the following:

    • Load the connection string from the .env file.
    • Connect to the Neon database.
    • Use a SQL DELETE statement to remove the book '1984' from the books table.
    • Commit the changes to the database.

    Run the script using the following command:

    python delete_data.py

    After running this script, you can run read_data.py again to verify that the row was deleted.

    python read_data.py

    When the code runs successfully, it produces the following output:

    Connection established
    
    --- Book Library ---
    ID: 2, Title: The Hobbit, Author: J.R.R. Tolkien, Year: 1937, In Stock: True
    ID: 1, Title: The Catcher in the Rye, Author: J.D. Salinger, Year: 1951, In Stock: True
    ID: 4, Title: Dune, Author: Frank Herbert, Year: 1965, In Stock: True
    --------------------

    You can see that the book '1984' has been successfully deleted from the books table.

Next steps: Using an ORM or framework

While this guide demonstrates how to connect to Neon using raw SQL queries, for more advanced and maintainable data interactions in your Python applications, consider using an Object-Relational Mapping (ORM) framework. ORMs not only let you work with data as objects but also help manage schema changes through automated migrations keeping your database structure in sync with your application models.

Explore the following resources to learn how to integrate ORMs with Neon:

Source code

You can find the source code for the applications described in this guide on GitHub.

Resources

Need help?

Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more details, see Getting Support.

Last updated on

Was this page helpful?