# Running PostgreSQL in Jupyter Notebook


# 🐘 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

```bash
conda install anaconda::postgresql
```

> **Note:** If you have both Anaconda and local Python installed and want to use your local Python, deactivate conda first:
> ```bash
> conda deactivate
> pip install ipython-sql --break-system-packages
> ```

---

## 🔧 Step 2 — Install Required Python Packages

```bash
pip install ipython-sql
pip install psycopg2
pip install SQLAlchemy
```

---

## 🚀 Step 3 — Initialize & Start the Server

```bash
# 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:

```bash
# 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

```bash
# 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

```python
# 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

```python
%sql \dt
```

### Create a table

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

### Insert values

```python
%%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

```python
%%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

```bash
# 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

```bash
# 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.

---

<div align="center">

Made by [Hassan](https://msjahid.me) &nbsp;·&nbsp; [GitHub](https://github.com/msjahid) &nbsp;·&nbsp; [msjahid.ai@gmail.com](mailto:msjahid.ai@gmail.com)

</div>