How to connect SQLAlchemy (SQLDatabaseChain from langchain) to SingleStoreDB

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.

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

I wanted some kind of uri as below.

singlestore://user:pass@some_mysql_db_address/db_name

sample code:

import pymysql
from langchain import OpenAI, SQLDatabase, SQLDatabaseChain

user = 'root'
host='localhost'
password='password'
database='classicmodels_test'
local_uri = f"mysql+pymysql://{user}:{password}@{host}/{database}"

Database connection:

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

Output if connected

CREATE TABLE customers (
	`customerNumber` INTEGER NOT NULL, 
	`customerName` VARCHAR(50) NOT NULL, 
	`contactLastName` VARCHAR(50) NOT NULL, 
	`contactFirstName` VARCHAR(50) NOT NULL, 
	phone VARCHAR(50) NOT NULL, 
	`addressLine1` VARCHAR(50) NOT NULL, 
	`addressLine2` VARCHAR(50), 
	city VARCHAR(50) NOT NULL, 
	state VARCHAR(50), 
	`postalCode` VARCHAR(15), 
	country VARCHAR(50) NOT NULL, 
	`salesRepEmployeeNumber` INTEGER, 
	`creditLimit` DECIMAL(10, 2), 
	PRIMARY KEY (`customerNumber`), 
	CONSTRAINT customers_ibfk_1 FOREIGN KEY(`salesRepEmployeeNumber`) REFERENCES employees (`employeeNumber`)
)DEFAULT CHARSET=latin1 ENGINE=InnoDB

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

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

SQLDatabase.connect(connection_obj)

• create engine

  from sqlalchemy import create_engine
  
  engine = create_engine(CONNECTION_URL)
  

• connect db to SQLDatabaseChain

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

• you need a llm to pass to SQLDatabaseChain

  from langchain import OpenAI
  
  llm = OpenAI(
      openai_api_key=OPENAI_API_KEY,
      temperature=0
  )
  

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

from sqlalchemy import *
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:

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

We use mysql connection not singlestore in url.