Python sql search inside a loop returning the same value

Question

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.

I tried this: val = ( data_row[4], data_row[3] ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM `seeTicketsTickets` WHERE `showName` = '%s' AND `showDate` = '%s' LIMIT 1",val) But get the error: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BRISTOL COMEDY CABARET - SATURDAY 8:00PM SHOW (rotunda comedy club)'' AND `showD' at line 1 — Steven Helliwell, Nov 04 '22 at 21:37
@StevenHelliwell: as per my answer, don't put quotes around the `%s` placeholders. — Luke Woodward, Nov 04 '22 at 21:39
You don´t need 2 queries to to that, you can do it with just one query — nacho, Nov 05 '22 at 09:24

score 1 · Answer 1 · answered Nov 04 '22 at 21:36

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))

Python sql search inside a loop returning the same value

1 Answers1