Exporting pandas dataframe to MySQL using SQLAlchemy

Question

I intend to export a pandas dataframe to MySQL using SQLAlchemy. Despite referring to all previous posts, I am unable to solve the issue:

import pandas as pd
import pymysql
from sqlalchemy import create_engine

df=pd.read_excel(r"C:\Users\mazin\1-601.xlsx")

cnx = create_engine('mysql+pymysql://[root]:[aUtO1115]@[localhost]:[3306]/[patenting in psis]', echo=False)

df.to_sql(name='inventor_dataset', con=cnx, if_exists = 'replace', index=False)

Following is the error:

OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost]:[3306' ([Errno 11001] getaddrinfo failed)")

are you able to connect to the sql database via cmd/terminal? — iam.Carrot
– iam.Carrot, Commented Feb 8, 2018 at 16:12
@iam.Carrot - I am using Python to interact with MySQL for the first time. I was able to input data from a sample MySQL table in Python as a pandas dataframe. — Mazahir Bhagat
– Mazahir Bhagat, Commented Feb 8, 2018 at 16:24
It seems that the database is not running. Please ensure your MySQL database is started, then use a client like MySQL Workbench to try to connect. — Milton
– Milton, Commented Jun 23, 2023 at 0:52

Mazahir Bhagat · Accepted Answer · 2018-02-23 15:22:42Z

2

After a lot of tinkering with the code and exploring different packages in Python, I was able to make the code work.

Code:

import mysql.connector
import sqlalchemy

database_username = 'root'
database_password = 'mysql'
database_ip       = '127.0.0.1'
database_name     = 'patenting_in_psi'
database_connection = sqlalchemy.create_engine('mysql+mysqlconnector://{0}:{1}@{2}/{3}'.
                                               format(database_username, database_password, 
                                                      database_ip, database_name), pool_recycle=1, pool_timeout=57600).connect()

df22.to_sql(con=database_connection, name='university_dataset_ca', if_exists='append',chunksize=100)
database_connection.close()

answered Feb 23, 2018 at 15:22

Mazahir Bhagat

1374 silver badges10 bronze badges

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

Comments

Milton · Accepted Answer · 2023-06-23 00:48:26Z

0

Here is another option:

import pandas as pd
import sqlalchemy

# Read your CSV file into a Pandas dataframe
df = pd.read_csv("test.csv")

# Store the dataframe in MySQL
engine = sqlalchemy.create_engine('mysql+mysqlconnector://user:[email protected]/schema')

with engine.begin() as connection:
    df.to_sql('your_table', con=connection, if_exists='replace', index=False)

answered Jun 23, 2023 at 0:48

Milton

9681 gold badge16 silver badges31 bronze badges

Collectives™ on Stack Overflow

Exporting pandas dataframe to MySQL using SQLAlchemy

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related