Python - implement range of mysql cursor command in for loop

Question

I am trying to extract data for many points from MySQL database via cursor() in python. So Far my code looks like:

cursor1 = db.cursor()
cursor2 = db.cursor()


sql_select_Query1 ="SELECT AVG(IWV) As AverageIWV FROM GNSS_OUT WHERE StationID=51 AND Datetime BETWEEN '2021-05-01' AND '2021-05-31' AND SourceGpsID=23 ;"
    cursor1.execute(sql_select_Query1)

sql_select_Query2 ="SELECT AVG(IWV) As AverageIWV FROM GNSS_OUT WHERE StationID=52 AND Datetime BETWEEN '2021-05-01' AND '2021-05-31' AND SourceGpsID=23 ;"

cursor2.execute(sql_select_Query2)

yaxis1 = cursor1.fetchall()
yaxis2 = cursor2.fetchall()

And it works well, but I need more than 2 values I want to make it compact, maybe in a for loop with range(). I've tried something like this:

for m in range(1,5):
    exec(f'cursor{m} = db.cursor()')

for n in range(1,5):
    for stat in range(51,55):
        exec(f'sql_select_AVG{n} = "SELECT AVG(IWV) As AverageIWV FROM GNSS_OUT WHERE StationID={stat} AND Datetime BETWEEN '2021-05-01' AND '2021-05-31' AND SourceGpsID=23 ;"')

        exec(f'cursor{n}.executemany(sql_select_AVG{n})')

        exec(f'yaxis{n}=cursor{n}.fetchall()')

And when I run the script I get:

exec(cursor{m} = db.cursor())
               ^
SyntaxError: invalid syntax

I've tried with single cursor, but it rewrites the extracted data and saves only the last value. — denbar
– denbar, Commented Feb 9, 2022 at 14:13

snakecharmerb · Accepted Answer · 2022-02-09 14:35:53Z

1

The best way to do this is to use lists or dictionaries to hold the queries and results, something like this:

queries = ['SELECT ...', 'SELECT ...', ...]
y_axes = []

cur = db.cursor()
for query in queries:
    cur.execute(query)
    y_axes.append(cur.fetchall())

Once the loop has completed, y_axes will contain the result of each query as a sublist, so you can loop over y_axes to perform further processing.

You can also use the zip built-in function to loop over lists in parallel.

answered Feb 9, 2022 at 14:35

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

Collectives™ on Stack Overflow

Python - implement range of mysql cursor command in for loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related