To add a connection to PostgreSQL, go to Integrations via the left sidebar, create a new PostgreSQL integration and enter credentials:
Don't forget to connect the newly created "PostgreSQL" integration in the Integrations sidebar.
The fastest way to query your connected postgres database is to use a SQL cell. You can create one by clicking "+ Block" or at the bottom of a notebook.
Alternatively, if you want to use python to access the database, access the connection details via environment variables with a common prefix that's generated from the name of your Postgres integration:
Then you'll be able to connect to a database like this:
import psycopg2import ostry:connection = psycopg2.connect(user=os.environ["MY_INTEGRATION_USER"],password=os.environ["MY_INTEGRATION_PASSWORD"],host=os.environ["MY_INTEGRATION_HOST"],port=os.environ["MY_INTEGRATION_PORT"],database=os.environ["MY_INTEGRATION_DATABASE"])with connection.cursor() as cursor:cursor.execute("SELECT version();")record = cursor.fetchone()print("You are connected to - ", record)except (Exception, psycopg2.Error) as error:print ("Error while connecting to database", error)
You can now use
connection.cursor() like shown in example above to run queries against the database. Here's how you can run a query and see its output:
import pandas as pdquery = """SELECT *FROM users"""df = pd.io.sql.read_sql_query(query, connection)df