pandas-dev
BUG: to_sql will not create lowercase tables in SAP HANA
Pandas version checks
-
[X] I have checked that this issue has not already been reported.
-
[X] I have confirmed this bug exists on the latest version of pandas.
-
[ ] I have confirmed this bug exists on the main branch of pandas.
Reproducible Example
import pandas as pd
import sqlalchemy
df = pd.DataFrame({
'id': [1, 2],
'name': ['foo', 'bar'],
})
connection_string = "hana://%s:%s@%s:%s/?encrypt=true&sslvalidatecertificate=false" % (hdb_user, hdb_password, hdb_host, hdb_port)
hdb_connection = create_engine(connection_string).connect()
df.to_sql(name='test_table', con=hdb_connection, index=False, if_exists='replace')
Issue Description
The table created on the SAP HANA side will be named "TEST_TABLE".
The reason might be, that SQLAlchemy does per default not use quotation marks for the queries, if not explicitly stated. See here for the documentation:
Names which contain no upper case characters will be treated as case insensitive names, and will not be quoted unless they are a reserved word or contain special characters. A name with any number of upper case characters is considered to be case sensitive, and will be sent as quoted.
To enable unconditional quoting for the table name, specify the flag quote=True to the constructor, or use the quoted_name construct to specify the name.
In SAP HANA, if no quotes are used for the table name in the query, the table name will be interpreted as uppercase per default.
Expected Behavior
The table created on the SAP HANA side should be named "test_table".
My suggestion would be to include SQLAlchemy's parameter in to_sql as well.
To enable unconditional quoting for the table name, specify the flag quote=True to the constructor, or use the quoted_name construct to specify the name.
Installed Versions
pandas : 1.3.5 numpy : 1.20.1 pytz : 2021.3 dateutil : 2.8.1 pip : 21.2.2 setuptools : 58.0.4 Cython : 0.29.25 pytest : 6.2.4 hypothesis : None sphinx : 4.2.0 blosc : None feather : None xlsxwriter : 3.0.2 lxml.etree : 4.7.1 html5lib : 1.1 pymysql : None psycopg2 : None jinja2 : 2.11.3 IPython : 7.22.0 pandas_datareader: None bs4 : 4.10.0 bottleneck : 1.3.2 fsspec : 2022.01.0 fastparquet : None gcsfs : None matplotlib : 3.5.0 numexpr : 2.8.1 odfpy : None openpyxl : 3.0.9 pandas_gbq : None pyarrow : None pyxlsb : None s3fs : None scipy : 1.7.3 sqlalchemy : 1.4.27 tables : 3.6.1 tabulate : None xarray : None xlrd : 2.0.1 xlwt : 1.3.0 numba : 0.54.1 None
Could you confirm this on pandas 1.4rc?
1.2.4 is not the newest pandas
Updated it to latest version 1.3.5, that's the latest i found. Behavior is still as described.
The bug also exists in Postgres.
So after performing a to_sql with upper case names they are imposible to select without ""
