Skip to content
Related Articles

Related Articles

Save Article
Improve Article
Save Article
Like Article

Python PostgreSQL – Limit Clause

  • Difficulty Level : Medium
  • Last Updated : 23 Aug, 2021

In this article, we are going to see how to use the limit clause in PostgreSQL using pyscopg2 module in Python. In PostgreSQL LIMIT constraints the number of rows returned by the query. By default, It is used to display some specific number of rows from the top. If we want to skip a number of rows before returning the rows then we use the OFFSET clause after the  LIMIT clause.

Syntax without using OFFSET: SELECT select_list FROM table_name LIMIT no_of_rows;

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

Syntax with using OFFSET: SELECT select_list FROM table_name LIMIT no_of_rows OFFSET rows_to_skip;



Table Used:

Here, we are using the product table for demonstration.

Now let’s use the limit in this table, for we will use will psycopg2 module to connect the PostgreSQL and execute the SQL query in the cursor.execute(query) object.

Syntax: cursor.execute(sql_query);

Example 1: Using a limit clause in Postgres using Python

Python3




# importing psycopg2
import psycopg2
  
conn=psycopg2.connect(
    database="geeks",
    user="postgres",
    password="root",
    host="localhost",
    port="5432"
)
  
# Creating a cursor object using the cursor() method
cursor = conn.cursor()
  
print("\ndisplaying five rows from top");
sql = '''SELECT * from products LIMIT 5 '''
  
# Executing the query
cursor.execute(sql)
  
# Fetching the data
result = cursor.fetchall();
print(result)
  
# Commit changes in the database
conn.commit()
  
# Closing the connection
conn.close()

Output:

Postgres limit clause

Example 2: Using limit and offset clause in Postgres using Python

Python3




# importing psycopg2
import psycopg2
  
conn=psycopg2.connect(
    database="geeks",
    user="postgres",
    password="root",
    host="localhost",
    port="5432"
)
  
# Creating a cursor object using the cursor() method
cursor = conn.cursor()
print("displaying four rows after a particular offset");
sql1 = '''SELECT * from products LIMIT 4 OFFSET 5 '''
  
# Executing the query
cursor.execute(sql1)
  
# Fetching the data
result1 = cursor.fetchall();
print(result1)
  
  
# Commit changes in the database
conn.commit()
  
# Closing the connection
conn.close()

Output:

Postgres limit clause with offset




My Personal Notes arrow_drop_up
Recommended Articles
Page :