python psycopg2 passes db connection and nothing happens

Question

I need your help. I have a problem connecting to PostgreSQL via Pycharm. First I connect to vpn:

    def run_openvpn(config_path):
        try:
            subprocess.run(['openvpn', '--config', config_path])
            print('OpenVPN connection established.')
        except FileNotFoundError:
            print("OpenVPN executable not found. Please ensure it is installed and in your system's PATH.")

        except subprocess.CalledProcessError as e:
            return f"OpenVPN connection failed with error: {e}"

It is followed by a function that sends a request:

    def execute_sql_request(host, port, database, user, password, sql_query):
        try:
            # Connect to the PostgreSQL database
            connection = psycopg2.connect(
                host=host,
                port=port,
                database=database,
                user=user,
                password=password
            )
        
            # Create a cursor to execute SQL queries
            cursor = connection.cursor()

            formatted_query = cursor.mogrify(sql_query)
            print("Formatted SQL Query:", formatted_query.decode('utf-8'))

            # Execute the SQL query
            cursor.execute(sql_query)

            # Fetch all the results
            results = cursor.fetchall()

            # Close the cursor and the database connection
            connection.commit()
            cursor.close()
            connection.close()

            return results  # Return the result of the SQL request as a list of tuples

        except psycopg2.Error as e:
            error_msg = str(e)
            return error_msg  # Return the error message if there was a problem

I run the code and get messages about successful vpn initialization, but nothing else happens. I don't get any error messages, I don't get values from the database. Please help me understand why the execute_sql_request function is not executing

I tried to add a display of some information to the execute_sql_request function in order to understand whether the function is being executed or it is falling. But I didn’t receive anything, so I can conclude that there is not even a function call

this code runs my functions

def test_execute_sql_query(browser)
    sql.run_openvpn(config_path=**)
    sql.execute_sql_request(host=**, port=**, database=**, user=**, password=**, sql_query=**)
    sql.stop_openvpn()

'sql' is a page that stores functions that work with SQL ' stop_openvpn' function to close connection with vpn. She works right

1) You need to show the code that actually calls `execute_sql_request()`. 2) Look at the Postgres log to see what is hitting the database. — Adrian Klaver, Aug 18 '23 at 17:28
Calling a function is very simple. First comes run_openvpn indicating where the .opvn file is located, and after it comes the execute_sql_request function indicating its host, port, database etc attributes — H8Umate, Aug 18 '23 at 22:07
Yes but something is doing `execute_sql_request(*parameters)`, that is what you need to add to your question. You can't expect an answer to *...nothing else happens* without providing the code that actually runs the function. — Adrian Klaver, Aug 18 '23 at 22:08
1) This `sql.execute_sql_request(...)` is not assigning the `return results` to anything, so where are you expecting to see them? 2) Again, look at the Postgres log to see if you are actually connecting to the database. — Adrian Klaver, Aug 19 '23 at 15:12

score 0 · Accepted Answer · answered Aug 23 '23 at 09:39

0

If someone faced the same problem. I found a solution to the problem: The problem turned out to be very simple, it was that the function that opened the connection to openvpn was blocking the call of other functions. Therefore, I combined the run_openvpn and execute_sql_request functions into one function. Now python, using the threading module, simultaneously establishes a vpn connection and sends an sql query to the database

answered Aug 23 '23 at 09:39

H8Umate

1
2

1

I am glad you solved your problem and I am happy you post the answer here, but the solution seems suboptimal. If I understand correctly, the problem is that `subprocess.run` does not return. This is an intentional behaviour of `run`. Instead of using a python-thread to perform the call, you should use another subprocess-function like `Popen` that does not block until execution finished. See here: https://stackoverflow.com/a/16071877/7465516 – julaine Aug 23 '23 at 12:06
Hello. Thank you very much for your comment and advice. When looking for a solution, I did not find information about Popen. I will study it and implement it in my code. Thank you – H8Umate Aug 24 '23 at 13:33

python psycopg2 passes db connection and nothing happens

1 Answers1