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"

Answer 1

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")

• AdiR: can you be specific with my code? I need to know what to change
• Filipe Lemos: 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.

Answer 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.