1054, unknown column 'index' in field list

Question

df_row.head()
    identifier  link    likes_count company
0   2292512316069378197 https://www.instagram.com/p/B_Qo84ihfiV 9608    Ralph Lauren
1   2292462538514040606 https://www.instagram.com/p/B_QdohlBQce 9462    Ralph Lauren
2   2292418655784545069 https://www.instagram.com/p/B_QTp8mhCst 22033   Ralph Lauren
3   2292372137723669561 https://www.instagram.com/p/B_QJFBSBaw5 14112   Ralph Lauren
4   2292334760619881771 https://www.instagram.com/p/B_QAlHJBzUr 5974    Ralph Lauren

# import the module
from sqlalchemy import create_engine

# create sqlalchemy engine
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost{db}"
                       .format(user="admin",
                               pw="abcdef",
                               db="ghi"))
df_row.to_sql('df_row', con = engine, if_exists = 'append', chunksize = 1000)

When I run the code above, the following message comes out: InternalError: (pymysql.err.InternalError) (1054, "Unknown column 'index' in 'field list'")

Answer 1

df_row.to_sql('df_row', con = engine, if_exists = 'append', chunksize = 1000)

change to

df_row.to_sql('df_row', con = engine, if_exists = 'append', chunksize = 1000, index= False)

the index which is present in the data frame. So the default to_sql tries to insert into the table. But the column doesn't exist in the table so adding index = false will help.

Answer 2

I think your connection string is wrong use this:

config = {
    'host': 'localhost',
    'user': 'newuser',
    'password': 'newpassword',
    'database': 'ghi'
}
db_user = config.get('user')
db_pwd = config.get('password')
db_host = config.get('host')
db_name = config.get('database')
connection_str = f'mysql+pymysql://{db_user}:{db_pwd}@{db_host}/{db_name}'

and everything works fine!