16

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

1
  • 1
    Your sql string formating seems weird to me. What does "print(sql)" gives you as output ? Also, you should give a more precise title if you want to get answers from people who had once the same issue. Your title is too vast. Commented Jun 22, 2016 at 10:31

1 Answer 1

26

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

try:
    self.cursor.execute(sql,data)
    records = self.cursor.fetchall()
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you ! Sorry for that noobs question I've completely forgot about that "fetch" ((
Thank you for your answer! Btw how do I print out the column headers?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.