How To Get Autoincrement Values For A Column After Uploading A Pandas Dataframe To A Mysql Database

I have a Pandas DataFrame (called df), which I would like to upload to a MySql database. The dataframe has columns [A, B, C] and the table in the database has columns [ID, A, B, C

Solution 1:

You can assign id by yourself:

import pandas as pd
df['ID'] = pd.read_sql_query('select ifnull(max(id),0)+1 from db_table',cnx).iloc[0,0]+range(len(df))

where cnx is your connection and then upload your df.

Solution 2:

import pandas as pd
df['ID'] = pd.read_sql_query('select MAX(ID)+1 from db_table',cnx).iloc[0,0] + range(len(df))