Python and Mysql - is it possible to increase number of rows returned by SELECT?


This is a followup to the previous message that I posted about Python and Mysql (and CASE(), etc.).

As mentioned in the previous message, the Python app is looping through the rows that got returned when it did a SELECT on Mysql, but I just found that one of the problem is that the SELECT only appeared to have returned 52 rows before the WHILE loop ends with a NONE, so the app is not able to search through the entire certificates table, but instead just falls through and kind of blows up.

So I was wondering/hoping if there is an easy way to increase the number of rows that the SELECT can return?


We would need to see the resulting code, in particular, exactly how you’re querying and “looping through”.

Hi Karl,

Here’s most of the code:

#Open MySQL Connection
conn = MySQLdb.connect(host="", user="cfssl_user", passwd=MYSQL_PASS, db="certdb")
#Create cursor to execute SQL query and store result set
curr = conn.cursor()
curr.execute("SELECT CAST(serial_number as CHAR), expiry, CAST(authority_key_identifier as CHAR) FROM certificates WHERE status='good' AND authority_key_identifier like '96aexxx681873'")
rowsReturnedBySelect = curr.rowcount
print("++++ Rows returned by SELECT = ", rowsReturnedBySelect)
#Good certs only, check date logic here
row = curr.fetchone()
currentDate ="%Y-%m-%d %H:%M:%S")
# Create an inner cursor to perform db modifications
innercurr = conn.cursor()
# Get the authorization key id from the cron job
req_auth_key_id = str(sys.argv[1])

currentRow = 0
while row is not None:
    print("ROW", row)
    # Get serialNumber from SQL query request
    serialNumber = str(row[0])
    # Parse the time and typecast to datetime object
    expiryString = str(row[1])
    expiryDate = datetime.datetime.strptime(expiryString, "%Y-%m-%d %H:%M:%S")
    # Get authority_key_identifier from SQL query request
    auth_key_id = str(row[2])
    print("auth_key_id IS", auth_key_id)
    if ((expiryString < currentDate) and (req_auth_key_id == auth_key_id)):
        print("MESSAGE", "expiryString is less than currentDate")
        url = ""
        r =, headers={'Content-Type':'application/json'}, data=json_data, verify=False)
        json_data = json.loads(json.dumps(r.json()))
        if (json_data["success"] == False):
            print("Error contacting the API. Revoke request failed. Aborting script.")
    # END IF
    # Fetch next row otherwise infinite loop
    row = curr.fetchone()
    print("CONTINUE NEXT ROW=", row)
    print("currentRow=", currentRow)
    if (currentRow >= rowsReturnedBySelect - 1):
    currentRow = currentRow + 1
#END date comparison loop for status check

The “curr.rowcount” value I am seeing is 52, i.e., the SELECT is pulling in 52 rows from the Mysql table, and there are something like 250 rows in the table, so the app currently is only looping through about 20% of the rows in the table, then it hits the “None” return from the “curr.fetchone()”, but I want to/need to be able to have the app loop through ALL of the rows in the table :frowning:

FYI, the code above has a temporary workaround in that the SELECT Where also has the:

AND authority_key_identifier like '96aexxx681873'")

condition added. That reduces the number of rows that the SELECT pulls but only for now. If the number or rows that match that authority_key_identifier starts growing (which it will) then eventually the number of retrieved rows will be more than 52 and this’ll stop working.

Ideally, I’d like a way where I can somehow maybe just set the max number of rows that the SELECT can retrieve (e.g., to 1000), preferably doing that in the Python app code, so I don’t have to make some setting change in the Mysql DB which would probably affect more than just this app.


Okay, so you know that there are about 250 rows in the entire table, but there are only 52 rows in the result from the query, and then the loop runs 52 times, once for each row. Correct? So. Why do you believe there is any error here? Did you suppose, for example, that the query should give every row in the table? Why? The query places some conditions on which rows to return (the WHERE clause). So maybe some rows in the table just don’t meet that condition?

Then why does the query say to only look at rows that meet a certain condition?

It sounds like you imagine that there is some artificial limit of 52 rows, and that you are trying to restrict the query so that you stay under the limit. The problem is the opposite: it’s because of the restriction that you already had to start with, that you only got 52 rows. If you want every row, there should not be a WHERE clause. The purpose of a WHERE clause is to have a chance to filter out some rows.

Hi Karl,

You are RIGHT!!!

While I was working with this app earlier, I had initially discovered that the original code had the ‘if’ checking only whether the expiry date was in the past, rather than doing an AND of both the expiry date check and checking if the row had the target authority_key_identifier, so I modified the ‘if’, but between making that change and also adding the additional condition for the WHERE, I got confused :(…

I think that I’ve been staring at this code too long :slight_smile: …!

Thanks for pointing that out!


1 Like