Hi Karl,
Here’s most of the code:
#Open MySQL Connection
conn = MySQLdb.connect(host="pod01.xxx.com", 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 = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
# Create an inner cursor to perform db modifications
innercurr = conn.cursor()
print(sys.argv)
# 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")
.
.
.
print(json_data)
url = "https://oas01.xxx.com:8888/api/v1/cfssl/revoke"
r = requests.post(url, headers={'Content-Type':'application/json'}, data=json_data, verify=False)
json_data = json.loads(json.dumps(r.json()))
print(json_data)
if (json_data["success"] == False):
print("Error contacting the API. Revoke request failed. Aborting script.")
curr.close()
innercurr.close()
conn.close()
exit()
# END IF
# Fetch next row otherwise infinite loop
row = curr.fetchone()
print("CONTINUE NEXT ROW=", row)
print("currentRow=", currentRow)
if (currentRow >= rowsReturnedBySelect - 1):
print("FINISHED PROCESSING ALL ROWS IN LOOP - NO ROWS WERE CHANGED", rowsReturnedBySelect)
curr.close()
innercurr.close()
conn.close()
exit()
currentRow = currentRow + 1
continue
#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 …
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.
Thanks,
Jim