Connecting PostgreSQL to Python and BI Tools

Why Connect PostgreSQL to External Tools?

Storing data in PostgreSQL is only the first step. Real analysis happens when you pull that data into Python for statistical work or into a visualization tool like Power BI for dashboards. This lesson covers two Python connection methods—psycopg2 and SQLAlchemy—and one BI connection method. Each approach serves a different workflow, but all three must handle credentials and queries securely.

SQL Injection: The Risk You Must Prevent

Before writing a single line of connection code, understand SQL injection. SQL injection occurs when untrusted user input is concatenated directly into a query string, allowing an attacker to alter the query's logic. For example, building a query with Python f-strings or string concatenation like `f"SELECT * FROM orders WHERE id = {user_input}"` is dangerous. If `user_input` is `1 OR 1=1`, the query returns every row in the table. In a destructive case, it could drop tables or expose passwords.

The defense is parameterized queries: you write a query template with placeholders, then pass values separately so the database driver handles escaping. The OWASP SQL Injection Prevention Cheat Sheet documents this as the primary defense. Never skip this step when your query touches any external input, including values from files, web forms, or command-line arguments.

Connecting with psycopg2

psycopg2 is the most widely used PostgreSQL adapter for Python. Install it with `pip install psycopg2-binary`. A basic connection looks like this:

```python

import psycopg2

import pandas as pd

conn = psycopg2.connect(

host="your_host",

dbname="your_db",

user="your_user",

password="your_password",

port=5432

)

cur = conn.cursor()

cur.execute(

"SELECT product_id, revenue FROM sales WHERE region = %s;",

("West",) # values passed as a tuple, NOT concatenated

)

rows = cur.fetchall()

df = pd.DataFrame(rows, columns=["product_id", "revenue"])

print(df.head())

cur.close()

conn.close()

```

The `%s` placeholder is psycopg2's parameterization syntax. The driver escapes the value before it reaches the database engine, preventing injection. Always close the cursor and connection when finished to release server resources. Never hard-code credentials in a script you will share or commit to version control; use environment variables or a config file excluded from your repository.

Connecting with SQLAlchemy Core

SQLAlchemy is a broader library that works with many database backends. Its Core layer (as opposed to the ORM layer) lets you reflect an existing table—meaning SQLAlchemy reads the table's structure directly from the database—and then run queries using Python objects rather than raw SQL strings.

Install with `pip install sqlalchemy psycopg2-binary`. A Core reflection example:

```python

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine(

"postgresql+psycopg2://user:password@host:5432/dbname"

)

meta = MetaData()

meta.reflect(bind=engine)

sales = meta.tables["sales"]

with engine.connect() as con:

stmt = select(sales).where(sales.c.region == "West")

result = con.execute(stmt)

df = pd.DataFrame(result.fetchall(), columns=result.keys())

print(df.head())

```

SQLAlchemy parameterizes values automatically when you use its query-building methods. The connection string embeds credentials; the same caution about secrets management applies. SQLAlchemy is preferred when your project needs to support multiple databases or when you want Python-object-level query construction. psycopg2 direct connections are simpler and faster for PostgreSQL-only projects.

Connecting PostgreSQL to Power BI Desktop

Power BI Desktop is a free Windows application from Microsoft. To connect to PostgreSQL you need the Npgsql driver installed on your machine (Power BI's PostgreSQL connector depends on it; download from the Npgsql releases page and install before opening Power BI).

Steps in Power BI Desktop:

1. Open Power BI Desktop and click Get Data on the Home ribbon.

2. Search for PostgreSQL and select it, then click Connect.

3. Enter the server address and database name. Choose Import mode for this exercise (Import loads a snapshot; DirectQuery sends live queries on each refresh—both are valid but Import is simpler for class work).

4. Enter your credentials in the authentication dialog.

5. In the Navigator pane, select the two tables for this exercise and click Load.

6. Switch to Model view and drag a key field from one table to the matching field in the other to define a relationship.

7. Switch to Report view, add a bar chart visual from one table and a table visual from the other, then configure their fields.

8. Save the file as a .pbix file and submit it.

Power BI enforces the relationship you defined, so measures calculated across tables will join correctly. If visuals show blank data, check that the relationship is active (solid line in Model view) and that the key data types match on both sides.

Credentials and Security Reminders

• Never commit database passwords to a public repository. Use environment variables (`os.environ`) or a `.env` file listed in `.gitignore`.
• Use a read-only database account for analytics work whenever possible. This limits damage if credentials are exposed.
• In Power BI, avoid storing credentials in the .pbix file when distributing reports outside the classroom; the Gateway service handles credential management in production.