Python: Add a column that contains row number to MySQL database-CodePudding

I'm trying to add a column to my MySQL database that contains the row number of the entry. I'm adding the data using a python script, and have been unsuccessful in doing so. I learnt about the ROW_NUMBER() MySQL function but haven't been able to use it successfully. Should I be using this function? Is there a way I can achieve this without retrieving the number of rows in the table first?

Thanks!

CodePudding user response：

Two options:

Do it yourself in Python:

cursor = cnx.cursor(dictionary=True)
result = cursor.execute("SELECT ... FROM MyTable")

rownum = 0
for row in cursor:
    row['rownum'] = rownum
    rownum  = 1
    print(row)

cnx.close()

Or use the MySQL ROW_NUMBER() window function (provided you are using at least MySQL 8.0, because earlier versions don't support the window functions).

cursor = cnx.cursor()
result = cursor.execute("SELECT ROW_NUMBER() OVER() AS rownum, ... FROM MyTable")

for row in cursor:
    print(row)

cnx.close()