I'm trying to retrieve information from a database with python and sql.
Currently one of the tables is taking a huge amount of time to query and is in effect unloadable.
How can I more effectively load this data?
This is the current code I am using,
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import psycopg2 as pg
from sqlalchemy import create_engine
#establishing the connection
conn = pg.connect(
database=POSTGRESQL_DATABASE, user=POSTGRESQL_USER, password=POSTGRESQL_PASSWORD, host=POSTGRESQL_HOST, port=POSTGRESQL_PORT
)
SQL = '''
select sentiments.user_id,
settings.diagnosis,expressions.angry,expressions.disgusted,
expressions.fearful,expressions.happy,expressions.neutral,
expressions.sad,expressions.surprised,expressions.created,
settings.medication,settings.dosage_times,settings.medication_activity_time,
settings.gender,settings.age, sentiments.id, sentiments.sentiment,
sentiments.magnitude, sentiments.created,
pupil_detections.created,pupil_detections.left_eye_upper,pupil_detections.left_eye_lower,pupil_detections.right_eye_upper
,pupil_detections.right_eye_lower,pupil_detections.left_eye_iris,pupil_detections.right_eye_iris
from sentiments
left join expressions
on sentiments.user_id = expressions.user_id
left join pupil_detections
on sentiments.user_id = pupil_detections.user_id
left join settings
on sentiments.user_id = settings.user_id;
'''
df = pd.read_sql(SQL, con=conn)
df
Is there a way to batch the request or something like this? THe table that is massive is the pupil_detections database.
Instead of using pandas is there another way I could access this data to do analysis on it?
Thanks!
