使用LangChain SQLDatabase连接MySQL失败，请求技术支持

问题描述 #

在Jupyter Notebook中尝试通过langchain.utilities.SQLDatabase.from_uri()连接MySQL数据库时失败。使用SQLAlchemy创建engine对象成功，但调用engine.connect()无法正常工作；直接用pymysql创建游标执行SQL查询一切正常，可排除MySQL服务器端问题。

代码示例 #

from langchain.utilities import SQLDatabase
import pymysql
username = "root"
password = "xxxxxx"
host = 'localhost'
database = 'atliq_tshirts'
SQLDatabase.from_uri(f"mysql+pymysql://{username}:{password}@{host/{database}",sample_rows_in_table_info = 3)

错误日志 #

---------------------------------------------------------------------------
gaierror                                  Traceback (most recent call last)
File ~\anaconda3\Lib\site-packages\pymysql\connections.py:644, in Connection.connect(self, sock)
    643 try:
--> 644     sock = socket.create_connection(
    645         (self.host, self.port), self.connect_timeout, **kwargs
    646     )
    647     break

File ~\anaconda3\Lib\socket.py:827, in create_connection(address, timeout, source_address, all_errors)
    826 exceptions = []
--> 827 for res in getaddrinfo(host, port, 0, SOCK_STREAM):
    828     af, socktype, proto, canonname, sa = res

File ~\anaconda3\Lib\socket.py:962, in getaddrinfo(host, port, family, type, proto, flags)
    961 addrlist = []
--> 962 for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
    963     af, socktype, proto, canonname, sa = res

gaierror: [错误号 11003] getaddrinfo 失败

后续异常处理略...

OperationalError: (pymysql.err.OperationalError) (2003, "无法连接到位于'@localhost'的MySQL服务器（[错误号 11003] getaddrinfo 失败）")

解决方案 #

• 修正连接字符串语法错误：代码中f-string格式有误，@{host/{database}应改为@{host}/{database}，正确写法如下：

  SQLDatabase.from_uri(f"mysql+pymysql://{username}:{password}@{host}/{database}", sample_rows_in_table_info=3)
  

  原代码错误的变量引用方式导致数据库连接地址解析异常，这是引发getaddrinfo失败的直接原因。

• 验证SQLAlchemy连接逻辑：若修正语法后仍有问题，可先单独测试SQLAlchemy连接是否正常：

  from sqlalchemy import create_engine
  engine = create_engine(f"mysql+pymysql://{username}:{password}@{host}/{database}")
  # 测试连接
  with engine.connect() as conn:
      result = conn.execute("SELECT 1")
      print(result.fetchone())
  

  若此测试正常，再尝试初始化SQLDatabase对象。

• 检查端口与权限：确认MySQL服务使用默认3306端口，若端口修改过，需在连接字符串中指定：mysql+pymysql://{username}:{password}@{host}:{port}/{database}；同时确保root用户拥有从localhost连接目标数据库的权限。

内容的提问来源于stack exchange，提问作者Gagan Dehal