2

I've seen many solutions about this problem but any of them solved mine.

I've this INSERT query which is wrapped in a try/except and, although it works fine in the database (the data is properly inserted), a Traceback is called, the code jumps to the except and the code below the query execution is just ignored (which cannnot happen in my case).

It is something like this:

query = """
  INSERT INTO
    mytable(user_id, tcx_extension, user_email, created_date)
  VALUES
    {}
""".format("('12345678910', '6666', 'test123@test.com', '2020/01/06 00:00:00')")

self.pgsql.execute_query(query)  // the error is triggered here

//important code below that is ignored when the error fires

the self.pgsql.execute_query function is shown below:

def execute_query(self, query, retry=0, format=True):
    if self.pool is None:
        self._open_pool(PGSQL["connection_string"])

    try:
        conn = self.pool.getconn()

        cur = conn.cursor()
        conn.autocommit = True

        LOG.info("Executing query", extra={"query": query})
        cur.execute(query)

        if format is True:
            data = Formatter.format_cursor(cur)
        else:
            data = None
        self.pool.putconn(conn)
        cur.close()

   except pool.PoolError as e:
        LOG.error("PoolError: {}".format(e), extra={"query": query})
        if retry > MAX_RETRIES:
            raise TooManyRetries("Too many retries: {}".format(e))

        LOG.info("Will retry: {}".format(e), extra={"query": query})
        time.sleep(1)
        return self.execute_query(query, retry + 1)

    return data

The Traceback is shown below:

[2021-01-06 15:28:15,421] ERROR in app: Exception on /members/manage_socket_members [POST]
Traceback (most recent call last):
  File "C:\Users\artur.santos\AppData\Local\Programs\Python\Python37\lib\site-packages\flask\app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\artur.santos\AppData\Local\Programs\Python\Python37\lib\site-packages\flask\app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:\Users\artur.santos\AppData\Local\Programs\Python\Python37\lib\site-packages\flask_restful\__init__.py", line 480, in wrapper
    resp = resource(*args, **kwargs)
  File "C:\Users\artur.santos\AppData\Local\Programs\Python\Python37\lib\site-packages\flask\views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "C:\Users\artur.santos\AppData\Local\Programs\Python\Python37\lib\site-packages\flask_restful\__init__.py", line 595, in dispatch_request
    resp = meth(*args, **kwargs)
  File "C:\Users\artur.santos\Documents\#salesforce\rc-analytics-api\src\decorators\auth.py", line 70, in wrap
    return f(*args, **kwargs)
  File "C:\Users\artur.santos\Documents\#salesforce\rc-analytics-api\src\members\manage_socket_members.py", line 30, in post
    return abort(500, error)
  File "C:\Users\artur.santos\AppData\Local\Programs\Python\Python37\lib\site-packages\werkzeug\exceptions.py", line 707, in abort
    return _aborter(status, *args, **kwargs)
  File "C:\Users\artur.santos\AppData\Local\Programs\Python\Python37\lib\site-packages\werkzeug\exceptions.py", line 687, in __call__
    raise self.mapping[code](*args, **kwargs)
werkzeug.exceptions.InternalServerError: 500 Internal Server Error: 'NoneType' object is not iterable

Any ideas on how to solve this?

EDIT 1: All the table fields are of type 'VARCHAR' except for the created_date.

EDIT 2: When I try to insert some values between double quotes the error changes (which is also a nonsense error for me). I don't know if gets me closer to the solution. Check it out the logs I printed

> query: INSERT INTO wfm.socket_user_relation (user_id, tcx_extension, user_email, created_date) VALUES (12345678937, 63, "test37@test.com.br", "2021/01/07 09:25:35")
> exception: column "test37@test.com.br" does not exist
LINE 1: ...ser_email, created_date) VALUES (12345678937, 63, "test37@te...
                                                             ^

EDIT 3: If I try to INSERT all of them double quoted, the error remains the same: Error: 500 Internal Server Error: 'NoneType' object is not iterable

Artur Freire
  • 21
  • 4
  • Can you print out the value of "data-to-fill-the-fields"? And also where do you close the connection? – demokritos Jan 06 '21 at 19:49
  • 1
    @demokritos the post was updated with the required information. The connection is closed with the return statement outside of the try/except. – Artur Freire Jan 06 '21 at 20:03
  • Change the formatting from `.format("('12345678910', '6666', 'test123@test.com', '2020/01/06 00:00:00')")` to `.format('12345678910', '6666', 'test123@test.com', '2020/01/06 00:00:00')` – John Gardounis Jan 07 '21 at 14:28
  • It didn't work, @JohnGardounis. Check out the "EDIT: 2" log I printed: the query is exactly what I expect it to be. It **DOES INSERT** the data in the database but I cannot get any response of success from it, I just know it because I have a GET route that selects all from that table. The not iterable error just skips every code that is after the line that executes the query. – Artur Freire Jan 07 '21 at 17:25
  • The error is on the `user_email` column, what is the data type on the database? – demokritos Jan 07 '21 at 20:54
  • @demokritos the data type is mentioned in the first "EDIT 1": all of them are of type `VARCHAR` except for the `created_date`. – Artur Freire Jan 08 '21 at 14:13
  • Can you try converting the last value to datetime type? `datetime.now()` Also, have you checked out https://stackoverflow.com/questions/10501521/column-not-allowed-here-error-in-insert-statement? It may give you an idea... – demokritos Jan 08 '21 at 14:52

1 Answers1

0

You are not setting the format parameter when executing the function and therefore it's always True (as you stablished in the function definition).

The response when you successfully insert something in the PgSQL DB is None and it's not possible to String format a NoneType Object.

Just use the code above and you will be fine.

self.pgsql.execute_query(query, format=False).
Artur Freire
  • 21
  • 4