Unable to write data to Vertica Database using Python SqlAlchemy - Type "TEXT" does not exist

Question

I am trying to upload pandas dataframe into Vertica Database was able to setup the engine and query database using sqlalchemy.

But when I try to upload data from pandas dataframe get error message as Type "TEXT" does not exist. I am using windows 10, and created an ODBC connection.

import sqlalchemy as sa
engine = sa.create_engine('vertica+pyodbc:///?odbc_connect=%s' %(urllib.parse.quote('DSN=TESTDB'),))
sql_query = "select * from sample_table"
df = pd.read_sql_query(sql_query, con=engine) # this works, get the data as required in the dataframe
*df.apply[Do various data transformations as required]*

# Write back to the database
df.to_sql(name='sample_table_cleaned', con = engine, schema = "Dev" , if_exists = 'append', index = True)

the above code (df.to_sql) snippet comes up with an error as : ProgrammingError: (pyodbc.ProgrammingError) ('42704', '[42704] ERROR 5108: Type "TEXT" does not exist\n (5108) (SQLExecDirectW)')

Can Anyone help on this,

Thanks in Advance !!

rrcal · Accepted Answer · 2019-08-07 01:05:49Z

2

Have faced similar thing at work, and have changed types using VARCHAR for the columns which are of string object

def updateType(df_para):
    dtypedict = {}  # create and empty dictionary
    for i,j in zip(df_para.columns, df_para.dtypes):
        if "object" in str(j):
            dtypedict.update({i: sa.types.VARCHAR})

    return dtypedict

updatedict = updateType(df)  # update the datafraame type

edited Aug 7, 2019 at 1:05

rrcal

3,7906 gold badges27 silver badges35 bronze badges

answered Aug 13, 2018 at 13:20

sidu

364 bronze badges

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

1 Comment

Adept Over a year ago

Thanks for answering, but what do you do with your updatedict then ? Trying to change dtypes with df= df.astype(updatedict) will raise an error dtype '<class 'sqlalchemy.sql.sqltypes.VARCHAR'>' not understood

Victor Criclivii · Accepted Answer · 2021-03-23 14:30:53Z

1

Nice solution @calestini !

import sqlalchemy as sa 

engine = create_engine('vertica+vertica_python://user:{}@host:5433/db_name')

df.to_sql(name='table_name', schema = 'schema_name', con = engine, if_exists='append', dtype = updatedict, index = False)

This is how the datatype dictionary looks after running your function:

{'custom_index': sqlalchemy.sql.sqltypes.VARCHAR,
 'lastmodifiedbysales': sqlalchemy.sql.sqltypes.VARCHAR,
 'linked_distributor': sqlalchemy.sql.sqltypes.VARCHAR,
 'linked_reseller': sqlalchemy.sql.sqltypes.VARCHAR,
 'number_of_licenses': sqlalchemy.sql.sqltypes.VARCHAR}

Thanks !

answered Mar 23, 2021 at 14:30

Victor Criclivii

413 bronze badges

Collectives™ on Stack Overflow

Unable to write data to Vertica Database using Python SqlAlchemy - Type "TEXT" does not exist

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related