CODEWITHSUNDEEP
pythonmysqlsql
Steven Helliwell
Steven Helliwell

Reputation: 1

Python sql search inside a loop returning the same value

I am currently having a few issues checking a database in python inside a loop.

Each time i am presented with the same value.

My goal is to check a table called seeTicketsEvents for empty event ids.

Then for each check another table called seeTicketsTickets for the name & data and then pass the id back to the first table.

when i query the second table with the function see_tickets_check_row(row) each time i get the same result.

`

def connect_to_db():
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="root",
        database="db",
        unix_socket= "/Applications/MAMP/tmp/mysql/mysql.sock"
    )

    return mydb

`

`

def see_tickets_map_db():
    mydb = connect_to_db()
    with mydb.cursor() as mycursor:

        mycursor.execute("SELECT * FROM `seeTicketsEvents` WHERE `ED_eventID` IS NULL")
        
        for row in mycursor:
            
            print(row)
            see_tickets_check_row(row)
            


def see_tickets_check_row(data_row):
    mydb = connect_to_db()
    with mydb.cursor() as mycursor:
        showdate = data_row[3]
        showname = data_row[4]

        print(showdate)
        print(showname)

        rowNew = []

        mycursor = mydb.cursor()
        mycursor.execute("SELECT * FROM `seeTicketsTickets` WHERE `showName` = showname AND `showDate` = showdate LIMIT 1")


        for rowNew in mycursor:
            #print(rowNew)
            return rowNew[2]

`

I have tried putting the connections inside a with satment and also tried closing the connection all with no luck.

Any help would be hugely appreacated.

Upvotes: 0

Views: 166

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64959

I think the problem is in this line:

        mycursor.execute("SELECT * FROM `seeTicketsTickets` WHERE `showName` = showname AND `showDate` = showdate LIMIT 1")

What does the query

SELECT * FROM `seeTicketsTickets` WHERE `showName` = showname AND `showDate` = showdate LIMIT 1

return?

It returns the first row from seeTicketsTickets where the value in the column showName equals the value in the column showname and the value in the column showDate equals the value in the column showdate. Column names are not case-sensitive in MySQL, so you are comparing two columns against themselves, and a value in a column will always be equal to itself if it's not NULL. So you'll get the first row where the values in the showName and showDate columns are not NULL, where 'first' is whichever row the database happens to return first.

I suspect you want to pass the values of your local variables showdate and showname into your query. To do that, write the following:

        mycursor.execute("SELECT * FROM `seeTicketsTickets` WHERE `showName` = %s AND `showDate` = %s LIMIT 1", (showname, showdate))

Upvotes: 1

Related Questions