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, Jul 04 '22 at 08:45

score 1 · Accepted Answer · answered Jul 04 '22 at 09:42

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 04 '22 at 09:42

Ergest Basha

7,870
4
8
28

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? – P.Salmon Jul 04 '22 at 10:36
@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 – wildcat89 Jul 04 '22 at 16:44
1

@MattWilson https://stackoverflow.com/questions/48255460/python-insert-into-vs-insert-into-on-duplicate-key-update – Ergest Basha Jul 04 '22 at 17:15

Add dataframe column WITH VARYING VALUES to MySQL table?

1 Answers1