unexpected errors when trying to query remote parquet

[cboettig]

I am trying to access a public h3 parquet file:

from pandasai import SmartDataframe 
from pandasai.llm import OpenAI

llm = OpenAI(api_token=OPENAI_API_KEY)
gbif_ca = SmartDataframe("https://data.source.coop/cboettig/gbif/gbif_ca_h3.parquet", 
              config={"llm": llm, "verbose": True })
gbif_ca.chat("How many species are there?")

Throws the error:

"Unfortunately, I was not able to get your answers, because of the following error:\n\nunhashable type: 'numpy.ndarray'\n"

I would expect something like we might get from duckdb:

import duckdb
con = duckdb.read_parquet("https://data.source.coop/cboettig/gbif/gbif_ca_h3.parquet")
con.scientificname.distinct().count("scientificname")

[dosubot[bot]]

To resolve the "unhashable type: 'numpy.ndarray'" error when querying a remote parquet file using pandasai and OpenAI, you need to modify the CustomEncoder class to handle numpy.ndarray types. The provided code snippet only handles np.integer types. You should extend it to convert numpy.ndarray to a list, which is hashable.

Here is the modified CustomEncoder class:

from json import JSONEncoder
import numpy as np

class CustomEncoder(JSONEncoder):
    def default(self, obj):
        if isinstance(obj, np.integer):
            return int(obj)
        elif isinstance(obj, np.ndarray):
            return obj.tolist()
        # Let the base class default method raise the TypeError
        return JSONEncoder.default(self, obj)

This modification will convert numpy.ndarray objects to lists, thus avoiding the "unhashable type: 'numpy.ndarray'" error ^[1].

To continue talking to Dosu, mention @dosu.

[dosubot[bot]]

To pass a SQLAlchemy connection to a pandas-ai Agent, you need to use a SQLConnector that can handle the SQLAlchemy connection. The Agent class accepts BaseConnector instances as part of its dfs parameter.

Here is an example of how you can achieve this:

from sqlalchemy import create_engine
from pandasai.agent.agent import Agent
from pandasai.connectors.sql import SQLConnector, SQLConnectorConfig

# Create a SQLAlchemy engine
engine = create_engine('your_connection_string')

# Define the configuration for the SQLConnector
config = SQLConnectorConfig(
    dialect="your_dialect",
    driver="your_driver",
    username="your_username",
    password="your_password",
    host="your_host",
    port="your_port",
    database="your_database",
    table="your_table"
).dict()

# Create a SQLConnector
sql_connector = SQLConnector(config)

# Pass the connector to the Agent
agent = Agent(dfs=sql_connector)

# Now you can use the agent with the SQLAlchemy connection

In this example, replace the placeholders in the SQLConnectorConfig with your actual SQLAlchemy connection details. The SQLConnector class from the pandas-ai library is used to wrap the SQLAlchemy engine and manage the connection ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[cboettig]

@dosu this makes no use of create_engine(), but instead creates an entirely new connection with SQLConnectorConfig. Given only an engine handle like engine=create_engine("duckdb:///:memory:"), can we initialize an SQLConnector?

[dosubot[bot]]

The SQLConnector class in the pandas-ai library does not directly support initializing with an existing SQLAlchemy engine. However, you can work around this by subclassing SQLConnector to accept an existing engine. Here is an example of how you can achieve this:

1. Subclass SQLConnector to accept an existing engine:

from pandasai.connectors.sql import SQLConnector, SQLConnectorConfig

class CustomSQLConnector(SQLConnector):
    def __init__(self, engine, config: SQLConnectorConfig):
        self._engine = engine
        self._connection = self._engine.connect()
        self._cache_interval = 600
        self.name = config.table

2. Use the custom connector with an existing SQLAlchemy engine:

from sqlalchemy import create_engine
from pandasai.agent.agent import Agent
from pandasai.connectors.sql import SQLConnectorConfig

# Create a SQLAlchemy engine
engine = create_engine("duckdb:///:memory:")

# Define the configuration for the SQLConnector
config = SQLConnectorConfig(
    dialect="duckdb",
    driver=None,
    username=None,
    password=None,
    host=None,
    port=None,
    database=None,
    table="your_table"
)

# Create a CustomSQLConnector with the existing engine
custom_sql_connector = CustomSQLConnector(engine, config)

# Pass the custom connector to the Agent
agent = Agent(dfs=custom_sql_connector)

# Now you can use the agent with the existing SQLAlchemy engine

This approach allows you to initialize an SQLConnector with an existing SQLAlchemy engine, such as engine=create_engine("duckdb:///:memory:"), and use it with the Agent class ^[1][2].

To continue talking to Dosu, mention @dosu.

[cboettig]

@dosu not so, you cannot just use None in the config, none is not an allowed value (type=type_error.none.not_allowed)

[dosubot[bot]]

To initialize an SQLConnector with an existing SQLAlchemy engine without using None in the SQLConnectorConfig, you can directly set the _engine attribute of the SQLConnector instance. Here is an example of how you can do this:

from sqlalchemy import create_engine
from pandasai.connectors.sql import SQLConnector, SQLConnectorConfig

# Create an existing SQLAlchemy engine
engine = create_engine("duckdb:///:memory:")

# Create a dummy config with the necessary attributes
config = {
    "dialect": "duckdb",
    "username": "",
    "password": "",
    "host": "",
    "port": "",
    "database": "",
    "table": "your_table_name",  # Replace with your actual table name
    "connect_args": {}
}

# Initialize the SQLConnector with the dummy config
connector = SQLConnector(config)

# Directly set the _engine attribute to the existing engine
connector._engine = engine
connector._connection = engine.connect()

# Now you can use the connector as needed
print(connector.head())

In this example, we create an SQLAlchemy engine and then initialize the SQLConnector with a dummy configuration. After that, we directly set the _engine and _connection attributes of the SQLConnector instance to use the existing engine ^[1].

To continue talking to Dosu, mention @dosu.