PostgreSQL in Jupyter Notebook

Install PostgreSQL via Conda

Install the PostgreSQL server from the Anaconda channel:

bash

conda install anaconda::postgresql

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

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

Install Required Python Packages

bash

pip install ipython-sql
pip install psycopg2
pip install SQLAlchemy

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. Stop it first:

bash

# Stop system postgres
sudo systemctl stop postgresql

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

Create a User & Database

bash

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

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

When prompted, answer the questions:

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

Connect in Jupyter Notebook

python · jupyter

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

Important: The %config SqlMagic.style = '_DEPRECATED_DEFAULT' line prevents a KeyError: 'DEFAULT' from prettytable version conflicts. Always include it right after %load_ext sql.

Working with SQL Magic

%sql

Single-line query — one line of SQL only.

%%sql

Cell magic — entire cell is treated as SQL. Best for multi-line queries.

Check existing tables:

python · jupyter

%sql \dt

Did not find any relations. ← no tables yet, that's fine

Create a table:

python · jupyter

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

Done.
[]

Insert values:

python · jupyter

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

5 rows affected.

View the data:

python · jupyter

%%sql
SELECT * FROM student_info;

5 rows affected.

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

Access from Terminal

bash

# Using psql
psql -U myuser -d mydb

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

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

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. The next post will cover NoSQL databases in Jupyter the same way.