DatabaseError: Write pandas dataframe to vertica using to_sql and vertica_python

Question

I have a simple dataframe like this

df = pd.DataFrame({"A":[1, 2, 3], "B":["a", "b", "c"]})

I would like to write this dataframe to vertica database using to_sql method. So I use vertica_python module and my code is the following

import pandas as pd
import vertica_python

cxn = {"user":'myuser',
       "password":'mypassword',
       "host":'xx.x.x.xx',
       "port":yyyy,
       "database":"mydb"}

engine = vertica_python.connect(**cxn)

df = pd.DataFrame({"A":[1, 2, 3], "B":["a", "b", "c"]})

df.to_sql("df", index=False, if_exists="replace", con=engine, schema="public", dtype={"A":"int", "B":"int"})

Then i got database error which i could not fix it as follows.

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting

May I have your suggestions how to solve this problem? Thank you very much.

You have given type of B column as int convert it to object i.e., as str column — Sumanth
– Sumanth, Commented Jan 28, 2019 at 5:34

rrcal · Accepted Answer · 2019-08-07 02:54:25Z

1

I bumped into a similar problem connecting to Vertica and managed to create a connection via the following:

Using sqlalchemy and specifically sqlalchemy-vertica

As you seem to be using vertica_python, I would recommend installing with:

pip install sqlalchemy-vertica[vertica-python]

This way you can connect the following way:

import sqlalchemy as sa
import vertica_python

engine = sa.create_engine('vertica+vertica_python://user:pwd@host:port/database')

Note that according to this thread, the upload with pandas and sqlalchemy can become a lot faster due to a recent improvement in sqlalchemy by passing the following configuration to create_engine:

engine = create_engine(sqlalchemy_url, fast_executemany=True)

I haven't tried it yet, but it looks promising. More on it on that answer.

EDIT

I tried the fast_executemany flag above and unfortunately it does not work with Vertica.

edited Aug 7, 2019 at 2:54

answered Aug 6, 2019 at 23:10

rrcal

3,7906 gold badges27 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

DatabaseError: Write pandas dataframe to vertica using to_sql and vertica_python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related