如何连接到Python中的PostgreSQL数据库？

安装psycopg2-binary库：使用pip install psycopg2-binary进行安装；2. 导入psycopg2并建立连接：提供host、database、user、password和port参数连接数据库；3. 使用cursor执行SQL查询：通过cursor.execute()执行语句并获取结果；4. 安全关闭连接：在finally块中关闭cursor和connection；5. 推荐使用环境变量存储敏感信息：避免硬编码用户名和密码；6. 使用contextmanager管理连接：确保资源正确释放；7. 可选使用SQLAlchemy：通过create_engine连接，适用于ORM场景。连接PostgreSQL数据库的最常用方法是使用psycopg2库，需正确配置连接参数并妥善管理连接生命周期，最终实现安全、稳定的数据库交互。

To connect to a PostgreSQL database in Python, the most common and reliable way is using the psycopg2 library. Here's how to do it step by step.

✅ Install psycopg2

First, install the psycopg2 package using pip:

pip install psycopg2-binary

psycopg2-binary is recommended for development and testing. For production, consider building psycopg2 from source.

✅ Import and Connect

Use the following code to establish a connection:

import psycopg2

try:
    connection = psycopg2.connect(
        host='localhost',
        database='your_database_name',
        user='your_username',
        password='your_password',
        port='5432'
    )

    print("Connection successful!")

    # Create a cursor object
    cursor = connection.cursor()

    # Execute a test query
    cursor.execute("SELECT version();")
    db_version = cursor.fetchone()
    print(f"PostgreSQL version: {db_version}")

except Exception as error:
    print(f"Error connecting to PostgreSQL: {error}")

finally:
    # Close the connection
    if connection:
        cursor.close()
        connection.close()
        print("PostgreSQL connection closed.")

? Connection Parameters Explained

You’ll need these details:

• host: Usually localhost or an IP address (e.g., for remote DBs)
• database: Name of your PostgreSQL database
• user: Your PostgreSQL username (often postgres)
• password: Password for the user
• port: Default is 5432

? Tip: Store credentials securely using environment variables or config files instead of hardcoding.

Example using environment variables:

import os
import psycopg2

connection = psycopg2.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    database=os.getenv('DB_NAME'),
    user=os.getenv('DB_USER'),
    password=os.getenv('DB_PASS'),
    port=os.getenv('DB_PORT', '5432')
)

? Using a Connection Context (Recommended)

To avoid forgetting to close resources, use context managers or wrap in functions:

from contextlib import contextmanager

@contextmanager
def get_db_connection():
    conn = None
    try:
        conn = psycopg2.connect(
            host='localhost',
            database='your_db',
            user='your_user',
            password='your_pass',
            port='5432'
        )
        yield conn
    except Exception as e:
        if conn:
            conn.rollback()
        raise e
    finally:
        if conn:
            conn.close()

# Usage
with get_db_connection() as conn:
    cur = conn.cursor()
    cur.execute("SELECT * FROM your_table LIMIT 5;")
    print(cur.fetchall())

? Alternative: Using SQLAlchemy (for ORM or advanced use)

If you're using SQLAlchemy, here's how to connect:

from sqlalchemy import create_engine

engine = create_engine('postgresql psycopg2://user:password@localhost:5432/your_db')

with engine.connect() as conn:
    result = conn.execute("SELECT version();")
    print(result.fetchone())

Install with: pip install sqlalchemy psycopg2-binary

Basically, psycopg2 is the go-to for raw PostgreSQL access in Python — lightweight, fast, and well-documented. Just keep your credentials safe and always close connections.

以上是如何连接到Python中的PostgreSQL数据库？的详细内容。更多信息请关注PHP中文网其他相关文章！