🐘 PostgreSQL in Jupyter Notebook

A complete setup guide — from conda install to writing SQL queries directly in your notebook.

---

📦 Step 1 — Install PostgreSQL via Conda

conda install anaconda::postgresql

Note: If you have both Anaconda and local Python installed and want to use your local Python, deactivate conda first:

conda deactivate
pip install ipython-sql --break-system-packages

---

🔧 Step 2 — Install Required Python Packages

pip install ipython-sql
pip install psycopg2
pip install SQLAlchemy

---

🚀 Step 3 — Initialize & Start the Server

# Create the data directory
mkdir ~/pgdata

# If it already exists, remove and recreate
rm -rf ~/pgdata

# Initialize the cluster
initdb -D ~/pgdata

# Start the server
pg_ctl -D ~/pgdata -l ~/pgdata/logfile start

Port conflict? If you get pg_ctl: could not start server, your system PostgreSQL is occupying port 5432:

# Stop system postgres
sudo systemctl stop postgresql

# Now start your conda postgres
pg_ctl -D ~/pgdata -l ~/pgdata/logfile start

---

👤 Step 4 — Create a User & Database

# Create a user (prompted for password)
createuser --interactive --pwprompt myuser

# Create a database owned by that user
createdb --owner myuser mydb

When prompted:

Enter password for new role: ****
Enter it again: ****
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

---

🪐 Step 5 — Connect in Jupyter Notebook

# Load the ipython-sql extension
%load_ext sql

# Fix PrettyTable KeyError (version compatibility issue)
%config SqlMagic.style = '_DEPRECATED_DEFAULT'

# Connect to your database
try:
    %sql postgresql://myuser:1234@localhost/mydb
    print("✅ Connected to mydb successfully!")
except Exception as e:
    print(f"❌ Connection failed: {e}")

✅ Connected to mydb successfully!

---

📝 Step 6 — Working with SQL Magic

Magic	Use case
%sql	Single-line query
%%sql	Multi-line query (entire cell)

Check tables

%sql \dt

Create a table

%%sql
CREATE TABLE student_info (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100),
    cgpa    DECIMAL(3,2)
);

Insert values

%%sql
INSERT INTO student_info (name, cgpa) VALUES
    ('Hassan', 4.00),
    ('Zaki',   3.85),
    ('Tina',   3.72),
    ('Sara',   3.90),
    ('John',   3.65);

View data

%%sql
SELECT * FROM student_info;

id	name	cgpa
1	Hassan	4.00
2	Zaki	3.85
3	Tina	3.72
4	Sara	3.90
5	John	3.65

---

💻 Step 7 — Access from Terminal

# Using psql
psql -U myuser -d mydb

# Using pgcli (host flag required)
pgcli -U myuser -h localhost -d mydb

Useful psql / pgcli commands

Command	Description
\l	List all databases
\dt	List all tables
\d tablename	Describe table structure
\c dbname	Switch to another database
\q	Quit

---

♻️ Every Reboot Reminder

# System postgres may auto-start again after reboot
sudo systemctl stop postgresql

# Start your conda postgres
pg_ctl -D ~/pgdata -l ~/pgdata/logfile start

---

🔭 What's Next?

The same workflow applies to MySQL / MariaDB, SQLite, and NoSQL databases like MongoDB and Redis — just swap the connection string and driver. Next post will cover NoSQL databases in Jupyter.

---