1

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')
vindex
  • 331
  • 6
  • 17

1 Answers1

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;
MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419
  • 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' – vindex May 28 '16 at 06:28
  • @vindex, have you already tried: `alter table KLSE add id int primary key auto_increment;` – MaxU - stand with Ukraine May 28 '16 at 07:34
  • ,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 May 28 '16 at 07:35
  • 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 May 28 '16 at 07:40
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/113203/discussion-between-vindex-and-maxu). – vindex May 28 '16 at 07:42