💡
features
🤝
Collaboration
⚙️ Environment
🔧
Integrations
📥
Import & export
PostgreSQL
Setup
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.
Whitelisting the Deepnote IP address for security reasons
If your connection is protected, you might need to whitelist Deepnote's IP adressed. Read more here
Usage
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.
A text input cell and a SQL cell working together
Advanced usage
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:
<INTEGRATION_NAME>_HOST<INTEGRATION_NAME>_DATABASE<INTEGRATION_NAME>_PORT<INTEGRATION_NAME>_USER<INTEGRATION_NAME>_PASSWORD
Then you'll be able to connect to a database like this:
1
import psycopg2
2
import os
3
4
try:
5
connection = psycopg2.connect(
6
user=os.environ["MY_INTEGRATION_USER"],
7
password=os.environ["MY_INTEGRATION_PASSWORD"],
8
host=os.environ["MY_INTEGRATION_HOST"],
9
port=os.environ["MY_INTEGRATION_PORT"],
10
database=os.environ["MY_INTEGRATION_DATABASE"])
11
12
with connection.cursor() as cursor:
13
cursor.execute("SELECT version();")
14
record = cursor.fetchone()
15
print("You are connected to - ", record)
16
17
except (Exception, psycopg2.Error) as error:
18
print ("Error while connecting to database", error)
Copied!
You can now use
connection and 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:1
import pandas as pd
2
3
query = """
4
SELECT *
5
FROM users
6
"""
7
df = pd.io.sql.read_sql_query(query, connection)
8
df
Copied!
Last modified 8mo ago