Python Psycopg2 cursor.execute returning None

Question

Hello I'm working on a script in Python that will connect to a db retrieve some information and send emails. I'have a problem with queries done with Psycopg.

I'would like to retrieve all the users where created_at = nb_days. My query work very good in navicat/pgadmin and I'have 53 records with the query :

select a.account_name, a.email, a.user_id, a.locale, a.firstname from v_accounts a where date(a.created_at) = date(current_date - interval '2 days') 

But when I execute my script I have None as result of the query. This is my script class :

import psycopg2

class MyDatabase(object):
    db_name='you'
    user='will'
    pw='never'
    host='know'
    port='it'

    def __init__(self, db=db_name, user=user, password=pw, host=host, port=port):
        """Connection to db - creation of the cursor"""
        try:
            self.baseDonn = psycopg2.connect(host=host, port=port, database=db, user=user,password=password)
        except Exception as err:
            print('La connexion avec la base de données à échoué : Erreur détéctée : %s' % err)
        else:
            self.cursor=self.baseDonn.cursor() # Création du curseur

    def get_data_from_accounts(self,nb_days):
        ''' Method that retrieves data from all accounts that were created nb_days before today '''
        sql="select a.account_name,u.email, u.user_id,u.locale, u.firstname  from accounts a inner join account_users au on a.account_id=au.account_id inner join users u on au.user_id=u.user_id where date(a.created_at) = date(current_date - interval '%s days');"
        print(sql)
        data=(nb_days,)
        try:
            records = self.cursor.execute(sql,data)
            print('cursor-execute={}'.format(records))
        except Exception as err:
            print("La requete n'est pas passée, erreur={}".format(err))
        else:
            return records

This is the main part

from my_db import MyDatabase
database=MyDatabase()

# On va récupérer les données des nouveaux accounts d'y a un jours
days_ago_2=database.get_data_from_accounts(2)

for personne_1 in days_ago_2:
    # data
    #account_name=personne_1[0]
    email=personne_1[1]
    user_id=personne_1[2]
    locale=personne_1[3]
    firstname='' if personne_1[4] is None else personne_1[4]

    language = locale.split('_')[1]
    activation_token= database.get_activation_token(user_id)

    subject = call_to_template2(firstname, language,activation_token)['subject']
    content = call_to_template2(firstname, language,activation_token)['content']

    print('EMAIL={} - ID={} -  LANGUE={} - FIRSTNAME={}'.format(email, user_id, language, firstname))
    #send_data(qui_envoie, email, subject, content, token, language)

And my error is on the line for personne_1 in days_ago_2: because None object is not iterable. And I've saw that the result of my query get_data_from_accounts(2) = None

Answer 1

cursor.execute allways returns None. You need to fetch the record set:

try:
    self.cursor.execute(sql,data)
    records = self.cursor.fetchall()