Chapter 15: Python program keeps closing database connection

[robml]

Having trouble with data, code, or exercises in Practical SQL, 1st Edition? I'm glad to help. Please answer these questions, and I'll typically reply within one to two business days.

Please include the chapter number in your issue title. For example: "Chapter 13: Regular Expression Question"

General

Please answer all of these as they're essential for troubleshooting.

What's your operating system (e.g. Windows 10, macOS Catalina, Ubuntu, etc.)?
Windows 10

Did you install PostgreSQL and pgAdmin according to the steps on page xxviii of the book's Introduction? If not, please describe your installation.
I have.

Which versions of PostgreSQL and pgAdmin 4 are you using? If you aren't sure, for PostgreSQL run the SQL command SELECT version(); and for pgAdmin 4, navigate to "About pgAdmin 4" under the "Help" menu.
PostgreSQL 14.1, pgAdmin 4 version 6.1

Did you download the book's code examples and data from GitHub using the directions on page xxvii of the Introduction? If not, please describe how you loaded the material on your computer.
Yes

Issue or Question

Chapter, page and code listing number:
Chapter 15, Code listing 15-15

Describe the issue or your question:
My issue is primarily with the Python program which had been a bit of a nightmare from the get-go. Currently the issue is that every time I try to execute the function in the listing (or anything ending with LANGUAGE plpython3u since I am using Python3) I get the error below.

My steps to get here: originally Python didn't seem to be installed, so I reran the EDB language pack installer and ran the StackBuilder installer selecting Language Pack version 2. Everything went smoothly from there and I was able to import Python through CREATE EXTENSION plpython3u. However afterwards when attempting to run the function creation above, I get the error below. I have scoured multiple forums and reporting sites, and have done the following.

First, I checked in the directory C:\Program Files\PostgreSQL\14\doc\installation-notes.html to see the language version compatibility. For PostgreSQL 14 it is Python 3.9, and that is both the version installed by the language pack as well as the default on my system (separately installed from the Python website for previous projects). Originally the error was that a python3.dll module could not be found. I solved this by copying the python39.dll from the default language pack directory C:\edb\languagepack\v2\Python-3.9\python39.dll to C:\Windows\System32\ and that seemed to do the trick. However then came the current connection closed error message below (which keeps on reappearing even after you press Continue). I searched a bit online and tried to add the EDB Language Pack directory mentioned above for python to a new System Variable called PYTHONHOME to no avail. I also added the Language Pack directory and %PYTHONHOME% to my PATH variable. Again nothing. At this point I have probably spent too many hours debugging instead of SQL, and would appreciate any help from anyone who has successfully managed to use Python in their SQL system. Thanks again.

Please paste the code and error message here. It's OK to alter identifying info such as a folder name

The application has lost the database connection:
⁃ If the connection was idle it may have been forcibly disconnected.
⁃ The application server or database server may have been restarted.
⁃ The user session may have timed out.
Do you want to continue and establish a new session?

[anthonydb]

Hi, @robml,

EDB has changed how to its Python support is configured for Windows since the first edition arrived in 2018. Here are screen shots from the Python install instructions in the upcoming 2nd Edition of Practical SQL. Please review and adjust your system to match. Make sure you restart so the updates take effect.

Let me know how this works for you.

[robml]

You may just be my favorite author. I have seen how you respond to issues and thank you so much for your help. A question I have regarding this issue, is this required me to set a System wide variable and consequently overrun my previous Python environment and corresponding installations. Do I need to reinstall said packages from my previous version and if so, is there even a point in keeping a separate Python installation if I cannot seem to access it because the EDB Python module takes precedence in the PATH listing. It just seems like a hassle to have to remove the the EDB Python Path every time I want to do Python work unrelated to SQL and if my base Python version for most other development is different from the EDB version. Do you know a workaround for this by any chance?

[anthonydb]

You're very welcome. I'm always happy to hear from readers and glad to help!

I unfortunately do not know a workaround on Windows, and I agree that it is frustrating. I was teaching someone how to use Jupyter Notebooks the other day and had to do exactly what you describe -- move the EDB path lower in precedence than my regular Python installation.

On my Macbook, I get around this by running Postgres.app, which requires Python 3.8, and running pyenv to manage Python environments. It looks like there may be a fork of that for Windows that you could try.

Let's leave this issue open; perhaps another reader can offer a solution.