pandas DataFrame to_sql Python

Question

I want to create new DB in mysql based on few csv files. what do I need to add? And how do I open a new db from python without manually opening it from phpmyadmin?

import pymysql
import pandas as pd

# Creating the DB:

DB = pymysql.connect(host='localhost',
    user='root',
    passwd='',
    db='DB')

csv1 = pd.read_csv('C:/.........csv')

csv1SQL =pd.DataFrame.to_sql(name='Orders', con=DB, flavor=None, schema=None, if_exists='fail', index=True,                         index_label=None, chunksize=None, dtype=None)

cursor.execute(csv1SQL)

cursor = pymysql.cursor()

the error:

    "pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting"

Filipe Lemos · Accepted Answer · 2017-10-27 18:21:22Z

10

As I looked into other topics I found out that a solution like this one from James at questions about pandas.to_sql could be the solution for your problem. Here is what he said.

Your way is not supported anymore. Try this?

from sqlalchemy import create_engine
import pandas as pd


engine = create_engine("mysql://root:matt123@localhost/ada")
df=pd.DataFrame(['A','B'],columns=['new_tablecol'])
df.to_sql(name='new_table',con=engine,if_exists='append')

Syntax is:

engine = create_engine("mysql://USER:PASSWORD@HOST/DATABASE")

answered Oct 27, 2017 at 18:21

Filipe Lemos

5304 silver badges16 bronze badges

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

2 Comments

AdiR Over a year ago

can you be specific with my code? I need to know what to change

Filipe Lemos Over a year ago

sql.write_frame(csv1, con=DB, name='csv1', if_exists='replace', flavor='mysql') Can you try this first? I'm alway from my desk so I can't check the codes.

NL23codes · Accepted Answer · 2019-03-20 14:35:30Z

2

I'm not sure if the use of pysql is a necessity, but in the event sqlite3 will suffice, then it could look like this:

import pandas
import sqlite3 as db

DB = db.connect('DB.db')
csv1 = pandas.read_csv('C:\\…..csv')
csv1.to_sql(name='Orders', con=DB, if_exists='replace')
#replace is one of three options available for the if_exists parameter
DB.close()

However, this format and method are probably unrelated to the error you received, which may have had something to do with the data within your csv file. Without seeing it, it's hard to be certain.

answered Mar 20, 2019 at 14:35

NL23codes

1,2291 gold badge15 silver badges32 bronze badges

Collectives™ on Stack Overflow

pandas DataFrame to_sql Python

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related