Celeritas
Celeritas

Reputation: 15053

How to check if column exists in a table, and not to insert into it if it doesn't

I'm trying to make a program not crash when it attempts to insert a value into a column that doesn't exist (the reason this happens in the first place is because the column names that it inserts into are coming from values red in another table which may contain typos).

If a column doesn't exist, how do I skip the INSERT?

for row in cur2:
            if row[1] > specialLimit:
               try:
                 cur.execute("INSERT INTO {}.{} (item_id) VALUES ('{}')".format(schema, table, row[0]))
                 con.commit()
               except psycopg2.DatabaseError as e:
                 print('Error: '+e)

This still gives the error:

LINE 1: INSERT INTO load_primary.item_properties (exotic) VALUES (...

Upvotes: 2

Views: 4846

Answers (2)

Chris Johnson
Chris Johnson

Reputation: 21956

The most pythonic approach is to not ask for permission -- execute the insert in a try/except block; inspect the exception to understand what exception was raised; and if it was a missing column, skip over that insert, log the error, or whatever you need to do.

Upvotes: 1

Vor
Vor

Reputation: 35129

You can run a simple query before making request:

select exists (
  select 1 from information_schema.columns 
  where table_name ='my_table' 
  and column_name='my_column'
);

This would return boolean and psycopg2 will automatically convert it ot python's boolean.

An alternative way would be to run a single query to check all the columns at once. And insert data only to those that exist. This mght be slightly more efficient.

Upvotes: 7

Related Questions