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

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 ?

CodePudding user response：

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()