Python中INSERT报错‘参数未全部转换’及CSV数据入库loc_dim表问题

Problem 1: Fixing the "not all arguments converted" Error in Python INSERT Statements #

Ah, this error is a classic! It almost always boils down to a mismatch between your SQL query’s placeholders and the number/format of parameters you’re passing. Let’s walk through the most common fixes:

Common Causes & Fixes #

• Placeholder-parameter count mismatch: If your INSERT targets 4 columns, you need exactly 4 placeholders and 4 corresponding values.
  Wrong example:

  # 4 columns but only 3 parameters
  query = "INSERT INTO loc_dim (abbr, state, reg, div) VALUES (?, ?, ?)"
  dbCur.execute(query, ("AL", "Alabama", 2))
  

  Correct example:

  # 4 placeholders for 4 parameters
  query = "INSERT INTO loc_dim (abbr, state, reg, div) VALUES (?, ?, ?, ?)"
  dbCur.execute(query, ("AL", "Alabama", 2, 3))
  

• Incorrect placeholder syntax: Different databases use different placeholder styles—don’t mix them up:

  • SQLite uses ?
  • MySQL uses %s
  • PostgreSQL uses %s (or %(column_name)s for named parameters)

• Passing a list/tuple as a single parameter: If you’re using a list of values, make sure to unpack it properly (most execute() methods accept tuples/lists directly, but double-check you’re not wrapping it in an extra layer).

Problem 2: Building a Working CSV → DataFrame → Database Workflow #

Let’s turn your partial code into a fully functional pipeline, with safeguards to avoid common pitfalls like missing data, duplicate keys, and parameter errors.

Step 1: Extract CSV Data to Pandas DataFrame #

First, read your CSV properly (replace locations.csv with your actual file path):

import pandas as pd
import sqlite3  # Swap this for your DB driver (e.g., psycopg2 for PostgreSQL)

# Read CSV into DataFrame
df = pd.read_csv("locations.csv")
# Drop rows with missing values (as you did)
df_clean = df.dropna()
print("Cleaned data:\n", df_clean)

Step 2: Create the loc_dim Table #

Your existing CREATE TABLE code is solid—we’ll just wrap it in a proper database connection:

# Connect to your database (adjust the connection string for non-SQLite DBs)
conn = sqlite3.connect("your_database.db")
dbCur = conn.cursor()

# Drop existing table (if needed) and create new one
dbCur.execute("DROP TABLE IF EXISTS loc_dim")
dbCur.execute("""
    CREATE TABLE loc_dim(
        abbr text PRIMARY KEY,
        state text,
        reg integer,
        div integer
    )
""")
conn.commit()

Step 3: Insert Data from DataFrame to Table #

Instead of writing manual INSERT queries (error-prone for large datasets), use Pandas’ built-in to_sql method—it handles parameterization and batch inserts automatically:

# Insert cleaned DataFrame into the table
df_clean.to_sql(
    name="loc_dim",
    con=conn,
    if_exists="append",  # We already dropped the table, so append works here
    index=False  # Don't insert Pandas' index column into the DB
)

# Verify the insert worked
dbCur.execute("SELECT * FROM loc_dim")
print("\nInserted data from DB:")
for row in dbCur.fetchall():
    print(row)

# Clean up the connection
conn.close()

If You Prefer Manual INSERT Queries #

If you want to stick with manual inserts (not recommended for large DataFrames), here’s how to fix your incomplete query and handle duplicates:

# Use the correct placeholder for your DB (example for SQLite)
query = "INSERT INTO loc_dim (abbr, state, reg, div) VALUES (?, ?, ?, ?)"

# Loop through rows and insert, skipping duplicate primary keys
for _, row in df_clean.iterrows():
    try:
        dbCur.execute(query, (row["abbr"], row["state"], row["reg"], row["div"]))
    except sqlite3.IntegrityError:
        print(f"Skipping duplicate primary key: {row['abbr']}")

conn.commit()

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