Unable to INSERT/UPDATE to Postgres Database on Heroku

[99nsx]

After completing the CS50 Finance assignment, I attempted to move the application to Heroku. I converted the SQLite3 database over to Postgres. I am able to connect to the Heroku database using:
from CS50 import SQL
db = SQL("Heroku---URI")

SELECT statements do work and does retrieve the requested data. However, INSERT and UPDATE statements did not work for me. I was able to INSERT and UPDATE to the database using my computer's command line, as well as from pgAdmin 4.

Finally, I was able to get my CS50 Finance working by importing the sqlalchemy module instead, and adding .fetchall() to the end of SELECT statements and after INSERT/UPDATE statements I added the db.commit() statement in a line below it to save the changes.

I didn't see any additional notes (such as .commit()) in the documentation on how to get the CS50 module to work with a Postgres database. Sorry in advance if there's something that I missed. CS50 is such a tremendous class by the way, it's absolutely brilliant! 👍

[eddyharrington]

I'm also affected by the issue described by @99nsx. The CS50 logger showed successful/green INSERTS/UPDATES/DELETES for my postgres database but those transactions were never actually committed or written to the database even though it works fine with SQLite. The same behavior occurred on a local postgres database.

My fix: used SQL Alchemy and re-wrote all of my SQL statements. One tip to others who run into this is to make a helper function that does what line 328 - 343 does in the CS50 SQL library so that you get a list of dictionary objects with column names as the keys (i.e. the same behavior you're use to with CS50s library). Copy/pasting my helper function for clarity:

# Used for SQL SELECT .fetchall() results
def convertSQLToDict(listOfRowProxy):
    # Coerce types
    rows = [dict(row) for row in listOfRowProxy]
    for row in rows:
        for column in row:

            # Coerce decimal.Decimal objects to float objects
            # https://groups.google.com/d/msg/sqlalchemy/0qXMYJvq8SA/oqtvMD9Uw-kJ
            if type(row[column]) is decimal.Decimal:
                row[column] = float(row[column])

            # Coerce memoryview objects (as from PostgreSQL's bytea columns) to bytes
            elif type(row[column]) is memoryview:
                row[column] = bytes(row[column])

    return rows

[dmalan]

Ah, sorry about that. We're going to try to fix this one asap!

[dmalan]

Fixed by #122.