pandas to_sql in django: insert foreign key into DB

Solution 1:

I had same problem and this is how I solved it. My answer isn't as straight forward but I trust it helps.

Inspect your django project to be sure of two things:

1. Target table name
2. Table column names

In My case, I use class Meta when defining django models to use explicit name (django has a way of automatically naming tables). I will use django tutorial project to illustrate.

class Question(models.Model):
    question_text = models.CharField(max_length=200)
    pub_date = models.DateTimeField('date published')
    class Meta:
        db_table = "poll_questions"

class Choice(models.Model):
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)
    class Meta:
        db_table = "question_choices"

Note: Django references Question foreign key in the database using pk of the Question object.

Assume I have a Question pk 1, and a dataframe df that I wish to update Question choices with. My df must look like one below if using pandas to batch insert into database!

import pandas as pd  

df = pd.DataFrame(
    {
        "question": [1, 1, 1, 1, 1],
        "choice_text": [
            "First Question",
            "Second Question",
            "Third Question",
            "Fourth Question",
            "Fifth Question"
        ],
        "votes":[5,3,10,1,13]
    }
)

I wish I could write the df as a table. Too bad that SO doesn't support usual markdown for tables

Nonetheless, we have our df next step is to create database connection for inserting the records.

from django.conf import settings
from sqlalchemy import create_engine

# load database settings from django

user = settings.DATABASES['default']['USER']
passwd = settings.DATABASES['default']['PASSWORD']
dbname = settings.DATABASES['default']['NAME']

# create database connection string
conn = 'postgresql://{user}:{passwd}@localhost:5432/{dbname}'.format(
    user=user,
    passwd=passwd,
    dbname=dbname
)

# actual database connection object.
conn = create_engine(conn, echo=False)

# write df into db
df.to_sql("question_choices", con=conn, if_exists="append", index=False, chunksize=500, method="multi")

Voila!
We are done!

Note:
django supports bulk-create which, however, isn't what you asked for.