Python Psycopg2 cursor.execute 返回 None

Question

您好，我正在用 Python 编写一个脚本，它将连接到数据库以检索一些信息并发送电子邮件。我对使用 Psycopg 完成的查询有疑问。

我想检索 created_at = nb_days 的所有用户。我的查询在 navicat/pgadmin 中工作得很好，我有 53 条查询记录:

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') 

但是当我执行我的脚本时，查询结果是 None。这是我的脚本 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

这是主要部分

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)

我的错误是在行 for personne_1 in days_ago_2: 因为 None object is not iterable。我已经看到我的查询结果 get_data_from_accounts(2) = None

Answer 1

cursor.execute总是返回 None。你需要 fetch记录集:

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