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

Question

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!

but haven't been able to use it successfully Show your attempts nevertheless. — Akina
– Akina, Commented Sep 28, 2021 at 12:33
I tried using the ROW_NUMBER() function in my python script and it didn't work? — AKang123.
– AKang123., Commented Sep 28, 2021 at 12:43
ROW_NUMBER() is MySQL function, not python one, it cannot be applied in python. — Akina
– Akina, Commented Sep 28, 2021 at 12:44

Bill Karwin · Accepted Answer · 2021-09-28 14:12:54Z

2

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

answered Sep 28, 2021 at 14:12

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

Collectives™ on Stack Overflow

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

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