def generate_excel():  #make table based on pgadmin4
    import psycopg2
    import re
    import pandas as pd
    column_names = []
    conn = psycopg2.connect("dbname='d8kmt03u16v621' user='ub7ar8om9fr3du' host='ec2-34-255-107-246.eu-west-1.compute.amazonaws.com' port='5432' password='p8fd5edd65c34fc3821be8507673ed326c4f116b7d4e7a6515765dab9fdafd028'")
    cur = conn.cursor()
    cur.execute("""select * from 
   (SELECT 
   id,id_in_subsession, round_number, modep1,modep2,choosep1,choosep2,"modeflagP1","predictionflagP1","chooseflagP1",
   session_id, "modeflagP2","predictionflagP2","chooseflagP2","predictionStoneP1","predictionStoneP2","predictionTreeP1","predictionTreeP2","competitionScore"
    ,"cooperationScore","independentScore","p1Score","p2Score","p1TotalScore","p2TotalScore","p1InterchangePenallty","p2InterchangePenallty","CreateTime",x,"actualTimeP1","actualTimeP2"
    ,"reactiontimeP1","reactiontimeP2","predictTimeNextP1","predictTimeNextP2","mousedownP1","mousedownP2","mouseupP1","mouseupP2"
              
       FROM  public.intro_hide_and_seek_2options_group
    
    ORDER BY id ASC) tm
    
    join 
 
 

(select sessionp1 , sessionp2 ,row_number() OVER (PARTITION BY session_id ORDER BY session_id) AS id_in_subsession, session_id from 
( SELECT lead(code)  over(partition by session_id  order by id_in_session)as sessionp2, code as sessionp1,id, session_id  FROM public.otree_participant

ORDER BY id ASC 

)t where id %2 =1)tn on tm.session_id= tn.session_id and  tm.id_in_subsession= tn.id_in_subsession



""")
    column_names = [desc[0] for desc in cur.description]

    result = cur.fetchall() 
    df = pd.DataFrame(list(result), columns=column_names)
    df = df.loc[df.choosep1.notnull()].reset_index(drop=True)    #removing the null fields
    df.to_excel("result.xlsx", sheet_name='result')   #creating excel
generate_excel()