Using Postgres Databases from Neptyne

Building apps in Neptyne often involves acquiring data from other places and a lot of data these days resides in postgres databases. Luckily connecting to a Postgres instance and querying databases is really straightforward. Let’s dive in. We’ll start with a little bit of config:

import pg8000.native

connection_information = dict(
    user="example",
    host="34.135.168.150",
    password="neptyne",
    database="postgres",
)

We import the postgres database library pg8000 and set up some connection information. This uses a database we have created so you’d need to insert your own information. If you are doing anything non-trivial, make sure to not put the password here in clear text but use neptyne’s secret management

Running a query is also not hard:

def query(query_str):
    with pg8000.native.Connection(**connection_information) as conn:
        res = conn.run(query_str)
        yield [meta["name"] for meta in conn.columns]
        yield from res

This yields first the headers and then the rows of the resulting query, something we can call directly from our spreadsheet:

=query (C2)

A

B

C

D

1

2

Query

Select first_name, last_name from actor limit 21

3

Result

first_name

last_name

4

SCARLETT

DAMON

5

ANGERLA

WITHERSPOON

6

RUSSELL

TEMPLE

7

RITA

REYNOLDS

8

HARRISON

BALE

9

CUBA

BRIRCH

10

FRANCES

TOMEI

11

CHRISTIAN

NEESON

12

DAN

STREEP

C3 calls query on C2 so any time we change the query in C2 we immediately see the results below. Try it out at:

https://app.neptyne.com/-/d975m3ytmx