0

I'm trying to build a script that combined data I need for specific users.

I want to be able to parse in the users id to parse that information.

This is the script - what am i doing wrong in terms of having the user id parsed in?

import pandas as pd
import numpy as np

def GetData(user_id,conn):

    print(user_id)

    SQL_Sentiment = """
    select 
    sentiments.id,sentiments.user_id,
    sentiments.sentiment,sentiments.magnitude,
    sentiments.created

    from sentiments
    where sentiments.user_id = {user_id};


    """

    SQL_Expressions = """
    select 

    expressions.angry,expressions.disgusted, 
    expressions.fearful,expressions.happy,expressions.neutral, 
    expressions.user_id,
    expressions.sad,expressions.surprised,expressions.created



    from expressions

    where expressions.user_id = {user_id};
    """


    

    # SQL_Pupils = """

    # Select
    # pupil_detections.user_id,
    # 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 pupil_detections;



    # """
    Sentiment = pd.read_sql(SQL_Sentiment, con=conn)#.set_index('created')
    Sentiment['created'] =pd.to_datetime(Sentiment['created'], unit='s')
    Sentiment=Sentiment.set_index('created')

    Expressions = pd.read_sql(SQL_Expressions, con=conn)
    Expressions['created'] =pd.to_datetime(Expressions['created'], unit='s')
    Expressions=Expressions.set_index('created')

    #.set_index('created')
    Settings = pd.read_sql(SQL_Settings, con=conn)
    

    return Sentiment.append(Expressions)

Additionally, any recommendations on the best way to run a cronjob like this? Is it possible to just do this transformation in SQL, is that more efficient?

Thanks!

3
  • Hint: use params argument of read_sql. Always research before asking. Commented May 30, 2021 at 15:09
  • See also How to use variables in SQL statement in Python (not Pandas-specific, but relevant) Commented May 30, 2021 at 15:12
  • 1
    Regarding last questions, SO is not a place for recommendations. Make a cronjob attempt and ask regarding issues with that specific attempt. And look into your specific RDBMS (not tagged here) that should have handlers to create date/time values from seconds since epoch. Research accordingly! Commented May 30, 2021 at 15:20

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.