Querying Data From Table¶
Let us understand how to build queries to get the data from the table.
We should leverage database capacity to filter as much data as possible (rather than fetching data into application layer and then filtering).
To follow the above pattern one need to have decent skills related to Databases and SQL. Feel free to Master SQL using Postgresql as target database using this course or playlist
We should avoid hard coding while filtering the data.
Note
Resetting users table to have 6 records.
%run 06_creating_database_table.ipynb
The sql extension is already loaded. To reload it, use:
%reload_ext sql
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
10 rows affected.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
10 rows affected.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
%run 07_inserting_data_into_table.ipynb
The sql extension is already loaded. To reload it, use:
%reload_ext sql
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
The sql extension is already loaded. To reload it, use:
%reload_ext sql
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
2 rows affected.
The sql extension is already loaded. To reload it, use:
%reload_ext sql
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.
The sql extension is already loaded. To reload it, use:
%reload_ext sql
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
%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()
(1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'U', False, datetime.datetime(2020, 12, 29, 10, 29, 37, 792601), datetime.datetime(2020, 12, 29, 10, 29, 37, 792601))
(2, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', False, datetime.datetime(2020, 12, 29, 10, 29, 37, 815507), datetime.datetime(2020, 12, 29, 10, 29, 37, 815507))
(3, 'Mickey', 'Mouse', 'mickey@mouse.com', False, None, 'U', True, datetime.datetime(2020, 12, 29, 10, 29, 37, 837445), datetime.datetime(2020, 12, 29, 10, 29, 37, 837445))
(4, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', False, 'h9LAz7p7ub', 'U', True, datetime.datetime(2020, 12, 29, 10, 29, 37, 859601), datetime.datetime(2020, 12, 29, 10, 29, 37, 859601))
(5, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', False, 'oEofndp', 'U', True, datetime.datetime(2020, 12, 29, 10, 29, 37, 859601), datetime.datetime(2020, 12, 29, 10, 29, 37, 859601))
Note
Here is how we can filter data based up on user id. As user_id is primary key in the table, we typically use cursor.fetchone to fetch the one record as object.
%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)
tuple
print(user)
(1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'U', False, datetime.datetime(2020, 12, 29, 10, 29, 37, 792601), datetime.datetime(2020, 12, 29, 10, 29, 37, 792601))
Note
Getting column names using the cursor after executing the query
cursor.description
(Column(name='user_id', type_code=23),
Column(name='user_first_name', type_code=1043),
Column(name='user_last_name', type_code=1043),
Column(name='user_email_id', type_code=1043),
Column(name='user_email_validated', type_code=16),
Column(name='user_password', type_code=1043),
Column(name='user_role', type_code=1043),
Column(name='is_active', type_code=16),
Column(name='create_ts', type_code=1114),
Column(name='last_updated_ts', type_code=1114))
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)
(1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'U', False, datetime.datetime(2020, 12, 29, 10, 29, 37, 792601), datetime.datetime(2020, 12, 29, 10, 29, 37, 792601))
type(user)
tuple
user = get_user_details(sms_connection, 2)
print(user)
(2, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', False, datetime.datetime(2020, 12, 29, 10, 29, 37, 815507), datetime.datetime(2020, 12, 29, 10, 29, 37, 815507))
sms_connection.close()
Note
Here is an example where the function will return dict 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)
[1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'U', False, datetime.datetime(2020, 12, 29, 10, 29, 37, 792601), datetime.datetime(2020, 12, 29, 10, 29, 37, 792601)]
type(user)
psycopg2.extras.DictRow
user['user_id']
1
user['user_email_id']
'scott@tiger.com'
sms_connection.close()
Note
Here is an example of a query which returns multiple records. We need to use fetchall or fetchmany to return the records as list of tuples or objects.
%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)
list
for user in users:
print(user)
(4, 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub')
(5, 'tlyness1@paginegialle.it', 'oEofndp')
(6, 'amesias2@twitpic.com', 'ih7Y69u56')
type(users[0])
tuple
from psycopg2.extras import DictCursor
cursor = sms_connection.cursor(cursor_factory=DictCursor)
cursor.execute(query)
users = cursor.fetchall()
type(users)
list
for user in users:
print(user)
[4, 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub']
[5, 'tlyness1@paginegialle.it', 'oEofndp']
[6, 'amesias2@twitpic.com', 'ih7Y69u56']
type(users[0])
psycopg2.extras.DictRow
users[0]['user_email_id']
'gbradock0@barnesandnoble.com'
sms_connection.close()