Loading data back to the database model

[chrisdev]

Got this via an email from didier@hatsch.fr

have a question related to putting the data back into the database. In the documentation it is stated that the django_pandas.io also handle saving data to the underlying model. However I don't see how it works ? or which method to call ? Can you help me out ?

[chrisdev]

We need to correct the docs since we do not currently provide this functionality.
However, this could be added there are some issues as we need to support uploading with foreign keys and the fact the DF may have verbose keys.

[dhatsch]

Thanks for taking in account this remark.

In fact, the goal of the full operation is to load data from a model, perform calculation/transformation in memory and then save the results either back on the same objects or to new objects.

Some people could say, that one way, would be to perform that directly at the database level and I agree. However, in that specific case, I'm interrested in the speed of doing that in memory and by the short dev time needed by using related python objects.

[tumb1er]

What about dumping DataFrame to temporary CSV file and use MySQL's 'LOAD DATA FROM LOCAL INFILE' command?

[dhatsch]

That could do the trick for new inserts, but we need to make sure that all data in the dataframe are clean (format, values etc). Somehow I would expect the django framework to check for me before (short dev time).
But I will give a try in some context, in my case, postgresql, the copy command is quiet efficient for integrating data.

[tumb1er]

https://docs.djangoproject.com/en/dev/ref/models/instances/#validating-objects
If you need to validate a dataframe, you need something like this:

def validate((i, row)):
    try:
        obj = Views(**row.to_dict())
        obj.clean_fields()
        obj.clean()
    except:
        return i

invalid = pd.Series(filter(None, map(validate, data.iterrows())))

It iterates over dataframe rows, constructs Model object and run model validation methods. OK, but for 800k rows it needs minutes to perform such a check. And it is not enough: if you have foreign keys in your model, you need to check if related object exists (it is one select per each row per each FK in dataframe); for unique_together you also need to select from db values that correspond to unique key value for each row.
Foreign key checks and unique key checks may be performed at database server, while inserting data to destination table; column types for values (I mean Model.clean_fields) could be checked somehow at LOAD DATA time (you can't insert a string to an integer column).
So what's the point of python-side validation? I see the only reason if you have some custom fields with custom validators (like ip-address field).