4
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'")

jarlh
  • 42,561
  • 8
  • 45
  • 63
Tony Flager
  • 95
  • 1
  • 8

2 Answers2

8
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.

Varaj Vignesh
  • 341
  • 1
  • 7
1

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!

badger
  • 2,908
  • 1
  • 13
  • 32
  • sorry, I am quite new here. I have run the code, but what happens next? How does this `config` variable affect the goal to insert rows into a created table? – Tony Flager Apr 22 '20 at 16:26
  • 2
    your code runs perfectly in my environment, the only mistake is that your connection string is malformed (you missed an / after host) – badger Apr 22 '20 at 18:22