SQLAlchemy Tip: Print Raw SQL with Literal Values (Oracle Edition)

🐍 SQLAlchemy Tip: Print Raw SQL with Literal Values (Oracle Edition)

When working with SQLAlchemy, especially in complex applications or when integrating with Oracle databases, it’s super helpful to see exactly what SQL is being executed—including the values being bound to the query.

That’s where this little gem of a snippet comes in:

from sqlalchemy.sql import text
from sqlalchemy.dialects.oracle import dialect

sql = "SELECT * FROM users WHERE user_id = :id"
compiled = text(sql).compile(
    dialect=dialect(), 
    compile_kwargs={"literal_binds": True}
)
print(str(compiled))

🧠 What’s Going On Here?

• text(sql): Creates a SQL expression from a raw SQL string.

• compile(dialect=dialect()): Tells SQLAlchemy which dialect to use (in this case, Oracle).

• compile_kwargs={"literal_binds": True}: Replaces bind parameters like :id with their actual literal values—super useful for debugging or logging.

🔍 Why You Need This

When debugging, the normal str(statement) will show bind parameters (:id) instead of actual values. But sometimes you just want to see the whole shebang—a raw SQL string with everything baked in. This is especially useful when:

• Logging slow queries

• Diagnosing issues in Oracle’s SQL Developer or TOAD

• Explaining a bug to your DBA without sounding like you made it up

⚠️ Caution

This is great for debugging, but don’t use this to generate SQL for production execution—it’s meant for inspection, not execution. Values will be hardcoded, which means no SQL injection protection.

---

🧪 Example Output

SELECT * FROM users WHERE user_id = 42

Neat, right?

---

Got another SQLAlchemy trick up your sleeve? Drop it in the comments—let's make debugging a little less mysterious and a lot more fun.

🧙‍♂️ Happy querying!