3

I'm trying to write a pandas dataframe to MySQL database with following code.

import pandas as pd
import numpy as np
from pandas.io import sql
import MySQLdb

df = pd.DataFrame([[1.1, 1.1, 1.1, 2.6, 2.5, 3.4,2.6,2.6,3.4,3.4,2.6,1.1,1.1,3.3], list('AAABBBBABCBDDD'), [1.1, 1.7, 2.5, 2.6, 3.3, 3.8,4.0,4.2,4.3,4.5,4.6,4.7,4.7,4.8]]).T

db = MySQLdb.connect("192.168.56.101","nilani","123","test")
cursor = db.cursor()

cursor.execute("DROP TABLE IF EXISTS TEST")

sql = """CREATE TABLE TEST (
         ID  INT NOT NULL,
         COL1 CHAR(20),
         COL2 CHAR(20),  
         COL3 CHAR(20))"""

cursor.execute(sql)

sql.write_frame(df, con=db, name='TEST', flavor='mysql')

db.close()

I have been referring this question and the other resources. Any way I get following error. What will be the reason?

sql.write_frame(df, con=db, name='TEST', flavor='mysql')
AttributeError: 'str' object has no attribute 'write_frame'
Community
  • 1
  • 1
Nilani Algiriyage
  • 32,876
  • 32
  • 87
  • 121

1 Answers1

7

You have overwritten the from pandas.io import sql with sql = """..., so sql is now a string and no longer a pandas module which holds the write_frame function.

EDIT: the AttributeError: 'numpy.int64' object has no attribute 'replace' error you get is due to the fact that you use integer column labels (this is a bug). Try setting the columns labels to something else, eg:

df.columns = ['COL1', 'COL2', 'COL3']
joris
  • 133,120
  • 36
  • 247
  • 202
  • Ok, So what I have to do now? – Nilani Algiriyage Jul 23 '13 at 09:50
  • I tried this after removing the table creation code...sql.write_frame(df, con=db, name='TEST', if_exists='replace', flavor='mysql'), It says : AttributeError: 'numpy.int64' object has no attribute 'replace' – Nilani Algiriyage Jul 23 '13 at 09:53
  • Note, there seems to be a bug with the 'replace' option (see eg https://github.com/pydata/pandas/issues/2971). Does it run without `if_exists='replace'`? – joris Jul 23 '13 at 10:10
  • And to answer what to do: just rename the table creation code to something else than `sql`, and execuste this with `cursor.execute(other_name)` – joris Jul 23 '13 at 10:12
  • Without replace it generates this error..ValueError: Table 'NEW' already exists. With replace gives the above error. I followed what you said, now the problem is with this replace thing.:( – Nilani Algiriyage Jul 23 '13 at 10:22
  • I think the best option at the moment is to delete the table yourself in MySQL if you want to overwrite it (as 'replace' is not working at the moment: https://github.com/pydata/pandas/issues/4110). Note that you don't have to create the table beforehand, `write_frame` will do it for you. – joris Jul 23 '13 at 10:32