Pandas DataFrame to MySQL/SQLAlchemy table fail with nans

[huggybearikf]

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:[email protected]/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}`
....