Add dataframe column WITH VARYING VALUES to MySQL table?-CodePudding

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

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.

CodePudding user response：

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