Using langchain and LLaMA2 to QA with a large SQL DB

Question

I was working with a sqlite DB that I created for a large dataset (~150k rows).

Code snippets:

db = SQLDatabase.from_uri("sqlite:///MLdata.sqlite")
SQLITE_PROMPT_TEXT = '''You are a SQLite expert. Given an input question, first create a 
syntactically correct SQLite query to run, then look at the results of the query and return 
the answer to the input question.
Unless the user specifies in the question a specific number of examples to obtain, query for 
at most {top_k} results using the LIMIT clause as per SQLite. You can order the results to 
 return the most informative data in the database.
 Never query for all columns from a table. You must query only the columns that are needed to 
 answer the question. Wrap each column name in double quotes (") to denote them as delimited 
  identifiers.
 Pay attention to use only the column names you can see in the tables below. Be careful to not 
 query for columns that do not exist. Also, pay attention to which column is in which table.

 Use the following format:

  Question: Question here
  SQLQuery: SQL Query to run
  SQLResult: Result of the SQLQuery
  Answer: Final answer here

 Only use the following tables:
 {table_info}

 Question: {input}'''

SQLITE_PROMPT = PromptTemplate(input_variables=['input', 'table_info', 'top_k'], template=SQLITE_PROMPT_TEXT)
sql_chain = SQLDatabaseChain(llm=local_llm, database=db, prompt=SQLITE_PROMPT, return_direct=False, return_intermediate_steps=False, verbose=False)

res=sql_chain("How many rows is in this db?")

Response: 'There are 142321 rows in the input_table of this db.'

Second query

res=sql_chain("Count rows with 'Abdominal pain', VAX_TYPE='COVID19', SEX= 'F' and HOSPITAL= 'Y' is in the input_table of this db")

Response: 'There are 115 rows in the input_table where Abdominal pain is present, VAX_TYPE is COVID19, Sex is Female, and Hospital is Yes.'

Third query I was trying to find the patient ID only instead of the count. But I am not able to get the patient ID.

res=sql_chain("What is the VAERS_ID with 'Abdominal pain', VAX_TYPE='COVID19', SEX= 'F' and HOSPITAL= 'Y' in this db. ")

Seems like the counting is working fine but nothing more. Can anyone help me in displaying table like output from sqlDbchain via langchain and llama2?

lif cc · Accepted Answer · 2023-11-29 06:11:22Z

0

You can ask it for output.

Like this:

sql_chain("Display all the users table in markdown")

# output
# {'query': 'Display all the users table in markdown',
# 'result': '| id | name |\n|----|------|\n| 1  | 2    |\n| 2  | 3    |'}

answered Nov 29, 2023 at 6:11

lif cc

5112 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tanay Sharma · Accepted Answer · 2024-06-05 09:30:47Z

db = SQLDatabase.from_uri(connection_string)

db_chain = SQLDatabaseChain.from_llm(llm = llm, db = db, verbose=True,return_intermediate_steps=True)


# return_intermediate_steps=True will give you sql query
q=db_chain("print top 100 cases are there where emi amount is greater than 15000")

# extract the sql query and query may include LIMIT 5
sql_query =q["intermediate_steps"][-2]["input"].split("\n")[1][9:]

# so to remove LIMIT 5 I used regex 
import re
sql_query=re.sub("LIMIT.*","",sql_query)

# read the sql query and convert the table given by sql query in pandas dataframe 
# you can show pandas dataframe effectively on streamlit 
data_to_plot =pd.read_sql(sql_query, db)

# show output
print(data_to_plot)

Collectives™ on Stack Overflow

Using langchain and LLaMA2 to QA with a large SQL DB

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related