how to see an sql query

[sakoser]

System Info

Good afternoon. Can you tell me if I can receive an SQL query when I connect via the API without using promt?

🐛 Describe the bug

from pandasai import SmartDataframe
from pandasai.ee.agents.semantic_agent import SemanticAgent
import os
import pandas as pd
from pandasai import Agent
pd.set_option("display.max_columns", None)

os.environ["PANDASAI_API_KEY"] = "PANDASAI_API_KEY"
agent = Agent("test.xlsx")

[dosubot[bot]]

Hello, @sakoser! I'm here to assist you with any bugs, questions, or contributions you may have while we wait for a human maintainer. Let's squash those bugs together! 🤖

Yes, you can receive an SQL query directly when connecting via the API without using a prompt. The execute_direct_sql_query method in the SQLConnector class allows you to execute an SQL query directly on the data source.

Here is the relevant code snippet:

def execute_direct_sql_query(self, sql_query):
    if not self._is_sql_query_safe(sql_query):
        raise MaliciousQueryError("Malicious query is generated in code")

    return pd.read_sql(sql_query, self._connection)

Additionally, you can configure the Agent to handle direct SQL queries by setting the direct_sql configuration to True. Here is an example using the PandasAI library with a PostgreSQL database:

import os
from pandasai import Agent
from pandasai.connectors import PostgreSQLConnector

# With a PostgreSQL database
order = PostgreSQLConnector(
    config={
        "host": "localhost",
        "port": 5432,
        "database": "testdb",
        "username": "postgres",
        "password": "123456",
        "table": "orders",
    }
)

order_details = PostgreSQLConnector(
    config={
        "host": "localhost",
        "port": 5432,
        "database": "testdb",
        "username": "postgres",
        "password": "123456",
        "table": "order_details",
    }
)

products = PostgreSQLConnector(
    config={
        "host": "localhost",
        "port": 5432,
        "database": "testdb",
        "username": "postgres",
        "password": "123456",
        "table": "products",
    }
)

# By default, unless you choose a different LLM, it will use BambooLLM.
# You can get your free API key signing up at https://pandabi.ai (you can also configure it in your .env file)
os.environ["PANDASAI_API_KEY"] = "your-api-key"

agent = Agent(
    [order, products, order_details],
    config={"direct_sql": True},
)

response = agent.chat("return orders with count of distinct products")
print(response)

This example demonstrates how to set up the Agent to execute direct SQL queries on a PostgreSQL database ^[1][2][3][4].

To continue talking to Dosu, mention @dosu.