PostgreSQL via Psycopg2¶
Description:
- Work with data in PostgreSQL
Resources:
- [GitHub] https://github.com/psycopg/psycopg2
- [Docs] https://www.psycopg.org/docs/
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
POSTGRES_HOST: hostname of your PostgreSQL serverPOSTGRES_USER: PostgreSQL user namePOSTGRES_PASSWORD: password for that userPOSTGRES_DB: database to connect to (must exist)
Example 1¶
Create a temporary table, insert rows with query parameters, and read them back with psycopg2
In [ ]:
Copied!
import os
import psycopg2
conn = psycopg2.connect(
host=os.environ["POSTGRES_HOST"],
user=os.environ["POSTGRES_USER"],
password=os.environ["POSTGRES_PASSWORD"],
dbname=os.environ["POSTGRES_DB"],
)
try:
# "with conn" runs the block as one transaction: it commits when the block succeeds and
# rolls back when it raises. It does not close the connection; conn.close() below does.
with conn, conn.cursor() as cur:
# A TEMP table disappears when the connection closes, so the demo leaves nothing behind.
cur.execute("CREATE TEMP TABLE demo_cities (name text PRIMARY KEY, population integer)")
cur.executemany(
"INSERT INTO demo_cities (name, population) VALUES (%s, %s)",
[("Lisbon", 545_000), ("Oslo", 709_000), ("Quito", 2_800_000)],
)
cur.execute(
"SELECT name, population FROM demo_cities WHERE population > %s ORDER BY name", (600_000,)
)
for name, population in cur.fetchall():
print(name, population)
finally:
conn.close()
import os
import psycopg2
conn = psycopg2.connect(
host=os.environ["POSTGRES_HOST"],
user=os.environ["POSTGRES_USER"],
password=os.environ["POSTGRES_PASSWORD"],
dbname=os.environ["POSTGRES_DB"],
)
try:
# "with conn" runs the block as one transaction: it commits when the block succeeds and
# rolls back when it raises. It does not close the connection; conn.close() below does.
with conn, conn.cursor() as cur:
# A TEMP table disappears when the connection closes, so the demo leaves nothing behind.
cur.execute("CREATE TEMP TABLE demo_cities (name text PRIMARY KEY, population integer)")
cur.executemany(
"INSERT INTO demo_cities (name, population) VALUES (%s, %s)",
[("Lisbon", 545_000), ("Oslo", 709_000), ("Quito", 2_800_000)],
)
cur.execute(
"SELECT name, population FROM demo_cities WHERE population > %s ORDER BY name", (600_000,)
)
for name, population in cur.fetchall():
print(name, population)
finally:
conn.close()