CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
amitchone
amitchone

Reputation: 1638

Python3.6 function not called

I'm developing a REST API in Python 3.6 using Flask-Rebar and PostgreSQL and am having trouble trying to execute some queries simultaneously using psycopg2.

More specifically, I execute a query and require the id value from this query for use in the next query. The first query successfully returns the expected value, however the function that calls the subsequent query doesn't even execute.

Here is the function responsible for calling the query function:

psql = PostgresHandler()

user_ids = [1, 5, 9]
horse = {"name": "Adam", "age": 400}

def createHorseQuery(user_ids, horse):
    time_created = strftime("%Y-%m-%dT%H:%M:%SZ")

    fields, values = list(), list()

    for key, val in horse.items():
        fields.append(key)
        values.append(val)

    fields.append('time_created')
    values.append(time_created)

    fields = str(fields).replace('[', '(').replace(']', ')').replace("'", "")
    values = str(values).replace('[', '(').replace(']', ')')

    create_horse_query = f"INSERT INTO horse {fields} VALUES {values} RETURNING horse_id;"

    horse_id = None
    for h_id in psql.queryDatabase(create_horse_query, returnInsert=True):
        horse_id = h_id

    link_user_query = ''

    for u_id in user_ids:
        link_user_query += f"INSERT INTO user_to_horse (user_id, horse_id) VALUES ({u_id}, {horse_id['horse_id']});"

    psql.queryDatabase(link_user_query)

    return horse_id, 201

Here is the PostgresHandler() class that contains the function queryDatabase:

class PostgresHandler(object):
    def __init__(self):
        self.connectToDatabase()

    def connectToDatabase(self):
        self.connection = psycopg2.connect(
            host        = '...',
            user        = '...',
            password    = '...',
            database    = '...'
        )

    def queryDatabase(self, query, returnInsert=False):
        cursor = self.connection.cursor(cursor_factory=RealDictCursor)

        cursor.execute(query)

        if "SELECT" in query.upper():
            for result in cursor.fetchall():
                yield result
        elif "INSERT" in query.upper():
            if returnInsert:
                for result in cursor.fetchall():
                    yield result

            self.connection.commit()

        cursor.close()

I can verify that the psql.queryDatabase(create_horse_query, returnInsert=True) operation is successful by querying the database manually and comparing against the return value, h_id.

I can verify that link_user_query is created and contains the user_ids and horse_id as expected by printing. I know the query that's generated is okay as I have tested this manually in the database.

It appears that the function called on the line psql.queryDatabase(link_user_query) is never actually called as a print statement at the very top of the queryDatabase function does not get executed.

I've tried with delays between the two query function calls, initialising a new connection with each function call and many other things to no avail and I am absolutely stumped. Any insight is greatly appreciated.

EDIT: FYI, The createHorseQuery function returns successfully and displays the two returned values as expected.

Upvotes: 1

Views: 87

Answers (1)

Rob Bricheno
Rob Bricheno

Reputation: 4653

queryDatabase in your code is a generator because it contains a yield statement. The generator only actually does things when you iterate over it (i.e. cause __next__() to be called). Consider the following:

def gen():
    print("Gen is running!")
    yield "Gen yielded: hello"
    print("Gen did: commit")

print("***Doing stuff with b***")
b = gen()
for a in b:
    print(a)

print("***Doing stuff with c***")
c = gen()

print("***Done***")

Output is:

***Doing stuff with b***
Gen is running!
Gen yielded: hello
Gen did: commit
***Doing stuff with c***
***Done***

When we called gen() to create c we didn't actually run it, we just instantiated it as a generator.

We could force it to run by calling __next__() on it a bunch of times:

c.__next__()
try:
    c.__next__()
except StopIteration:
    print("Iteration is over!")

outputs:

Gen is running!
Gen did: commit
Iteration is over!

But really, you should probably not use a generator like this where you are never intending to yield from it. You could consider adding a new function which is not a generator called insertSilently (or similar).

Upvotes: 1

Related Questions