Vectorized lookup of MySQL, and add to DataFrame
<p>I'm trying to do the following:</p>

<ol>
<li>Go through a DataFrame, that contains Columns 'Col1' and 'Col2'</li>
<li>Take each row in 'Col1', Search MySQL db using that value</li>
<li>Replace the value on the same row in 'Col2' with the result</li>
</ol>

<p>I'm leaning towards a For loop approach, but is there a faster vectorized approach. Rough code I'm using thus far:</p>

<pre><code>    rsp_df = pd.DataFrame(pd.read_csv(raw_data_path))
cur = mydb.cursor()

for x in rsp_df['Col1']:
    query = ("SELECT stuff FROM some-table WHERE Asin = '%s'" % str(x))
    cur.execute(query)
    rows = cur.fetchone()
    print rows
</code></pre>

<p>Thanks a lot!</p>

<p>Consider merging the MySQL query with the Pandas dataframe by importing the query into a separate dataframe. This way you match across all cases at once without looping and any conditional changes to columns can be done in one call.</p>

<p>Below is a <code>left</code> join merge to keep all records in <em>rsp_df</em> matched or not. Missing <em>stuff</em> from mydf denotes unmatched records. Then you can replace <em>Col2</em> with needed <em>result</em> (which I am not sure what you refer to as <em>result</em>, possibly a column in query's <em>stuff</em>):</p>

<pre><code>from sqlalchemy import create_engine

engine = create_engine('mysql://user:pwd@localhost/database')

mydf = pd.read_sql("SELECT stuff FROM some-table", con=engine)

merged_df = pd.merge(rsp_df, mydf, left_on=['Col1'], right_on=['Asin'], how='left')

mergedf.loc[pd.notnull(mergedf['Asin']), 'Col2'] = mergedf['result']
</code></pre>

python mysql dataframe