Trying to send DF to Vertica

[rm17tink]

engine = sqlalchemy.create_engine('vertica+vertica_python://username:password@localhost:3306/db_name')
connection  = engine.raw_connection()

df.to_sql("df", if_exists="replace", con=connection, schema="schema_name")

---

ValueError Traceback (most recent call last)
File ~\AppData\Roaming\Python\Python311\site-packages\pandas\io\sql.py:2675, in SQLiteDatabase.execute(self, sql, params)
2674 try:
-> 2675 cur.execute(sql, *args)
2676 return cur

File ~\AppData\Roaming\Python\Python311\site-packages\vertica_python\vertica\cursor.py:188, in Cursor.handle_ctrl_c..wrap(self, *args, **kwargs)
187 try:
--> 188 return func(self, *args, **kwargs)
189 except KeyboardInterrupt:

File ~\AppData\Roaming\Python\Python311\site-packages\vertica_python\vertica\cursor.py:265, in Cursor.execute(self, operation, parameters, use_prepared_statements, copy_stdin, buffer_size)
264 if parameters:
--> 265 operation = self.format_operation_with_parameters(operation, parameters)
266 self._execute_simple_query(operation)

File ~\AppData\Roaming\Python\Python311\site-packages\vertica_python\vertica\cursor.py:739, in Cursor.format_operation_with_parameters(self, operation, parameters, is_copy_data)
738 if parameters and '%s' not in operation:
--> 739 raise ValueError(f'Invalid SQL: {operation}'
740 "\nHINT: When argument 'parameters' is a tuple/list, "
741 'variables in SQL should be specified with positional format (%s) placeholders. '
742 'Question mark (?) placeholders have to be used with use_prepared_statements=True setting.')
743 tlist = []

ValueError: Invalid SQL:
SELECT
name
FROM
sqlite_master
WHERE
type IN ('table', 'view')
AND name=?;

HINT: When argument 'parameters' is a tuple/list, variables in SQL should be specified with positional format (%s) placeholders. Question mark (?) placeholders have to be used with use_prepared_statements=True setting.

The above exception was the direct cause of the following exception:

DatabaseError Traceback (most recent call last)
Cell In[56], line 1
----> 1 df.to_sql("df", if_exists="replace", con=connection, schema="rtinkler")

File ~\AppData\Roaming\Python\Python311\site-packages\pandas\util_decorators.py:333, in deprecate_nonkeyword_arguments..decorate..wrapper(*args, **kwargs)
327 if len(args) > num_allow_args:
328 warnings.warn(
329 msg.format(arguments=_format_argument_list(allow_args)),
330 FutureWarning,
331 stacklevel=find_stack_level(),
332 )
--> 333 return func(*args, **kwargs)

File ~\AppData\Roaming\Python\Python311\site-packages\pandas\core\generic.py:3081, in NDFrame.to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
2886 """
2887 Write records stored in a DataFrame to a SQL database.
2888
(...)
3077 [(1,), (None,), (2,)]
3078 """ # noqa: E501
3079 from pandas.io import sql
-> 3081 return sql.to_sql(
3082 self,
3083 name,
3084 con,
3085 schema=schema,
3086 if_exists=if_exists,
3087 index=index,
3088 index_label=index_label,
3089 chunksize=chunksize,
3090 dtype=dtype,
3091 method=method,
3092 )

File ~\AppData\Roaming\Python\Python311\site-packages\pandas\io\sql.py:842, in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
837 raise NotImplementedError(
838 "'frame' argument should be either a Series or a DataFrame"
839 )
841 with pandasSQL_builder(con, schema=schema, need_transaction=True) as pandas_sql:
--> 842 return pandas_sql.to_sql(
843 frame,
844 name,
845 if_exists=if_exists,
846 index=index,
847 index_label=index_label,
848 schema=schema,
849 chunksize=chunksize,
850 dtype=dtype,
851 method=method,
852 engine=engine,
853 **engine_kwargs,
854 )

File ~\AppData\Roaming\Python\Python311\site-packages\pandas\io\sql.py:2851, in SQLiteDatabase.to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
2840 raise ValueError(f"{col} ({my_type}) not a string")
2842 table = SQLiteTable(
2843 name,
2844 self,
(...)
2849 dtype=dtype,
2850 )
-> 2851 table.create()
2852 return table.insert(chunksize, method)

File ~\AppData\Roaming\Python\Python311\site-packages\pandas\io\sql.py:984, in SQLTable.create(self)
983 def create(self) -> None:
--> 984 if self.exists():
985 if self.if_exists == "fail":
986 raise ValueError(f"Table '{self.name}' already exists.")

File ~\AppData\Roaming\Python\Python311\site-packages\pandas\io\sql.py:970, in SQLTable.exists(self)
969 def exists(self):
--> 970 return self.pd_sql.has_table(self.name, self.schema)

File ~\AppData\Roaming\Python\Python311\site-packages\pandas\io\sql.py:2866, in SQLiteDatabase.has_table(self, name, schema)
2855 wld = "?"
2856 query = f"""
2857 SELECT
2858 name
(...)
2863 AND name={wld};
2864 """
-> 2866 return len(self.execute(query, [name]).fetchall()) > 0

File ~\AppData\Roaming\Python\Python311\site-packages\pandas\io\sql.py:2687, in SQLiteDatabase.execute(self, sql, params)
2684 raise ex from inner_exc
2686 ex = DatabaseError(f"Execution failed on sql '{sql}': {exc}")
-> 2687 raise ex from exc

DatabaseError: Execution failed on sql '
SELECT
name
FROM
sqlite_master
WHERE
type IN ('table', 'view')
AND name=?;
': Invalid SQL:
SELECT
name
FROM
sqlite_master
WHERE
type IN ('table', 'view')
AND name=?;

HINT: When argument 'parameters' is a tuple/list, variables in SQL should be specified with positional format (%s) placeholders. Question mark (?) placeholders have to be used with use_prepared_statements=True setting

[sitingren]

I'm not familiar with sqlalchemy, one thing you can try is modifying your connection string to

vertica+vertica_python://username:password@localhost:3306/db_name?use_prepared_statements=True