Today’s blog post will show an example of how to delete an existing record from a PostgreSQL database utilizing Psycopg2, a feature-rich Python library, that offers a tremendous amount of functionality for working with Python and PostgreSQL databases.
I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus), Python 3, PostgreSQL 9.6.2 and psycopg2 version 2.7.1 for these exercises.
Note: All data, names or naming found within the database and Python code presented in this post, are strictly used for practice, instruction and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
The example data found in our PostgreSQL database will depict typical pipeline survey as-built attributes and data.
First, we will get a list of tables in our database using the PostgreSQL command line utility, psql from the Linux command line:
Issuing the\dt command on the as_built2 database, we can see we have four tables here: attributes, bend, combo_bendand weld.
Querying the attributes and the bend tables with SELECT * FROM PostgreSQL statements, we can see both tables have mock data already present within them:
Also notice here with these two queries, both tables share a ‘common’ value. The gps_shot column is present in both tables. This number is used to ‘link’ both tables together. The bend table’s gps_shot number is a foreign key that references back to the attributes table’s gps_shot column helping to ensure some data integrity between the two tables since this data is divided and stored separately. Utilizing this foreign key, we can use a PostgreSQL INNER JOIN command to join the associated and correct data from both of these tables, into a single unified structure to view:
Here we can see all the attributes from both tables, combined into a single row per record. We must also be explicit with this command and let PostgreSQL know exactly which gps_shot we want to present since this value is found in multiple tables. To accomplish this and avoid errors, we use attributes.gps_shot (table_name.column_name) to let PostgreSQL know we mean the gps_shot from the attributes table.
Note how the whole_station and the offset_station columns from the attributes table have been concatenated here with ||'+'|| into one column temporarily named station (using PostgreSQL’s AS keyword). The PostgreSQL concatenation operator || is used to concatenate (join, or glue) strings together, but can be used with non-string values as well. Joining the integer value of the whole_station column, the string value of the plus or addition character “+”, and the real (decimal) value of the offset_station column gives us a complete Linear Referencing station number for ‘demonstration and viewing purposes’.
Retyping this INNER JOIN query multiple times daily would become old, cumbersome and monotonous. Luckily we can use a PostgreSQL CREATE VIEW command that will make a VIEW we can use anytime we would want to look at these specific values of these two joined tables in the future. Here we will name our VIEW bend_view.
With the bend_view VIEW stored in the database, we can then use it to query the database for this same joined view of attributes as before, however without typing the previous long INNER JOIN query:
Now that we know what data is present and linked together from these two tables, let’s use Python and psycopg2 to delete the record that has the gps_shot number 4587.
I have created a Python function remove_record() that takes three parameters to use for this purpose:
The cur parameter accepts an active psycopg2 cursor object to the database.
The con parameter accepts an active psycopg2 connection to the database. (Cursor and connection Python code creation is not shown here.)
The gps_shot parameter is a user-supplied gps shot number of the record the user wishes to delete.
Using the psycopg2 execute() method (as shown above) and Python, we can issue SQL commands with parameters, to perform operations on our database in the backend. However, we must be careful here, passing in user-captured input directly into a SQL statement and use some best practices to guard against possible SQL Injection attacks on our database server. We should never use Python string concatenation or string parameters interpolation to pass variables to a SQL query string. As per the psycopg2 docs, we should utilize the second parameter of the execute() method. One way to accomplish this is by passing in a single-element Python tuple named here as point_to_delete, that contains, the gps_shot number of the record we are deleting, instead of directly passing in the user-supplied gps_shot number. The remove_sql variable contains the PostgreSQL command in a Python string, we want to execute, which is passed in as the first argument to the psycopg2 execute() method. The positional placeholder %s will contain the value (4587) of the point_to_delete variable.
Using our function is as simple as collecting input from the user for the gps shot number of the record to delete and calling the remove_record() function with the correct parameters passed in. (Note: The entire Python program not shown below)
Using Python’s built-in input()function we collect the number from the user then cast its value to type integer with another built-in Python function, int()(Integer is the data type for the gps_shot column in our database) and assign it to the gps_to_delete variable. Then passing in that gps_to_delete variable along with the active cursor and connection objects to the remove_record() function executes the operation. (Note: the db_manager. Python code from the snippet above is used to call the remove_record() function in the main Python module, from another python module where it is contained.) The below snippet shows what the command-line interaction with the user would look like, while the above code snippet is executed behind the scenes, along with the complete Python program (not shown).
Entering 4587, we are presented with a warning from the remove_record() function, informing us that this operation will completely remove the record from the database. Once yes is entered confirming that we wish to proceed, we are notified that the record has been deleted from the database.
To confirm this, using our bend_view VIEW, we can now query the database and clearly see that the record, along with all attributes belonging to gps_shot 4587, has been deleted.
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
Updated on 12/17/17
07/08/18
2 thoughts on “Delete existing record from PostgreSQL database with Python and Psycopg2.”