How to insert pandas dataframe via mysqldb into database?

by

Update:

There is now a to_sql method, which is the preferred way to do this, rather than write_frame:

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

Also note: the syntax may change in pandas 0.14…

You can set up the connection with MySQLdb:

from pandas.io import sql
import MySQLdb

con = MySQLdb.connect()  # may need to add some other options to connect

Setting the flavor of write_frame to 'mysql' means you can write to mysql:

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

The argument if_exists tells pandas how to deal if the table already exists:

if_exists: {'fail', 'replace', 'append'}, default 'fail'
     fail: If table exists, do nothing.
     replace: If table exists, drop it, recreate it, and insert data.
     append: If table exists, insert data. Create if does not exist.

Although the write_frame docs currently suggest it only works on sqlite, mysql appears to be supported and in fact there is quite a bit of mysql testing in the codebase.

More Related Contents:

  1. Installing specific package version with pip
  2. Database does not update automatically with MySQL and Python
  3. How to convert SQL Query result to PANDAS Data Structure?
  4. mysql-python install error: Cannot open include file ‘config-win.h’
  5. Replacing Pandas or Numpy Nan with a None to use with MysqlDB
  6. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  7. Install mysql-python (Windows)
  8. When to close cursors using MySQLdb
  9. Python MYSQL update statement
  10. Python 3.4.0 with MySQL database
  11. How can I use executemany to insert into MySQL a list of dictionaries in Python
  12. MySQL: Get column name or alias from query
  13. Python Pandas write to sql with NaN values
  14. ImportError: No module named MySQLdb
  15. Print the actual query MySQLdb runs?
  16. Python Pandas to_sql, how to create a table with a primary key?
  17. Can’t install mysql-python (newer versions) in Windows
  18. Dynamic SQL Queries with Python and mySQL
  19. What’s the difference between MySQLdb, mysqlclient and MySQL connector/Python?
  20. How to use python mysqldb to insert many rows at once
  21. ‘pip install MySQL-python’ fails with ‘IndexError’
  22. What is PyMySQL and how does it differ from MySQLdb? Can it affect Django deployment?
  23. How to disable query cache with mysql.connector
  24. Pandas GroupBy.apply method duplicates first group
  25. Pandas groupby month and year
  26. Could pandas use column as index?
  27. Pandas error when using if-else to create new column: The truth value of a Series is ambiguous
  28. Given a pandas Series that represents frequencies of a value, how can I turn those frequencies into percentages?
  29. Add numpy array as column to Pandas data frame
  30. How to use ast.literal_eval in a pandas dataframe and handle exceptions

Leave a Comment