PostgreSQL Python

To connect to PostgreSQL from Python, you use Psycopg 3. Psycopg 3 is a new PostgreSQL database adapter for Python. Psycopg 3 is a complete implementation of the Python DB API 2.0 spec. Additionally, Psycopg 3 introduces new capabilities, including asynchronous support, COPY support, connection pools, and typing-related features.

In this tutorial series, you will learn how to:

• Connect to PostgreSQL from Python using Psycopg 3
• Execute SQL statements
• Insert, retrieve, update, and delete data
• Manage transactions

Install psycopg3 #

Step 1. Create a new project directory and navigate to it:

mkdir python_postgres
cd python-python_postgresCode language: Shell Session (shell)

Step 2. Create a virtual environment:

python -m venv .venvCode language: Shell Session (shell)

Step 3. Activate the virtual environment on macOS or Linux:

source .venv/bin/activateCode language: Shell Session (shell)

on Windows:

.venv/Scripts/activateCode language: Shell Session (shell)

Step 4. Install Psycopg 3 module:

pip install "psycopg[binary]"Code language: JavaScript (javascript)

Note that the package name is psycopg not psycopg3.

Create a new database #

Step 1. Connect to the PostgreSQL server via the psql client:

psql -U postgresCode language: Shell Session (shell)

Step 2. Create a new database:

CREATE DATABASE pyinventory;Code language: SQL (Structured Query Language) (sql)

Step 3. Exit psql:

exitCode language: Shell Session (shell)

We’ll use the pyinventory across the tutorial series:

• Connecting to the PostgreSQL – Learn how to create a database connection so you can start building real Python apps that work with PostgreSQL.
• Creating tables – Learn how to create new tables programmatically from Python.
• Inserting data into a table – Learn how to insert data into a table so your app can save data effectively.
• Updating data – learn how to modify data in a table safely and efficiently.
• Retrieve data from a table – Learn how to query data from tables.
• Transaction – shows you how to perform transactions to keep your data accurate and reliable.
• Calling a PostgreSQL function – Learn how to call a PostgreSQL function to reuse business logic and simplify Python code.
• Calling a PostgreSQL procedure – Show you how to call a procedure to handle more advanced database operations directly from Python.
• Managing PostgreSQL BLOB data in Python – Learn how to store and retrieve binary large objects (BLOBs) such as images and documents in PostgreSQL.