Need to edit subset of rows from MySQL table using Pandas Dataframe
<p>I'm in the process of trying to alter a table in my database. However I am finding it difficult using the to_sql method provided by Pandas. My <code>price_data</code> Dataframe looks something like this:</p>

<h2>Initial Dataframe (as rows in the database):</h2>

<p><a href="http://i.stack.imgur.com/5sPFo.jpg" rel="nofollow"><img src="http://i.stack.imgur.com/5sPFo.jpg" alt="enter image description here"></a></p>

<h2>Code used to alter data:</h2>

<pre><code>with con:
    price_data.to_sql(con=con, name='clean_prices2', if_exists='append', index=False, flavor='mysql')
</code></pre>

<p>The ultimate goal here is to modify the initial dataframe (converting zero values into Nan's, then interpolate them), and saving it back in the database. The results should look like this (except with the same <code>id</code>):</p>

<h2>Desired Output:</h2>

<p><a href="http://i.stack.imgur.com/qCSw4.jpg" rel="nofollow"><img src="http://i.stack.imgur.com/qCSw4.jpg" alt="enter image description here"></a></p>

<p>If you look specifically at the <code>close_price</code> column you can see the 0 value was assigned 90.7350</p>

<p>My current solution is appending the datarows, which results in duplicate enteries like this:</p>

<h2>Actual Output:</h2>

<p><a href="http://i.stack.imgur.com/b5NQ7.jpg" rel="nofollow"><img src="http://i.stack.imgur.com/b5NQ7.jpg" alt="enter image description here"></a></p>

<p>Finally, I would have to perform another query to remove the duplicate rows (based on price_date)</p>

<p>I know I could change the <code>if_exists</code> parameter to <strong>replace</strong> but that would remove the rest of my database table. Basically I want to perform this query multiple times on different <code>symbol_id</code>'s</p>

<p>Is there anyway to modify a subset (in the case, just the 3 rows) without removing the rest of the data in my table? The solution can either modify the existing rows (keeping the same <code>id</code>) or delete the old rows, and create the new ones without zeroes. I am just trying to accomplish this without the additional delete duplicate query.</p>

<p>Consider a temp table with exact structure as final table but regularly replaced and will then be used to update existing final table. Try using <a href="http://docs.sqlalchemy.org/en/latest/core/connections.html" rel="nofollow">sqlalchemy engine</a> for both operations. </p>

<p>Specifically, for the latter SQL you would use an <a href="http://www.mysqltutorial.org/mysql-update-join/" rel="nofollow">UPDATE JOIN</a> query between temp and final table. Below assumes you use <code>pymysql</code> module (adjust as needed):</p>

<pre><code>import pymysql
from sqlalchemy import create_engine
...

engine = create_engine("mysql+pymysql://user:password@hostname:port/database")

# PANDAS UPLOAD
price_data.to_sql(name='clean_prices_temp', con=engine, if_exists='replace', index=False)

# SQL UPDATE (USING TRANSACTION)
with engine.begin() as conn:     
    conn.execute("UPDATE clean_prices_final f" +
                 " INNER JOIN clean_prices_temp t" +
                 " ON f.symbol_id = t.symbol_id" +
                 " AND f.price_date = t.price_date" +
                 " SET f.open_price = t.open_price," +
                 "     f.high_price = t.high_price," +
                 "     f.low_price = t.low_price," +
                 "     f.close_price = t.close_price," +
                 "     f.adj_close_price = t.adj_close_price;")

engine.dispose()
</code></pre>

python mysql pandas pandasql