Reputation: 15053
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
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
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