Python: Iterating over rows in a MySQL table-CodePudding

Let me preface this by saying that I am only just starting out with Python, and am really liking it so far!

I am trying to create a Python script that updates certain values in a MySQL database based on another value. I have that part mostly working, but no matter what I try I can't get the script to properly iterate to the other rows. Currently, it just takes the value (location in ost_user__cdata) from the first row and applies it to every row in the destination column (org_id in ost_user).

If anyone is interested, what I am trying to accomplish here is to update the user's organization in osTicket based off of a custom field chosen by the end-user on their profile (the "organization" in this case will represent which office location the user is at, so I want the users to be able to pick their location themselves and have that automatically put them in the correct organization). osTicket doesn't allow for this in the app (only agents can set a user's org, which makes sense for how it would typically be used), however I have found that it is possible to set through the database.

Any help on this is much appreciated! Please see my script below.

Thanks!

import mysql.connector

db = mysql.connector.connect(
    host="ip",
    user="user",
    passwd="pass",
    database="db"
    )

cursor = db.cursor(buffered=True)
cursor2 = db.cursor(buffered=True)
cursor.execute("SELECT id from ost_user")
goCheck = cursor.fetchone()

if (goCheck is not None):

    #cursor.execute("SELECT * from ost_user__cdata WHERE user_id=" str(dbid))
    ##cursor.execute("SELECT * from ost_user")
    cursor.execute("SELECT id from ost_user")
    numrows = cursor.rowcount
    row1 = cursor.fetchall()

    #for id in cursor:
    for id in range(0,numrows):
        cursor2.execute("SELECT id from ost_user")
        getID = cursor2.fetchone()
        dbid = str(getID[0])
        cursor2.execute("SELECT location from ost_user__cdata WHERE user_id=" (dbid))
        loca = cursor2.fetchone()
        sel = str(loca[0])
        
        if (sel == "1"):
            loc = "8"

        elif (sel == "2"):
            loc = "2"

        elif (sel == "3"):
            loc = "3"

        elif (sel == "4"):
            loc = "4"

        elif (sel == "5"):
            loc = "5"

        elif (sel == "6"):
            loc = "6"

        elif (sel == "7"):
            loc = "7"

        elif (sel == "8"):
            loc = "9"

        else:
            print("Location not recognized.")
            quit()

        cursor2.execute("UPDATE ost_user SET org_id=" (loc),"WHERE id=" (dbid))
        db.commit()
        print("User ID: " (dbid),"added to org: " (loc))
            

else:
    print("?")
    quit()

Output from being run (currently there are only 2 entries in the ost_user table, with the IDs 3 and 4 - User 3 does belong in org 9, but user 4 should be in org 5):

User ID: 3 added to org: 9
User ID: 3 added to org: 9

CodePudding user response：

Do this with a single database query, not a Python loop. You can use a CASE expression to handle the cases where the location doesn't map directly to an organization ID.

UPDATE ost_user AS u
JOIN ost_user__cdata AS c ON u.id = c.user_id
SET u.org_id = 
    CASE c.location
        WHEN '1' THEN '8'
        WHEN '8' THEN '9'
        ELSE c.location
    END