to_sql() infers appropriate SQL column types from the DataFrame's dtypes and either creates a brand-new table or writes into an existing one, depending on the if_exists parameter: 'fail', the default, raises an error if the table already exists, 'replace' drops and recreates it entirely, and 'append' adds the new rows to whatever's already there without touching existing data. Like to_csv()/to_excel(), it includes the DataFrame's index as a column by default unless you pass index=False.
1Understanding df.to_sql()
to_sql() infers appropriate SQL column types from the DataFrame's dtypes and either creates a brand-new table or writes into an existing one, depending on the if_exists parameter: 'fail', the default, raises an error if the table already exists, 'replace' drops and recreates it entirely, and 'append' adds the new rows to whatever's already there without touching existing data. Like to_csv()/to_excel(), it includes the DataFrame's index as a column by default unless you pass index=False.
Double-check if_exists carefully before writing to a table that already has important data — 'replace' completely drops and recreates the table, permanently discarding whatever was in it before, which is easy to do by accident if you were expecting 'append' behavior.
import pandas as pd
import sqlite3
conn = sqlite3.connect("shop.db")
df = pd.DataFrame({"product": ["Widget"], "price": [9.99]})
df.to_sql("products", conn, if_exists="replace", index=False)
print(pd.read_sql("SELECT * FROM products", conn))2Practical Example
Here is a real-world application of df.to_sql() showing how it is used in production Pandas code.
import pandas as pd
import sqlite3
conn = sqlite3.connect("shop.db")
new_orders = pd.DataFrame({"id": [101], "total": [59.99]})
new_orders.to_sql("orders", conn, if_exists="append", index=False)3Best Practices
Follow these guidelines when working with df.to_sql():
1. Choose if_exists deliberately based on intent — 'append' to add new rows, 'replace' only when you genuinely want to wipe and recreate the table, 'fail' as a safety default when you're not sure
2. Pass index=False unless the DataFrame's index specifically needs to become its own database column
3. Write large DataFrames in chunks, using the chunksize parameter, rather than one massive insert, to avoid excessive memory usage or transaction size on the database side
Tip: Double-check if_exists carefully before writing to a table that already has important data — 'replace' completely drops and recreates the table, permanently discarding whatever was in it before, which is easy to do by accident if you were expecting 'append' behavior.
import pandas as pd
import sqlite3
conn = sqlite3.connect("shop.db")
df = pd.DataFrame({"product": ["Widget"], "price": [9.99]})
df.to_sql("products", conn, if_exists="replace", index=False)
print(pd.read_sql("SELECT * FROM products", conn))