Hyperscience DatabaseAccessBlock - Test Connection Failing-CodePudding

I’m leveraging a DatabaseAccessBlock on the Hyperscience application (v34.0.3) to communicate with an external database. I’m not able to see the query in the UI and the test connection fails. I'm not finding much information regarding debugging database queries in the SDK; how do I debug this?

Here is my code snippet:

def build_query(submission: Any) -> Any:
    table_name = 'table'
    id = '1'
    query = 'SELECT * FROM '   table_name   ' WHERE 1 = 1 AND id = '   id
    return query

cct_build_query = CodeBlock(
    reference_name='build_query',
    code=build_query,
    code_input={'submission': load_submission.output()},
    title='Build Query',
    description='Build Query',
)

db_lookup = DatabaseAccessBlock(
    reference_name='db_lookup',
    title='Database Lookup',
    description='Perform database lookup',
    db_type='postgres',
    database='<database_name>',
    host='<hostname>',
    username='<username>',
    password='<password>',
    port = 5432,
    timeout = 200,
    query= cct_build_query.output(),
)

CodePudding user response：

The Hyperscience UI does not show the query field if the query is dynamically drawing values from a previous block, but only when a static query has been defined within the DatabaseAccessBlock itself.

To test a connection, set a static query value, test the connection, and then revert to the dynamically set value. It seems that while a query is required to test the connection, the query does not have to be valid - the system simply checks if there is a value present in the query field to go on to test the connection.

You can change the db block to the following, for example:

db_lookup = DatabaseAccessBlock(
    reference_name='db_lookup',
    title='Database Lookup',
    description='Perform database lookup',
    db_type='postgres',
    database='<database_name>',
    host='<hostname>',
    username='<username>',
    password='<password>',
    port = 5432,
    timeout = 200,
    query= select * from random_table,
)