如何使用pyodbc将变量插入数据库？求正确插入与取值代码

Hey there! Let's sort out your pyodbc insert issues. I'll start with the general best practice for inserting variables, then fix up your specific code snippet.

General Approach for Inserting Variables #

The safest and most reliable way to insert variables into a database with pyodbc is to use parameterized queries (also called parameter binding). This avoids SQL injection attacks and handles data type formatting automatically. Here's the step-by-step breakdown:

• First, establish your database connection and create a cursor:

  import pyodbc
  
  # Adjust this connection string to match your database type/credentials
  conn_str = "DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_password"
  conn = pyodbc.connect(conn_str)
  cursor = conn.cursor()
  

• Create an SQL insert statement with placeholders (? is pyodbc's standard placeholder—some databases use alternatives like %s, but ? works for most). Always specify column names to avoid ambiguity:

  sql = "INSERT INTO tble (hscore) VALUES (?)"
  

• Execute the query by passing your variable as the second argument to cursor.execute():

  highscore = score  # Assuming 'score' is your source value defined elsewhere
  cursor.execute(sql, highscore)
  

• Commit the transaction—pyodbc doesn't auto-save changes by default:

  conn.commit()
  

• Clean up connections when you're done:

  cursor.close()
  conn.close()
  

Fixing Your Specific Code Example #

Looking at your original snippet:

highscore= score cursor.execute("insert into tble values (hscore) hishscore.getvalue")

There are a few key issues here:

1. Syntax error: Two statements are mashed together without a separator.
2. Incorrect variable usage: hscore in the SQL is treated as a column name, not your variable.
3. Typo: hishscore should be highscore, and getvalue isn't a valid method for a standard variable.
4. Missing column specification: Explicitly naming columns makes your code more robust.

Here's the corrected, working version:

import pyodbc

# 1. Set up connection (adjust to your database details)
conn = pyodbc.connect("DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_password")
cursor = conn.cursor()

# 2. Assign your score to the highscore variable
highscore = score

# 3. Run the parameterized insert query
sql = "INSERT INTO tble (hscore) VALUES (?)"
cursor.execute(sql, highscore)

# 4. Save the change to the database
conn.commit()

# 5. Clean up resources
cursor.close()
conn.close()

If you need to retrieve the highscore later (e.g., fetch the current highest value), here's how you can do that:

# Example: Fetch the maximum highscore from the table
cursor.execute("SELECT MAX(hscore) FROM tble")
result = cursor.fetchone()
if result:
    retrieved_highscore = result[0]
    print(f"Current highest score: {retrieved_highscore}")

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