Insert data from pandas into sql db - keys doesn't fit columns

Question

I have a database with around 10 columns. Sometimes I need to insert a row which has only 3 of the required columns, the rest are not in the dic.

The data to be inserted is a dictionary named row : (this insert is to avoid duplicates)

row = {'keyword':'abc','name':'bds'.....}
df = pd.DataFrame([row]) # df looks good, I see columns and 1 row.
engine  = getEngine()
connection = engine.connect()
df.to_sql('temp_insert_data_index', connection, if_exists ='replace',index=False)
result = connection.execute(('''
        INSERT INTO {t} SELECT * FROM temp_insert_data_index 
        ON CONFLICT DO NOTHING''').format(t=table_name))
connection.close()

Problem : when I don't have all columns in the row(dic), it will insert dic fields by order (a 3 keys dic will be inserted to the first 3 columns) and not to the right columns. ( I expect the keys in dic to fit the db columns)

Why ?

Parfait · Accepted Answer · 2022-02-15 22:31:57Z

2

Consider explicitly naming the columns to be inserted in INSERT INTO and SELECT clauses which is best practice for SQL append queries. Doing so, the dynamic query should work for all or subset of columns. Below uses F-string (available Python 3.6+) for all interpolation to larger SQL query:

# APPEND TO STAGING TEMP TABLE
df.to_sql('temp_insert_data_index', connection, if_exists='replace', index=False)

# STRING OF COMMA SEPARATED COLUMNS
cols = ", ".join(df.columns)

sql = (
    f"INSERT INTO {table_name} ({cols}) "
    f"SELECT {cols} FROM temp_insert_data_index "
    "ON CONFLICT DO NOTHING"
)
result = connection.execute(sql)
connection.close()

answered Feb 15, 2022 at 22:31

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

baltiturg Over a year ago

Thanks a lot, but I still didn't understand where was my mistake ? why my data frame will not be written into columns? and what 'cols' does here?

baltiturg Over a year ago

oh I see. thanks a lot!

Collectives™ on Stack Overflow

Insert data from pandas into sql db - keys doesn't fit columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related