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
Allows Python code to execute PostgreSQL command in a database session. Cursors are created by the connection. cursor() method: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection.
execute() Method. This method executes the given database operation (query or command). The parameters found in the tuple or dictionary params are bound to the variables in the operation.
The Cursor class of the psycopg library provide methods to execute the PostgreSQL commands in the database using python code. Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures. You can create Cursor object using the cursor() method of the Connection object/class.
cursor.execute
allways returns None
. You need to fetch
the record set:
try:
self.cursor.execute(sql,data)
records = self.cursor.fetchall()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With