无法使用 Python SqlAlchemy 将数据写入 Vertica 数据库 - 键入“TEXT"；不存在 [英] Unable to write data to Vertica Database using Python SqlAlchemy - Type "TEXT" does not exist

问题描述

我正在尝试将 Pandas 数据帧上传到 Vertica 数据库能够使用 sqlalchemy 设置引擎和查询数据库.

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

但是当我尝试从 Pandas 数据帧上传数据时，会收到错误消息，因为文本"类型不存在.我使用的是 Windows 10，并创建了一个 ODBC 连接.

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)

上面的代码 (df.to_sql) 片段出现错误:ProgrammingError: (pyodbc.ProgrammingError) ('42704', '[42704] ERROR 5108: Type "TEXT" does not exist\n(5108) (SQLExecDirectW)')

有人可以帮忙吗，

提前致谢！！

推荐答案

在工作中遇到过类似的事情，并且对字符串对象的列使用 VARCHAR 更改了类型

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

这篇关于无法使用 Python SqlAlchemy 将数据写入 Vertica 数据库 - 键入“TEXT"；不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！