There are several similar issues like this scattered throughout the internet, but none that address my particular issue exactly.
Hopefully you folks can help!
Here is a small portion of my script:
import psycopg2 as p
import psycopg2.extras as e
# The 'AsIs' extension is here because I've attempted using it to fix my issue - with no luck unfortunately...
from psycopg2.extensions import AsIs
con = p.connect("dbname='my_db' user='user_name' host='123.45.67.89' port='5432'")
cur = con.cursor(cursor_factory=e.DictCursor)
query = "INSERT INTO happy_alerts_triggered (happy_affiliate_id, alert_format, alert_minutes, happy_client_internal_id, alert_triggered_date, alert_processed_date, alert_id, affiliate_name, client_name)
SELECT r.happy_affiliate_id, r.alert_format, r.alert_minutes, r.happy_client_internal_id, now() as alert_triggered_date, null as alert_processed_date, r.id, ha.happy_affiliate_description, hc.happy_client_description
FROM happy_alerts_config r
INNER JOIN happy_affiliates ha ON r.happy_affiliate_id = ha.id
INNER JOIN happy_clients hc ON r.happy_client_internal_id = hc.id
WHERE not exists
(SELECT 1 FROM "happyEvents" he
WHERE he."messageType" = r.alert_format
AND he."affiliateClient" = hc.happy_client_description
AND he."insertTime" > (now() - (r.alert_minutes * interval '1 minute')))"
cur.execute(query)
con.commit()
cur.close()
As you can see in the final SELECT statment, I am attempting to SELECT from a table with a name formatted in camelCase (which is not able to be changed :/).
I have tried the AsIs extension in a few different ways with no luck.
I have attempted parameterizing the camelCase variables, however this causes issues when attempting to do so with table names.
If I have researched correctly, parameterizing with 'AsIs' only fixes the camelCase issue when the parameters themselves are VALUES not tables/indexable items.
Lastly, the reason for this script is to UPDATE a table in my DB (with the query above), then use another query to return data from that table which will be used as information in an email generated within this same script.
If there are suggestions on how to run this query in other ways (i.e. within a bash script using psql commands, a node.js file, or any other file type that can be set to a crontab), I am open to suggestions. The query does not have to remain in this file, although I would like it to. (pgAgent is NOT a preferred method.)
I am running this on an Ubuntu 14.04 server.
Any help is appreciated! Thanks!
