Chapter 45. PL/Python — Python Procedural Language

Table of Contents

45.1. Python 2 vs. Python 3

45.2. PL/Python Functions

45.3. Data Values

45.3.1. Data Type Mapping

45.3.2. Null, None

45.3.3. Arrays, Lists

45.3.4. Composite Types

45.3.5. Set-Returning Functions

45.4. Sharing Data

45.5. Anonymous Code Blocks

45.6. Trigger Functions

45.7. Database Access

45.7.1. Database Access Functions

45.7.2. Trapping Errors

45.8. Explicit Subtransactions

45.8.1. Subtransaction Context Managers

45.8.2. Older Python Versions

45.9. Transaction Management

45.10. Utility Functions

45.11. Environment Variables

1. PostgreSQL Python This PostgreSQL Python section shows you how to work with the PostgreSQL database using the Python programming language. Python has various database drivers for PostgreSQL. Currently, the psycopg is the most popular PostgreSQL database adapter for the Python language.
2. Postgresql.driver provides a PG-API, postgresql.api, interface to a PostgreSQL server using PQ version 3.0 to facilitate communication. It makes use of the protocol’s extended features to provide binary datatype transmission and protocol level prepared statements for strongly typed parameters.
3. Py-postgresql is a project dedicated to improving the Python client interfaces to PostgreSQL. At its core, py-postgresql provides a PG-API, postgresql.api, and DB-API 2.0 interface for using a PostgreSQL database.
4. With Python, there are various ways we can access a PostgreSQL database. There are many database drivers for Python that we can use for this purpose, but psycopg is the most popular one. In this article we showed how to install the module, establish a connection to your PostgreSQL database, and execute common SQL queries using Python code.

The PL/Python procedural language allows PostgreSQL functions to be written in the Python language.

To install PL/Python in a particular database, use CREATE EXTENSION plpythonu (but see also Section 45.1).

Py Postgresql Vs

Py-postgresql is a set of Python modules providing interfaces to various parts of PostgreSQL. Primarily, it provides a pure-Python driver with some C optimizations for querying a PostgreSQL database.

Tip

If a language is installed into template1, all subsequently created databases will have the language installed automatically.

PL/Python is only available as an “untrusted” language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted variant plpython might become available in the future if a secure execution mechanism is developed in Python. The writer of a function in untrusted PL/Python must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator. Only superusers can create functions in untrusted languages such as plpythonu.

Note

Users of source packages must specially enable the build of PL/Python during the installation process. (Refer to the installation instructions for more information.) Users of binary packages might find PL/Python in a separate subpackage.

PostgreSQL is an amazing and modern Relational Database Management System (RDBMS). PostgreSQL is also an open source database. PostgreSQL is cross platform. You can install PostgreSQL on Windows, Mac OS and Linux very easily.

You can easily interact with PostgreSQL database with Python programming language. All you have to do is install the Python module psycopg2 with PIP and you are good to go.

Python Postgresql

In this article, I will show you how to access PostgreSQL database with Python on Linux. I am going to use Debian 9 Stretch as my operating system and Python 3 programming language. The PostgreSQL database version I am going to use is PostgreSQL 9.6. So Let’s get started.

You must have

• Any modern Linux distribution such as Ubuntu/Debian/CentOS etc installed.
• Python programming language installed.
• PIP or PIP3 installed depending on the version of your Python.
• PostgreSQL installed.

You can find many articles on linuxhint.com that may help you set up PostgreSQL and install Python+PIP on your favorite Linux distribution. Just search for it.

Creating a PostgreSQL Database and User:

In this section, I will show you how to create a PostgreSQL database and user on Linux. We will be connecting to this database from Python later in this article.

First find out your login username with the following command:

As you can see, my login username is shovon. Yours will be different. Make sure you take a note of it as you will need it later.

Now start the PostgreSQL interactive terminal with the following command:

The PostgreSQL interactive terminal should start. Slots on youtube 2021.

Now create a PostgreSQL database pyapp with the following SQL command:

The pyapp database should be created.

Now you have to create a new PostgreSQL user. Also, make sure that the username is the same as your login username.

Make a new PostgreSQL user with the following SQL command:

NOTE: replace your_login_username and your_password with your own login username and password.

The PostgreSQL user should be created.

Now grant the newly created user all the privileges to the newly created database pyapp with the following SQL command:

All the privileges for the pyapp database are granted to your login user.

Now exit out of the PostgreSQL terminal with the following command:

Now let’s see whether we can login to our newly created database pyapp using our login username with the following command:

Now type in the password that you set earlier for your PostgreSQL user and press <Enter>.

You should be logged in.

Installing psycopg2 with PIP and PIP3:

Now it’s time to install psycopg2 Python module.

If you’re using Python 3, then run the following command to install psycopg2:

If you’re using Python 2, then run the following command to install psycopg2:

psycopg2-binary PIP module should be installed.

Creating the Project Directory:

Now create a project directory, pyapp with the following command:

And navigate to the directory with the following command:

This is where I will create all the Python script to access PostgreSQL database.

Connecting to the PostgreSQL Database:

First, create a python program connect.py in your project directory.

Now type in the following lines and save the file.

Now run the script connect.py with one of the following command:

For Python 3:

For Python 2:

Postgresql And Python Examples

As you can see, I am connected to the database.

Here on line 1, the psycopg2 module is imported. In line 4, psycopg2.connect() method is used to connect to the PostgreSQL database. A try-except block is used to catch errors if in case something goes wrong and connection to the database fails.

Executing SQL Commands:

In this section, I will create a simple table users using Python psycopg2

Py-postgresql Sample

Type in the following code to a new Python script create_table.py and save it.

Now run the script:

As you can see, the table users is created.

With psycopg2, if you want to execute a SQL command, first you have to create a cursor.

On line 9, I created a cursor with conn.cursor() method and stored it to cur variable. Here conn is the variable where I stored the database connection from psycopg2.connect() method.

Then you execute SQL command with the cursor as cur.exec(“YOUR_SQL_GOES_HERE”), which I did on line 12-17 to create an users table.

If your SQL command makes changes to the database you’re connected to, then you have to call conn.commit() method to make the changes permanent as I did in line 19.

Inserting Data to PostgreSQL Database:

Now that you have users table ready, let’s insert some data into the table.

Create a new file insert.py into your project’s directory and type in the following codes and save the file.

Now run the Python script insert.py as follows:

The data should be inserted.

As you can see in the PostgreSQL terminal.

In the insert.py script, line 12 cur.execute() method runs the SQL query to insert into the users table. The %s’s are replaced by the strings from the tuple, the second parameter of the cur.execute() method.

The first occurrence of %s is replaced by the first element of the tuple, the second %s is replaced by the second element of the tuple and so on. You can also mix data types if you want. For example, %d represents integer.

Fetching Data from PostgreSQL Database:

Now you can fetch the data that you inserted into the PostgreSQL database.

First create a new Python script fetch.py and type in the following lines of code. Then save the file.

Now run the script fetch.py with the following command:

Py Postgresql

As you can see, the data I inserted is fetched. It’s returned as a tuple, which is kind of like an array.

Python Library For Postgresql

In the fetch.py script, everything is similar as in other scripts. Here, cur.fetchone() method is used to return the first row of the table. If you have many rows, then you can keep calling cur.fetchone() to iterate through the list. When all the rows are returned, cur.fetchone() will return None.

Thanks for reading this article.