Python: Add primary key to MySQL with dataframe.to_sql

Question

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

MaxU - stand with Ukraine · Accepted Answer · 2016-05-28 07:09:02Z

1

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;

edited May 28, 2016 at 7:09

answered May 27, 2016 at 17:29

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

5 Comments

vindex Over a year ago

I add the command in python cur.execute("alter table KLSE modify ID int auto_increment primary key") and mysql, both return ERROR 1054 (42S22): Unknown column 'ID' in 'KLSE'

MaxU - stand with Ukraine Over a year ago

@vindex, have you already tried: alter table KLSE add id int primary key auto_increment;

vindex Over a year ago

,there is no id column. I try alter table KLSE modify index int auto_increment primary key; it return ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index int auto_increment primary key' at line 1

vindex Over a year ago

It works to add the id at the last column. But if I use python, can I add the command cur.excecute("alter table KLSE add id int primary key auto_increment")? I need to run the script everyday and export teh data to MySQL.Thanks

vindex Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Python: Add primary key to MySQL with dataframe.to_sql

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related