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
1 | response = input(optional title) |
Example of user input
1 2 | t_answer = input("What is your answer?") print (t_answer) |
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
1 2 3 4 | for i in range(6): if (i == 2 or i==4): continue print(i) |
Results
1 2 3 4 | 0 1 3 5 |
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
1 | sleep(seconds) |
Example of Python sleep function
1 2 | import time time.sleep(3.8) |
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
1 2 | import time import psycopg2 |
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
1 2 3 4 5 6 7 | t_host = "PostgreSQL database host address" t_port = "5432" t_dbname = "database name" t_user = "database user name" t_pw = "password" db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw) db_cursor = db_conn.cursor() |
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.
1 2 3 4 5 6 7 8 9 10 | s = "" s += "INSERT INTO tbl_products" s += "(" s += "ID_product" s += ", t_name_product" s += ") VALUES (" s += "(%ID_product)" s += ", '(%t_name_product)'" s += ")" db_cursor.execute(s, [ID_product, t_name_product]) |
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
1 | time.sleep(3.8) |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | import time # Python library for the sleep function. import psycopg2 # Python library for database connection # connect to the Postgres database t_host = "PostgreSQL database host address" # this will be either "localhost", a domain name, or an IP address. t_port = "5432" # default port for postgres server t_dbname = "database name" t_user = "database user name" t_pw = "password" db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw) db_cursor = db_conn.cursor() @app.route("/addProduct") def addProduct(): # create SQL for Postgres s = "" s += "INSERT INTO tbl_products" s += "(" s += "ID_product" s += ", t_name_product" s += ") VALUES (" s += "(%ID_product)" s += ", '(%t_name_product)'" s += ")" # execute the SQL above db_cursor.execute(s, [ID_product, t_name_product]) # pause 3.8 seconds time.sleep(3.8) # do other things now |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started