Programming Essentials Python - CRUD Operations - Querying Data From Table

Let us understand how to build queries to get the data from the table.

To follow the above pattern, one needs to have decent skills related to Databases and SQL. Feel free to Master SQL using Postgresql as the target database using this course or playlist

We should leverage database capacity to filter as much data as possible (rather than fetching data into the application layer and then filtering).

We should avoid hard coding while filtering the data.

# Resetting users table to have 6 records
%run 06_creating_database_table.ipynb
%run 07_inserting_data_into_table.ipynb
%run 05_function_get_database_connection.ipynb

cursor = sms_connection.cursor()

query = """
    SELECT * FROM users LIMIT 5
"""

cursor.execute(query)

for user in cursor:
    print(user)

sms_connection.close()
# Filtering data based on user id

%run 05_function_get_database_connection.ipynb
cursor = sms_connection.cursor()

query = """
    SELECT * FROM users 
    WHERE user_id = %s 
"""

cursor.execute(query, (1,))

user = cursor.fetchone()
type(user)
print(user)
# Getting column names using the cursor after executing the query

cursor.description

def get_user_details(connection, user_id):
    cursor = connection.cursor()

    query = """
        SELECT * FROM users 
        WHERE user_id = %s 
    """

    cursor.execute(query, (user_id,))

    return cursor.fetchone()

user = get_user_details(sms_connection, 1)
print(user)
type(user)

user = get_user_details(sms_connection, 2)
print(user)

sms_connection.close()
# Example of a query which returns a dictionary type object

%run 05_function_get_database_connection.ipynb
import psycopg2
from psycopg2.extras import DictCursor

def get_user_details(connection, user_id):
    cursor = connection.cursor(cursor_factory=DictCursor)

    query = """
        SELECT * FROM users 
        WHERE user_id = %s 
    """

    cursor.execute(query, (user_id,))

    return cursor.fetchone()

user = get_user_details(sms_connection, 1)
print(user)
type(user)
user['user_id']
user['user_email_id']

sms_connection.close()
# Query returning multiple records

%run 05_function_get_database_connection.ipynb

cursor = sms_connection.cursor()
query = """
    SELECT user_id, user_email_id, user_password
    FROM users
    WHERE user_password IS NOT NULL
"""
cursor.execute(query)
users = cursor.fetchall()
type(users)

for user in users:
    print(user)

type(users[0])

from psycopg2.extras import DictCursor

cursor = sms_connection.cursor(cursor_factory=DictCursor)
cursor.execute(query)
users = cursor.fetchall()
type(users)

for user in users:
    print(user)

type(users[0])

users[0]['user_email_id']

sms_connection.close()

Explanation for the video

Placeholder for the video.

Watch the video tutorial here