Difficulty: intermediate
Estimated Time: < 10 minutes

This scenario gives you a quick introduction to using Python as an embedded language in PostgreSQL. The database has already been started and Python has been installed into the learning environment as well.

You'll want to complete the Basics of PostgreSQL Functions scenario before proceeding with this one if you're not familiar with writing and using functions in Postgres.

Here are the details on the database we are connecting to:

  1. Username: groot
  2. Password: password (same password for the postgres user)
  3. A database named: workshop

We will not be going in depth in Python or PL/Python - just enough to whet your appetite.

We hope this scenario helped give you an idea of how you might use Python in PostgreSQL to create better applications. Don't forget to check out the official PL/Python docs for even more details, as well as the following pages on Postgres functions:

Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.

Introduction to PL/Python in PostgreSQL

Step 1 of 3

Use Python in PostgreSQL

In the Basic Functions for Developers scenario, you saw that we can use a variety of programming languages to add even more functionality to PostgreSQL. This comes in handy if you happen to be already familiar with one of these languages.

Python is one of the most popular programming languages and is also supported in core PostgreSQL. In this scenario, we'll learn how to enable PL/Python and get started with creating some simple user-defined functions.

With that said, just because the base distribution of Postgres already comes with Python support, doesn't mean Postgres also includes Python itself. You'll need to make sure Python is installed on the same machine as well.

What about different Python versions?
Postgres supports both Python 2 and Python 3. The current "default" is Python 2.

Enable PL/Python

In this environment, we already have Python 3 installed. To enable PL/Python in Postgres, we first need a superuser role to load the extension in the database we're working in.

psql -U postgres -h localhost workshop

(The password is password, same as the user groot.)

CREATE EXTENSION plpython3u;

PL/Python is only available as an untrusted language. As you may have also seen in the PL/R in PostgreSQL scenario, the language can be updated to make it trusted, like so:

UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpython3u';

We also want to emphasize here that Python, like many of the procedural languages supported in Postgres, can interact with the underlying operating system. So, as also mentioned in the PL/R scenario, our proposed workflow for development is:

  1. Allow PL/Python to be a trusted language only in the development environment.
  2. Developers can create, iterate, and test PL/Python functions in their dev environment.
  3. When ready, someone with superuser privileges can transfer the function into production.