Today’s blog post will show an example of how to delete an existing record from a PostgreSQL database utilizing Pyscopg2, 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:
\dt command on the
as_built2 database, we can see we have four tables here:
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
gps_shot number is a
foreign key that references back to the
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
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’.
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 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:
cur parameter accepts an active psycopg2 cursor object to the database.
con parameter accepts an active psycopg2 connection to the database. (Cursor and connection Python code creation is not shown here.)
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
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
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). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
Updated on 10/18/17