1

I'm creating a simple app to connect to a postgresql database, query it to list all the tables, and query each of the table to output some information. My code is running but I need to fix some issues of the part below. df_dbtables is my pandas dataframe of db schemas and tables.

for index, row in df_dbtables.iterrows():
    try:
        schema_table = row['schema'] + "." + row['table']
        cur.execute("SELECT type,stylename FROM %s" % schema_table)
        rows = cur.fetchall()
        for row in rows:
            data.append({"Type" : row[0], "Stylename" : row[1]})
    except:
        continue

Issue #1: My first table runs perfectly. But the second table doesn't have a type field, so It runs into this postgresql error: psycopg2.errors.UndefinedColumn: column "type" does not exist Then, the code runs into the except that tells the code to continue. The problem is that after the first time running into the except, all my others table queries run into the except too, ignoring that they have type and stylename fields. How can I properly ignore this error message and continue to the next iteration? Also, what is the best way to output SQL errors using try/except?

Issue #2: Once fixed the above issue I would like to know how can I prevent this: If a field doesn't exists and run into a SQL error, it will ignore the other field (if it exists) because it will run into the except. For example: The script is querying table X, that is a table without type field, when it runs into the except It will ignore its stylenames data.

Improvement: I've tried many ways to parameterize the sql query. I know that way that I used is very prune to SQL injections, but the correct ways just don't work.

Tried these methods and others but couldn't run them successfully.

APP My next step is creating a Flask app for this code. So, if you have a solution that uses Flask it will be welcome.

Code updated at July 12th. But still with the same issues:

for index, row in df_dbtables.iterrows():
    try:
        schema_table = row['schema'] + "." + row['table']
        cur = con.cursor()
        cur.execute("SELECT type,stylename FROM %s" % schema_table)
        for r in rs:
            data.append({"Type" : r[0], "Stylename" : r[1]})
    #except psycopg2.OperationalError: traceback.print_exc()
    except: continue
Ualas Rohrer
  • 67
  • 7
  • *Never* write `except: continue`. It will mask all sorts of errors that you want to know about until your program is complete. Do this instead: `except psycopg2.OperationalError: traceback.print_exc()`. The problem you report is probably that after the exception, the cursor is in an error state and reusing it doesn't work. Actually printing the error instead of suppressing it will show this. – BoarGules Jul 10 '19 at 22:06
  • @BoarGules thanks for your comment. Adding your code shows me the error. `psycopg2.errors.UndefinedColumn: column "type" does not exist` for the second iteration (same as stated above) and then it stops running. How can I proceed? – Ualas Rohrer Jul 10 '19 at 22:22
  • Create a fresh cursor for each `fetchall` instead of trying to reuse it. Depending on the dams, curators get used up. – BoarGules Jul 11 '19 at 06:35
  • @BoarGules I've added a cur = con.cursor(), but nothing has changed, do you have any suggestion? . Updated code in the main thread. – Ualas Rohrer Jul 12 '19 at 15:07

0 Answers0