Insert Python Dataframes into MySQL

Question

I am trying to call in data from an API as raw jsons, normalize them, and then send them along to a table in MySQL. But am having formatting issues in getting the values to place in my INSERT statement. Is there anyway to do this without converting everything into a single String then passing that as the SQL command?

#Getting Data    
conn = http.client.HTTPSConnection("api.address.com")
conn.request("GET", url, headers=datheaders)
eventres = conn.getresponse()
eventjson = json.loads(eventres.read().decode("utf-8"))
eventtable = json_normalize(eventjson)

At this point I have a large DataFrame, assuming I have already created the table in MySQL, I want to then upload the data I have to it.

conn = pymysql.connect(host='testrds.value.com',
    user='val',
    passwd='######',
    db='Data')
cursor = conn.cursor()

This is where I have problems formatting my values into a SQL INSERT statement.

front = "INSERT INTO `tablename` (`client_version` text,  `device_surrogate` text,  `envelope_id` text,  `envelope_timestamp` text,  `geohash` text,  `location_method` text,  `location_precision` text,  `received_timestamp` text,  `schema_version` double DEFAULT NULL, `timestamp` text) VALUES ("
back = " )"
for row in eventtable.iterrows():
     sql = front + str(row.values) + back
     cursor.execute(sql)

I'm iterating through the dataframe and want to use the values of each row to concat together a SQL statement, if I try something like print(row.values) I get something that looks very close to what I want:

In: print(row.values) 
Out: ['7.6.0.62' 'cellularphone.YN_7FZ0nUrqbHW--YylTnJH67qvJ-i6gLgTIljnFnRA=' 'fc76a4bd-1ad9-11e9-a705-3b719d243433' '2019-01-18T04:31:54.334+0000' 'com.tmobile.pr.analyticssdk' '1.0.52' 'mockCamp' '0cfe18e0-7bf8-4340-b8ed-4c768ec48cbd' nan 0.0 '89da2c58-6ac7-46ff-a3e8-124914c8e53b' '5fc3d106-e412-4826-818c-95ad40414e5a']

I will need to add commas and remove the brackets and, unfortunately when I actually try to evaluate row.values as a string, it comes out with a bunch of \n values too.

I can likely get this with regular expression to format how I need so that SQL will accept it, but I feel like there has got to be a better way to pass a row of data to MySQL or event the entire dataframe.

EDIT: to_sql() is definitely the answer I was looking for, however connecting was still an issue, I was able to get the below solution to work, for anyone who has similar issues.

database_username = 'Name'
database_password = '#####'
database_ip       = 'host'
database_name     = 'Data'

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()
eventtable.to_sql(con=database_connection, name=eventtype, if_exists='append',chunksize=100)

If you have your data in a DataFrame, you can use the pandas to_sql to insert it into your database. Take a look at this https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html — nacho, Mar 07 '19 at 16:52

perl · Accepted Answer · 2019-03-07T18:22:25.417

5

You probably don't need to iterate over the DataFrame, just use to_sql method:

import sqlalchemy as sa

e = sa.create_engine(...)
df.to_sql("table_name", e, if_exists="replace", index=False)

Here's an example for MySQL: Writing to MySQL database with pandas using SQLAlchemy, to_sql

edited Mar 07 '19 at 18:22

answered Mar 07 '19 at 16:57

perl

9,826
1
10
22

1

create_engine() and to_sql() are exactly the things I was looking for. Thank you! To get the connection to work some finagling was needed, I've added to the comment. – Dick McManus Mar 07 '19 at 21:15

Insert Python Dataframes into MySQL

1 Answers1