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.