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