CODEWITHSUNDEEP
pythonmysqlpandasdataframe
CandleWax
CandleWax

Reputation: 2219

How to append new column to MySQL with pandas?

It's my understanding that you could pull data from a database (in this case MySQL), load this data into a pandas df to manipulate it (add new columns, complex calculations not easily done in SQL, etc) then load the new columns back into MySQL. However, I am having trouble with the final step of adding the newly created columns back into the same existing table in MySQL where I pulled the original data. The only similar question I found is here, but I am missing a key component somewhere.

I am using pandas to_sql to perform this task (is there a better option?). My code is pulling crypto data from my MySQL table, loading this into a pandas df and I am adding a new volatility column (I know you could calculate this in SQL, this is just an example).

SQL table (crypto) 

    date        open_price  close_price   high_price    low_price       market_cap       volume
1   2018-03-11  8852.78     9578.63       9711.89       8607.12      149,716,000,000    6,296,370,000
2   2018-03-10  9350.59     8866.00       9531.32       8828.47      158,119,000,000    5,386,320,000
3   2018-03-09  9414.69     9337.55       9466.35       8513.03      159,185,000,000    8,704,190,000
4   2018-03-08  9951.44     9395.01       10147.40      9335.87      168,241,000,000    7,186,090,000

pandas df

    date        open_price  close_price   high_price    low_price       market_cap       volume        Volatility
1   2018-03-11  8852.78     9578.63       9711.89       8607.12      149,716,000,000    6,296,370,000  .2
2   2018-03-10  9350.59     8866.00       9531.32       8828.47      158,119,000,000    5,386,320,000  .58
3   2018-03-09  9414.69     9337.55       9466.35       8513.03      159,185,000,000    8,704,190,000  .56
4   2018-03-08  9951.44     9395.01       10147.40      9335.87      168,241,000,000    7,186,090,000  .12

I first went into MySQL and added a new column using:

ALTER TABLE `crypto`
    ADD COLUMN Volatility float NOT NULL;

The code below is what I used to first push data to MySQL. I changed if_exists to append but I am receiving an error message

df.to_sql(name='crypto', con=engine, if_exists = 'append', chunksize=10000)

DatabaseError: (mysql.connector.errors.DatabaseError) 1364 (HY000): Field 'Volitility_90' doesn't have a default value

How can I append just 1 new column to my MySQL table? Do I need to use another package and use cursor.execute(). Any help with this code, or with some online tutorials would be helpful! I just can't seem to find a lot of content on this specific example, which has me second guessing this whole data manipulation strategy altogether.

Upvotes: 1

Views: 1653

Answers (1)

Omri Segal
Omri Segal

Reputation: 397

Since you already have rows in your table, SQL doesn't know what values to fill in the new column for the existing rows.

You need to state the default value when creating the new column.

See here how to do that: Adding a new SQL column with a default value

Upvotes: 2

Related Questions