login with flask - unable to obtain data from Postgres using SELECT after an INSERT

Question

So I am trying to make an authentication system using OAuth, Flask and Postgres. I'm not too sure what is going on with my code. I created a class for the database which would house the connection and create cursors whenever a query is made. I also made a class for the user which would send queries to be executed on the database class.

This is what I am trying to do:

In the main application, we check if the user has been authenticated
If not, we go through the process of OAuth/OpenConnect
Once we get the credentials from the Google API, we check the database if there was anything stored with the id from the response with a method from the user class
If no id was found, we insert into the database using the user class method to make a query
Then we use flask-login methods, @login_manager.user_loader which then calls the user class method to SELECT from the database
Where my issue is, it appears that I am unable to retrieve the data

Some resources I have tried to follow to fix this problem:

Not sure what's wrong.. I have psql open on my terminal and when I run the SELECT, I do in fact see the entry with the correct id.. But my application isn't able to fetch the data.

db.py

import psycopg2 
from psycopg2 import Error

class Database:
    def __init__(self, db, user, password, port, host):
        self.db = db
        self.user = user
        self.password = password
        self.port = port 
        self.host = host

    def connect(self):
        self.connection = psycopg2.connect(
            database=self.db,
            user=self.user,
            password=self.password,
            port=self.port,
            host=self.host
        )

    def executeQuery(self, query, values=None):
        print('query:', query)
        print('values:', values)
        cursor = self.connection.cursor()
        cursor.execute(query, values)
        self.connection.commit()
        cursor.close()

    def close(self):
        self.connection.close()

user.py

from flask_login import UserMixin
from db import db

class User(UserMixin):
    def __init__(self, id, name, email, profile_pic):
        self.id = id
        self.name = name
        self.email = email
        self.profile_pic = profile_pic
    
    @staticmethod
    def get(user_id):
        user = db.executeQuery('SELECT * FROM users WHERE id=%s;', (user_id,))
        print('user:', user)
        if not user:
            return None
        current_user = user.fetchone()
        user = User(
            id = current_user[0],
            name = current_user[1],
            email = current_user[2],
            profile_pic = current_user[3]
        )
        return user

    @staticmethod
    def create(id, name, email, profile_pic):
        db.executeQuery(
            'INSERT INTO users (id, name, email, profile_pic) VALUES (%s,%s,%s,%s);', (id, name, email, profile_pic,)
        )

Documentation for Psycopg2:
https://www.psycopg.org/docs/usage.html

Maybe, my design for the database and user class is completely off. Feel free to let me know if I need to provide more information!

score 0 · Answer 1 · edited Sep 26 '20 at 00:57

0

I think the mistake is here

user = db.executeQuery('SELECT * FROM users WHERE id=%s;', (user_id,))

It should be

user = db.executeQuery('SELECT * FROM users WHERE id=(%s);' , (user_id))

edited Sep 26 '20 at 00:57

SecretAgentMan

2,756
6
18
38

answered Sep 26 '20 at 00:13

Abdullah Alessa

81
2

Thanks for the response. However, it is in the documentation for how to write queries: https://www.psycopg.org/docs/usage.html – jeff Sep 26 '20 at 00:15
that's mean the issue still on ? – Abdullah Alessa Sep 26 '20 at 00:16
Unfortunately, yes. :( The syntax is weird.. But I don't think that was the issue. Thanks though! – jeff Sep 26 '20 at 00:18
i edited the answer check it again please and tell me – Abdullah Alessa Sep 26 '20 at 00:18
I've used this: 'SELECT * FROM users WHERE id=%s;' on psql running on a separate terminal and I am able to fetch the results. My application however is not. I don't think that is a valid query string – jeff Sep 26 '20 at 00:22

score 0 · Answer 2 · edited Sep 26 '20 at 13:20

I realized that my previous database class used executeQuery for all types of queries. But I believe that may be the reason why I am getting wrong results. I found this link: Python MySQLdb - Connection in a class. I noticed that one person implemented separate methods for executing and fetching queries, so I thought I would give that a try and to my surprise, it worked! So I have refactored my code to look like this:

def fetch(self, query, values=None):
    cursor = self.connection.cursor()
    cursor.execute(query, values)
    result = cursor.fetchall()
    cursor.close()
    return result

def execute(self, query, values=None):
    cursor = self.connection.cursor()
    cursor.execute(query, values)
    self.connection.commit()
    cursor.close()

login with flask - unable to obtain data from Postgres using SELECT after an INSERT

2 Answers2