NotImplementedError: executemany is implemented for simple INSERT statements only

Question

I try to append my vertica (SQL-type) table through pandas using sqlalchemy

import pandas as pd
import sqlalchemy as sa

Create engine to vertica:

def get_engine(base):
    engine = sa.create_engine("{sys}+{dri}://{user}:" + \
                               "{password}@{host}:{port}/{database}".format(**login[base]))
    return engine
engine = get_engine('vertica')

Just for clarity a simple query:

table = '***'

sql =\
'''
select *
from public.{table}
'''.format(table=table)

connection = engine.connect()
data = pd.read_sql(sql, connection)
connection.close()

Data is not empty:

print(len(data))
569955

And try to write to the same table:

fields = list(data.columns)
connection = engine.connect()
data.to_sql(table, connection, schema='public', index=False, if_exists='append', chunksize=30000,
            dtype={fields[0]:sa.types.Integer,
            fields[1]:sa.types.VARCHAR,
            fields[2]:sa.types.Integer,
            fields[3]:sa.types.Integer,
            fields[4]:sa.types.Integer,
            fields[5]:sa.types.VARCHAR,
            fields[6]:sa.types.VARCHAR,
            fields[7]:sa.types.VARCHAR,
            fields[8]:sa.types.VARCHAR,
            fields[9]:sa.types.VARCHAR,
            fields[10]:sa.types.VARCHAR,
            fields[11]:sa.types.VARCHAR,
            fields[12]:sa.types.DateTime
           })
connection.close()

And get this mistake:

...
\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py in do_executemany(self, cursor, statement, parameters, context)
    465 
    466     def do_executemany(self, cursor, statement, parameters, context=None):
--> 467         cursor.executemany(statement, parameters)
    468 
    469     def do_execute(self, cursor, statement, parameters, context=None):

\Anaconda3\lib\site-packages\vertica_python\vertica\cursor.py in executemany(self, operation, seq_of_parameters)
    153         else:
    154             raise NotImplementedError(
--> 155                 "executemany is implemented for simple INSERT statements only")
    156 
    157     def fetchone(self):

NotImplementedError: executemany is implemented for simple INSERT statements only

Answer 1

I got the same error when I was trying to write my data to vertica using sqlalchemy. For my case the issue was the column names. It seems that it can't write column names that include special characters. I could fix the error by removing all the '_', '%' and white space characters from column names in pandas and then I used df.to_sql() to write it in vertica.