Database schema changing when using pandas to_sql in django

Question

I am trying to insert a dataframe to an existing django database model using the following code:

database_name = settings.DATABASES['default']['NAME']
database_url = 'sqlite:///{database_name}'.format(database_name=database_name)
engine = create_engine(database_url)
dataframe.to_sql(name='table_name', con=engine, if_exists='replace', index = False)

After running this command, the database schema changes also eliminating the primary key and leading to the following error: django.db.utils.OperationalError: foreign key mismatch

Note: The pandas column names and the database columns are matching.

Dharman · Accepted Answer · 2020-07-28 11:24:25Z

1

It seems that the problem comes from the if_exists='replace' parameter in the to_sql method. The pandas documentation says the following:

if_exists{‘fail’, ‘replace’, ‘append’}, default ‘fail’ How to behave if the table already exists.

fail: Raise a ValueError.
replace: Drop the table before inserting new values.
append: Insert new values to the existing table.

The 'replace' parameter replaces the table with another table defined by a predefined schema, if the table already exists. In your case it replaces your table created by the django migration with a base table, thus losing the primary key, foreign key and all. Try replacing the 'replace' parameter with 'append'.

edited Jul 28, 2020 at 11:24

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

answered Jul 28, 2020 at 11:19

Manolescu Cosmin

261 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database schema changing when using pandas to_sql in django

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related