CODEWITHSUNDEEP
pythonmysqlpython-3.xpymysql
José Carlos
José Carlos

Reputation: 2922

How to execute more than once the same query with different data?

I'm trying to execute the same query but with different data but I always get data the first time. The others times, dispite of there are data for the querys in the data base, mysql returns empty data.

This is the code:

def get_team_colour_map(self, players, id_competition):
    tcm = FIBAColourMap()
    for p in players:
        args = [p["id"], id_competition]
        conn = pymysql.Connect(host = DDBB.DDBB_FIBA_HOST,
                                      user = DDBB.DDBB_FIBA_USER,
                                      password = DDBB.DDBB_FIBA_PSWD,
                                      db = DDBB.DDBB_FIBA_NAME,
                                      charset = DDBB.DDBB_FIBA_CHARSET,
                                      cursorclass=pymysql.cursors.DictCursor)
        with conn.cursor() as cursor:
            print("id player: {}".format(p["id"]))
            print("args: {}".format(args))
            cursor.execute("select sc.* from tbl030_shots_chart sc, tbl006_player_team pt, tbl007_game g, tbl004_jornada j, tbl012_competition c where pt.id = %s and pt.id_player_feb = sc.id_fiba and sc.id_game = g.id and g.id_jornada = j.id and j.id_competition = c.id and c.id = %s", args)
            data = cursor.fetchall()
            print("data: {}".format(data))
            print("Total rows: {}".format(cursor.rowcount))
            if cursor.rowcount > 0:
                for s in data:
                    x = float(FIBASCReport.adjust_x(s["x"]))
                    y = float(FIBASCReport.adjust_y(s["y"]))
                    color = tcm.image.getpixel((x,y))
                    color = ("#%02x%02x%02x" % color).upper()
                    if tcm.exists_color(color):
                        if int(s["m"]) == 0:
                            tcm.set_scored_shots(color, 1)
                        else:
                            tcm.set_failed_shots(color, 1)
                    else:
                        if int(s["m"]) == 0:
                            tcm.set_scored_shots("OTROS", 1)
                        else:
                            tcm.set_failed_shots("OTROS", 1)
            else:
                #tcm = None
                print("Jugadora con id: {} NO ha realizado ningún tiro en competición: {}".format(p["id"], id_competition))
    return tcm

In this code, cursor.fetchall() returns data the first query but the next querys returns empty results.

How can I run several querys? I'm using mySQL 8.0 and Python 3.6

Upvotes: 0

Views: 966

Answers (1)

a5vTech
a5vTech

Reputation: 229

Its because you are using the same cursor each time. create a new instance of the cursor each time you loop through to excecute the query. After the first query is run the cursor is already positioned after all the data. Hence no rows returned after that

You can also try this:

Look at the documentation for MySQLCursor.execute().

It claims that you can pass in a multi parameter that allows you to run multiple queries in one string.

If multi is set to True, execute() is able to execute multiple statements specified in the operation string.

multi is an optional second parameter to the execute() call:

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation, multi=True):

Upvotes: 4

Related Questions