Langchain cannot connect with Azure SQL Database

营赢盈英

于 2024-07-26 10:28:52 发布

阅读量187

点赞数 4

分类专栏： AI 文章标签： langchain 数据库 azure sql database

本文链接：https://blog.csdn.net/suiusoar/article/details/140708286

版权

AI 专栏收录该内容

82 篇文章 0 订阅

订阅专栏

题意：Langchain 无法连接到 Azure SQL 数据库

问题背景：

Trying to connect with my Azure SQL Database, it contains multiple tables.

尝试连接到我的 Azure SQL 数据库，它包含多个表。

Here's a sample of my code 下面是我的代码示例

from sqlalchemy import create_engine

driver = '{ODBC Driver 17 for SQL Server}'
odbc_str = 'mssql+pyodbc:///?odbc_connect=' \
                'Driver='+driver+ \
                ';Server=tcp:' + os.getenv("SQL_SERVER")+'.database.windows.net;PORT=1433' + \
                ';DATABASE=' + os.getenv("SQL_DB") + \
                ';Uid=' + os.getenv("SQL_USERNAME")+ \
                ';Pwd=' + os.getenv("SQL_PWD") + \
                ';Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'

db_engine = create_engine(odbc_str)

from langchain.agents import AgentType, create_sql_agent
from langchain.sql_database import SQLDatabase
from langchain.agents.agent_toolkits.sql.toolkit import SQLDatabaseToolkit

db = SQLDatabase(db_engine)

And this is the error that I get thrown: 下面是我得到的错误

Error: ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid precision value (0) (SQLBindParameter)')

The above exception was the direct cause of the following exception:

DBAPIError                                Traceback (most recent call last)
Cell In[16], line 5
      2 from langchain.sql_database import SQLDatabase
      3 from langchain.agents.agent_toolkits.sql.toolkit import SQLDatabaseToolkit
----> 5 db = SQLDatabase(db_engine)
      7 sql_toolkit = SQLDatabaseToolkit(db=db, llm=llm)
      8 sql_toolkit.get_tools()
...
[SQL: SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]]
[parameters: ('dbo', 'BASE TABLE')]
(Background on this error at: https://sqlalche.me/e/20/dbapi)
Output is truncated. View as a scrollable element or open in a text editor. Adjust cell output settings...

Does anyone have an idea? 有人知道怎么解决吗？

I want to connect to my azure SQL database, but it is not working and throwing me an error. I tried changing the driver, and encryption and such but it all does not seem to work.

我想连接到我的 Azure SQL 数据库，但它无法工作并抛出一个错误。我尝试更改驱动程序、加密等设置，但似乎都不起作用

问题解决：

Error: ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid precision value (0) (SQLBindParameter)')

According to this the issue may get lower version of SQL alchemy package, use compatible version of SQL alchemy. You can use below code to connect Azure SQL database to Lang chain:

根据这个信息，问题可能是因为你使用的 SQLAlchemy 包版本较低，建议使用与 SQLAlchemy 兼容的版本。你可以使用以下代码将 Azure SQL 数据库连接到 Langchain

import urllib
import sqlalchemy
from langchain.agents import AgentType, create_sql_agent
from langchain.sql_database import SQLDatabase
from langchain.agents.agent_toolkits.sql.toolkit import SQLDatabaseToolkit

server = '<serverName>.database.windows.net'
database = '<dbName>'
username = '<userName>'
password = '<password>'
driver = '{ODBC Driver 17 for SQL Server}'
odbc_str = 'DRIVER='+driver+';SERVER='+server+';PORT=1433;UID='+username+';DATABASE='+ database + ';PWD='+ password
connect_str = 'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(odbc_str)
engine = sqlalchemy.create_engine(connect_str)
db = SQLDatabase(engine)
db.run("SELECT [TABLE_NAME] FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_SCHEMA] = 'dbo' AND [TABLE_TYPE] = 'BASE TABLE' ORDER BY [TABLE_NAME]")