0

I have recently moved a flask app to serverless and its all running fine apart from some of the more complicated sql statements.

I want to count when the Player name appears but only if the Team A result is less than the Team B result.

Take the example below. The statement called sql works but the statement called sql2 fails with error:

botocore.exceptions.ClientError: An error occurred (ValidationException) when calling the ExecuteStatement operation: Unexpected path component at 1:8:5

client = boto3.client('dynamodb')

player = 'Player2'

sql = f"SELECT * FROM player_table WHERE Name = '{player}'"
sql2 = f"SELECT COUNT(CASE WHEN 'Team A Player 1' = '{player}' AND 'Team A Result?' < 'Team B Result?' THEN 1 END) FROM results_table"

response = client.execute_statement(Statement= sql)
response2 = client.execute_statement(Statement= sql2)

print(sql)
print(response['Items'])

print(sql2)
print(response2['Items'])

Here is the downloaded csv version of the dynamodb table:


"Date","Team A Colour","Team A Player 1","Team A Player 2","Team A Player 3","Team A Player 4","Team A Player 5","Team A Result?","Team A Total","Team B Colour","Team B Player 1","Team B Player 2","Team B Player 3","Team B Player 4","Team B Player 5","Team B Result?","Team B Total"
"2023-01-11","blue","Player17","Player1","Player10","Player14","Player13","'-","385","green","Player11","Player12","Player15","Player18","Player16","'-","385"
"2023-01-01","black","Player1","Player2","Player3","Player4","Player5","1","400","white","Player6","Player7","Player8","Player9","Player10","1","400"

--EDIT--

Based on the answer below saying this is not possible using SQL, ive written it in python to get the same result but want to run it multiple times. Can someone help me tidy the python code to reduce the repetition as im not great at python coding?

import boto3
client = boto3.client('dynamodb')

player = 'Player2'
sql1 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team A Player 1" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql2 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team A Player 2" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql3 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team A Player 3" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql4 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team A Player 4" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql5 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team A Player 5" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql6 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team B Player 1" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql7 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team B Player 2" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql8 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team B Player 3" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql9 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team B Player 4" = '{player}' AND "Team A Result?" > "Team B Result?";"""
sql10 = f"""SELECT "Team A Result?" FROM "results_table" WHERE "Team B Player 5" = '{player}' AND "Team A Result?" > "Team B Result?";"""


response1 = client.execute_statement(Statement= sql1)
response2 = client.execute_statement(Statement= sql2)
response3 = client.execute_statement(Statement= sql3)
response4 = client.execute_statement(Statement= sql4)
response5 = client.execute_statement(Statement= sql5)
response6 = client.execute_statement(Statement= sql6)
response7 = client.execute_statement(Statement= sql7)
response8 = client.execute_statement(Statement= sql8)
response9 = client.execute_statement(Statement= sql9)
response10 = client.execute_statement(Statement= sql10)

string1 = response1['Items']
string2 = response2['Items']
string3 = response3['Items']
string4 = response4['Items']
string5 = response5['Items']
string6 = response6['Items']
string7 = response7['Items']
string8 = response8['Items']
string9 = response9['Items']
string10 = response10['Items']

n = 0
for i in [string1, 
        string2, 
        string3, 
        string4,
        string5,
        string6,
        string7,
        string8,
        string9,
        string10]:
    if len(i) != 0:
        n+=1
print(n)
Richard Bignell
  • 21
  • 1
  • 5
  • 2
    DynamoDB does not support arbitrary SQL queries. It supports a [subset of PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html#ql-reference.what-is), which is SQL compatible but limited to be consistent with its data model. See [PartiQL select statements for DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html) and [Use PartiQL functions with amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-functions.html) in the docs. – fedonev Jan 08 '23 at 10:45
  • Ah thank you. That answers the question. I guess i can add some python around separate select statements to count. – Richard Bignell Jan 08 '23 at 14:39
  • Are you any good at python? Can you help me streamline the code ive added to the edit? – Richard Bignell Jan 08 '23 at 15:45

0 Answers0