Detailed Guide to Using PostgreSQL Database Connections in Python

Connecting to PostgreSQL database in Python, the most commonly used library is psycopg2. Here are the detailed usage guides:

Install psycopg2

First, you need to install the psycopg2 library:

pip install psycopg2
# Or use binary version (install faster)pip install psycopg2-binary

Basic connections and operations

1. Establish a database connection

import psycopg2

# Create a connectionconn = (
    dbname="your_database",
    user="your_username",
    password="your_password",
    host="your_host",
    port="your_port"
)

# Create a cursor objectcur = ()

2. Execute SQL query

# Perform a simple query("SELECT * FROM your_table LIMIT 5;")

# Get resultsrows = ()
for row in rows:
    print(row)

3. Execute parameterized query (prevent SQL injection)

# Use parameterized queryuser_id = 5
("SELECT * FROM users WHERE id = %s;", (user_id,))
user = ()
print(user)

4. Insert data

# Insert a single piece of data(
    "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id;",
    ('John Doe', 'john@')
)
user_id = ()[0]
()  # Transaction must be submittedprint(f"Inserted userID: {user_id}")

# Batch Insertusers_data = [
    ('Alice', 'alice@'),
    ('Bob', 'bob@'),
    ('Charlie', 'charlie@')
]
(
    "INSERT INTO users (name, email) VALUES (%s, %s);",
    users_data
)
()

5. Update data

(
    "UPDATE users SET email = %s WHERE id = %s;",
    ('new_email@', 1)
)
()

6. Delete data

(
    "DELETE FROM users WHERE id = %s;",
    (5,)
)
()

Using the context manager (recommended)

# Use with statement to automatically manage connectionswith (
    dbname="your_database",
    user="your_username",
    password="your_password",
    host="your_host"
) as conn:
    with () as cur:
        ("SELECT * FROM users;")
        for row in cur:
            print(row)
    # There is no need to explicitly call commit() or close(), the with statement will be processed automatically

Using connection pools (for web applications)

For scenarios such as web applications that require frequent connection to the database, you can use the connection pool:

from psycopg2 import pool

# Create a connection poolconnection_pool = (
    minconn=1,
    maxconn=10,
    dbname="your_database",
    user="your_username",
    password="your_password",
    host="your_host"
)

# Get connection from the connection poolconn = connection_pool.getconn()
cur = ()
("SELECT * FROM users;")
# ...Perform an action ...
# Return the connection to the connection poolconnection_pool.putconn(conn)

Using SQLAlchemy (ORM method)

If you prefer to use ORM, you can install SQLAlchemy:

pip install sqlalchemy psycopg2-binary

Then use:

from sqlalchemy import create_engine, text

# Create an engineengine = create_engine('postgresql://user:password@localhost:5432/dbname')

# Execute querywith () as connection:
    result = (text("SELECT * FROM users;"))
    for row in result:
        print(row)

Things to note

Always remember to commit transactions (()) or rollback (())

Prevent SQL injection using parameterized queries

Close cursor and connection after operation is completed

For production environments, consider using connection pools

Store database credentials in environment variables or configuration files, do not hardcode them in code

The above is the detailed usage guide for Python's PostgreSQL database connection. For more information about Python PostgreSQL database connection, please follow my other related articles!