Search code examples
verticavertica-python

How to delete temp table created by VerticaPy - error the table "v_temp_schema"."train" already exists

I want to read a csv file using VerticaPy - https://www.vertica.com/python/documentation_last/utilities/read_csv/

I am running Vertica using a Docker image on my laptop.

The Jupyter code is

import sys
!{sys.executable} -m pip install vertica-python
!{sys.executable} -m pip install verticapy

from verticapy import *
conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'dbadmin',
             'password': '',
             'database': 'kaggle_titanic'}
train = read_csv("train.csv")
train

The code runs fine the first time but when I run it again, I get error

NameError                                 Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_8344/783506563.py in <module>
     23 #    cur.execute("drop table kaggle_titanic.v_temp_schema.train")
     24 
---> 25 train = read_csv("train.csv")
     26 train
     27 #iris2

~\Anaconda3\lib\site-packages\verticapy\utilities.py in read_csv(path, cursor, schema, table_name, sep, header, header_names, dtype, na_rep, quotechar, escape, genSQL, parse_n_lines, insert, temporary_table, temporary_local_table, ingest_local)
    918     result = cursor.fetchall()
    919     if (result != []) and not (insert):
--> 920         raise NameError(
    921             'The table "{}"."{}" already exists !'.format(schema, table_name)
    922         )

NameError: The table "v_temp_schema"."train" already exists !

Looks like VerticaPy creates a temp table in the first run. How do I delete it?

I tried adding this code

with vertica_python.connect(**conn_info) as conn:
    cur = conn.cursor()
    cur.execute("drop table kaggle_titanic.v_temp_schema.train")

But got error

MissingSchema: Severity: ROLLBACK, Message: Schema "v_temp_schema" does not exist, Sqlstate: 3F000, Routine: RangeVarGetObjid, File: /data/jenkins/workspace/RE-ReleaseBuilds/RE-Jackhammer_3/server/vertica/Catalog/Namespace.cpp, Line: 281, Error Code: 4650, SQL: 'drop table kaggle_titanic.v_temp_schema.train'

I had to stop/start the database (from docker image) to run the code but clearly that isn't the right way.

How do I delete v_temp_schema.train?

Solution

  • I answered to this request on Github: https://github.com/vertica/VerticaPy/issues/285