0

I am new to databases and MySql.

My goal - to write files to a local database.

I have 7 files that I create from calling APIs and databases. Currently, I am able to export the files to CSV format. I want to import those files to separate tables into MySQL local database.

Files have the following structure:

date        id_1   id_2   id_3   id_4   id_5   ...
2019-09-12  1      0      1      0      1      ...
...
...

I have created the table that matches the structure using myphpadmin and named the table test_table.

I have inserted a dummy row in order to check if I am able to connect to the database - I am:

import mysql.connector


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

cur = db.cursor()
cur.execute("SELECT * FROM test_table")
for row in cur.fetchall():
    print(row)
db.close()

2019-12-9, 1, 0, 1, 0, 1

I export my files like this:

export.to_csv(f"{export_folder}Finalized Data Export.csv",
                                encoding="utf-8", index=False)

My desired output:

Instead of exporting files to CSV I want to write those files to a local database that I have created. I am able to connect to it and read information, now I need to write the information to the database.

The database should look the same as my current CSV files that I export. I am using pandas to create the CSV files, maybe pandas package has something to write to MySQL database instead of writing information to CSV?

How would I proceed forward with this task? Thank you for your suggestions.

Jonas Palačionis
  • 4,591
  • 4
  • 22
  • 55

1 Answers1

0

you can use pandas to_sql() function You will need to use sqlalchemy for this:

import sqlalchemy
engine = sqlalchemy.create_engine('mysql://root:password@localhost:3307') # connect to server
engine.execute("USE analytics") # select analytics db

Then dump your df as:

df.to_sql('table_name', con=engine)
gokaai
  • 91
  • 7
  • I am getting an error - (1045, "Access denied for user 'root'@'localhost' (using password: YES)") In your answer I have replaced only password with my actual password. – Jonas Palačionis Dec 09 '19 at 11:38
  • You may have to grant these privileges using your db console: https://stackoverflow.com/questions/10181344/mysql-exceptions-operationalerror-1045-access-denied-for-user-rootlocalh – gokaai Dec 09 '19 at 11:47