0

Simple, i just want to create lowercase tables with pandas.to_sql. Tried the following:

df.to_sql(name=hdb_table_name.lower(), schema="SCHEMA_NAME", con=hdb_connection, index=True, if_exists='replace')

Only when hdb_table_name includes whitespaces, it actually creates lowercase (or case sensitive) tables in HANA. But this does not sound like a sufficient workaround to have a whole database based on whitespace tables names.

Why not capital letters? Unfortunately the above code will throw the following error if the table already exists and has an uppercase name:

Could not reflect: requested table(s) not available in Engine(HANA_ADDRESS) schema 'SCHEMA_NAME': (TABLE_NAME_UPPERCASE)

Found related questions here and here, but not on this specific behavior.

sony
  • 35
  • 5

2 Answers2

0

This might be helpful:

in SAP Hana

  • At the time of table creation, the case of a column name is not preserved unless the column name is quoted. Snowflake stores unquoted column names in uppercase.

  • The column names in a select statement are not case sensitive unless quoted.

  • Equal (=) and Not Equal (!=) in a where clause are case sensitive.

1

eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • This behavior is exactly the problem. I assume that the df.to_sql(...) function does not put quotation marks around the name parameter in the query sent to the hanaDB. The only way i found was to put a space in the name, but as mentioned, i don't find this a long-term solution. – sony Jan 19 '22 at 16:46
  • @sony give an example of what you have in database and what df_tosql gives you and what you need.and how do you run that query , if you are using sqlalchemy ( which is what pandas use) we might find a solution – eshirvana Jan 20 '22 at 02:35
  • 1
    i created an issue on the pandas repository, as i think i might found the underlying issue. There is also a more detailed explanation. You can check it out, maybe you'll find a fix :). https://github.com/pandas-dev/pandas/issues/45474 – sony Jan 20 '22 at 10:00
  • @sony what happens if you pass tablename when you call to_sql like this : `df.to_sql(name='"tablename"' ,...` . send table name with quotation – eshirvana Jan 20 '22 at 14:32
  • this creates a table named "tablename", so including the quotation marks – sony Jan 20 '22 at 15:52
-1

You could try using the Python Machine Learning Client for SAP HANA (available on PyPI). It offers the HANA Dataframe, which behaves similar to a Pandas Dataframe, but pushes operations to the database instead of keeping the data on the client.

For simply creating a table on HANA from a given Pandas Dataframe, you can use function create_dataframe_from_pandas().

I haven't tested it with your specific example, but it's worth a try checking if it treats the double quotes more consequently.

Mathias Kemeter
  • 933
  • 2
  • 11