python - How to insert pandas dataframe via mysqldb into database? -

i can connect local mysql database python, , can create, select from, , insert individual rows.

my question is: can directly instruct mysqldb take entire dataframe , insert existing table, or need iterate on rows?

in either case, python script simple table id , 2 data columns, , matching dataframe?

update:

there to_sql method, preferred way this, rather write_frame:

df.to_sql(con=con, name='table_name_for_df', if_exists='replace', flavor='mysql') 

also note: syntax may change in pandas 0.14...

you can set connection mysqldb:

from pandas.io import sql import mysqldb  con = mysqldb.connect()  # may need add other options connect 

setting flavor of write_frame 'mysql' means can write mysql:

sql.write_frame(df, con=con, name='table_name_for_df',                  if_exists='replace', flavor='mysql') 

the argument if_exists tells pandas how deal if table exists:

if_exists: {'fail', 'replace', 'append'}, default 'fail'
     fail: if table exists, nothing.
     replace: if table exists, drop it, recreate it, , insert data.
     append: if table exists, insert data. create if not exist.

although write_frame docs suggest works on sqlite, mysql appears supported , in fact there quite bit of mysql testing in codebase.