Python - implement range of mysql cursor command in for loop

user_12485074 0
Viewed 44

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
mysql python
1 Answers

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.

user_53209060
Related