使用DataFrame在SQLite创建表后在SQLite Studio中无法查看的问题排查

Hey there! I see you're trying to create a table from a DataFrame in SQLite but can't find it in SQLite Studio—let's fix that. Here are the key steps you might have missed:

1. You didn't commit the transaction #

SQLite runs most write operations in uncommitted transactions by default. If you don't explicitly commit your CREATE TABLE AS statement, those changes stay in memory and never get saved to the actual database file.

Add this line right after your conn.execute() call:

conn.commit()

2. You didn't properly close the database connection #

Leaving the connection open can sometimes prevent pending changes from being flushed to disk. Always wrap up your database work by closing the connection:

conn.close()

3. Tweak your to_sql call for cleaner results #

While your current to_sql line works, adding index=False is a good practice—it avoids writing pandas' default index column into your SQL table (unless you specifically need that index, which most people don't here). This keeps your table structure cleaner:

my_data.to_sql("my_data", conn, if_exists="replace", index=False)

4. Refresh SQLite Studio's view #

Sometimes SQLite Studio doesn't automatically pick up new tables. Right-click your pythonsqlite.db entry in the left sidebar and select Refresh to force it to scan for new objects.

Corrected Full Code #

Here's your code with all fixes included:

import pandas as pd
import sqlite3

# Import CSV into DataFrame
my_data = pd.read_csv("my_input_file.csv")

# Connect to database
conn = sqlite3.connect("pythonsqlite.db")

# Write DataFrame to SQL (without extra index column)
my_data.to_sql("my_data", conn, if_exists="replace", index=False)

# Create new table from the imported data
conn.execute("""CREATE TABLE my_table AS SELECT * FROM my_data""")

# Commit changes and close connection
conn.commit()
conn.close()

Give these steps a try, and your my_table should show up in SQLite Studio right away!

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