Write Pandas Dataframe via SQLAlchemy in MySQL database

Question

I am trying to write a pandas dataframe into a mysql database using pandas. This works perfect for me using

to_sql

But what I want is to write the date, ticker and the adj_close in the table test on my own using sqlalchemy. Somehow I tried doing it but it is not working. using the following:

ins = test.insert()

ins = test.insert().values(date=DataLevels['Date'], ticker=DataLevels['ticker'], adj_close=DataLevels['adj_close'])
ins.compile().params
mysql_engine.execute(ins)

Using it I receive the following error message:

(mysql.connector.errors.ProgrammingError) Failed processing pyformat-parameters; Python 'series' cannot be converted to a MySQL type [SQL: 'INSERT INTO test (date, ticker, adj_close) VALUES (%(date)s, %(ticker)s, %(adj_close)s)'] [parameters: {'ticker': 0
AAPL

Does anbody has a clue how that can work? The code is below wothout the code parts mentioned above:

import sqlalchemy as sqlal
import pandas_datareader.data as pdr
import pandas as pd

mysql_engine = sqlal.create_engine('mysql+mysqlconnector://xxx')
mysql_engine.raw_connection()

metadata = sqlal.MetaData()

test = sqlal.Table('test', metadata,
                                sqlal.Column('date', sqlal.DateTime, nullable=True),
                                sqlal.Column('ticker', sqlal.String(10), sqlal.ForeignKey("product.ticker"), nullable=True), 
                                sqlal.Column('adj_close', sqlal.Float, nullable=True),
                                ) 

metadata.create_all(mysql_engine) 

DataLevels = pdr.DataReader(['ACN','AAPL','^GDAXI'], 'yahoo', '2016-11-19', '2016-12-1')
DataLevels = pd.melt(DataLevels['Adj Close'].reset_index(), id_vars='Date', value_name='adj_close', var_name='minor').rename(columns={'minor': 'ticker'})

Igor Raush · Accepted Answer · 2016-12-16 01:31:53Z

2

To insert multiple rows in a single INSERT, don't pass a Series for each column, pass a list of records.

test.insert().values(DataLevels[['Date', 'ticker', 'adj_close']].to_dict('records'))

edited Dec 16, 2016 at 1:31

answered Dec 16, 2016 at 1:24

Igor Raush

15.3k1 gold badge38 silver badges58 bronze badges

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

4 Comments

MCM Over a year ago

thanks for the help. now I receive the following error message: Cannot add or update a child row: a foreign key constraint fails (financialanalysis.market_price_data, CONSTRAINT market_price_data_ibfk_1` FOREIGN KEY (ticker) REFERENCES product (ticker)) [SQL: 'INSERT INTO`

Igor Raush Over a year ago

This looks like a data integrity issue. The rows you are inserting violate a foreign key constraint in your schema. Without knowing all table relationships, I can't really help more.

MCM Over a year ago

Thanks for the answer. I updated my code above which provides the error message.

MCM Over a year ago

It worked out. Found my problem the data was missing in one table :)

Collectives™ on Stack Overflow

Write Pandas Dataframe via SQLAlchemy in MySQL database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related