Repository navigation
Pandas DataFrame to MySQL/SQLAlchemy table fail with nans #611
Description
I get the error Invalid column name 'nan' when passing a DataFrme with nans to a SQLAlchemy table that allows nans. I've tried passing a na_values with a sqlalchemy null but it doesn't seem to be recognizing the replace.
From the error it seems like it is passing literally nan into the SQL INSERT statements.
Code as below:
# Upload to Server
import sqlalchemy as sa
from odo import odo
tmps = pd.read_csv(EPS_FILE)
tmps['DT'] = pd.to_datetime(fdata['Period (YYYYMMDD)'], format='%Y%m%d')
tmps['Ticker'] = fdata['Ticker'].str.split('-').str[0]
tmps['YYYY'] = tmps['DT'].dt.year
tmps['MM'] = tmps['DT'].dt.month
tmps['DD'] = tmps['DT'].dt.month
rnamecol = {
'NI-LYEAR': 'LYEAR_MED',
'NI-LYEAR_MEAN': 'LYEAR_MEAN',
'NI-CYEAR': 'CYEAR_MED',
'NI-CYEAR_MEAN': 'CYEAR_MEAN',
'NI-NYEAR': 'NYEAR_MED',
'NI-NYEAR_MEAN': 'NYEAR_MEAN',
'NI-Current_Annual_Stated': 'CURR_ANN',
'NI-Current_LTM_Sem': 'CURR_LTMSA',
'NI-Current_LTM_Q': 'CURR_LTMQ',
'NI-Revise Up': 'ReviseUp',
'NI-Revise Down': 'ReviseDown',
'NI-NEST': 'NEST'
}
tmps.rename(columns=rnamecol, inplace=True)
md = sa.MetaData(bind='mssql+pymssql://w:1234@192.168.0.242/UserDB')
tbl = sa.Table('tmp_FS_NetIncome', md,
sa.Column('YYYY', sa.Integer, nullable=False),
sa.Column('MM', sa.Integer, nullable=False),
sa.Column('DD', sa.Integer, nullable=False),
sa.Column('DT', sa.DATE, nullable=False),
sa.Column('Ticker', sa.String(6), nullable=False),
sa.Column('CURR_ANN', sa.Float),
sa.Column('CURR_LTMSA', sa.Float),
sa.Column('CURR_LTMQ', sa.Float),
sa.Column('LYEAR_MED', sa.Float),
sa.Column('LYEAR_MEAN', sa.Float),
sa.Column('CYEAR_MED', sa.Float),
sa.Column('CYEAR_MEAN', sa.Float),
sa.Column('NYEAR_MED', sa.Float),
sa.Column('NYEAR_MEAN', sa.Float),
sa.Column('ReviseUp', sa.Integer),
sa.Column('ReviseDown', sa.Integer),
sa.Column('NEST', sa.Integer)
)
tmps = tmps.loc[:, [l.name for l in tbl.columns]]
notnum = set([
'Ticker', 'DT'
])
tmps['Ticker'] = tmps['Ticker'].astype('str')
tmps['DT'] = pd.to_datetime(tmps['DT'])
for cc in tmps.columns:
if not cc in notnum:
tmps[cc] = pd.to_numeric(tmps[cc])
t = odo(
tmps, tbl, na_values='NULL' # Also tried na_values=sa.null() or na_values=''
)
And error (truncated) below:
`Check messages from the SQL Server\n")
[SQL: 'INSERT INTO [tmp_FS_NetIncome] ([YYYY], [MM], [DD], [DT], [Ticker], [CURR_ANN], [CURR_LTMSA], [CURR_LTMQ], [LYEAR_MED], [LYEAR_MEAN], [CYEAR_MED], [CYEAR_MEAN], [NYEAR_MED], [NYEAR_MEAN], [ReviseUp], [ReviseDown], [NEST]) VALUES (%(YYYY)s, %(MM)s, %(DD)s, %(DT)s, %(Ticker)s, %(CURR_ANN)s, %(CURR_LTMSA)s, %(CURR_LTMQ)s, %(LYEAR_MED)s, %(LYEAR_MEAN)s, %(CYEAR_MED)s, %(CYEAR_MEAN)s, %(NYEAR_MED)s, %(NYEAR_MEAN)s, %(ReviseUp)s, %(ReviseDown)s, %(NEST)s)']
[parameters: ({
'MM': 12, 'Ticker': '300671', 'DD': 12, 'CURR_ANN': nan, 'LYEAR_MED': nan, 'ReviseDown': nan, 'NYEAR_MED': nan, 'NEST': nan, 'YYYY': 2002, 'LYEAR_MEAN': nan, 'ReviseUp': nan, 'CURR_LTMSA': nan, 'NYEAR_MEAN': nan, 'CYEAR_MED': nan, 'DT': datetime.datetime(2002, 12, 31, 0, 0), 'CURR_LTMQ': nan, 'CYEAR_MEAN': nan}, {'MM': 12, 'Ticker': '300532', 'DD': 12, 'CURR_ANN': nan, 'LYEAR_MED': nan, 'ReviseDown': nan, 'NYEAR_MED': nan, 'NEST': nan, 'YYYY': 2002, 'LYEAR_MEAN': nan, 'ReviseUp': nan, 'CURR_LTMSA': nan, 'NYEAR_MEAN': nan, 'CYEAR_MED': nan, 'DT': datetime.datetime(2002, 12, 31, 0, 0), 'CURR_LTMQ': nan, 'CYEAR_MEAN': nan}`
....
Activity