Pandas to_sql in django

Question

I am trying to use Django's db connection variable to insert a pandas dataframe to Postgres database. The code I use is

df.to_sql('forecast',connection,if_exists='append',index=False)

And I get the following error

Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': relation "sqlite_master" does not exist LINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...

I think this happens because the Django connection object is not an sqlalchemy object and therefor Pandas assumes I am using sqlite. Is there any way to use .to_sql other than make another connection to the database?

Answer 1

It is possible to create db configuration in setting.py file

DATABASES = {
    'default': env.db('DATABASE_URL_DEFAULT'),
    'other': env.db('DATABASE_URL_OTHER')
}
DB_URI_DEFAULT=env.str('DATABASE_URL_DEFAULT')
DB_URI_OTHER=env.str('DATABASE_URL_OTHER')

If you want to create sql_alchemy connection you should use DB_URI_DEFAULT or DB_URI_OTHER

in the init method of the class you will use .to_sql method you should write

from you_project_app import settings
from sqlalchemy import create_engine
import pandas as pd

class Example:

 def __init__(self):
        self.conn_default = create_engine(settings.DB_URI_DEFAULT).connect()

And when you use .to_sql method of pandas it should be like this:

df_insert.to_sql(table, if_exists='append',index=False,con=self.conn_default)