[Python] Basics of PostgreSQL Operations: Connection and Data Retrieval with psycopg2

2025年12月31日

When manipulating a PostgreSQL database from Python, psycopg2 is the most widely used library. It comprehensively supports PostgreSQL features and is known for being fast and robust.

A distinct difference from other drivers like MySQLdb is its powerful feature that automatically converts (adapts) Python tuples and lists into SQL arrays or lists for IN clauses.

Here, I will explain how to manage connection settings and how to retrieve data using list data in conditions.

Library Installation

For development environments and general use, it is recommended to install psycopg2-binary, which includes pre-built binaries.

pip install psycopg2-binary

Executable Sample Code

The following code assumes an order management system for an e-commerce site. It extracts data for “orders over a certain amount” and “within specific delivery region codes.”

A key feature of psycopg2 is that for the SQL IN clause, you can simply pass a Python tuple to a single placeholder %s, and it will automatically expand into the correct SQL syntax (there is no need to manually increase placeholders using string manipulation).

import psycopg2
import os

# Connection settings
# In production, it is recommended to load these from environment variables or a config file
DB_CONFIG = {
    "user": "order_manager",
    "password": "secure_db_password",
    "host": "localhost",
    "port": "5432",
    "dbname": "ec_system"
}

def main():
    # Search criteria
    # Amount is 5000 or more, AND region code is one of 'TK', 'OS', 'NG'
    min_amount = 5000
    target_regions = ('TK', 'OS', 'NG')

    # Constructing SQL
    # In psycopg2, the content of the IN clause can be represented by a single %s
    sql = """
        SELECT 
            order_id, 
            customer_name, 
            amount, 
            region_code, 
            order_date
        FROM 
            orders 
        WHERE 
            amount > %s 
            AND region_code IN %s
    """

    print(f"Connecting to database: {DB_CONFIG['dbname']}...")

    try:
        # Database connection
        # Unpack the dictionary and pass it as arguments
        with psycopg2.connect(**DB_CONFIG) as conn:
            
            # Getting the cursor
            with conn.cursor() as cur:
                
                # Execute SQL
                # The second argument is passed as a tuple.
                # target_regions (tuple) itself corresponds to the second %s.
                cur.execute(sql, (min_amount, target_regions))
                
                # Fetch results
                rows = cur.fetchall()
                
                # Display results
                print(f"\nHit Count: {len(rows)}")
                print("-" * 40)
                
                for row in rows:
                    # row is a tuple (order_id, name, amount, region, date)
                    print(f"ID: {row[0]} | Amount: {row[2]} | Region: {row[3]}")

    except psycopg2.Error as e:
        print(f"Database error: {e.pgcode} - {e.pgerror}")
    except Exception as e:
        print(f"System error: {e}")

if __name__ == "__main__":
    main()

Explanation of Key Code Points

1. Dictionary Management of Connection Parameters

Using dictionary unpacking like psycopg2.connect(**DB_CONFIG) improves code readability. The default port for PostgreSQL is 5432.

2. Context Manager (with Syntax)

By using with psycopg2.connect(...) as conn: and with conn.cursor() as cur:, the connection and cursor are automatically closed when processing ends or an exception occurs, preventing resource leaks.

Additionally, when exiting the conn with block, the transaction is automatically committed if there are no exceptions, and rolled back if an exception occurs.

3. Passing Tuples to the IN Clause (Important)

In other drivers like MySQLdb, you must prepare placeholders corresponding to the number of elements in the list (e.g., %s, %s, ...). However, psycopg2 automatically converts a Python tuple into the SQL list representation (item1, item2, ...).