0

I have a list of values created from some analysis I did on Pandas. I then want to run a for loop to execute a SQL query using each of the values in that list.

I'd like these results to either be appended to a new DataFrame- I can initialise previously, or to add each one to a new DataFrame, which I can then append myself.

Example code is below, where df['a'] would be the results column of my previous analysis.

df = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]),
columns=['a', 'b', 'c'])
x = df['a']
r = pd.DataFrame(columns=['F', 'G', 'H', 'I'])
for m in x:
    r.append(pd.read_sql_query("""SELECT a.F,a.G,a.H,a.I
                                    FROM users a
                                    WHERE a.F ="""+m,engine))

I'd like all the the data to append to r

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
David McConaghy
  • 1
  • 1
  • [Never call DataFrame.append or pd.concat inside a for-loop. It leads to quadratic copying.](https://stackoverflow.com/a/36489724/1422451) – Parfait Aug 08 '19 at 23:11

1 Answers1

0

You should execute a single query instead of iterating over x, like this:

SELECT a.F,a.G,a.H,a.I FROM users a WHERE a.F in (1, 4, 7)

The code below will do that:

import pandas as pd
import numpy as np

df = pd.DataFrame(
    np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]),
    columns=['a', 'b', 'c']
)

x = df['a']
x_list = ', '.join(str(i) for i in x)

r = pd.DataFrame(columns=['F', 'G', 'H', 'I'])
query = f'SELECT a.F,a.G,a.H,a.I FROM users a WHERE a.F in ({x_list})'
r.append(pd.read_sql_query(query, engine))
Lucas
  • 655
  • 5
  • 17