[SOLVED] Python: Add primary key to MySQL with dataframe.to

Python: Add primary key to MySQL with dataframe.to_sql

My current python code is working when I export the dataframe to MySQL by db4free.net. However, now I need to use another MySQL that is provided by backand, I have seen the database table, 'KLSE' that I created has a primary key problem(honestly, I don't understand how primary key can help me). I checked on the documents for dataframe.to_sql, I notice that even index=True does not mean a primary key. I want to know what is the workaround that I can use to add a primary key, to a currently exists. FYI, I did a dataframe.read_sql from backand database, I manage to query the data (although in the backand it shows error as no primary key, thus I unable to create a REST API). Previous code that work for db4free.net MySQL but primary key error for backand MySQL

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqldb://abc:abc@abc')

df_2.to_sql(name='KLSE', con=engine, if_exists='replace')

I try to use MySQLdb library and add primary key,but the KLSE table already exists, thus unable to create.

import pandas as pd
import MySQLdb as mdb

engine = mdb.connect(host='abc',user='abc',passwd='pw',db='abc$default')
cur = engine.cursor()
cur.execute("INSERT INTO KLSE(Id INT PRIMARY KEY AUTO_INCREMENT, \Name VARCHAR(25))")
df_2.to_sql(name='KLSE', con=engine, if_exists='replace')

Solution

You need to add a primary key just once. You can do it this way:

if your want to add a new column as a PK (Primary Key):

alter table KLSE add id int primary key auto_increment;

if your table already has a column which you are going to use as a PK (Primary Key):

alter table KLSE modify <existing_column_name> int auto_increment primary key;