Skip to content

Miscellaneous ORM and SQL scripts

Jump to bottom
Paul Girard edited this page Mar 22, 2017 · 7 revisions

Table of contents

Synopsis

It is possible to get thorough data sets in the form of CSV files in the oTree admin panel. Although powerful, this feature is sometimes too coarse-grained. During our experiments, we often wanted to obtain finer-grained data sets. Instead of altering a fork of otree-core, we ended up crafting ad-hoc solutions on the Python shell, and using the associated SQL queries to forge our own bespoke data sets.

Transposed into Heroku Dataclips, these scripts returned the same type of CSV file you could expect from oTree, with the missing details.

In the following scripts, duplicates are based on redundant values for the participant_label field, incompletes are any participants that have not reached the redirect_completes app, and speedsters are any participants among the incompletes, that are currently on the redirect_speedsters app.

Scripts

Preliminary import to discriminate payoff_groups

Before anything else, do this (don't forget to edit the value to the relevant payoff_group!):

from otree.models import Session

sessions_ids = [
  s.id for s in Session.objects.all()
  if s.config['payoff_group'] == 2
]

last_app = 'redirect_completes'
speedsters_app = 'redirect_speedsters'

Return all completes from redirect_speedsters

Django ORM:

from redirect_speedsters import models as RedirectSpeedstersModels

try:
    RedirectSpeedstersModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=speedsters_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'payoff',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "redirect_speedsters_player"."id_in_group",
       "redirect_speedsters_player"."payoff",
       "redirect_speedsters_group"."id_in_subsession",
       "redirect_speedsters_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "redirect_speedsters_player"
INNER JOIN "otree_session" ON ("redirect_speedsters_player"."session_id" = "otree_session"."id")
INNER JOIN "otree_participant" ON ("redirect_speedsters_player"."participant_id" = "otree_participant"."id")
LEFT OUTER JOIN "redirect_speedsters_group" ON ("redirect_speedsters_player"."group_id" = "redirect_speedsters_group"."id")
INNER JOIN "redirect_speedsters_subsession" ON ("redirect_speedsters_player"."subsession_id" = "redirect_speedsters_subsession"."id")
WHERE ("redirect_speedsters_player"."session_id" IN (1, 2, 4, 5, 6)
       AND "otree_participant"."_current_app_name" = 'redirect_speedsters')

Return all completes from redirect_completes

Django ORM:

from redirect_completes import models as RedirectCompletesModels

try:
    RedirectCompletesModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'payoff',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "redirect_completes_player"."id_in_group",
       "redirect_completes_player"."payoff",
       "redirect_completes_group"."id_in_subsession",
       "redirect_completes_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "redirect_completes_player"
INNER JOIN "otree_session" ON ("redirect_completes_player"."session_id" = "otree_session"."id")
INNER JOIN "otree_participant" ON ("redirect_completes_player"."participant_id" = "otree_participant"."id")
LEFT OUTER JOIN "redirect_completes_group" ON ("redirect_completes_player"."group_id" = "redirect_completes_group"."id")
INNER JOIN "redirect_completes_subsession" ON ("redirect_completes_player"."subsession_id" = "redirect_completes_subsession"."id")
WHERE ("redirect_completes_player"."session_id" IN (1, 2, 4, 5, 6)
       AND "otree_participant"."_current_app_name" = 'redirect_completes')

Return all completes from timer_start

Django ORM:

from timer_start import models as TimerStartModels

try:
    TimerStartModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'epoch', 'payoff',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "timer_start_player"."id_in_group",
       "timer_start_player"."epoch",
       "timer_start_player"."payoff",
       "timer_start_group"."id_in_subsession",
       "timer_start_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "timer_start_player"
INNER JOIN "otree_session" ON ("timer_start_player"."session_id" = "otree_session"."id")
INNER JOIN "otree_participant" ON ("timer_start_player"."participant_id" = "otree_participant"."id")
LEFT OUTER JOIN "timer_start_group" ON ("timer_start_player"."group_id" = "timer_start_group"."id")
INNER JOIN "timer_start_subsession" ON ("timer_start_player"."subsession_id" = "timer_start_subsession"."id")
WHERE ("timer_start_player"."session_id" IN (1, 2, 4, 5, 6)
       AND "otree_participant"."_current_app_name" = 'redirect_completes')

Return all completes from timer_stop

Django ORM:

from timer_stop import models as TimerStopModels

try:
    TimerStopModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'epoch', 
        'global_time_in_seconds', 'payoff',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "timer_stop_player"."id_in_group",
       "timer_stop_player"."epoch",
       "timer_stop_player"."global_time_in_seconds",
       "timer_stop_player"."payoff",
       "timer_stop_group"."id_in_subsession",
       "timer_stop_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "timer_stop_player"
INNER JOIN "otree_session" ON ("timer_stop_player"."session_id" = "otree_session"."id")
INNER JOIN "otree_participant" ON ("timer_stop_player"."participant_id" = "otree_participant"."id")
LEFT OUTER JOIN "timer_stop_group" ON ("timer_stop_player"."group_id" = "timer_stop_group"."id")
INNER JOIN "timer_stop_subsession" ON ("timer_stop_player"."subsession_id" = "timer_stop_subsession"."id")
WHERE ("timer_stop_player"."session_id" IN (1, 2, 4, 5, 6)
       AND "otree_participant"."_current_app_name" = 'redirect_completes')

Return all completes from dictator

Django ORM:

from dictator import models as DictatorModels

try:
    DictatorModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'given',
        'training_participant1_payoff',
        'training_participant2_payoff',
        'payoff', 'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "dictator_player"."id_in_group",
       "dictator_player"."given",
       "dictator_player"."training_participant1_payoff",
       "dictator_player"."training_participant2_payoff",
       "dictator_player"."payoff",
       "dictator_group"."id_in_subsession",
       "dictator_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "dictator_player"
INNER JOIN "otree_session" ON ("dictator_player"."session_id" = "otree_session"."id")
INNER JOIN "otree_participant" ON ("dictator_player"."participant_id" = "otree_participant"."id")
LEFT OUTER JOIN "dictator_group" ON ("dictator_player"."group_id" = "dictator_group"."id")
INNER JOIN "dictator_subsession" ON ("dictator_player"."subsession_id" = "dictator_subsession"."id")
WHERE ("dictator_player"."session_id" IN (1, 2, 4, 5, 6)
       AND "otree_participant"."_current_app_name" = 'redirect_completes')

Return all completes from earnings

Django ORM:

from earnings import models as EarningsModels

try:
    EarningsModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'donation',
        'calculation_from_game', 
        'calculation_from_role',
        'calculation_from_matched_player_id',
        'trust_game_player_a_transfer',
        'trust_game_player_b_transfer',
        'pg_joint_sum',
        'pg_player_a_transfer',
        'pg_player_b_transfer',
        'pg_player_c_transfer',
        'pg_player_d_transfer',
        'dictator_player_a_transfer',
        'dictator_player_a_remaining',
        'dictator_base_money', 'payoff',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "earnings_player"."id_in_group",
       "earnings_player"."donation",
       "earnings_player"."calculation_from_game",
       "earnings_player"."calculation_from_role",
       "earnings_player"."calculation_from_matched_player_id",
       "earnings_player"."trust_game_player_a_transfer",
       "earnings_player"."trust_game_player_b_transfer",
       "earnings_player"."pg_joint_sum",
       "earnings_player"."pg_player_a_transfer",
       "earnings_player"."pg_player_b_transfer",
       "earnings_player"."pg_player_c_transfer",
       "earnings_player"."pg_player_d_transfer",
       "earnings_player"."dictator_player_a_transfer",
       "earnings_player"."dictator_player_a_remaining",
       "earnings_player"."dictator_base_money",
       "earnings_player"."payoff",
       "earnings_group"."id_in_subsession",
       "earnings_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "earnings_player"
INNER JOIN "otree_session" ON ("earnings_player"."session_id" = "otree_session"."id")
INNER JOIN "otree_participant" ON ("earnings_player"."participant_id" = "otree_participant"."id")
LEFT OUTER JOIN "earnings_group" ON ("earnings_player"."group_id" = "earnings_group"."id")
INNER JOIN "earnings_subsession" ON ("earnings_player"."subsession_id" = "earnings_subsession"."id")
WHERE ("earnings_player"."session_id" IN (1, 2, 4, 5, 6)
       AND "otree_participant"."_current_app_name" = 'redirect_completes')

Return all completes from iat

Django ORM:

from iat import models as IatModels

try:
    IatModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'iat_successes',  
        'iat_failures', 'iat_meta', 'payoff',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "iat_player"."id_in_group",
       "iat_player"."iat_successes",
       "iat_player"."iat_failures",
       "iat_player"."iat_meta",
       "iat_player"."payoff",
       "iat_group"."id_in_subsession",
       "iat_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "iat_player"
INNER JOIN "otree_session" ON ("iat_player"."session_id" = "otree_session"."id")
INNER JOIN "otree_participant" ON ("iat_player"."participant_id" = "otree_participant"."id")
LEFT OUTER JOIN "iat_group" ON ("iat_player"."group_id" = "iat_group"."id")
INNER JOIN "iat_subsession" ON ("iat_player"."subsession_id" = "iat_subsession"."id")
WHERE ("iat_player"."session_id" IN (1, 2, 3, 4, 5, 6)
       AND "otree_participant"."_current_app_name" = 'redirect_completes')

Return all completes from public_goods

Django ORM:

from public_goods import models as PublicGoodsModels

try:
    PublicGoodsModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'contribution',
        'contribution_back_0',
        'contribution_back_1',
        'contribution_back_2',
        'contribution_back_3',
        'contribution_back_4',
        'contribution_back_5',
        'contribution_back_6',
        'contribution_back_7',
        'contribution_back_8',
        'contribution_back_9',
        'contribution_back_10',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "public_goods_player"."id_in_group",
       "public_goods_player"."contribution",
       "public_goods_player"."contribution_back_0",
       "public_goods_player"."contribution_back_1",
       "public_goods_player"."contribution_back_2",
       "public_goods_player"."contribution_back_3",
       "public_goods_player"."contribution_back_4",
       "public_goods_player"."contribution_back_5",
       "public_goods_player"."contribution_back_6",
       "public_goods_player"."contribution_back_7",
       "public_goods_player"."contribution_back_8",
       "public_goods_player"."contribution_back_9",
       "public_goods_player"."contribution_back_10",
       "public_goods_group"."id_in_subsession",
       "public_goods_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "public_goods_player"
INNER JOIN "otree_participant" ON ("public_goods_player"."participant_id" = "otree_participant"."id")
INNER JOIN "otree_session" ON ("public_goods_player"."session_id" = "otree_session"."id")
LEFT OUTER JOIN "public_goods_group" ON ("public_goods_player"."group_id" = "public_goods_group"."id")
INNER JOIN "public_goods_subsession" ON ("public_goods_player"."subsession_id" = "public_goods_subsession"."id")
WHERE ("otree_participant"."_current_app_name" = 'redirect_completes'
       AND "public_goods_player"."session_id" IN (1, 2, 4, 5, 6))

Return all completes from survey_i18n

Django ORM:

from survey_i18n import models as SurveyModels

try:
    SurveyModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'donation', 'payoff'
        '_01_how_satisfied_are_you_with_life_as_a_whole',
        '_01_would_you_say_that_most_people_can_be_trusted',
        '_01_are_you_a_person_fully_prepared_to_take_risks',
        '_02_treats_you_unfairly',
        '_02_treats_others_unfairly',
        '_02_give_to_good_causes',
        '_02A_altruism',
        '_03_does_me_a_favor',
        '_03_treated_injustly',
        '_04_thank_you_gift',
        '_04A_try_to_help_each_other',
        '_04A_try_to_take_advantage_of_you',
        '_04A_can_be_justified',
        '_04B_your_family',
        '_04B_your_neighbourhood',
        '_04B_personally',
        '_04B_meet_for_the_first_time',
        '_04B_another_religion',
        '_04B_another_nationality',
        '_04C_wallet',
        '_04C_you_vote_in_the_last_national_election',
        '_04C_good_at_math',
        '_05_your_government',
        '_05_the_parliament',
        '_05_the_judicial_system',
        '_05_the_media',
        '_05_financial_institutions',
        '_05_the_police',
        '_06_public_institutions_deliver_services',
        '_06_public_institutions_pursue_long_term_objectives',
        '_06_people_working_in_public_institutions_ethical',
        '_06_public_institutions_are_transparent',
        '_06_public_institutions_treat_all_citizens_fairly',
        '_07_what_is_your_date_of_birth',
        '_07_what_is_your_gender',
        '_07_all_the_people_who_live_in_the_same_household',
        '_07_how_many_people_children',
        '_07_how_many_people_adults',
        '_07_which_country_were_you_born',
        '_07_what_year_did_you_arrive_in_country',
        '_07_do_you_live_in',
        '_08_highest_level_of_education_that_you_have_completed',
        '_09_which_of_these_best_describes_your_situation',
        '_09_do_you_work_in_the',
        '_09_people_only_have_the_best_intentions',
        '_10_main_ways_income',
        '_10_household_income',
        '_10A_household_income',
        '_10B_household_income',
        '_10C_income',
        '_10D_income',
        '_10G_income',
        '_10G_religion',
        '_10G_religion_B',
        '_10H_income',
        '_11_other_participants_are_real_persons',
        '_11_earnings_will_be_calculated_in_euro',
        '_11_you_read_the_descriptions_associated',
        '_11_were_you_in_a_calm_environment',
        '_11_have_you_ever_participated_in_another_study',
        '_11_which_device_did_you_take_this_study',
        '_11_which_browser',
        '_12_final_comments',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "survey_i18n_player"."id_in_group",
       "survey_i18n_player"."donation",
       "survey_i18n_player"."_01_how_satisfied_are_you_with_life_as_a_whole",
       "survey_i18n_player"."_01_would_you_say_that_most_people_can_be_trusted",
       "survey_i18n_player"."_01_are_you_a_person_fully_prepared_to_take_risks",
       "survey_i18n_player"."_02_treats_you_unfairly",
       "survey_i18n_player"."_02_treats_others_unfairly",
       "survey_i18n_player"."_02_give_to_good_causes",
       "survey_i18n_player"."_02A_altruism",
       "survey_i18n_player"."_03_does_me_a_favor",
       "survey_i18n_player"."_03_treated_injustly",
       "survey_i18n_player"."_04_thank_you_gift",
       "survey_i18n_player"."_04A_try_to_help_each_other",
       "survey_i18n_player"."_04A_try_to_take_advantage_of_you",
       "survey_i18n_player"."_04A_can_be_justified",
       "survey_i18n_player"."_04B_your_family",
       "survey_i18n_player"."_04B_your_neighbourhood",
       "survey_i18n_player"."_04B_personally",
       "survey_i18n_player"."_04B_meet_for_the_first_time",
       "survey_i18n_player"."_04B_another_religion",
       "survey_i18n_player"."_04B_another_nationality",
       "survey_i18n_player"."_04C_wallet",
       "survey_i18n_player"."_04C_you_vote_in_the_last_national_election",
       "survey_i18n_player"."_04C_good_at_math",
       "survey_i18n_player"."_05_your_government",
       "survey_i18n_player"."_05_the_parliament",
       "survey_i18n_player"."_05_the_judicial_system",
       "survey_i18n_player"."_05_the_media",
       "survey_i18n_player"."_05_financial_institutions",
       "survey_i18n_player"."_05_the_police",
       "survey_i18n_player"."_06_public_institutions_deliver_services",
       "survey_i18n_player"."_06_public_institutions_pursue_long_term_objectives",
       "survey_i18n_player"."_06_people_working_in_public_institutions_ethical",
       "survey_i18n_player"."_06_public_institutions_are_transparent",
       "survey_i18n_player"."_06_public_institutions_treat_all_citizens_fairly",
       "survey_i18n_player"."_07_what_is_your_date_of_birth",
       "survey_i18n_player"."_07_what_is_your_gender",
       "survey_i18n_player"."_07_all_the_people_who_live_in_the_same_household",
       "survey_i18n_player"."_07_how_many_people_children",
       "survey_i18n_player"."_07_how_many_people_adults",
       "survey_i18n_player"."_07_which_country_were_you_born",
       "survey_i18n_player"."_07_what_year_did_you_arrive_in_country",
       "survey_i18n_player"."_07_do_you_live_in",
       "survey_i18n_player"."_08_highest_level_of_education_that_you_have_completed",
       "survey_i18n_player"."_09_which_of_these_best_describes_your_situation",
       "survey_i18n_player"."_09_do_you_work_in_the",
       "survey_i18n_player"."_09_people_only_have_the_best_intentions",
       "survey_i18n_player"."_10_main_ways_income",
       "survey_i18n_player"."_10_household_income",
       "survey_i18n_player"."_10A_household_income",
       "survey_i18n_player"."_10B_household_income",
       "survey_i18n_player"."_10C_income",
       "survey_i18n_player"."_10D_income",
       "survey_i18n_player"."_10G_income",
       "survey_i18n_player"."_10G_religion",
       "survey_i18n_player"."_10G_religion_B",
       "survey_i18n_player"."_10H_income",
       "survey_i18n_player"."_11_other_participants_are_real_persons",
       "survey_i18n_player"."_11_earnings_will_be_calculated_in_euro",
       "survey_i18n_player"."_11_you_read_the_descriptions_associated",
       "survey_i18n_player"."_11_were_you_in_a_calm_environment",
       "survey_i18n_player"."_11_have_you_ever_participated_in_another_study",
       "survey_i18n_player"."_11_which_device_did_you_take_this_study",
       "survey_i18n_player"."_11_which_browser",
       "survey_i18n_player"."_12_final_comments",
       "survey_i18n_player"."payoff",
       "survey_i18n_group"."id_in_subsession",
       "survey_i18n_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "survey_i18n_player"
INNER JOIN "otree_participant" ON ("survey_i18n_player"."participant_id" = "otree_participant"."id")
INNER JOIN "otree_session" ON ("survey_i18n_player"."session_id" = "otree_session"."id")
LEFT OUTER JOIN "survey_i18n_group" ON ("survey_i18n_player"."group_id" = "survey_i18n_group"."id")
INNER JOIN "survey_i18n_subsession" ON ("survey_i18n_player"."subsession_id" = "survey_i18n_subsession"."id")
WHERE ("otree_participant"."_current_app_name" = 'redirect_completes'
       AND "survey_i18n_player"."session_id" IN (1, 2, 4, 5, 6))

Return all completes from trust

Django ORM:

from trust import models as TrustModels

try:
    TrustModels.Player.objects.filter(
        session__id__in=sessions_ids,
        participant___current_app_name=last_app
    ).select_related(
        'participant', 'group', 'subsession', 'session'
    ).values(
        'participant__id_in_session', 
        'participant__code', 
        'participant__label', 
        'participant___is_bot', 
        'participant___index_in_pages', 
        'participant___max_page_index', 
        'participant___current_app_name', 
        'participant___round_number', 
        'participant___current_page_name', 
        'participant__ip_address', 
        'participant__time_started', 
        'participant__exclude_from_data_analysis', 
        'participant__visited', 
        'participant__mturk_worker_id', 
        'participant__mturk_assignment_id', 
        'id_in_group', 'sent_amount',
        'sent_back_amount_0',
        'sent_back_amount_1',
        'sent_back_amount_2',
        'sent_back_amount_3',
        'sent_back_amount_4',
        'sent_back_amount_5',
        'sent_back_amount_6',
        'sent_back_amount_7',
        'sent_back_amount_8',
        'sent_back_amount_9',
        'sent_back_amount_10',
        'group__id_in_subsession',
        'subsession__round_number',
        'session__code',
        'session__label',
        'session__experimenter_name',
        'session__time_scheduled',
        'session__time_started',
        'session__mturk_HITId',
        'session__mturk_HITGroupId',
        'session__comment',
        'session__is_demo'
    )
except Exception as e:
    print(e)

SQL (don't forget to adapt values for "session_id"!):

SELECT "otree_participant"."id_in_session",
       "otree_participant"."code",
       "otree_participant"."label",
       "otree_participant"."_is_bot",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_max_page_index",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_page_name",
       "otree_participant"."ip_address",
       "otree_participant"."time_started",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."visited",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."mturk_assignment_id",
       "trust_player"."id_in_group",
       "trust_player"."sent_amount",
       "trust_player"."sent_back_amount_0",
       "trust_player"."sent_back_amount_1",
       "trust_player"."sent_back_amount_2",
       "trust_player"."sent_back_amount_3",
       "trust_player"."sent_back_amount_4",
       "trust_player"."sent_back_amount_5",
       "trust_player"."sent_back_amount_6",
       "trust_player"."sent_back_amount_7",
       "trust_player"."sent_back_amount_8",
       "trust_player"."sent_back_amount_9",
       "trust_player"."sent_back_amount_10",
       "trust_group"."id_in_subsession",
       "trust_subsession"."round_number",
       "otree_session"."code",
       "otree_session"."label",
       "otree_session"."experimenter_name",
       "otree_session"."time_scheduled",
       "otree_session"."time_started",
       "otree_session"."mturk_HITId",
       "otree_session"."mturk_HITGroupId",
       "otree_session"."comment",
       "otree_session"."is_demo"
FROM "trust_player"
INNER JOIN "otree_participant" ON ("trust_player"."participant_id" = "otree_participant"."id")
INNER JOIN "otree_session" ON ("trust_player"."session_id" = "otree_session"."id")
LEFT OUTER JOIN "trust_group" ON ("trust_player"."group_id" = "trust_group"."id")
INNER JOIN "trust_subsession" ON ("trust_player"."subsession_id" = "trust_subsession"."id")
WHERE ("otree_participant"."_current_app_name" = 'redirect_completes'
       AND "trust_player"."session_id" IN (1, 2, 4, 5, 6))

Return all incompletes

Django ORM:

from introduction import models as IntroductionModels

IntroductionModels.Player.objects.filter(
    session__id__in=sessions_ids
).select_related(
    'participant'
).exclude(
    participant___current_app_name=last_app
)

SQL (don't forget to adapt values for "session_id"!):

SELECT "introduction_player"."id",
       "introduction_player"."id_in_group",
       "introduction_player"."payoff",
       "introduction_player"."participant_id",
       "introduction_player"."session_id",
       "introduction_player"."round_number",
       "introduction_player"."_group_by_arrival_time_arrived",
       "introduction_player"."_group_by_arrival_time_grouped",
       "introduction_player"."subsession_id",
       "introduction_player"."group_id",
       "otree_participant"."id",
       "otree_participant"."session_id",
       "otree_participant"."vars",
       "otree_participant"."label",
       "otree_participant"."id_in_session",
       "otree_participant"."exclude_from_data_analysis",
       "otree_participant"."time_started",
       "otree_participant"."mturk_assignment_id",
       "otree_participant"."mturk_worker_id",
       "otree_participant"."start_order",
       "otree_participant"."_index_in_subsessions",
       "otree_participant"."_index_in_pages",
       "otree_participant"."_waiting_for_ids",
       "otree_participant"."code",
       "otree_participant"."last_request_succeeded",
       "otree_participant"."visited",
       "otree_participant"."ip_address",
       "otree_participant"."_last_page_timestamp",
       "otree_participant"."_last_request_timestamp",
       "otree_participant"."is_on_wait_page",
       "otree_participant"."_current_page_name",
       "otree_participant"."_current_app_name",
       "otree_participant"."_round_number",
       "otree_participant"."_current_form_page_url",
       "otree_participant"."_max_page_index",
       "otree_participant"."_browser_bot_finished",
       "otree_participant"."_is_bot"
FROM "introduction_player"
INNER JOIN "otree_participant" ON ("introduction_player"."participant_id" = "otree_participant"."id")
WHERE ("introduction_player"."session_id" IN (1, 2, 3, 4)
       AND NOT ("otree_participant"."_current_app_name" = 'redirect_completes'
                AND "otree_participant"."_current_app_name" IS NOT NULL))

Return all speedsters

Django ORM:

from introduction import models as IntroductionModels

IntroductionModels.Player.objects.filter(
    session__id__in=sessions_ids, 
    participant___current_app_name=speedsters_app
)

SQL (don't forget to adapt values for "session_id"!):

SELECT "introduction_player"."id",
       "introduction_player"."id_in_group",
       "introduction_player"."payoff",
       "introduction_player"."participant_id",
       "introduction_player"."session_id",
       "introduction_player"."round_number",
       "introduction_player"."_group_by_arrival_time_arrived",
       "introduction_player"."_group_by_arrival_time_grouped",
       "introduction_player"."subsession_id",
       "introduction_player"."group_id"
FROM "introduction_player"
INNER JOIN "otree_participant" ON ("introduction_player"."participant_id" = "otree_participant"."id")
WHERE ("otree_participant"."_current_app_name" = redirect_speedsters
       AND "introduction_player"."session_id" IN (1, 2, 3, 4))

01 - Settings and session configuration

02 - CLI Commands

03 - Animated simulations

04 - IAT

05 - Miscellaneous ORM/SQL scripts

Clone this wiki locally