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
.
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.
Create a Neon project
If you do not have one already, create a Neon project.
- Navigate to the Projects page in the Neon Console.
- Click New Project.
- Specify your project settings and click Create Project.
Your project is created with a ready-to-use database named
neondb
. In the following steps, we'll connect to this database.Create a Python project
For your Python project, create a project directory, set up a virtual environment, and install the required libraries.
-
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).
-
Create and activate a Python virtual environment. This isolates your project's dependencies from your system's Python environment.
# Create a virtual environment python3 -m venv venv # Activate the virtual environment source venv/bin/activate
-
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
orasyncpg
, depending on whether you want to use synchronous or asynchronous code.
-
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.- In the Neon Console, select your project on the Dashboard.
- Click Connect on your Project Dashboard to open the Connect to your database modal.
- Copy the connection string, which includes your password.
- Add the connection string to your
.env
file as shown below.DATABASE_URL="postgresql://[user]:[password]@[neon_hostname]/[dbname]?sslmode=require&channel_binding=require"
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 namedbooks
, and inserts some sample data into it.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)
In the above code, we:
- 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 forid
,title
,author
,publication_year
, andin_stock
. - Insert a single book record.
- Insert multiple book records.
- Commit the changes to the database.
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 thebooks
table.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)
In the above code, we:
- Load the connection string from the
.env
file. - Connect to the Neon database.
- Use a SQL
SELECT
statement to fetch all rows from thebooks
table, ordered bypublication_year
. - Print each book's details in a formatted output.
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' toTrue
.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)
In the above code, we:
- Load the connection string from the
.env
file. - Connect to the Neon database.
- Use a SQL
UPDATE
statement to change thein_stock
status of the book 'Dune' toTrue
. - Commit the changes to the database.
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 --------------------
We 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 thebooks
table.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)
In the above code, we:
- Load the connection string from the
.env
file. - Connect to the Neon database.
- Use a SQL
DELETE
statement to remove the book '1984' from thebooks
table. - Commit the changes to the database.
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 --------------------
We can see that the book '1984' has been deleted from the library.
- Load the connection string from the
Source code
You can find the source code for the applications described in this guide on GitHub.
Get started with Python and Neon using psycopg2
Get started with Python and Neon using psycopg2
Get started with Python and Neon using asyncpg
Get started with Python and Neon using asyncpg
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.