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. 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. The gps_shot parameter is a user-supplied gps shot number of the record the user wishes to delete.
(Cursor and connection Python code creation is not shown here.) The con parameter accepts an active psycopg2 connection to the database. The cur parameter accepts an active psycopg2 cursor object to the database.
I have created a Python function remove_record ( ) that takes three parameters to use for this purpose: 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. 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: 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. Retyping this INNER JOIN query multiple times daily would become old, cumbersome and monotonous.
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’. The PostgreSQL concatenation operator || is used to concatenate (join, or glue) strings together, but can be used with non-string values as well. 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). To accomplish this and avoid errors, we use attributes.gps_shot (table_lumn_name) to let PostgreSQL know we mean the gps_shot from the attributes table. 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. Here we can see all the attributes from both tables, combined into a single row per record. 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: 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. This number is used to ‘link’ both tables together. The gps_shot column is present in both tables. Querying the attributes and the bend tables with SELECT * FROM PostgreSQL statements, we can see both tables have mock data already present within them:Īlso notice here with these two queries, both tables share a ‘common’ value. Issuing the \dt command on the as_built2 database, we can see we have four tables here: attributes, bend, combo_bendand weld. The example data found in our PostgreSQL database will depict typical pipeline survey as-built attributes and data.įirst, we will get a list of tables in our database using the PostgreSQL command line utility, psql from the Linux command line: It by no means depicts actual data belonging to or being used by any party or organization. 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. 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.
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.