CODEWITHSUNDEEP
pythonmysqlpandas
wildcat89
wildcat89

Reputation: 1285

Add dataframe column WITH VARYING VALUES to MySQL table?

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: mysqltable

Pandas DataFrame: pandasdataframe

Desired MySQL table: desired

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

Desired MySQL output: newDesired

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

Upvotes: 0

Views: 167

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 8973

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

Upvotes: 1

Related Questions