Unable To Write Data To Vertica Database Using Python Sqlalchemy - Type "text" Does Not Exist

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 g

Solution 1:

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

defupdateType(df_para):
    dtypedict = {}  # create and empty dictionaryfor i,j inzip(df_para.columns, df_para.dtypes):
        if"object"instr(j):
            dtypedict.update({i: sa.types.VARCHAR})

    return dtypedict

updatedict = updateType(df)  # update the datafraame type

Solution 2:

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 !