CODEWITHSUNDEEP
pythondjangopandas
teodor96
teodor96

Reputation: 3

Database schema changing when using pandas to_sql in django

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.

Upvotes: 0

Views: 1007

Answers (1)

Manolescu Cosmin
Manolescu Cosmin

Reputation: 26

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'.

Upvotes: 1

Related Questions