Saving Pandas DataFrame in Django Model

Question

I have stock price data that is stored in a pandas DataFrame as shown below (it was actually in the panel, but I converted it to a DataFrame)

date ticker close tsr 0 2013-03-28 abc 22.81 1.000439 1 2013-03-28 def 94.21 1.006947 2 2013-03-28 ghi 95.84 1.014180 3 2013-03-28 jkl 31.80 1.000000 4 2013-03-28 mno 32.10 1.003125 ...many more rows 

I want to save this in a Django model that looks like this (matches column names):

 class HistoricalPrices(models.Model): ticker = models.CharField(max_length=10) date = models.DateField() tsr = models.DecimalField() close = models.DecimalField() 

The best I've come up with so far is to use to save it, where df is my DataFrame:

 entries = [] for e in df.T.to_dict().values(): entries.append(HistoricalPrices(**e)) HistoricalPrices.objects.bulk_create(entries) 

Is there a better way to keep this?

I looked at django-pandas , but it looks like it is just reading from a database.

Answer 1

It would be most efficient to use to_sql() with the appropriate connection parameters for the engine and run it inside your Django instead of repeating using a DataFrame and saving one instance of model at a time:

 from django.conf import settings user = settings.DATABASES['default']['USER'] password = settings.DATABASES['default']['PASSWORD'] database_name = settings.DATABASES['default']['NAME'] database_url = 'postgresql://{user}:{password}@localhost:5432/{database_name}'.format( user=user, password=password, database_name=database_name, ) engine = create_engine(database_url, echo=False) df.to_sql(HistoricalPrices, con=engine) 

Answer 2

If you do not want to contact sql directly, you can use something like this:

 entries = df.to_dict('records') HistoricalPrices.objects.bulk_create(entries) 

I did not compare performance, but I think it is much cleaner.