PostgreSQL

PostgreSQL is a relational database commonly used with FastAPI for storing application data.

FastAPI can connect to PostgreSQL and execute SQL queries to read and manage data.

Install psycopg

psycopg is a PostgreSQL adapter for Python.

Install it using:

pip install psycopg

Connecting to PostgreSQL

import psycopg

connection = psycopg.connect(
    "postgresql://postgres:password@localhost/mydb"
)

cursor = connection.cursor()

cursor.execute(
    "SELECT * FROM users"
)

users = cursor.fetchall()

print(users)

Understanding the Code

First, import psycopg:

import psycopg

Then create a connection to the PostgreSQL database:

connection = psycopg.connect(
    "postgresql://postgres:password@localhost/mydb"
)

The connection string contains:

postgresql://username:password@host/database

In this example:

Username: postgres
Password: password
Host: localhost
Database: mydb

Creating a Cursor

A cursor is used to execute SQL queries:

cursor = connection.cursor()

Then execute a SQL query:

cursor.execute(
    "SELECT * FROM users"
)

This query retrieves all records from the users table.

Fetching Data

Use fetchall() to retrieve all records returned by the query:

users = cursor.fetchall()

print(users)

Output

For example, if the users table contains two records:

[
    (1, 'Tarun', 'tarun@example.com'),
    (2, 'Rahul', 'rahul@example.com')
]

Complete Example

import psycopg

connection = psycopg.connect(
    "postgresql://postgres:password@localhost/mydb"
)

cursor = connection.cursor()

cursor.execute(
    "SELECT * FROM users"
)

users = cursor.fetchall()

print(users)

Summary

PostgreSQL can store application data with FastAPI. Using psycopg, Python applications can connect to PostgreSQL and execute SQL queries.

The basic flow is:

Connect to PostgreSQL
        ↓
Create Cursor
        ↓
Execute SQL Query
        ↓
Fetch Results