[
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.
In [1]:
%run 06_creating_database_table.ipynb
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db 1 rows affected. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db Done. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 0 rows affected. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db Done. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 1 rows affected. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 10 rows affected. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 0 rows affected.
In [2]:
%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:itversity@pg.itversity.com:5432/itversity_sms_db * postgresql://itversity_sms_user:***@pg.itversity.com: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:itversity@pg.itversity.com:5432/itversity_sms_db * postgresql://itversity_sms_user:***@pg.itversity.com: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:***@pg.itversity.com: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:itversity@pg.itversity.com:5432/itversity_sms_db * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 6 rows affected.
In [3]:
%run 05_function_get_database_connection.ipynb
In [4]:
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(2022, 3, 30, 12, 13, 30, 894139), datetime.datetime(2022, 3, 30, 12, 13, 30, 894139)) (2, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', False, datetime.datetime(2022, 3, 30, 12, 13, 30, 914635), datetime.datetime(2022, 3, 30, 12, 13, 30, 914635)) (3, 'Mickey', 'Mouse', 'mickey@mouse.com', False, None, 'U', True, datetime.datetime(2022, 3, 30, 12, 13, 30, 935288), datetime.datetime(2022, 3, 30, 12, 13, 30, 935288)) (4, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', False, 'h9LAz7p7ub', 'U', True, datetime.datetime(2022, 3, 30, 12, 13, 30, 956403), datetime.datetime(2022, 3, 30, 12, 13, 30, 956403)) (5, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', False, 'oEofndp', 'U', True, datetime.datetime(2022, 3, 30, 12, 13, 30, 956403), datetime.datetime(2022, 3, 30, 12, 13, 30, 956403))
{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.In [5]:
%run 05_function_get_database_connection.ipynb
In [6]:
cursor = sms_connection.cursor()
query = """
SELECT * FROM users
WHERE user_id = %s
"""
cursor.execute(query, (1,))
user = cursor.fetchone()
In [7]:
type(user)
Out[7]:
tuple
In [8]:
print(user)
(1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'U', False, datetime.datetime(2022, 3, 30, 12, 13, 30, 894139), datetime.datetime(2022, 3, 30, 12, 13, 30, 894139))
{note}
Getting column names using the cursor after executing the queryIn [9]:
cursor.description
Out[9]:
(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))
In [10]:
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()
In [11]:
user = get_user_details(sms_connection, 1)
print(user)
(1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'U', False, datetime.datetime(2022, 3, 30, 12, 13, 30, 894139), datetime.datetime(2022, 3, 30, 12, 13, 30, 894139))
In [12]:
type(user)
Out[12]:
tuple
In [13]:
user = get_user_details(sms_connection, 2)
print(user)
(2, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', False, datetime.datetime(2022, 3, 30, 12, 13, 30, 914635), datetime.datetime(2022, 3, 30, 12, 13, 30, 914635))
In [14]:
sms_connection.close()
{note}
Here is an example where the function will return `dict` type object.In [15]:
%run 05_function_get_database_connection.ipynb
In [16]:
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()
In [17]:
user = get_user_details(sms_connection, 1)
print(user)
[1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'U', False, datetime.datetime(2022, 3, 30, 12, 13, 30, 894139), datetime.datetime(2022, 3, 30, 12, 13, 30, 894139)]
In [18]:
type(user)
Out[18]:
psycopg2.extras.DictRow
In [19]:
user['user_id']
Out[19]:
1
In [20]:
user['user_email_id']
Out[20]:
'scott@tiger.com'
In [21]:
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.In [22]:
%run 05_function_get_database_connection.ipynb
In [23]:
cursor = sms_connection.cursor()
In [24]:
query = """
SELECT user_id, user_email_id, user_password
FROM users
WHERE user_password IS NOT NULL
"""
In [25]:
cursor.execute(query)
In [26]:
users = cursor.fetchall()
In [27]:
type(users)
Out[27]:
list
In [28]:
for user in users:
print(user)
(4, 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub') (5, 'tlyness1@paginegialle.it', 'oEofndp') (6, 'amesias2@twitpic.com', 'ih7Y69u56')
In [29]:
type(users[0])
Out[29]:
tuple
In [30]:
from psycopg2.extras import DictCursor
In [31]:
cursor = sms_connection.cursor(cursor_factory=DictCursor)
In [32]:
cursor.execute(query)
In [33]:
users = cursor.fetchall()
In [34]:
type(users)
Out[34]:
list
In [35]:
for user in users:
print(user)
[4, 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub'] [5, 'tlyness1@paginegialle.it', 'oEofndp'] [6, 'amesias2@twitpic.com', 'ih7Y69u56']
In [36]:
type(users[0])
Out[36]:
psycopg2.extras.DictRow
In [37]:
users[0]['user_email_id']
Out[37]:
'gbradock0@barnesandnoble.com'
In [39]:
sms_connection.close()
]