Dump a pandas DataFrame which contains column name as "name" to mysql table

Question

I am trying to dump my crawled data to mysql using pandas and to_sql.

I am approaching in two ways 1>

{# -*- coding: utf-8 -*-
import numpy as np
import pandas as pd
import MySQLdb
from sqlalchemy import create_engine
import os
from pandas.io import sql
import MySQLdb

columns_list= ['persons','c_number','name']
df = pd.DataFrame()
file_name = "/home/*****/****/data.xlsx"
data = pd.read_excel(file_name)
df = df.append(data)
df.columns = columns_list
engine=create_engine('mysql+mysqldb://root:root@localhost:3306/database_name?charset=utf8&use_unicode=1', echo=False)
con = MySQLdb.connect(host="localhost",user="user_name",passwd="password",                db="database_name")
df.to_sql(con=con, name='table_name',if_exists='replace',flavor='mysql')}

Here I am getting TypeError: to_sql() got multiple values for keyword argument 'name' error

2>

import numpy as np
import pandas as pd
import MySQLdb
from sqlalchemy import create_engine
import os

columns_list= ['persons','c_number','name']
df = pd.DataFrame()
file_name = "data.xlsx"
data = pd.read_excel(file_name)
df = df.append(data)
df_n = pd.DataFrame()
df = df_n.append(df_new)
df = np.array_split(df, 100)
for x in range(len(df_new)):
    print x
    engine = create_engine('mysql+mysqldb://user_name:password@localhost:3306/database_name?charset=utf8&use_unicode=1', echo=False)
    df[x].to_sql(name='table_name', con=engine, if_exists = 'append', index=False)
    engine.dispose()

This way I am able to dump the data but name field I am getting Null in all records.

Is there a way to dump the data using column name "name"

O.Suleiman · Accepted Answer · 2017-11-13 13:21:45Z

1

I think you have a typo there since you have written df inside the to_sql method, try: df.to_sql(con=con, name='table_name',if_exists='replace',flavor='mysql')

answered Nov 13, 2017 at 13:21

O.Suleiman

9281 gold badge6 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sarvajeetsuman · Accepted Answer · 2017-11-14 05:22:18Z

0

I have faced the similar situation before and i don't know the exact reason why it happens but here is something that worked for me.

I read the dataframe with read_excel like you have done, and re-written the excel file with modified column names.

 writer = pd.ExcelWriter("data_new.xlsx")
 data.to_excel(writer,'sheet1')
 writer.save()

Then i pushed to the database. I hope it works for you too.

answered Nov 14, 2017 at 5:22

sarvajeetsuman

2364 silver badges13 bronze badges

Collectives™ on Stack Overflow

Dump a pandas DataFrame which contains column name as "name" to mysql table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related