python - 在 Python 中使用变量与 PostgreSQL 通信

Question

我一直在尝试将变量放入我的 SQL 命令中。但是，当我尝试这样做时:

def interact_database(command):
    connection = psycopg2.connect("dbname=NAME user=NAME password=PASSWORD")
    cursor = connection.cursor()

    cursor.execute(command)
    connection.commit()

    results = None
    try:
        results = cursor.fetchall()
    except psycopg2.ProgrammingError:
        print("Connection failure.")
        pass

    cursor.close()
    connection.close()

    return results


def pick_question(type):
    if type == 'G':
        QuestionID = random.randint(1,38)
    elif type == 'Gr':
        QuestionID = random.randint(39,60)
    elif type == 'R':
        QuestionID = random.randint(61,89)
    else:
        QuestionID = random.randint(90,119)
    interact_database("SELECT Question FROM Questions WHERE Question_ID = %s")(QuestionID)

pick_question('G')

我收到这个错误

psycopg2.ProgrammingError: syntax error at or near "%"
LINE 1: SELECT Question FROM Questions WHERE Question_ID = %s

我已经多次尝试用谷歌搜索它，但无论我在哪里读到它，它都应该有效。有人知道我在这里做错了什么吗？提前致谢!

Answer 1

尝试将数据库连接 api 包装在自己的类中是一个常见的 newby 错误。它总是会导致这样的问题。所以不要

connection = psycopg2.connect("dbname=NAME user=NAME password=PASSWORD")
cursor = connection.cursor()

def pick_question(type, cursor):
    if type == 'G':
        QuestionID = random.randint(1,38)
    elif type == 'Gr':
        QuestionID = random.randint(39,60)
    elif type == 'R':
        QuestionID = random.randint(61,89)
    else:
        QuestionID = random.randint(90,119)

    cursor.execute("SELECT Question FROM Questions WHERE Question_ID = %s" , (QuestionID,))
    connection.commit()


pick_question('G', cursor)