Use Pause in Python for Postgres

Introduction

In this tutorial we’ll address a few ways you may want to pause with Python and Postgres. The three kinds of pause we will address here are (a) pause to wait for user input, (b) using “continue” in a for loop, and (c) pause to wait for database response using time.sleep.

Wait for user input

Using Python’s input() function, we will explore its way of pausing to wait for the user to answer a question displayed in a pop-up on their screen.

Continue a For loop

Another way to influence program flow is to selectively use the continue statement to skip parts of a loop.

Wait for database response

Finally, we will learn how to use the sleep() function to wait for PostgreSQL to accomplish some task.

Prerequisites

Following are some functionalities that will aid you in the lessons in this tutorial.

Python scripting

We used Python 3.8 for this article, which is the latest as of the time when we wrote this. You don’t have to use the latest for this work, but if you want to, you can get the latest Python environment here for free.

SQL scripting

Understanding of the basics of writing Postgres queries with SQL. We use dBeaver’s free database management software for error checking, greater efficiency, and ease than is afforded by the PG Admin database management tool.

Recordsets

The basics of what a recordset is in Postgres, otherwise known as “record set”, “dataset”, and “data set”. Basically a set of data like you see in a spreadsheet, with rows and columns.

Naming conventions

• On naming conventions showing how and why you may want to prefix variables, column names, table names, etc. as you see done in this article. For example, naming “tvariable” with the “t” prefix to define it as a “text” (string) object and “tbl_” before table names in order to clearly distinguish those objects as tables. The tutorial goes a bit deeper, as well, talking about how to name variables based on a hierarchy system.

Pause for user input

As a developer, there will be times when you want to pause your program to wait for user input; that is you want to put an input box up in front of the user and wait for their response. In order to accomplish this in Python, we use the input function.

Syntax for user input

Example of user input

Analysis

The above Python code causes an “input box” to pop up in front of the user using the input() function. The box in this case causes the question “What is your name?” to be shown to the user. Program execution pauses until the user has entered an answer. Once the user submits their answer, that string (text) is stored in the “t_answer” variable. The next line of code uses the print() function to output the contents of “t_answer”.

Continue a For loop

Another way we might think about pausing in Python is to use the “Continue” command to skip parts of a loop while staying in the loop. This shifts the execution path back to the top of the loop.

Example of using Continue

Results

Analysis

As you can see here, use of “continue” shifted program flow when i is equal to 2 or 4, effectively skipping the print() function in both those instances by returning to the top of the for loop.

Another situation we may encounter includes various reasons for wanting to pause to wait for a response from your PostgreSQL database.

Pause for database response

Let’s say you have some blocks of code that access data in ways where you need to pause your Python briefly in order to wait a few seconds for Postgres to catch up. Maybe your PostgreSQL server is slow. Maybe bandwidth is an issue. In this case, we will utilize Python’s sleep() function, which is part of the “time” library.

Syntax of sleep function

Example of Python sleep function

Analysis

As you can see by the “import time” statement above, the sleep function requires the “time” library be imported in Python. Then the “time.sleep(3.8)” causes Python to pause for 3.8 seconds.

Example of sleep for Postgres

Since the code to pause with Python for a Postgres insert will be a bit lengthy because we want to include all the preparation you will need, we’ll break this part down into easy-to-digest pieces:

Libraries to import

Analysis

• import time: We need the “time” library in order to use the sleep function, crucial to the “pause” in our Python application.
• import psycopg2: This library supplies a method for connecting to, pulling data from, and sending data to a Postgres database. Today we will be doing an SQL INSERT of data the user submitted in the HTML form we built above.

Connect to Postgres database

Analysis

Use the above code as a template to plug in your own credentials like host, which might be as simple as “localhost” or the IP address of your PostgreSQL server; port, where the default for Postgres is 5432; the name of your database in Postgres; the name of the user who has permissions to read and modify the database; and their password. Also, we have used the “connect” function of the psycopg2 library in order to set up a database connection so we can then use the cursor() function to create our database cursor that we’ll use later.

Write SQL for Postgres

Now that we have a database cursor, we can write some SQL to execute using that cursor.

Analysis

The above query fills two columns in our Postgres table using the INSERT SQL statement. We have also parameterized this query in order to maintain a certain level of application security, while using the cursor we created earlier to execute the SQL.

Finally, it’s time to use pause in our Python code.

Sleep for Postgres

Analysis

As we’ve shown you above, the sleep function – requiring import of the “time” library – allows us to pause Python for whatever number of seconds we want.

Now that we have studied each piece of the overall code puzzle, let’s look at it all put together:

Source code to pause for Postgres

Conclusion

In this tutorial we learned a few ways we may want to use a pause in Python with a Postgres example included. We investigated three ways a developer may want to pause or shift program flow in Postgres, including pause for user input (or you might say wait for user input), use of “continue” to redirect flow in a for loop, and the sleep function to pause for the database to get something done. Finally, we provided analyzed and commented source code throughout the tutorial.