在Python中安全高效地将变量应用于PostgreSQL SQL语句

import psycopg2

# 假设 inputed_email 是一个从用户输入获取的变量
inputed_email = "test@example.com"

try:
    conn = psycopg2.connect("dbname=postgres user=postgres password=postgres")
    cur = conn.cursor()

    # 错误示例：直接将变量作为 execute() 的第三个参数传入
    cur.execute("SELECT password FROM user WHERE email = ", inputed_email, ";")
    print(cur.fetchone())

except TypeError as e:
    print(f"发生TypeError: {e}")
except Exception as e:
    print(f"发生其他错误: {e}")
finally:
    if 'cur' in locals() and cur:
        cur.close()
    if 'conn' in locals() and conn:
        conn.close()

import psycopg2

inputed_email = "test@example.com" # 假设这是从用户输入获取的变量

try:
    conn = psycopg2.connect("dbname=postgres user=postgres password=postgres")
    cur = conn.cursor()

    # 正确示例：使用占位符 %s 和参数列表
    sql_query = "SELECT password FROM user WHERE email = %s"
    cur.execute(sql_query, [inputed_email]) # 注意：即使只有一个变量，也需用列表或元组封装

    result = cur.fetchone()
    if result:
        print(f"用户密码: {result[0]}")
    else:
        print("未找到该用户。")

except Exception as e:
    print(f"发生错误: {e}")
finally:
    if 'cur' in locals() and cur:
        cur.close()
    if 'conn' in locals() and conn:
        conn.close()

import psycopg2

email_address = "admin@example.com"
last_name = "Smith"

try:
    conn = psycopg2.connect("dbname=postgres user=postgres password=postgres")
    cur = conn.cursor()

    # 多个变量替换示例
    sql_query = "SELECT username, email FROM user WHERE email = %s AND lastname = %s"
    cur.execute(sql_query, [email_address, last_name]) # 列表中的值顺序与占位符顺序一致

    results = cur.fetchall()
    if results:
        for row in results:
            print(f"用户名: {row[0]}, 邮箱: {row[1]}")
    else:
        print("未找到匹配的用户。")

except Exception as e:
    print(f"发生错误: {e}")
finally:
    if 'cur' in locals() and cur:
        cur.close()
    if 'conn' in locals() and conn:
        conn.close()

# 使用 with 语句管理连接和游标的示例
import psycopg2

inputed_email = "test@example.com"

try:
    with psycopg2.connect("dbname=postgres user=postgres password=postgres") as conn:
        with conn.cursor() as cur:
            sql_query = "SELECT password FROM user WHERE email = %s"
            cur.execute(sql_query, [inputed_email])
            result = cur.fetchone()
            if result:
                print(f"用户密码: {result[0]}")
            else:
                print("未找到该用户。")
except Exception as e:
    print(f"发生错误: {e}")