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.
flask-sqlalchemyor some other higher level library.flask-sqlalchemy; anything to not have this horrible string manipulation!if "SELECT" in query.upper():would be triggered. Then you have to do all kinds of magic to see if a horse contains any SQL terms or punctuation etc. SQLalchemy takes a bit of time to learn, but it saves you much time, bugs, and security problems in the end.