SQLAlchemy engine issue - data not transferred to db using Pandas

Using Django Using the ORM
1

Hello all

My use case is to take excel data and load it to database… I am using SQLAlchemy (with Pandas df) to do that, but am not able to do… When I try with ORM bulk_create, it works, suggesting to me that Django side of stuff (eg template, url, view, model connections) is working fine, and there could be some issue in defining the Alchemy engine??

I need help from you to debug and resolve this… My code details are as below… Please note I dont get any errors, its just that when I click on Add/ replace button, the data does not transfer… Appreciate your help… many thanks…

SQLAlchemy engine details (saved in engine.py)

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from django.conf import settings

engine = create_engine('postgresql://' +
                       settings.DATABASES['default']['USER'] + ':' +
                       settings.DATABASES['default']['PASSWORD'] + '@' +
                       settings.DATABASES['default']['HOST'] + ':' +
                       str(settings.DATABASES['default']['PORT']) + '/' +
                       settings.DATABASES['default']['NAME'])

Session = sessionmaker(bind=engine)
session = Session()

views.py

from .models import Industry
from .engine import engine, session
import pandas as pd

def industry_import_replace(request):
    df = pd.read_excel('Industry.xlsx')    
    df.to_sql('Industry', engine, if_exists='replace', index=False)    
    return render(request, 'portfolio/partials/imports/industry-import.html',{})

template (html)

<form method="post">
  <div class="row">
    <div class="col col-md-4 col-lg-4">
      <table class="table">
        <thead>
          <tr>
            <th scope="col-md-4 col-lg-4">Imports</th>
            <th scope="col-md-4 col-lg-4">&nbsp;</th>
            <th scope="col-md-4 col-lg-4">&nbsp;</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <th scope="col-md-4 col-lg-4">Industry</th>
            <td>
              <button type="submit" name="action" value="replace" class="btn btn-danger"
                hx-post="{% url 'portfolio:industry-import-replace' %}" hx-target="#imports">Add/ Replace</button>
            </td>
            <td>
              <button type="submit" name="action" value="append" class="btn btn-success"
                hx-post="{% url 'portfolio:industry-import-append' %}" hx-target="#imports">Append</button>
            </td>
          </tr>
        </tbody>
        <div id="imports"></div>
    </div>

excel file data

Stored right at the top; no row headers etc…
ID industry_name
Ind1
Ind2
Ind3

Industry model file

class Industry(models.Model):
    industry_name = models.CharField(max_length=100, unique=True)

    class Meta:
        verbose_name_plural = 'Industry'

    def __str__(self):
        return self.industry_name
2

Caveat: I don’t use Pandas and I don’t use SQLAlchemy. This is all gleaned from about a 5 minute browsing of the docs.

From Session Basics — SQLAlchemy 2.0 Documentation it appears that a session object is effectively a transaction. This would imply to me that you need to create the session in the view and not at the module layer.

Also see Session API — SQLAlchemy 2.0 Documentation