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 — shaik moeed
– shaik moeed, Commented Dec 9, 2019 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
– Jonas Palačionis, Commented Dec 9, 2019 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
– J2112O, Commented Dec 9, 2019 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
– Jonas Palačionis, Commented Dec 9, 2019 at 12:42

Jonas Palačionis · Accepted Answer · 2019-12-09 13:17:02Z

-1

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.

answered Dec 9, 2019 at 13:17

Jonas Palačionis

4,8704 gold badges31 silver badges74 bronze badges

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

Collectives™ on Stack Overflow

I am trying to insert dataframe to a local MySQL database

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related