Snowflake表操作异常求助：无报错但无法写入/仅显示旧记录

Let's break down your two Snowflake problems and walk through practical fixes step by step:

1. No Error Returned But Can't Write to Snowflake Table #

If you're unable to insert data without any error messages, focus on these key areas:

• Uncommitted Transactions: Snowflake uses implicit transactions by default. If your write is stuck in an uncommitted state, the data won't persist. Run SELECT CURRENT_TRANSACTION() to check for active transactions, and execute COMMIT to finalize it if needed.
• Warehouse Status: Make sure your target warehouse isn't suspended. Verify this with SHOW WAREHOUSES LIKE '<your_warehouse_name>' and resume it with ALTER WAREHOUSE <your_warehouse_name> RESUME if it's inactive.
• Permission Checks: Confirm your user has INSERT/SELECT privileges on the target table, plus USAGE access to the database, schema, and warehouse. Run SHOW GRANTS ON TABLE demo_db.public.test_f1 to validate permissions.
• Warehouse Throttling: A small warehouse might silently struggle with larger data volumes. Try temporarily scaling up the warehouse to rule out resource constraints.

2. Python Code Runs Without Error But Only Shows Old Records #

Looking at your code, there are a few common reasons this happens:

Issue 1: Uncommitted Transaction in SQLAlchemy #

SQLAlchemy with Snowflake might hold an uncommitted transaction after the to_sql call. Even within the same connection, uncommitted changes won't be visible to subsequent queries unless you explicitly commit.

Here's a modified version of your code with transaction handling:

import pandas as pd
from sqlalchemy import create_engine
from snowflake.sqlalchemy import URL
from config import config

engine = create_engine(URL(
    account=config.account, 
    user=config.username, 
    password=config.password, 
    warehouse=config.warehouse, 
    database=config.database, 
    schema=config.schema,
))

conn = engine.connect()
trans = conn.begin()
try:
    df = pd.DataFrame([('AAA', '1234'), ('BBB', '5678')], columns=['name', 'pswd'])
    # Use just the table name (engine already specifies db/schema)
    df.to_sql('test_f1', con=engine, index=False, if_exists='append', index_label=None)
    trans.commit()
    
    # Use a fresh connection to read latest data
    with engine.connect() as new_conn:
        df = pd.read_sql_query('select * from demo_db.public.test_f1', new_conn)
    print(df.head(5))
except Exception as e:
    trans.rollback()
    print(f"Error occurred: {e}")
finally:
    conn.close()
    engine.dispose()

Note: I removed the full demo_db.public.test_f1 path from to_sql—since your engine already specifies the database and schema, using just the table name avoids potential duplication issues.

Issue 2: Connection Isolation Level Mismatch #

Snowflake's default isolation level is READ COMMITTED, but if your connection uses a different setting, it might not see uncommitted changes. Add this line right after creating the connection to enforce the correct level:

conn.execution_options(isolation_level="READ_COMMITTED")

Issue 3: Schema/Database Misalignment #

Double-check that the database and schema values in your config match demo_db.public. If the engine points to a different database/schema, your to_sql call might be writing to a completely different table than you're querying.

Quick Verification Test #

After running your Python code, execute this query directly in the Snowflake UI:

SELECT * FROM demo_db.public.test_f1;

If you see the new records here, the problem is definitely transaction handling in your Python code. If not, the write never completed, and you'll need to revisit the checks from the first issue.

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