databricks
DATEPART Function fails if you use text('YEAR') instead of 'YEAR'
Overview
When I attempt to use a datepart function with text('YEAR'), the compile code for the databricks dialect throws and error (see sample code below as it is trying to treat the text part as a quoted identifier common to databricks `.
Environment Information
OS = Windows 2022 Server Python Version = 3.10.2 Python Package Versions
- sqlalchemy=1.4.48
- databricks-sql-connector=2.5.2
Code Sample
from configparser import ConfigParser
from pathlib import Path
from sqlalchemy import create_engine, Identity
from sqlalchemy.orm import Session
from sqlalchemy.engine import URL
from sqlalchemy.schema import Table, Column, MetaData, CreateTable
from sqlalchemy.sql.sqltypes import BIGINT, VARCHAR, DATE
from sqlalchemy.sql.expression import func, and_, or_, text, distinct, insert, union_all
config_path = Path.home() / '.databrickscfg'
config = ConfigParser()
config.read(config_path)
token = config['DEFAULT']['token']
url_info = {
'drivername': 'databricks',
'username': 'token',
'password': token,
'host': config['DEFAULT']['host'].replace('https://', '').replace('/', ''),
'port': 43,
'database': 'curv'
}
catalog = config['DEFAULT']['catalog']
http_path = "/sql/1.0/warehouses/3c0fbf823204df89"
url = URL.create(
**{key: value for key, value in url_info.items()},
query={'http_path': http_path, 'catalog': catalog}
)
engine = create_engine(url=url,
pool_pre_ping=1)
session = Session(engine)
table_date = Table(
'test_date',
MetaData(),
Column('date_field', DATE, nullable=False)
)
table_date.create(bind=engine)
table_date.insert().values(date_field='2019-02-12')
query = session.query(func.DATEPART(text('YEAR'), table_date.c['date_field']).label('date_field'))
session.execute(query)
I'm sure that it will fail for other datepart calls as well such as text('MONTH'), text('DAY')
I'll review this in detail tomorrow but just wanted to throw out: I love that you're reading .databrickscfg in your repro scripts. We have an open feature request to build this support directly into the connector https://github.com/databricks/databricks-sql-python/issues/148. It's good to see another usage of .databrickscfg in the wild.
