TypeError: 'GetAggregatedReport' object does not support indexing

user_11145822 0
Viewed 122

I'm executing one python script and im getting the below error while inserting the data to PostgreSQL database.Below is the database schema and sample query i use.

Any suggestions on how to deal with it ?

cur.execute(cmd, api_response)
TypeError: 'GetAggregatedReport' object does not support indexing

Also i would like to add the datetime to the this table, If i have a timestamp in the table schema, How can i insert the current time to the table and what change i need to do?

Below is the sample script.

import psycopg2

def db_connect():
 try:
     DSN = "dbname='postgres' user='postgres' host='localhost' password='postgres' port='5432'"
     conn = psycopg2.connect(DSN)
     return conn
 except (Exception, psycopg2.Error) as error:
     print('database connection failed')
     quit()

start_dt = date(2020, 10, 1)
end_dt =  datetime.date(datetime.now())

connection=db_connect()

cur = connection.cursor()

cmd="""INSERT into email_stats(raw_data) SELECT %s"""

for dt in daterange(start_dt, end_dt):
  start_date = dt.strftime("%Y-%m-%d")
  end_date = dt.strftime("%Y-%m-%d")

  try:
     api_response = api_instance.get_aggregated_smtp_report(start_date=start_date, end_date=end_date)
     #print(api_response)
     cur.execute(cmd, api_response)
   
connection.commit()

Table schema

CREATE TABLE  email_stats
(raw_data text COLLATE pg_catalog."default")
WITH (OIDS = FALSE)TABLESPACE pg_default;

Sample API response

Database connected...
Cursor initiated
Connection succesful
{'blocked': 0,
 'clicks': 0,
 'delivered': 140,
 'hard_bounces': 0,
 'invalid': 0,
 'opens': 7501,
 'range': '2020-10-29|2020-10-29',
 'requests': 4,
 'soft_bounces': 0,
 'spam_reports': 1,
 'unique_clicks': 0,
 'unique_opens': 3502,
 'unsubscribed': 9}
Traceback (most recent call last):
  File "/Users/user123/Documents/sb.py", line 52, in <module>
    cur.execute(cmd, api_response)
TypeError: 'GetAggregatedReport' object does not support indexing
>>>
python-3.x postgresql python
1 Answers

Problem

Psycopg2 is iterating through api_response, which is not an iterable type. Psycopg2 is throwing an error because it expects an iterable type, from which it extract SQL variables.

Solution

Replace:

cur.execute(cmd, api_response)

With:

cur.execute(cmd, (api_response,))

References

Passing parameters to psycopg2: https://www.psycopg.org/docs/usage.html#query-parameters

user_8068760
Related