How do I insert into a Table one Primary Key and Two Foreign Keys?

Question

I work with Rolls of plastic film in different legnth and width. And I'm creating a Database to store all the orders, and, in order to avoid repetition, I created separate tables for length(class(Comprimento)) and width(class(Largura)). I used UUID to create distinct ID's. Now, I want to cross both tables in a Model class. Which is:

class Largura(Base):
    __tablename__ = 'largura'
    id = Column(GUID(), primary_key=True, default=lambda: str(uuid.uuid4()))
    largura = Column(String)

    modelos_l = relationship('Modelo', back_populates='larguras', cascade='all, delete')

    def __repr__(self):
        return f"<Largura {self.largura}>"

class Comprimento(Base):
    __tablename__ = 'comprimento'
    id = Column(GUID(), primary_key=True, default=lambda: str(uuid.uuid4()))
    comprimento = Column(String)

    modelos_c = relationship('Modelo', back_populates='comprimentos', cascade='all, delete')

    def __repr__(self):
        return f"<Comprimento {self.comprimento}>"

 class Modelo(Base):
    __tablename__ = 'modelo'
    id = Column(GUID(), primary_key=True, default=lambda: str(uuid.uuid4()))
    descricao = Column(String(50))
    largura_id = Column(GUID(), ForeignKey("largura.id"), default=lambda: str(uuid.uuid4()))
    comprimento_id = Column(GUID(), ForeignKey("comprimento.id"), default=lambda: str(uuid.uuid4()))

    larguras = relationship('Largura', back_populates='modelos_l')
    comprimentos = relationship('Comprimento', back_populates='modelos_c')

    def __repr__(self):
        return f"<Modelo {self.id}>"

Then, i created a file dedicated to my data insert on this table:

from DBModelPy3 import Comprimento,Largura,Modelo,session
from sqlalchemy import create_engine
import pandas as pd

#Pre Loading my CSV file
df = pd.read_csv("dataorged.csv", sep=',')
pd.set_option('display.float_format','{:.0f}'.format) #change the number format to hide the ','
cnx = create_engine('sqlite:///data_hub2.db', echo=True).connect()

df_modelo = df[['larg_ajustada', 'comp']] # My dataframe that contains the orders. I chose the specifics columns needed for this insertion.
#print(df_modelo)

# Loading the Tables from my database
df_largura = pd.read_sql_table('largura', cnx)
df_comprimento = pd.read_sql_table('comprimento', cnx)

With everything loaded I decided to combine all the legnths and widths i had already on my two tables (df_largura and df_comprimento), and then filtered using the original file which contains the orders.

# COMBINING ALL THE LENGTH AND WIDTH OF MY TABLES 

model_num = []
for n_larg in range(len(df_largura)):
    db_larg = str(df_largura['largura'][n_larg])
    for n_comp in range(len(df_comprimento)):
        db_comp = df_comprimento['comprimento'][n_comp]
        combined = str(db_larg) + "x" + str(db_comp)
        model_num.append([db_larg,db_comp,combined])

df_modelos_ex = pd.DataFrame(model_num)
df_modelos_ex.columns = ['larg','comp','combined']

With these, i had all possible combinations on my dataframe. And created the combined variable to match later

modelos_existentes = []

#    COMBINATIONS THAT APPEAR IN THE ORDER DATAFRAME     #

for item in range(len(df_modelo)):
    mod_larg = df_modelo['larg_ajustada'][item]
    mod_comp = df_modelo['comp'][item]
    mod_comb = str(mod_larg) + "x" + str(mod_comp)
    modelos_existentes.append([mod_larg,mod_comp,mod_comb])

df_mod_existentes = pd.DataFrame(modelos_existentes)
df_mod_existentes.columns = ['ex_larg','ex_comp','ex_comb']
df_limpo = df_mod_existentes.drop_duplicates(subset=['ex_comb'])
df_limpo.reset_index(drop=True, inplace=True)

With all my elements, then the madness began. I started a loop to run through all my Dataframes:

for l_row in range(len(df_limpo)):                        # For Each Row in my dataframe which contains the orders,
    larg = df_limpo['ex_larg'][l_row]                     # create variable for width
    comp = df_limpo['ex_comp'][l_row]                     # create variable for lenght
    comb = df_limpo['ex_comb'][l_row]                     # create variable for combination of both
    for n_row in range(len(df_largura)):                  # For each row in my width table from DB,
        db_larg_id = df_largura['id'][n_row]              # I create a Variable for the PK from width
        db_larg_largura = df_largura['largura'][n_row]    # Create a Variable with the value
        lar = session.query(Largura).filter(Largura.id == db_larg_id).first()
        if db_larg_largura == larg:                       # If the value on my table matches the value of the row in the order,
            for m_row in range(len(df_comprimento)):      # For each length in my table on the DB,
                db_comp_id = df_comprimento['id'][m_row]
                db_comp_comprimento = df_comprimento['comprimento'][m_row]
                compr = session.query(Comprimento).filter(Comprimento.id == db_comp_id).first()
                if db_comp_comprimento == comp:           # If the value on my table matches the value of the row in the order 
                    new_model = Modelo(descricao=df_limpo['ex_comb'][n_linha], larguras=lar, comprimentos=compr)

from here, i would only add the session.add(new_model) and session.commit() to finish my code. But it's not adding. What I would like is for my Modelo table be like:

MODELO Table

ID(PK) | DESCRIPTION (Combined values String) | Largura_id (width_id, FK) | Comprimento_id (length_id, FK)

Sorry about the long explanation. Tried my best!

Answer 1

If anyone have the same trouble:

##########################
# ADDING TO THE DATABANK #
##########################
lista_a = []                                                #Created an empty List
for n_linha in range(len(df_limpo)):                        #Ran through my dataframe
    larg_a = df_limpo['ex_larg'][n_linha]                   #Extracted width and length from it
    comp_a = df_limpo['ex_comp'][n_linha]
    for m_linha in range(len(df_largura)):                  #Ran my width table from database
        db_larg_id = df_largura['id'][m_linha]
        db_larg_largura = df_largura['largura'][m_linha]
        if larg_a == db_larg_largura:                       #Checked if the width from my dataframe matches the one on the table
            lista_a.append([larg_a,comp_a,db_larg_id])      #appended to the list_a
#print(lista_a)
df_lista_a = pd.DataFrame(lista_a)                          #Created a new Dataframe
df_lista_a.columns = ['larg','comp','id_larg'] 


lista_b = []                                                #Created a new list
for n_row in range(len(df_lista_a)):                        #Ran through my new dataframe
    larg_b = df_lista_a['larg'][n_row]                      #Extracted each column from it
    comp_b = df_lista_a['comp'][n_row]
    larg_b_id = df_lista_a['id_larg'][n_row]
    #df_limpo_lrow = df_limpo['ex_larg'][n_row]
    #df_limpo_crow = df_limpo['ex_comp'][n_row]
    #df_limpo_cbrow = df_limpo['ex_comb'][n_row]
    #print(larg_b,comp_b,larg_b_id,n_row)
    for m_row in range(len(df_comprimento)):                #Ran through my lenght table
        db_comp_id = df_comprimento['id'][m_row]
        db_comp_comprimento = df_comprimento['comprimento'][m_row]
        if comp_b == db_comp_comprimento:                   #Check if the lenght from dataframe matches the lenght on my table on the database
            #print(larg_b,comp_b,n_row,m_row,df_limpo_lrow)
            lista_b.append([larg_b,comp_b,larg_b_id,db_comp_id]) #appended the lenght id to my list
            break
#print(lista_b)
#print(len(df_lista_a),len(df_limpo),len(lista_b))
df_lista_b = pd.DataFrame(lista_b)                               #converted to Dataframe.
df_lista_b.columns = ['larg','comp','id_larg','id_comp']

# HERE's the ACTUAL INSERTION

for n_model in range(len(df_lista_b)):                            #For each model found on the list, extract the values and add to new_model. 
    mod_largura = df_lista_b['larg'][n_model]
    mod_comprimento = df_lista_b['comp'][n_model]
    mod_largura_id = df_lista_b['id_larg'][n_model]
    mod_comprimento_id = df_lista_b['id_comp'][n_model]
    lar = session.query(Largura).filter(Largura.id == df_largura['id'][1]).first()
    compr = session.query(Comprimento).filter(Comprimento.id == df_comprimento['id'][1]).first()
    new_model = Modelo(descricao=df_limpo['ex_comb'][n_model], larguras=lar, comprimentos=compr)
    print("Modelo: " + df_limpo['ex_comb'][n_model] + " com Id's " + mod_largura_id + " e " + mod_comprimento_id + " adicionados!")
    session.add(new_model)
    session.commit()

Then it's done.