How to connect a PostgreSQL database?

Connecting to PostgreSQL Database in Python: A Comprehensive Guide for Developers

Working with databases is a crucial part of any modern software project. PostgreSQL is one of the most popular relational DBMSs, known for its reliability, high performance, and rich functionality.

In this article, we will thoroughly analyze how to connect to a PostgreSQL database in Python, work with data using SELECT and DELETE, and explore the use of the psycopg2 library with examples. We will also touch on working with SQLite and its differences from PostgreSQL.

Advantages of PostgreSQL for Developers

PostgreSQL is in high demand among developers due to the following characteristics:

• Support for complex SQL queries and extended data types
• High reliability and data security
• Support for transactions, indexes, triggers, and views
• Excellent scalability for large projects
• Open-source code and active community

PostgreSQL also supports JSON data, arrays, user-defined data types, and many extensions, making it a versatile solution for various tasks.

Installing PostgreSQL and Setting Up the Environment

Installing PostgreSQL on Various Operating Systems

• Windows: Download the installation file from the official PostgreSQL website and follow the instructions of the installation wizard.
• Linux (Ubuntu/Debian):

sudo apt update
sudo apt install postgresql postgresql-contrib

• macOS:

brew install postgresql

Creating a Database and User

After installing PostgreSQL, you need to create a database and user to work with:

CREATE DATABASE my_database;
CREATE USER my_user WITH PASSWORD 'my_password';
GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

Setting Up Database Access

For secure connection, it is recommended to:

• Configure the pg_hba.conf file to manage access
• Set strong passwords for users
• Use SSL connections for remote connections
• Limit network access through a firewall

Connecting to PostgreSQL from Python

Installing and Configuring psycopg2

The psycopg2 library is most commonly used to work with PostgreSQL in Python. Install it via pip:

pip install psycopg2

If you encounter compilation problems, use the binary version:

pip install psycopg2-binary

Basic Database Connection

import psycopg2

try:
    connection = psycopg2.connect(
        database="my_database",
        user="my_user",
        password="my_password",
        host="localhost",
        port="5432"
    )
    print("Connection established successfully!")
except Exception as e:
    print(f"Connection error: {e}")
finally:
    if connection:
        connection.close()
        print("Connection closed.")

Using a Context Manager

It is recommended to use a context manager for automatic connection management:

import psycopg2

with psycopg2.connect(
    database="my_database",
    user="my_user",
    password="my_password",
    host="localhost",
    port="5432"
) as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT version();")
    version = cursor.fetchone()
    print(f"PostgreSQL version: {version}")

Executing SQL Queries in PostgreSQL

Executing SELECT Queries

import psycopg2

connection = psycopg2.connect(
    database="my_database",
    user="my_user",
    password="my_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM employees;")
rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
connection.close()

Working with Different Data Retrieval Methods

psycopg2 provides several methods for retrieving results:

• fetchone() — retrieves one record
• fetchall() — retrieves all records
• fetchmany(size) — retrieves the specified number of records

cursor.execute("SELECT id, name FROM employees;")

# Retrieving one record
first_row = cursor.fetchone()
print(f"First record: {first_row}")

# Retrieving multiple records
next_rows = cursor.fetchmany(5)
print(f"Next 5 records: {next_rows}")

Executing DELETE Queries

import psycopg2

connection = psycopg2.connect(
    database="my_database",
    user="my_user",
    password="my_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()

try:
    cursor.execute("DELETE FROM employees WHERE id = %s;", (1,))
    connection.commit()
    print("Record deleted successfully.")
except Exception as e:
    connection.rollback()
    print(f"Deletion error: {e}")

cursor.close()
connection.close()

Security When Working with Queries

It is important to always use parameterized queries to protect against SQL injections:

# Correct way
user_id = 5
cursor.execute("SELECT * FROM employees WHERE id = %s;", (user_id,))

# Incorrect way (vulnerable to SQL injections)
cursor.execute(f"SELECT * FROM employees WHERE id = {user_id};")

The psycopg2 Library: Features and Peculiarities

Key Features of psycopg2

psycopg2 is the most popular library for working with PostgreSQL in Python due to the following features:

• Full support for transactions and autocommit management
• Working with large amounts of data through server-side cursors
• Support for connection pools for performance optimization
• Ability to work with asynchronous queries
• Support for all PostgreSQL data types

Working with Transactions

import psycopg2

connection = psycopg2.connect(
    database="my_database",
    user="my_user",
    password="my_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()

try:
    cursor.execute("BEGIN;")
    cursor.execute("INSERT INTO employees (name, position) VALUES (%s, %s);", 
                   ("Ivan Ivanov", "Developer"))
    cursor.execute("UPDATE employees SET salary = %s WHERE name = %s;", 
                   (50000, "Ivan Ivanov"))
    connection.commit()
    print("Transaction completed successfully.")
except Exception as e:
    connection.rollback()
    print(f"Transaction error: {e}")
finally:
    cursor.close()
    connection.close()

Configuring Autocommit

connection.autocommit = False  # Disabling autocommit
connection.autocommit = True   # Enabling autocommit

Advanced Database Operations

Parameterized Queries

# Search by multiple parameters
cursor.execute(
    "SELECT * FROM employees WHERE department = %s AND salary > %s;",
    ("IT", 40000)
)

# Using named parameters
cursor.execute(
    "SELECT * FROM employees WHERE department = %(dept)s AND salary > %(min_salary)s;",
    {"dept": "IT", "min_salary": 40000}
)

Inserting Data

# Inserting one record
cursor.execute(
    "INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s);",
    ("Maria Petrova", "Analyst", 45000)
)

# Mass data insertion
employees_data = [
    ("Alexey Sidorov", "Tester", 40000),
    ("Elena Kozlova", "Designer", 35000),
    ("Dmitry Nikolaev", "DevOps", 55000)
]

cursor.executemany(
    "INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s);",
    employees_data
)
connection.commit()

Working with Connection Pools

from psycopg2 import pool

# Creating a connection pool
connection_pool = pool.SimpleConnectionPool(
    1, 10,
    user="my_user",
    password="my_password",
    host="localhost",
    port="5432",
    database="my_database"
)

# Getting a connection from the pool
connection = connection_pool.getconn()

# Working with the connection
cursor = connection.cursor()
cursor.execute("SELECT * FROM employees;")
results = cursor.fetchall()

# Returning the connection to the pool
connection_pool.putconn(connection)

Comparing PostgreSQL and SQLite

Working with SQLite in Python

SQLite is an embedded DBMS that does not require a separate server and is often used in small projects:

import sqlite3

connection = sqlite3.connect('my_database.db')
cursor = connection.cursor()

# Deleting a record in SQLite
cursor.execute("DELETE FROM employees WHERE id = ?", (1,))
connection.commit()

cursor.close()
connection.close()

Key Differences Between PostgreSQL and SQLite

• Scalability:
  • PostgreSQL: high scalability, support for millions of records
  • SQLite: limited scalability, suitable for small projects
• Performance:
  • PostgreSQL: high performance due to server architecture
  • SQLite: good performance for local operations
• Multi-user access:
  • PostgreSQL: full support for concurrent access by multiple users
  • SQLite: limited support, database-level locking
• Data types:
  • PostgreSQL: rich set of built-in data types and the ability to create custom ones
  • SQLite: limited set of data types
• Usage:
  • PostgreSQL: large projects, web applications, analytical systems
  • SQLite: prototypes, mobile applications, embedded systems

Solving Common Problems

Problems Connecting to PostgreSQL

If you encounter connection problems, check:

• Correctness of connection parameters (host, port, database name)
• PostgreSQL server status
• pg_hba.conf file settings
• Port availability through a firewall
• User rights to access the database

Handling psycopg2.OperationalError Errors

import psycopg2

try:
    connection = psycopg2.connect(
        database="my_database",
        user="my_user",
        password="my_password",
        host="localhost",
        port="5432"
    )
except psycopg2.OperationalError as e:
    print(f"Connection error: {e}")
    print("Check connection parameters and server availability")

Getting Database Information

# Getting a list of all tables
cursor.execute(
    "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';"
)
tables = cursor.fetchall()
print(f"Tables in the database: {tables}")

# Getting information about table columns
cursor.execute(
    "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'employees';"
)
columns = cursor.fetchall()
print(f"Columns of the employees table: {columns}")

Working with Large Datasets

# Using a server cursor for large results
cursor = connection.cursor(name='large_result_cursor')
cursor.execute("SELECT * FROM large_table;")

while True:
    rows = cursor.fetchmany(1000)
    if not rows:
        break
    # Processing a portion of the data
    for row in rows:
        process_row(row)

cursor.close()

Alternatives to psycopg2

Using ORM Libraries

To simplify working with databases, you can use ORM libraries:

• SQLAlchemy:

from sqlalchemy import create_engine, text

engine = create_engine('postgresql://my_user:my_password@localhost/my_database')
with engine.connect() as connection:
    result = connection.execute(text("SELECT * FROM employees"))
    for row in result:
        print(row)

• Django ORM:

# In a Django project
from myapp.models import Employee

# Getting all employees
employees = Employee.objects.all()

# Deleting an employee
Employee.objects.filter(id=1).delete()

Asynchronous Database Operations

import asyncio
import asyncpg

async def main():
    connection = await asyncpg.connect(
        user='my_user',
        password='my_password',
        database='my_database',
        host='localhost'
    )
    
    rows = await connection.fetch('SELECT * FROM employees')
    for row in rows:
        print(row)
    
    await connection.close()

asyncio.run(main())

Conclusion

Now you know how to connect to a PostgreSQL database from Python, execute basic SQL queries, process data, and optimize work using connection pools. Using psycopg2 ensures efficient and secure database operations.

For small local projects, you can use SQLite, but for serious and heavily loaded projects, PostgreSQL remains the preferred choice due to its reliability, performance, and rich functionality.