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!

LeCoda
  • 538
  • 7
  • 36
  • 79
  • Hint: use *params* argument of [`read_sql`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html). Always [research](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) before asking. – Parfait May 30 '21 at 15:09
  • See also [How to use variables in SQL statement in Python](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python?r=SearchResults&s=2|26.8629) (not Pandas-specific, but relevant) – snakecharmerb May 30 '21 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! – Parfait May 30 '21 at 15:20

0 Answers0