Python PostgreSQL - Limit Clause

Python PostgreSQL - Where Clause

Last Updated : 17 Jan, 2023

In this article, we are going to see how to use the Where clause in PostgreSQL using Psycopg2 in Python.

Where Clauses help us to easily deal with the databases. As we know we have a huge amount of data stored in our database, so extracting only useful and required information clauses is helpful. The WHERE clause is used to extract only those records that fulfill a required condition.

Syntax: SELECT column1, column2, ..... FROM table_name WHERE condition

Table demonstration with where clause:

Example: At first, here we have shown how to create a table and then to insert values in it.

Python3

import psycopg2

# establishing the connection
conn = psycopg2.connect(
    database="test",
    user='postgres',
    password='password',
    host='localhost',
    port='5432'
)

# Creating a cursor object using the cursor() method
cursor = conn.cursor()

sql = '''CREATE TABLE WORKER(
   ID BIGSERIAL NOT NULL PRIMARY KEY,
   NAME VARCHAR(100) NOT NULL,
   COUNTRY VARCHAR(50) NOT NULL,
   AGE INT,
   SALARY FLOAT    
)'''
cursor.execute(sql)

# Inserting values into the table
insert_stmt = "INSERT INTO WORKER (NAME, COUNTRY, AGE, SALARY) \
VALUES (%s, %s, %s, %s)"
data = [('Krishna', 'India', 19, 2000),
        ('Harry', 'USA', 20, 7000),
        ('Malang', 'Nepal', 25, 5000),
        ('Apple', 'London', 26, 2000),
        ('Vishnu', 'India', 29, 2000),
        ('Frank', 'UAE', 21, 7000),
        ('Master', 'USA', 25, 5000),
        ('Montu', 'India', 26, 2000),
        ]
cursor.executemany(insert_stmt, data)

# Retrieving specific records using the where clause
cursor.execute("SELECT * from WORKER WHERE AGE <23")
print(cursor.fetchall())

# Retrieving specific records using the where clause
cursor.execute("SELECT * from WORKER WHERE COUNTRY='India' ")
print(cursor.fetchall())


# Retrieving name of employees whose salary is 5000
cursor.execute("SELECT name from WORKER WHERE salary=5000 ")
print(cursor.fetchall())

# Retrieving name and country of employees whose salary is 2000
cursor.execute("SELECT name, country from WORKER WHERE salary=2000 ")
print(cursor.fetchall())

# Commit your changes in the database
conn.commit()

# Closing the connection
conn.close()

Output:

where clause using pyscopg2 in Python

Python PostgreSQL - Limit Clause

K

kumaribabita2402

Improve

Article Tags :

Practice Tags :

python

Similar Reads

PostgreSQL - WHERE clause

The PostgreSQL WHERE clause is a critical component of SQL queries, allowing users to filter records based on specified conditions. In this tutorial, we'll explore how the WHERE clause works in PostgreSQL, its integration with the SELECT statement, and various examples. By using the WHERE clause, we

Python MySQL - Where Clause

Where clause is used in MySQL database to filter the data as per the condition required. You can fetch, delete or update a particular set of data in MySQL database by using where clause.SyntaxÂ SELECT column1, column2, .... columnN FROM [TABLE NAME] WHERE [CONDITION];Â Â The above syntax is used for

Python PostgreSQL - Limit Clause

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 b

PostgreSQL - WITH Clause

The WITH clause in PostgreSQL, also known as a Common Table Expression (CTE), simplifies complex queries by breaking them into smaller, readable sections. It allows us to define temporary result sets that can be referenced later in our main query. This makes the PostgreSQL code easier to manage and

Python PostgreSQL - Select Data

In this article, we are going to see how to use select data using Python in PostgreSQL and psycopg2. Installation Open the command prompt and write the command given below. pip install psycopg2 SELECT statement is used to retrieve the required details of an existing table in PostgreSQL. The data tha

PostgreSQL Python - Querying Data

Psycopg2 acts as a bridge between Python applications and PostgreSQL databases. Widely employed in diverse Python systems, from web applications to data analysis tools and other software projects, Psycopg2 enables developers to execute queries and manipulate data stored in PostgreSQL databases. In t

PostgreSQL - Create Tables in Python

Creating tables in PostgreSQL using Python is an essential skill for developers working with databases. This article will explore the process of creating new tables in the PostgreSQL database using Python.Why Create PostgreSQL Tables with Python?Using Python to create PostgreSQL tables is beneficial

PostgreSQL - Create table using Python

Creating tables in a PostgreSQL database using Python is a common task for developers working with databases. This process involves defining the structure of your data and ensuring that your database is optimized for efficient storage and retrieval. In this article, we will walk through the steps of

SQLite WHERE Clause

SQLite is the serverless database engine that is used most widely. It is written in c programming language and it belongs to the embedded database family. In this article, you will be learning about the where clause and functionality of the where clause in SQLite. Where ClauseSQLite WHERE Clause is

PL/SQL WHERE Clause

The WHERE clause in PL/SQL is essential for filtering records based on specified conditions. It is used in SELECT, UPDATE, and DELETE statements to limit the rows affected or retrieved, allowing precise control over data manipulation and retrieval.In this article, We will learn about the WHERE Claus