将Pandas DataFrame写入MySQL数据库 [英] Write Pandas DataFrame in to MySQL database
问题描述
我正在尝试使用以下代码将熊猫数据帧写入MySQL数据库.
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'
推荐答案
您已经用sql = """...
覆盖了from pandas.io import sql
,因此sql
现在是字符串,不再是保存write_frame
的pandas
模块>功能.
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.
AttributeError: 'numpy.int64' object has no attribute 'replace'
错误是由于您使用整数列标签(这是一个错误)而引起的.尝试将列标签设置为其他内容,例如:
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']
这篇关于将Pandas DataFrame写入MySQL数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!