python使用pg数据库

1.方法一psycopg2

安装

pip install psycopg2-binary # 简化版（适合开发） # 或 pip install psycopg2 # 需系统安装libpq-dev（推荐生产环境）

import psycopg2

# 连接数据库
conn = psycopg2.connect(
    host="localhost",
    database="your_db",
    user="your_user",
    password="your_password",
    port="5432"
)

# 创建游标
cur = conn.cursor()

# 执行SQL（示例：创建表）
cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        email VARCHAR(100) UNIQUE
    )
""")

# 插入数据
cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("Alice", "alice@example.com"))

# 查询数据
cur.execute("SELECT * FROM users")
rows = cur.fetchall()
for row in rows:
    print(row)

# 提交事务并关闭连接
conn.commit()
cur.close()
conn.close()

2.方法二sqlalchemy

安装

pip install sqlalchemy

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

# 1. 定义模型和表结构
Base = declarative_base()

class User(Base):
    """用户表"""
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    email = Column(String(100), unique=True)
    
    # 一对多关系（可选）
    addresses = relationship("Address", back_populates="user")

class Address(Base):
    """地址表"""
    __tablename__ = 'addresses'
    
    id = Column(Integer, primary_key=True)
    email = Column(String(100))
    city = Column(String(50))
    user_id = Column(Integer, ForeignKey('users.id'))
    
    # 关系映射
    user = relationship("User", back_populates="addresses")

# 2. 连接数据库
# 格式：postgresql://用户名:密码@主机:端口/数据库名
engine = create_engine('postgresql://postgres:password@localhost:5432/mydb')
Base.metadata.create_all(engine)  # 创建表

# 3. 获取会话
Session = sessionmaker(bind=engine)
session = Session()

# ------------------- CRUD 操作示例 -------------------

def add_user(name, email):
    """增：插入数据"""
    new_user = User(name=name, email=email)
    session.add(new_user)
    session.commit()
    print(f"新增用户ID: {new_user.id}")

def get_users():
    """查：获取所有用户"""
    users = session.query(User).all()
    for user in users:
        print(f"ID: {user.id}, 姓名: {user.name}, 邮箱: {user.email}")

def update_user(user_id, new_name):
    """改：更新用户信息"""
    user = session.query(User).filter_by(id=user_id).first()
    if user:
        user.name = new_name
        session.commit()
        print(f"用户 {user_id} 更新成功")
    else:
        print("用户不存在")

def delete_user(user_id):
    """删：删除用户"""
    user = session.query(User).filter_by(id=user_id).first()
    if user:
        session.delete(user)
        session.commit()
        print(f"用户 {user_id} 已删除")
    else:
        print("用户不存在")

# ------------------- 测试操作 -------------------
if __name__ == "__main__":
    # 插入数据
    add_user("张三", "zhangsan@example.com")
    add_user("李四", "lisi@example.com")
    
    # 查询数据
    print("当前用户列表:")
    get_users()
    
    # 更新数据（假设ID为1的用户存在）
    update_user(1, "张三丰")
    
    # 删除数据（假设ID为2的用户存在）
    delete_user(2)
    
    # 再次查询
    print("更新后的用户列表:")
    get_users()
    
    # 关闭会话
    session.close()