Hello, good afternoon.
I'm having problems with the VerticaPy function "pandas_to_vertica()". It turns out that before I could create a DataFrame with pandas, from SQL queries to a PostgreSQL database, and with pandas_to_vertica() it let me correctly insert it into an already existing table.
However now I am getting the following error: " FileNotFoundError: [WinError 2] The system cannot find the file specified: 'billing.csv' ". It left me baffled since this problem with direct ingestion of DataFrames had not thrown me before, however now that I want to ingest a much larger DataFrame (5000 records) it throws me this error.
I share my code to see when I use this function and the error it throws me.
From already thank you very much.
Code:
Assembly of the data frame for Argentina
def dataframe_billing_AR(window_size):
ultimo_id_AR = vp.readSQL("select NVL(max(billing_id),0) LAST_BILLING_ID from POCDATA.billing where SERVER='AR'")
query = f"SELECT 'AR' as SERVER, * FROM hca.billing WHERE billing_id BETWEEN {ultimo_id_AR['LAST_BILLING_ID'][0]} AND {ultimo_id_AR['LAST_BILLING_ID'][0]} + {window_size}"
frame_ARG = pd.read_sql(query, connection_db_postgre_AR())
pd.set_option('display.expand_frame_repr', False)
print(frame_ARG)
return frame_ARG
Data ingestion in Vertica
def ingest_frame(frame):
vp.pandas_to_vertica(df = frame, name = "billing", schema = "POCDATA", insert = True)
return
Full function for Argentina
def data_passage_ARG(window_size):
try:
connection_db_postgre_AR()
connection_vertica()
dataframe_billing_AR(window_size)
ingest_frame(dataframe_billing_AR(window_size))
finally:
connection_db_postgre_AR().close()
close_connection()
Implementation:
data_passage_ARG(5000)
(The assembled data frame is shown correct, but the error is in the part of the ingestion function I guess)
Error:
KeyError Traceback (most recent call last)
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\verticapy\utilities.py:1122, in pandas_to_vertica(df, name, schema, dtype, parse_nrows, temp_path, insert)
1120 for c in df.columns:
1121 if df[c].dtype == object and isinstance(
-> 1122 df[c].loc[df[c].first_valid_index()], str
1123 ):
1124 str_cols += [c]
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\indexing.py:967, in _LocationIndexer.getitem(self, key)
966 maybe_callable = com.apply_if_callable(key, self.obj)
--> 967 return self._getitem_axis(maybe_callable, axis=axis)
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\indexing.py:1205, in _LocIndexer._getitem_axis(self, key, axis)
1204 self._validate_key(key, axis)
-> 1205 return self._get_label(key, axis=axis)
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\indexing.py:1153, in _LocIndexer._get_label(self, label, axis)
1151 def _get_label(self, label, axis: int):
1152 # GH#5667 this will fail if the label is not present in the axis.
-> 1153 return self.obj.xs(label, axis=axis)
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\generic.py:3864, in NDFrame.xs(self, key, axis, level, drop_level)
3863 else:
-> 3864 loc = index.get_loc(key)
...
-> 1187 os.remove(path)
1188 if clear:
1189 del tmp_df
FileNotFoundError: [WinError 2] El sistema no puede encontrar el archivo especificado: 'billing.csv'
