7

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
Ivan Savin
  • 151
  • 8
  • What database, what DB-API driver? In other words what are `login['vertica']['sys']` and `login['vertica']['dri']`? Not familiar with vertica, so would like them to be explicitly mentioned. Possibly `vertica+pyodbc`? – Ilja Everilä May 18 '17 at 09:04
  • Hello. Using **sqlalchemy-vertica-python (0.1.3)**: `login = {'vertica': {"sys":"vertica", "dri":"vertica_python", "database":"***", "user":"***", "password":"***", "host":"***", "port":"5433"}, }` – Ivan Savin May 18 '17 at 09:10
  • It seems that *sqlalchemy-vertica-python* provides the dialect and uses [*vertica-python*](https://github.com/uber/vertica-python) as the DB-API. The latter has had [`Cursor.executemany()` added about a month ago](https://github.com/uber/vertica-python/commit/e51e6fb993bca9a9d53d99729435df0a6de92166), so perhaps you just need to upgrade. Pandas support is explicitly mentiond in the (closed) [issue about the missing method](https://github.com/uber/vertica-python/issues/79). – Ilja Everilä May 18 '17 at 09:21
  • The problem is not with SQLAlchemy. sqlalchemy-vertica-python is a 3rd party addon and vertica-python a separate DB-API library. You need to upgrade **vertica-python**. – Ilja Everilä May 18 '17 at 10:36
  • Yes, the problem is somewhere here. I have get new error and change the description. – Ivan Savin May 18 '17 at 11:27
  • @IvanSavin did you figure out what version `vertica_python` works? – lv10 Sep 14 '18 at 16:24

1 Answers1

4

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.

VahidB
  • 145
  • 2
  • 8