如何在Python中执行纯SQL查询及临时表数据插入问题

Hey there, let's break down why your SQL Server operations aren't showing up when running from Python—I've dealt with similar head-scratchers before, so here are the most likely issues and fixes:

SQL Server's local temp tables (#table_name) only exist for the duration of the database connection they're created in. If your Python code is opening/closing connections between steps (e.g., creating the temp table in one connection, inserting data in another), the temp table will vanish before you can sync it to the permanent table.

Fix: Keep a single connection open for the entire workflow—create the temp table, insert your DataFrame, and sync to the permanent table all using the same connection object.

Most Python SQL drivers (like pyodbc or pymssql) default to auto-commit being off. That means any changes you make will be rolled back automatically when the connection closes unless you explicitly commit them. This is the #1 culprit for "no changes showing up" issues.

Example fix snippet:

import pyodbc
import pandas as pd

conn = pyodbc.connect(your_connection_string)
cursor = conn.cursor()

# Create temp table
cursor.execute(sql_create_table)
# Insert DataFrame into temp table
df.to_sql('#temp_table', conn, if_exists='append', index=False)
# Sync to permanent table
cursor.execute("INSERT INTO permanent_table SELECT * FROM #temp_table")

# Critical step: Commit all changes
conn.commit()

cursor.close()
conn.close()

Sometimes to_sql behaves unexpectedly with temp tables:

• If you're using pyodbc, try adding method='multi' to the to_sql call—batch inserts can occasionally fail with temp tables.
• Double-check that your DataFrame's column names and data types exactly match the temp table schema (e.g., varchar lengths, NULL permissions). Mismatches might cause silent failures or partial inserts.
• Avoid using separate connections for execute() (create table) and to_sql()—stick to the same connection to keep the temp table alive.

It's easy to overlook:

• Verify your connection string points to the correct database—you might be running all operations in a different DB than you're checking.
• If you're using Windows Auth in SSMS but a SQL account in Python, make sure the Python account has the right permissions (create temp tables, insert into the permanent table). Run SELECT CURRENT_USER in Python to confirm which account you're using, then check its permissions in SSMS.

• After each step, run a quick check: For example, right after inserting the DataFrame, execute SELECT COUNT(*) FROM #temp_table and print the result to confirm data made it into the temp table.
• Use SQL Server Profiler or Extended Events to capture the exact SQL statements Python is sending—this will show if there are hidden errors or incorrect syntax being executed.
• Copy the full SQL workflow (create temp table → insert data → sync to permanent) and run it in SSMS using the same account Python uses. If it works there, the problem is in your Python code; if not, the issue is with the SQL or permissions.

Here's a full, tested example to reference:

import pyodbc
import pandas as pd

# Update with your connection details
conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=your_server;"
    "DATABASE=your_db;"
    "UID=your_user;"
    "PWD=your_pass;"
)

# Use a context manager to handle connection lifecycle
with pyodbc.connect(conn_str) as conn:
    cursor = conn.cursor()
    
    # 1. Create temp table
    create_temp_sql = """
    CREATE TABLE #temp_sales (
        sale_id INT,
        product_name VARCHAR(100),
        sale_amount DECIMAL(10,2)
    )
    """
    cursor.execute(create_temp_sql)
    
    # 2. Sample DataFrame
    df = pd.DataFrame({
        'sale_id': [101, 102, 103],
        'product_name': ['Laptop', 'Phone', 'Tablet'],
        'sale_amount': [999.99, 699.99, 299.99]
    })
    
    # 3. Insert DataFrame into temp table
    df.to_sql('#temp_sales', conn, if_exists='append', index=False, method='multi')
    
    # 4. Sync to permanent table
    sync_sql = """
        INSERT INTO permanent_sales (sale_id, product_name, sale_amount)
        SELECT sale_id, product_name, sale_amount FROM #temp_sales
    """
    cursor.execute(sync_sql)
    
    # 5. Commit changes
    conn.commit()
    
    # Verify success
    cursor.execute("SELECT COUNT(*) FROM permanent_sales")
    print(f"Permanent table now has {cursor.fetchone()[0]} rows")

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