Support for Column Comments in SQLServer Table Structure Output in get_table_info()

[4mophy]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

"""
The sample code employs the chain portion of the Langchain use case[Q&A over SQL + CSV](https://python.langchain.com/docs/use_cases/sql/quickstart/), with primary modifications focused on the connection and question sections, while remaining consistent with the guidance provided by the Langchain codebase for all other aspects.
"""
from langchain_community.utilities.sql_database import SQLDatabase
from dotenv import load_dotenv
import os

load_dotenv()

username = os.environ["DB_USERNAME"]
password = os.environ["DB_PASSWORD"]
host = os.environ["DB_HOST"]
database = os.environ["DB_DATABASE"]
db = SQLDatabase.from_uri(
    f"mssql+pyodbc://{username}:{password}@{host}/{database}?driver=ODBC+Driver+17+for+SQL+Server",
    {
        "supports_comments": True,
    },
    sample_rows_in_table_info=3,
)

Description

While working with a Langchain use case, I encountered an issue where the console output of the SQL Server table structure did not include column comments. Seeking assistance from a relevant technical community, I learned that MySQL indeed supports displaying such comments in its table structure information. Upon examining the source code of the SQLDatabase component and consulting SqlAlchemy, I discovered a method to retrieve comment details for SQL Server columns.

Consequently, I resorted to creating a custom agent that enables the large language model to comprehend the meaning of fields—particularly those with non-standard naming conventions or special identifiers—when generating statistical SQL queries. Ideally, in a future update, the get_table_info function within the SQLDatabase section would natively incorporate support for retrieving these column comments.

from langchain.agents import tool
from sqlalchemy import create_engine,MetaData,Column
from sqlalchemy import Table as Table
from dotenv import load_dotenv
import os
load_dotenv()

username = os.environ['DB_USERNAME']
password = os.environ['DB_PASSWORD']
host = os.environ['DB_HOST']
# port = os.environ['DB_PORT']
database = os.environ['DB_DATABASE']

from langchain.agents import tool
from sqlalchemy import create_engine,MetaData,Column
from sqlalchemy import Table as Table
from dotenv import load_dotenv
import os
load_dotenv()

username = os.environ['DB_USERNAME']
password = os.environ['DB_PASSWORD']
host = os.environ['DB_HOST']
# port = os.environ['DB_PORT']
database = os.environ['DB_DATABASE']

@tool
def get_table_info_with_comments(table_name: str) -> str:
    """return the table info with comments"""

    connect_string = f"mssql+pyodbc://{username}:{password}@{host}/{database}?driver=ODBC+Driver+17+for+SQL+Server"
    engine = create_engine(connect_string)
    metadata = MetaData()
    # metadata.reflect(bind=engine)
    # 创建表对象
    table = Table(table_name, metadata, autoload_with=engine)
    table_info: str = (
        "TABLE NAME: {}\n".format(table.name)
        + "COMMENT: {}\n".format(table.comment)
        + "COLUMNS:\n"
    )

    for column in table.columns:
        column_info: str = (
            f"\t'{column.name}', TYPE : {column.type}, COMMENT: {column.comment}"
        )
        if column.primary_key:
            column_info += f", PRIMARY_KEY: {column.primary_key}"
        if column.foreign_keys:
            column_info += f", FOREIGN_KEY: {column.foreign_keys}"
        if column.nullable is not None and not column.nullable:
            column_info += f", NULLABLE: {column.nullable}"
        if column.server_default is not None:
            column_info += f", SERVER_DEFAULT: {column.server_default}"
        table_info += column_info + "\n"
    return table_info

print(get_table_info_with_comments("my_table"))

System Info

System Information

OS: Windows
OS Version: 10.0.19045
Python Version: 3.10.13 | packaged by Anaconda, Inc. | (main, Sep 11 2023, 13:24:38) [MSC v.1916 64 bit (AMD64)]

Package Information

langchain_core: 0.1.41
langchain: 0.1.15
langchain_community: 0.0.32
langsmith: 0.1.17
langchain_experimental: 0.0.55
langchain_openai: 0.1.1
langchain_text_splitters: 0.0.1
langchainhub: 0.1.15
langserve: 0.0.51
SQLAlchemy: 2.0.29

Packages not installed (Not Necessarily a Problem)

The following packages were not found:

langgraph

[dlinx]

the sqlalchemy's CreateTable is not including comments while creating the schema.
I think we need fix here to include table and column comments.
https://github.com/langchain-ai/langchain/blob/master/libs/community/langchain_community/utilities/sql_database.py#L346