cursor.fetchall() function return an empty list - python mysql

Question

I tried to query some data from mysql database, but i couldn't fetch any data even though the data is indeed exist in the database and there is no error message whatsoever.

mysql_config={
    'user' : 'root',
    'password' : '*********',
    'host' : '*******',
    'database' : 'aa_dev',
    'port' : 3306
}

sql = 'SELECT idfi, aircraft, file_location FROM flights_info WHERE idfi =%s'
val = [('PK-YSG-23001',), ('PK-YSG-23002',), ('PK-YSG-23003',), ('PK-YSG-23004',)]

mydb = mysql.connector.connect(**mysql_config)
mycursor = mydb.cursor()

mycursor.executemany(sql, val)
d = mycursor.fetchall()
mycursor.close()
print(d) #Output an empty list

Then I tried query only one data and get the result. But i still don't understand why can't i query the data in bulk?.

sql = 'SELECT idfi, aircraft, file_location FROM flights_info WHERE idfi =%s'
val = ('PK-YSG-23001',)

mydb = mysql.connector.connect(**mysql_config)
mycursor = mydb.cursor()

mycursor.execute(sql, val)
d = mycursor.fetchall()
mycursor.close()
print(d) #Output the data

Don't you rather want to execute one query with WHERE idfi IN ..., then many separate queries? — deceze
– deceze ♦, Commented Nov 30, 2023 at 4:54

TSCAmerica.com · Accepted Answer · 2023-11-30 13:18:47Z

1

What I did below is instead of using the executemany, I modified the query to use an IN clause. This will allow us to fetch all the desired rows in one go.

sql = 'SELECT idfi, aircraft, file_location FROM flights_info WHERE idfi IN (%s)'

placeholders = ', '.join(['%s'] * len(val))
flattened_values = [item for sublist in val for item in sublist]

mydb = mysql.connector.connect(**mysql_config)
mycursor = mydb.cursor()

mycursor.execute(sql % placeholders, flattened_values)
d = mycursor.fetchall()
mycursor.close()
print(d)

answered Nov 30, 2023 at 13:18

TSCAmerica.com

5,45712 gold badges57 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

cursor.fetchall() function return an empty list - python mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related