anuksharam/websitespace

1import streamlit as st2 3from langchain_community.utilities import SQLDatabase4from langchain_openai import ChatOpenAI5from langchain.chains import create_sql_query_chain6 7# +8# Set up Langchain SQL access9parquet = "https://espm-157-f24.github.io/spatial-carl-amanda-tyler/new_haven_stats.parquet"10 11db = SQLDatabase.from_uri("duckdb:///tmp.db", view_support=True)12db.run(f"create or replace view mydata as select * from read_parquet('{parquet}');")13 14llm = ChatOpenAI(model="llama3", 15                 temperature=0, 16                 api_key=st.secrets["LITELLM_KEY"], 17                 base_url = "https://llm.nrp-nautilus.io")18 19db = SQLDatabase.from_uri("duckdb:///tmp.db", view_support=True)20 21# -22 23 24from langchain_core.prompts import PromptTemplate25template = '''26You are a {dialect} expert. Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer to the input question.27Always return all columns from a query (select *) unless otherwise instructed.28Wrap each column name in double quotes (") to denote them as delimited identifiers.29Pay attention to use only the column names you can see in the tables below. 30Be careful to not query for columns that do not exist. 31Also, pay attention to which column is in which table.32Pay attention to use today() function to get the current date, if the question involves "today".33 34Respond with only the SQL query to run.  Do not repeat the question or explanation. Just the raw SQL query.35 36Only use the following tables:37{table_info}38 39Question: {input}40      41'''42prompt = PromptTemplate.from_template(template, partial_variables = {"dialect": "duckdb", "top_k": 10})43chain = create_sql_query_chain(llm, db, prompt)44 45# +46#print(db.dialect)47#print(db.get_usable_table_names())48#chain.get_prompts()[0].pretty_print()49# -50 51response = chain.invoke({"question": "what is the mean ndvi by grade?"})52response53 54# +55# use the response in a query56 57import ibis58from ibis import _59con = ibis.duckdb.connect()60tbl = con.read_parquet(parquet, "mydata")61tbl.sql(response).execute()62 63 64