Prefect: Postgres Tasks

[joshmeek]

Using https://github.com/psycopg/psycopg2 let's add some tasks for working with a postgres database. The main function that we should add is execute which takes in an arbitrary SQL string to execute against a db.

On immediate appearance the ExecuteTask (or whatever it becomes called) should take in a db host, db name, user, pass, SQL string, data tuple, and optional commit param. http://initd.org/psycopg/docs/cursor.html?highlight=execute#cursor.execute

There are other functions such as fetchone, fetchmany, rowcount, etc... that may only be _slightly_ useful and we should use this issue as a means to debate what we think is important. (http://initd.org/psycopg/docs/cursor.html)

cc @jlowin for your psycopg2 expertise

[zangell44]

@joshmeek did you want to add any other tasks besides execute?

[joshmeek]

Good question, I think execute is the most useful in it's ability to essentially allow any SQL command. If you see any others in psycopg2 which seem crucial for a Postgres aspect of someone's workflow I welcome them but execute is a good first step!

[zangell44]

I'll add in a separate task for fetch for now too and we can go from there

[RoyalTS]

Are there any docs with examples on how this is to be used? Having a bit of trouble getting it to run as intended.

[cicdw]

Hi @RoyalTS - here are our API docs for this Task: https://docs.prefect.io/api/unreleased/tasks/postgres.html

Could you share some code / provide an example where it's not behaving as expected?