Issue:
When I attempt to create a sqlalchemy table with an indentity seed the compiled code doesn't have the required information needed to setup an identity column in the database.
Sample Code:
assume that you already have databricks configured for command line usage ("databricks config")
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
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']['token'].replace('http://', '').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)
session = Session(engine)
table = Table(
'test',
MetaData(),
Column('id', BIGINT, primary_key=True, nullable=False, server_default=Identity(start=1)),
Column('comments', VARCHAR(255), nullable=True)
)
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
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']['token'].replace('http://', '').replace('/', ''),
'port': 43,
'database': '<database_name>'
}
catalog = config['DEFAULT']['catalog']
http_path = "<path_to_cluster>"
url = URL.create(
**{key: value for key, value in url_info.items()},
query={'http_path': http_path, 'catalog': catalog}
)
engine = create_engine(url=url)
session = Session(engine)
table = Table(
'test',
MetaData(),
Column('id', BIGINT, primary_key=True, nullable=False, server_default=Identity(start=1)),
Column('comments', VARCHAR(255), nullable=True)
)
# with needed column information
print(CreateTable(table))
# without needed column information
print(CreateTable(table).compile(bind=engine))what printed CreateTable looks like (correct)
# with needed column information
print(CreateTable(table))
# output
CREATE TABLE test (
id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
comments VARCHAR(255),
PRIMARY KEY (id)
)
what printed compiled CreateTable looks like (incorrect)
# without needed column information
print(CreateTable(table).compile(bind=engine))
CREATE TABLE test (
id BIGINT NOT NULL,
comments STRING,
PRIMARY KEY (id)
) USING DELTA
Machine Info
OS: Window Server 2022 VM
Software Info:
Python - 3.10.2
Python Packages:
databricks-sql-connector : 2.5.2
sqlalchemy : 1.4.48
Issue:
When I attempt to create a sqlalchemy table with an indentity seed the compiled code doesn't have the required information needed to setup an identity column in the database.
Sample Code:
assume that you already have databricks configured for command line usage ("databricks config")
what printed CreateTable looks like (correct)
what printed compiled CreateTable looks like (incorrect)
Machine Info
OS: Window Server 2022 VM
Software Info:
Python - 3.10.2
Python Packages:
databricks-sql-connector : 2.5.2
sqlalchemy : 1.4.48