Search code examples
azureazure-sql-databaselangchain

Langchain cannot connect with Azure SQL Database

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

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.

Solution 
  • 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:

    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]")

    You will get the output as shown below:

    enter image description here