CODEWITHSUNDEEP
pythonsqlalchemysnowflake-cloud-data-platform
bosnjak
bosnjak

Reputation: 8614

Snowflake-sqlalchemy can't inspect column comments

Problem

When inspecting columns on Snowflake tables with SQLAlchemy, the column comments are not visible.

MCVE

Requirements

pip install snowflake-sqlalchemy

Test

import sqlalchemy

# set up the connection
snowflake_conn = '<your-connection-string-here>'
engine = sqlalchemy.create_engine(snowflake_conn)

# create a table for testing, add a column comment
engine.execute('create table public.test ("col1" text);')
engine.execute("""alter table public.test alter "col1" comment 'this is my comment'""")

# check if comment is successfully stored in the information schema
engine.execute("select comment from information_schema.columns where table_name='TEST'").fetchall()
# Output: [('this is my comment',)]


# now let's try to inspect the table
inspector = sqlalchemy.inspect(engine)
inspector.get_columns('TEST', schema='PUBLIC')

Actual result

[{'name': 'col1',
  'type': VARCHAR(length=16777216),
  'nullable': True,
  'default': None,
  'autoincrement': False,
  'primary_key': False}]

Expected result

[{'name': 'col1',
  'type': VARCHAR(length=16777216),
  'nullable': True,
  'default': None,
  'comment': 'this is my comment',  # <-- this is added
  'autoincrement': False,
  'primary_key': False}]

Question

Am I doing something wrong to inspect for column comments, or is this simply a bug in snowflake-sqlalchemy?

Upvotes: 1

Views: 420

Answers (2)

ynux
ynux

Reputation: 1366

I just tested your (well prepared) code with snowflake-sqlalchemy==1.1.18, and the comment is now returned as expected. 

{
   'name' : 'col1',
   'primary_key' : False,
   'default' : 'None',
   'type' : VARCHAR(length=16777216),
   'nullable' : True,
   'autoincrement' : False,
   'comment' : 'this is my comment'
}

Upvotes: 2

Mark Keller
Mark Keller

Reputation: 11

Thank you for posting your finding!

This seems to be a problem in snowflake-sqlalchemy, we have started investigation of the problem, keep an eye on here for updates: https://github.com/snowflakedb/snowflake-sqlalchemy/issues/90

Upvotes: 1

Related Questions