如何在SQL查询中传入多个变量？示例代码执行失败求助

Hey there! Let's get that INSERT statement working properly. The code you wrote has two main issues: incorrect SQL syntax, and a critical security/reliability problem from directly embedding variables into your query string. Here's how to fix it step by step:

The Core Problems in Your Original Code #

• Your INSERT statement is missing parentheses around the values: it should be VALUES (a, b, c) instead of VALUES a, b, c
• Directly putting variables like a, b, c into the SQL string is a huge risk (it opens you up to SQL injection attacks) and will fail if your variables contain special characters (like quotes)

The Correct Approach: Parameterized Queries #

Parameterized queries let you safely pass variables to SQL without embedding them directly. The exact syntax depends on your database driver, but here are the most common examples:

Example 1: SQLite (using sqlite3 in Python) #

import sqlite3

# Set up your database connection and cursor first
conn = sqlite3.connect("your_database.db")
cursor = conn.cursor()

# Get user input
a = input("first digit: ")
b = input("second digit: ")
c = input("third digit: ")

# Use ? as placeholders, pass variables as a tuple to execute()
cursor.execute("INSERT INTO batch VALUES (?, ?, ?)", (a, b, c))

# Don't forget to commit the transaction and clean up
conn.commit()
conn.close()

Example 2: PostgreSQL (using psycopg2) #

PostgreSQL uses %s as placeholders instead of ?:

import psycopg2

# Establish connection (update with your DB credentials)
conn = psycopg2.connect("dbname=your_db user=your_username password=your_password")
cursor = conn.cursor()

a = input("first digit: ")
b = input("second digit: ")
c = input("third digit: ")

cursor.execute("INSERT INTO batch VALUES (%s, %s, %s)", (a, b, c))

conn.commit()
conn.close()

Example 3: MySQL (using mysql-connector-python) #

MySQL also uses %s as placeholders:

import mysql.connector

conn = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_db"
)
cursor = conn.cursor()

a = input("first digit: ")
b = input("second digit: ")
c = input("third digit: ")

cursor.execute("INSERT INTO batch VALUES (%s, %s, %s)", (a, b, c))

conn.commit()
conn.close()

Pro Tip: Specify Column Names #

For better clarity and to avoid issues if your table structure changes, always specify which columns you're inserting into:

INSERT INTO batch (column1, column2, column3) VALUES (?, ?, ?)

Replace column1, column2, column3 with the actual column names from your batch table.

Key Takeaways #

• Never embed variables directly into your SQL string—always use parameterized queries
• The VALUES clause must always have parentheses around the list of values
• Match the placeholder syntax to your database driver (? for SQLite, %s for PostgreSQL/MySQL)
• Always call conn.commit() after making changes to your database, otherwise the insert won't be saved

内容的提问来源于stack exchange，提问作者LanaReversed