Python. Optimize Append DF to MySQL DB

Question

I am adding in a DB the output of a model like this:

cursor = conn.cursor()
for i in range(len(df)):
    UserId = df.loc[i, 'UserId']
    Timestamp = df.loc[i, 'Timestamp']
    ChurnPropensity = df.loc[i, 'ChurnPropensity']

    sql = "INSERT INTO DB_Name (UserId, Timestamp, ChurnPropensity) VALUES ({},'{}',{});".format(UserId, Timestamp, ChurnPropensity)

    cursor.execute(sql)

conn.commit()

However, it takes a long time due to the for loop. How would you improve computation time?

Salut,

How big is the table? Have you tried using pd.DataFrame.to_sql? — BernardL
– BernardL, Commented Oct 24, 2018 at 14:23
I have actually, but did not manage to set up properly the 'create_engine' bit. The table is a few thousand rows, but I'd like it to be able to easily scale. — prp
– prp, Commented Oct 24, 2018 at 14:28
Managed to get pd.DataFrame.to_sql working, and time was reduced by 90% :) — prp
– prp, Commented Oct 26, 2018 at 14:40
Awesome. But I think there are still bottlenecks with bulk inserts even with to_sql because of the I/O. You can try building raw queries and then using sqlalchemy to execute them as well. But great it helped! — BernardL
– BernardL, Commented Oct 26, 2018 at 14:44

Oli · Accepted Answer · 2018-10-24 15:10:03Z

1

Try this method. Ideally it should speed up the execution.

query = "INSERT INTO DB_Name (UserId, Timestamp, ChurnPropensity) VALUES ({},'{}',{});"
df.apply(lambda row: cursor.execute(query.format(row['UserId'], 
                                                 row['Timestamp'], 
                                                 row['ChurnPropensity'])),
axis=1)

From my experience, execute method itself is slow, so you could speed up by trying to execute multiple queries at once.

query = "INSERT INTO DB_Name (UserId, Timestamp, ChurnPropensity) VALUES ({},'{}',{});"
queries_list = df.apply(lambda row: query.format(row['UserId'], 
                                            row['Timestamp'], 
                                            row['ChurnPropensity']),  axis=1).values.tolist()

queries = ' '.join(queries_list)
cursor.execute(queries, multi=True)

edited Oct 24, 2018 at 15:10

answered Oct 24, 2018 at 14:29

Oli

1,3931 gold badge16 silver badges33 bronze badges

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

2 Comments

prp Over a year ago

Thanks! but it takes the same amount of time.

prp Over a year ago

unexpected keyword argument 'multi'

Collectives™ on Stack Overflow

Python. Optimize Append DF to MySQL DB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related