Postgres CRUD operations with Python Part 4: [Delete]

Hello there, hope the series is going quite well for you. If you have been stuck at any point or either [Disagree with any of the techniques used | have a better way | or simply wanna chat], feel free to drop a comment and am happily available for any of the above.

Now, so far, we have created a table, inserted a bunch of records and learned how we can update them. Now, what if we want to do away with a record. What if you are keeping a table with a record of all your pets and your cat eats your sandwich? She definitely doesn’t deserve to be on the pets table anymore and must be REMOVED with absolute haste!

Like all of the previous steps, this one too is super duper easy.

If you feel like it, I would challenge you to minimize this tab, check the previous class’ code and try to tweak it to do a delete instead of an update. You might surprise yourself.

As always we’ll begin by creating a new file pg_delete.py

Here, we’ll do something different and define a method. This method will encompass everything we do from here on.

My method is called deletecake and takes cake_id as the argument.

def deletecake(cake_id):

I then define the connection:

connection = psycopg2.connect(user = "postgres",
                                      password = "xxxxxxx",
                                      host = "127.0.0.1",
                                      port = "5432",
                                      database = "crudseries")
cursor = connection.cursor()

I would also like to see which record it is that I am deleting so I will print ount the record:

#view record before deleting
        print("Cake records")
        select = """SELECT * FROM stage1.cake_flavours
                                where cake_id = %s;"""
        cursor.execute(select, (cake_id, ))
        record = cursor.fetchone()
        print(record)

You probably have noticed that I am using %s in my queries. I am doing this when I am passing a parameter to my query. In this case, I will pass the cake_id when I call the method at the end.

I will then define my delete query and execute it:

#Delete the frigging cake
        delete = """Delete FROM stage1.cake_flavours
                    where cake_id = %s"""
        cursor.execute(delete, (cake_id, ))
        connection.commit()
        count = cursor.rowcount
        print(count, "cake has been successfully deleted")

Finally, I put everything inside the method and include my feedback statements to know if my code executes correctly and then call the method:

All code is available on this Github repo.