Add dataframe column WITH VARYING VALUES to MySQL table?

Question

Pretty simple question, but not sure if it’s possible from what I’ve seen so far online.

To keep it simple, let’s say I have a MySQL table with 1 column and 5 rows made already. If I have a pandas dataframe with 1 column and 5 rows, how can I add that dataframe column (with its values) to the database table?

The guides I’ve read so far only show you how to simply create a new column with either null values or 1 constant value, which doesn’t help much. The same question was asked here but the answer provided didn’t answer the question, so I’m asking it again here.

As an example:

MySQL table:

Pandas DataFrame:

Desired MySQL table:

Then for kicks, let's say we have a string column to add as well:

Desired MySQL output:

Safe to assume the index column will always match in the DF and the MySQL table.

You want to insert the values in the existing column ? Why just not insert the values ? Are the data type the same ? Can you edit the question and add a practical example — Ergest Basha
– Ergest Basha, Commented Jul 4, 2022 at 8:45

Ergest Basha · Accepted Answer · 2022-07-04 09:42:28Z

1

You can use INSERT ... ON DUPLICATE KEY UPDATE.

You have the following table:

create table tbl (
index_ int ,
col_1 int ,
primary key index_(`index_`)
) ;

insert into tbl values  (1,1), (2,2), (3,3), (4,4), (5,5);

And want to add the following data in a new column on the same table ;

(1,0.1),(2,0.2),(3,0.3),(4,0.4),(5,0.5)

First you need to add the column with the alter command,

alter table tbl add column col_2 decimal(5,2) ;

Then use INSERT ON DUPLICATE KEY UPDATE Statement

INSERT INTO tbl (index_,col_2)
VALUES 
(1,0.1),
(2,0.2),
(3,0.3),
(4,0.4),
(5,0.5)
ON DUPLICATE KEY UPDATE col_2=VALUES(col_2);

Fiddle

answered Jul 4, 2022 at 9:42

Ergest Basha

9,3064 gold badges12 silver badges31 bronze badges

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

3 Comments

P.Salmon Over a year ago

I think the core problem is OP does not know how to ALTER table add columns in pandas - neither do I, do you know of a way?

wildcat89 Over a year ago

@ErgestBasha well, I should reiterate, it’s CLOSE. The query will work, but how can I iterate through the pandas column to insert the values into the table? These columns I made are small, but we have to assume the size of the columns is too large to manually define like this

Ergest Basha Over a year ago

@MattWilson stackoverflow.com/questions/48255460/…

Collectives™ on Stack Overflow

Add dataframe column WITH VARYING VALUES to MySQL table?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related