I am trying to insert dataframe to a local MySQL database

Question

I am new to MySQL and pandas.

I have created a random dataframe:

data_frame = pd.DataFrame(np.random.randint(
    0, 100, size=(100, 4)), columns=list('ABCD'))

And I want to insert it to my local MySQL database:

db = mysql.connector.connect(host="localhost",
                             user='root',
                             password='pw123',
                             database='analytics',
                             port='3307',
                             auth_plugin='mysql_native_password')

data_frame.to_sql(name='test',
                  con=db, if_exists='append', index=False)

But I get an error

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Not all parameters were used in the SQL statement

If I use sqlalchemy:

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

I get this error:

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

And I know I am using the right password. At this point I have no idea how I could insert my dataframes to a local database.

Thank you for your help.

Does this answer your question? [Pandas 0.20.2 to\_sql() using MySQL](https://stackoverflow.com/questions/44933704/pandas-0-20-2-to-sql-using-mysql) — shaik moeed, Dec 09 '19 at 12:29
No, I still get the error - `sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1045, "Access denied for user 'root'@'localhost' (using password: YES)")` — Jonas Palačionis, Dec 09 '19 at 12:33
Is the port 3307? The default is 3306 and if you have changed it to 3307 then nevermind but I noticed this in the connection string and wanted to bring notice to it. — J2112O, Dec 09 '19 at 12:39
Yes, I have changed it to 3307. I've tried with 3306 and get the same error. I know the connection is good because I am able to write information to the database using the following test `mySql_insert_query = """INSERT INTO test (Id, Name, Price, Purchase_date) VALUES (10, 'Lenovo ThinkPad P71', 6459, '2019-08-14')""" cur.execute(mySql_insert_query)` — Jonas Palačionis, Dec 09 '19 at 12:42

score -1 · Answer 1 · answered Dec 09 '19 at 13:17

Ended up using:

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

engine = create_engine(
    'mysql+pymysql://root:password@localhost:3307/analytics')
data_frame = pd.DataFrame(np.random.randint(
    0, 100, size=(100, 4)), columns=list('ABCD'))
data_frame.to_sql('new', con=engine)

Had to import

pip install cryptography

Then ended up replacing to_csv with to_sql in my cronjobs:

joined.to_sql('export_1', con=engine, if_exists = 'replace')

Which worked fine.

I am trying to insert dataframe to a local MySQL database

1 Answers1