如何将SQLAlchemy（来自langchain的SQLDatabaseChain）连接到SingleStoreDB

huangapple
go评论180阅读模式
英文:

How to connect SQLAlchemy (SQLDatabaseChain from langchain) to SingleStoreDB

问题

SingleStoreDB
langchain
more info from medium

从本地能够使用以下URI连接，但在SingleStore中使用相同的URI无法工作。

  1. mysql+pymysql://user:pass@some_mysql_db_address/db_name

我希望有以下类型的URI。

  1. singlestore://user:pass@some_mysql_db_address/db_name

示例代码:

  1. import pymysql
  2. from langchain import OpenAI, SQLDatabase, SQLDatabaseChain
  4. user = 'root'
  5. host = 'localhost'
  6. password = 'password'
  7. database = 'classicmodels_test'
  8. local_uri = f"mysql+pymysql://{user}:{password}@{host}/{database}"

数据库连接:

  1. db = SQLDatabase.from_uri(local_uri)
  2. print(db.table_info)

如果连接成功的输出

  1. CREATE TABLE customers (
  2. 	`customerNumber` INTEGER NOT NULL, 
  3. 	`customerName` VARCHAR(50) NOT NULL, 
  4. 	`contactLastName` VARCHAR(50) NOT NULL, 
  5. 	`contactFirstName` VARCHAR(50) NOT NULL, 
  6. 	phone VARCHAR(50) NOT NULL, 
  7. 	`addressLine1` VARCHAR(50) NOT NULL, 
  8. 	`addressLine2` VARCHAR(50), 
  9. 	city VARCHAR(50) NOT NULL, 
  10. 	state VARCHAR(50), 
  11. 	`postalCode` VARCHAR(15), 
  12. 	country VARCHAR(50) NOT NULL, 
  13. 	`salesRepEmployeeNumber` INTEGER, 
  14. 	`creditLimit` DECIMAL(10, 2), 
  15. 	PRIMARY KEY (`customerNumber`), 
  16. 	CONSTRAINT customers_ibfk_1 FOREIGN KEY(`salesRepEmployeeNumber`) REFERENCES employees (`employeeNumber`)
  17. )DEFAULT CHARSET=latin1 ENGINE=InnoDB

或者我们是否可以使用支持pymysql连接对象的pymysql函数来获得数据库连接对象

  1. connection_obj = pymysql.connect(user='root', host='localhost', password='password', database='classicmodels_test')
  3. SQLDatabase.connect(connection_obj)
英文:

SingleStoreDB
langchain
more info from medium

From local able to connect throught using this uri but in single store it is not working with same.

  1. mysql+pymysql://user:pass@some_mysql_db_address/db_name

I wanted some kind of uri as below.

  1. singlestore://user:pass@some_mysql_db_address/db_name

sample code:

  1. import pymysql
  2. from langchain import OpenAI, SQLDatabase, SQLDatabaseChain
  4. user = 'root'
  5. host='localhost'
  6. password='password'
  7. database='classicmodels_test'
  8. local_uri = f"mysql+pymysql://{user}:{password}@{host}/{database}"

Database connection:

  1. db = SQLDatabase.from_uri(local_uri)
  2. print(db.table_info)

Output if connected

  1. CREATE TABLE customers (
  2. 	`customerNumber` INTEGER NOT NULL, 
  3. 	`customerName` VARCHAR(50) NOT NULL, 
  4. 	`contactLastName` VARCHAR(50) NOT NULL, 
  5. 	`contactFirstName` VARCHAR(50) NOT NULL, 
  6. 	phone VARCHAR(50) NOT NULL, 
  7. 	`addressLine1` VARCHAR(50) NOT NULL, 
  8. 	`addressLine2` VARCHAR(50), 
  9. 	city VARCHAR(50) NOT NULL, 
  10. 	state VARCHAR(50), 
  11. 	`postalCode` VARCHAR(15), 
  12. 	country VARCHAR(50) NOT NULL, 
  13. 	`salesRepEmployeeNumber` INTEGER, 
  14. 	`creditLimit` DECIMAL(10, 2), 
  15. 	PRIMARY KEY (`customerNumber`), 
  16. 	CONSTRAINT customers_ibfk_1 FOREIGN KEY(`salesRepEmployeeNumber`) REFERENCES employees (`employeeNumber`)
  17. )DEFAULT CHARSET=latin1 ENGINE=InnoDB

OR can we have databse connection object with pymysql function that supporting pymysql connection object

  1. connection_obj = pymysql.connect(user='root',host='localhost', password='password', database='classicmodels_test')
  3. SQLDatabase.connect(connection_obj)

答案1

得分: 1

  • 创建引擎
  1. from sqlalchemy import create_engine
  3. engine = create_engine(CONNECTION_URL)
  • 连接数据库到 SQLDatabaseChain
  1. from langchain.sql_database import SQLDatabase
  2. from langchain.chains import SQLDatabaseChain
  4. db = SQLDatabase(engine)
  5. sql_chain = SQLDatabaseChain(llm=llm, database=db, verbose=True)
  • 你需要一个 llm 传递给 SQLDatabaseChain
  1. from langchain import OpenAI
  3. llm = OpenAI(
  4.     openai_api_key=OPENAI_API_KEY,
  5.     temperature=0
  6. )
英文:

  • create engine

    1. from sqlalchemy import create_engine
    3. engine = create_engine(CONNECTION_URL)

  • connect db to SQLDatabaseChain

    1. from langchain.sql_database import SQLDatabase
    2. from langchain.chains import SQLDatabaseChain
    4. db = SQLDatabase(engine)
    5. sql_chain = SQLDatabaseChain(llm=llm, database=db, verbose=True)

  • you need a llm to pass to SQLDatabaseChain

    1. from langchain import OpenAI
    3. llm = OpenAI(
    4.     openai_api_key=OPENAI_API_KEY,
    5.     temperature=0
    6. )

答案2

得分: 0

如果您正在使用SingleStoreDB Cloud Portal中的笔记本，您可以使用以下方法：

  1. from sqlalchemy import *
  2. db_connection = create_engine(connection_url)

确保您选择工作区，然后选择数据库。

如果您想在本地连接，也可以使用以下连接：

  1. from sqlalchemy import *
  2. database_name = 'mydatabase'
  3. connection_user = 'username'
  4. connection_password = 'password'
  5. connection_port = '3306'
  6. connection_host = 'svc-XXXXX.svc.singlestore.com'
  7. db_connection_str = "mysql+pymysql://"+connection_user+":"+connection_password+"@"+connection_host+":"+connection_port+"/"+database_name+"?ssl_cipher=HIGH"
  8. db_connection = create_engine(db_connection_str)

在URL中使用的是mysql连接，而不是singlestore。

英文:

If you are using Notebook in SingleStoreDB Cloud Portal, you can use the following method:

  1. from sqlalchemy import *
  2. db_connection = create_engine(connection_url)

Make sure you pick the workspace and then the database.

You can also use the following connection if you want to connect locally:

  1. from sqlalchemy import *
  2. database_name = 'mydatabase'
  3. connection_user = 'username'
  4. connection_password = 'password'
  5. connection_port = '3306'
  6. connection_host = 'svc-XXXXX.svc.singlestore.com'
  7. db_connection_str = "mysql+pymysql://"+connection_user+":"+connection_password+"@"+connection_host+":"+connection_port+"/"+database_name+"?ssl_cipher=HIGH"
  8. db_connection = create_engine(db_connection_str)

We use mysql connection not singlestore in url.

huangapple
  • 本文由 发表于 2023年7月17日 14:00:21
  • 转载请务必保留本文链接：https://go.coder-hub.com/76701829.html
  • langchain
  • python
  • python-3.x
  • singlestore
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定