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"

score 10 · Accepted Answer · answered Oct 27 '17 at 18:21

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 '17 at 18:21

Filipe Lemos

500
3
13

can you be specific with my code? I need to know what to change – AdiR Oct 27 '17 at 18:29
`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. – Filipe Lemos Oct 27 '17 at 18:49

score 2 · Answer 2 · answered Mar 20 '19 at 14:35

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.

pandas DataFrame to_sql Python

2 Answers2