Skip to content

Instantly share code, notes, and snippets.

@jhw
Last active April 30, 2024 09:54
Show Gist options
  • Save jhw/61a72a2718b4f774e47cfe4719fa7174 to your computer and use it in GitHub Desktop.
Save jhw/61a72a2718b4f774e47cfe4719fa7174 to your computer and use it in GitHub Desktop.
One to many relationships in a single table world with DynamoDB

Some sample DynamoDB usage patterns, one branch for each

env
*.pyc
__pycache__
tmp
AppName=single-table-demo-02
#!/usr/bin/env bash
. app.props
aws cloudformation delete-stack --stack-name $AppName
#!/usr/bin/env bash
. app.props
aws cloudformation deploy --stack-name $AppName --template-file stack.yaml --capabilities CAPABILITY_NAMED_IAM
import boto3, json, sys
def load_props():
return dict([tuple(row.split("="))
for row in open("app.props").read().split("\n")
if row!=''])
def fetch_outputs(cf, stackname):
outputs = {}
for stack in cf.describe_stacks()["Stacks"]:
if (stack["StackName"].startswith(stackname) and
"Outputs" in stack):
for output in stack["Outputs"]:
outputs[output["OutputKey"]] = output["OutputValue"]
return outputs
if __name__ == "__main__":
try:
if len(sys.argv) < 2:
raise RuntimeError("please enter league name")
leaguename=sys.argv[1]
props=load_props()
cf=boto3.client("cloudformation")
outputs=fetch_outputs(cf, props["AppName"])
tablename=outputs["AppTable"]
table=boto3.resource("dynamodb").Table(tablename)
resp=table.get_item(Key={"pk": f"LEAGUE#{leaguename}"})
if "Item" not in resp:
raise RuntimeError("league not found")
league=resp["Item"]
print (", ".join(json.loads(league["teams"])))
except RuntimeError as error:
print ("Error: %s" % str(error))
import boto3, json, yaml
def load_props():
return dict([tuple(row.split("="))
for row in open("app.props").read().split("\n")
if row!=''])
def fetch_outputs(cf, stackname):
outputs = {}
for stack in cf.describe_stacks()["Stacks"]:
if (stack["StackName"].startswith(stackname) and
"Outputs" in stack):
for output in stack["Outputs"]:
outputs[output["OutputKey"]] = output["OutputValue"]
return outputs
def filter_teams(results, filterfn = lambda x: x.startswith("ENG")):
teams={}
for result in results:
leaguename=result["league"]
if filterfn(leaguename):
teams.setdefault(leaguename, set())
for teamname in result["name"].split(" vs "):
teams[leaguename].add(teamname)
return teams
def push_league(table, leaguename, teamnames):
print (leaguename)
for teamname in teamnames:
item={"pk": f"LEAGUE#{leaguename}",
"teams": json.dumps(teamnames)}
table.put_item(Item=item)
if __name__ == "__main__":
props=load_props()
cf=boto3.client("cloudformation")
outputs=fetch_outputs(cf, props["AppName"])
tablename=outputs["AppTable"]
table=boto3.resource("dynamodb").Table(tablename)
results=yaml.safe_load(open("results.yaml").read())
teams=filter_teams(results)
for leaguename in sorted(teams.keys()):
teamnames=sorted(list(teams[leaguename]))
push_league(table, leaguename, teamnames)
#!/usr/bin/env bash
. app.props
aws cloudformation describe-stack-events --stack-name $AppName --query "StackEvents[].{\"1.Timestamp\":Timestamp,\"2.Id\":LogicalResourceId,\"3.Type\":ResourceType,\"4.Status\":ResourceStatus,\"5.Reason\":ResourceStatusReason}"
#!/usr/bin/env bash
. app.props
aws cloudformation describe-stacks --stack-name $AppName --query 'Stacks[0].Outputs' --output table
#!/usr/bin/env bash
. app.props
aws cloudformation describe-stack-resources --stack-name $AppName --query "StackResources[].{\"1.Timestamp\":Timestamp,\"2.LogicalId\":LogicalResourceId,\"3.PhysicalId\":PhysicalResourceId,\"4.Type\":ResourceType,\"5.Status\":ResourceStatus}"
#!/usr/bin/env bash
aws cloudformation describe-stacks --query "Stacks[].{\"1.Name\":StackName,\"2.Status\":StackStatus}"
awscli
boto3
botocore
pyyaml
- date: '2024-02-19'
league: ENG1
name: Everton vs Crystal Palace
score: 1-1
- date: '2024-02-19'
league: SPA1
name: Athletic Bilbao vs Girona
score: 3-2
- date: '2024-02-20'
league: ENG1
name: Manchester City vs Brentford
score: 1-0
- date: '2024-02-20'
league: ENG2
name: Cardiff City vs Blackburn Rovers
score: 0-0
- date: '2024-02-20'
league: ENG2
name: Ipswich Town vs Rotherham United
score: 4-3
- date: '2024-02-20'
league: ENG2
name: Plymouth Argyle vs West Bromwich Albion
score: 0-3
- date: '2024-02-20'
league: ENG2
name: Southampton vs Hull City
score: 1-2
- date: '2024-02-20'
league: ENG3
name: Cambridge United vs Bolton Wanderers
score: 1-2
- date: '2024-02-20'
league: ENG3
name: Oxford United vs Northampton Town
score: 2-2
- date: '2024-02-20'
league: ENG3
name: Reading vs Port Vale
score: 2-0
- date: '2024-02-20'
league: ENG4
name: AFC Wimbledon vs Crawley Town
score: 0-1
- date: '2024-02-20'
league: ENG4
name: Gillingham vs Stockport County
score: 0-0
- date: '2024-02-20'
league: ENG4
name: Milton Keynes Dons vs Wrexham
score: 1-1
- date: '2024-02-20'
league: ENG4
name: Walsall vs Morecambe
score: 3-0
- date: '2024-02-20'
league: SCO3
name: Falkirk vs Montrose
score: 3-0
- date: '2024-02-20'
league: SCO4
name: Clyde vs Forfar Athletic
score: 0-2
- date: '2024-02-20'
league: SCO4
name: Peterhead vs Bonnyrigg Rose
score: 0-0
- date: '2024-02-21'
league: ENG1
name: Liverpool vs Luton Town
score: 4-1
- date: '2024-02-22'
league: ITA1
name: Torino vs Lazio
score: 0-2
- date: '2024-02-23'
league: ENG2
name: Coventry City vs Preston North End
score: 0-3
- date: '2024-02-23'
league: ENG2
name: Leeds United vs Leicester City
score: 3-1
- date: '2024-02-23'
league: ENG3
name: Wigan Athletic vs Cheltenham Town
score: 1-1
- date: '2024-02-23'
league: FRA1
name: Metz vs Lyon
score: 1-2
- date: '2024-02-23'
league: GER1
name: Bayer 04 Leverkusen vs Mainz 05
score: 2-1
- date: '2024-02-23'
league: ITA1
name: Bologna vs Hellas Verona
score: 2-0
- date: '2024-02-23'
league: NED1
name: FC Utrecht vs Heracles Almelo
score: 1-0
- date: '2024-02-23'
league: SCO2
name: Partick Thistle vs Dunfermline
score: 1-3
- date: '2024-02-23'
league: SPA1
name: Real Sociedad vs Villarreal
score: 1-3
- date: '2024-02-24'
league: ENG1
name: AFC Bournemouth vs Manchester City
score: 0-1
- date: '2024-02-24'
league: ENG1
name: Arsenal vs Newcastle United
score: 4-1
- date: '2024-02-24'
league: ENG1
name: Aston Villa vs Nottingham Forest
score: 4-2
- date: '2024-02-24'
league: ENG1
name: Brighton & Hove Albion vs Everton
score: 1-1
- date: '2024-02-24'
league: ENG1
name: Crystal Palace vs Burnley
score: 3-0
- date: '2024-02-24'
league: ENG1
name: Manchester United vs Fulham
score: 1-2
- date: '2024-02-24'
league: ENG2
name: Blackburn Rovers vs Norwich City
score: 1-1
- date: '2024-02-24'
league: ENG2
name: Cardiff City vs Stoke City
score: 2-1
- date: '2024-02-24'
league: ENG2
name: Hull City vs West Bromwich Albion
score: 1-1
- date: '2024-02-24'
league: ENG2
name: Ipswich Town vs Birmingham City
score: 3-1
- date: '2024-02-24'
league: ENG2
name: Middlesbrough vs Plymouth Argyle
score: 0-2
- date: '2024-02-24'
league: ENG2
name: Queens Park Rangers vs Rotherham United
score: 2-1
- date: '2024-02-24'
league: ENG2
name: Sheffield Wednesday vs Bristol City
score: 2-1
- date: '2024-02-24'
league: ENG2
name: Southampton vs Millwall
score: 1-2
- date: '2024-02-24'
league: ENG2
name: Sunderland vs Swansea City
score: 1-2
- date: '2024-02-24'
league: ENG2
name: Watford vs Huddersfield Town
score: 1-2
- date: '2024-02-24'
league: ENG3
name: Barnsley vs Derby County
score: 2-1
- date: '2024-02-24'
league: ENG3
name: Blackpool vs Bolton Wanderers
score: 4-1
- date: '2024-02-24'
league: ENG3
name: Bristol Rovers vs Carlisle United
score: 2-1
- date: '2024-02-24'
league: ENG3
name: Burton Albion vs Northampton Town
score: 0-2
- date: '2024-02-24'
league: ENG3
name: Cambridge United vs Peterborough United
score: 0-1
- date: '2024-02-24'
league: ENG3
name: Charlton Athletic vs Portsmouth
score: 0-0
- date: '2024-02-24'
league: ENG3
name: Exeter City vs Fleetwood Town
score: 1-1
- date: '2024-02-24'
league: ENG3
name: Oxford United vs Leyton Orient
score: 1-2
- date: '2024-02-24'
league: ENG3
name: Port Vale vs Lincoln City
score: 0-2
- date: '2024-02-24'
league: ENG3
name: Reading vs Shrewsbury Town
score: 2-3
- date: '2024-02-24'
league: ENG3
name: Stevenage vs Wycombe Wanderers
score: 1-0
- date: '2024-02-24'
league: ENG4
name: Accrington Stanley vs Crawley Town
score: 0-1
- date: '2024-02-24'
league: ENG4
name: Doncaster Rovers vs AFC Wimbledon
score: 1-0
- date: '2024-02-24'
league: ENG4
name: Forest Green Rovers vs Tranmere Rovers
score: 1-0
- date: '2024-02-24'
league: ENG4
name: Gillingham vs Wrexham
score: 1-0
- date: '2024-02-24'
league: ENG4
name: Harrogate Town vs Walsall
score: 0-2
- date: '2024-02-24'
league: ENG4
name: Mansfield Town vs Salford City
score: 5-1
- date: '2024-02-24'
league: ENG4
name: Milton Keynes Dons vs Newport County
score: 3-0
- date: '2024-02-24'
league: ENG4
name: Morecambe vs Grimsby Town
score: 1-1
- date: '2024-02-24'
league: ENG4
name: Notts County vs Crewe Alexandra
score: 1-3
- date: '2024-02-24'
league: ENG4
name: Stockport County vs Swindon Town
score: 0-0
- date: '2024-02-24'
league: ENG4
name: Sutton United vs Colchester United
score: 1-1
- date: '2024-02-24'
league: FRA1
name: Lorient vs Nantes
score: 0-1
- date: '2024-02-24'
league: FRA1
name: Strasbourg vs Brest
score: 0-3
- date: '2024-02-24'
league: GER1
name: 1. FC Union Berlin vs 1. FC Heidenheim 1846
score: 2-2
- date: '2024-02-24'
league: GER1
name: Bayern Munich vs RB Leipzig
score: 2-1
- date: '2024-02-24'
league: GER1
name: "Borussia M\xF6nchengladbach vs VfL Bochum 1848"
score: 5-2
- date: '2024-02-24'
league: GER1
name: "VfB Stuttgart vs 1. FC K\xF6ln"
score: 1-1
- date: '2024-02-24'
league: GER1
name: Werder Bremen vs Darmstadt 98
score: 1-1
- date: '2024-02-24'
league: ITA1
name: Genoa vs Udinese
score: 2-0
- date: '2024-02-24'
league: ITA1
name: Salernitana vs Monza
score: 0-2
- date: '2024-02-24'
league: ITA1
name: Sassuolo vs Empoli
score: 2-3
- date: '2024-02-24'
league: NED1
name: N.E.C. vs Sparta Rotterdam
score: 2-0
- date: '2024-02-24'
league: NED1
name: PEC Zwolle vs PSV Eindhoven
score: 1-7
- date: '2024-02-24'
league: NED1
name: RKC Waalwijk vs Fortuna Sittard
score: 0-1
- date: '2024-02-24'
league: SCO2
name: Arbroath vs Inverness Caledonian Thistle
score: 1-1
- date: '2024-02-24'
league: SCO2
name: Ayr United vs Raith Rovers
score: 1-2
- date: '2024-02-24'
league: SCO2
name: Dundee United vs Queen's Park
score: 3-1
- date: '2024-02-24'
league: SCO2
name: Greenock Morton vs Airdrieonians
score: 2-1
- date: '2024-02-24'
league: SCO3
name: Cove Rangers vs Annan Athletic
score: 2-1
- date: '2024-02-24'
league: SCO3
name: Hamilton Academical vs Alloa Athletic
score: 1-2
- date: '2024-02-24'
league: SCO3
name: Kelty Hearts vs Falkirk
score: 0-1
- date: '2024-02-24'
league: SCO3
name: Queen of the South vs Montrose
score: 2-3
- date: '2024-02-24'
league: SCO3
name: Stirling Albion vs Edinburgh City
score: 4-0
- date: '2024-02-24'
league: SCO4
name: Dumbarton vs Stranraer
score: 2-1
- date: '2024-02-24'
league: SCO4
name: East Fife vs Forfar Athletic
score: 1-1
- date: '2024-02-24'
league: SCO4
name: Peterhead vs Elgin City
score: 1-1
- date: '2024-02-24'
league: SCO4
name: Stenhousemuir vs Clyde
score: 1-6
- date: '2024-02-24'
league: SCO4
name: The Spartans vs Bonnyrigg Rose
score: 2-1
- date: '2024-02-24'
league: SPA1
name: "Alav\xE9s vs Mallorca"
score: 1-1
- date: '2024-02-24'
league: SPA1
name: "Almer\xEDa vs Atl\xE9tico Madrid"
score: 2-2
- date: '2024-02-24'
league: SPA1
name: Barcelona vs Getafe
score: 4-0
- date: '2024-02-25'
league: ENG1
name: Wolverhampton Wanderers vs Sheffield United
score: 1-0
- date: '2024-02-25'
league: FRA1
name: Le Havre vs Reims
score: 1-2
- date: '2024-02-25'
league: FRA1
name: Lens vs Monaco
score: 2-3
- date: '2024-02-25'
league: FRA1
name: Marseille vs Montpellier
score: 4-1
- date: '2024-02-25'
league: FRA1
name: Nice vs Clermont
score: 0-0
- date: '2024-02-25'
league: FRA1
name: Paris Saint Germain vs Rennes
score: 1-1
- date: '2024-02-25'
league: FRA1
name: Toulouse vs Lille
score: 3-1
- date: '2024-02-25'
league: GER1
name: Borussia Dortmund vs 1899 Hoffenheim
score: 2-3
- date: '2024-02-25'
league: GER1
name: Eintracht Frankfurt vs VfL Wolfsburg
score: 2-2
- date: '2024-02-25'
league: GER1
name: FC Augsburg vs SC Freiburg
score: 2-1
- date: '2024-02-25'
league: ITA1
name: AC Milan vs Atalanta
score: 1-1
- date: '2024-02-25'
league: ITA1
name: Cagliari vs Napoli
score: 1-1
- date: '2024-02-25'
league: ITA1
name: Juventus vs Frosinone
score: 3-2
- date: '2024-02-25'
league: ITA1
name: Lecce vs Inter Milan
score: 0-4
- date: '2024-02-25'
league: NED1
name: AZ Alkmaar vs Ajax
score: 2-0
- date: '2024-02-25'
league: NED1
name: Almere City FC vs Feyenoord
score: 0-2
- date: '2024-02-25'
league: NED1
name: Excelsior vs Vitesse
score: 1-2
- date: '2024-02-25'
league: NED1
name: FC Twente vs Go Ahead Eagles
score: 3-0
- date: '2024-02-25'
league: NED1
name: FC Volendam vs sc Heerenveen
score: 0-4
- date: '2024-02-25'
league: SPA1
name: "C\xE1diz vs Celta Vigo"
score: 2-2
- date: '2024-02-25'
league: SPA1
name: Las Palmas vs Osasuna
score: 1-1
- date: '2024-02-25'
league: SPA1
name: Real Betis vs Athletic Bilbao
score: 3-1
- date: '2024-02-25'
league: SPA1
name: Real Madrid vs Sevilla
score: 1-0
#!/usr/bin/env bash
export AWS_DEFAULT_OUTPUT=table
export AWS_PROFILE=woldeploy
export AWS_REGION=eu-west-1
---
Outputs:
AppTable:
Value:
Ref: AppTable
Resources:
AppTable:
Type: AWS::DynamoDB::Table
Properties:
AttributeDefinitions:
- AttributeName: pk
AttributeType: S
BillingMode: PAY_PER_REQUEST
KeySchema:
- AttributeName: pk
KeyType: HASH

short

  • create branch

done

  • change teams upload to load with pk only and dump JSON teams
  • change teams fetch to fetch with pk only and dump JSON
  • change delete to delete usink PK 01
  • remove gsi and range
  • remove matches upload
  • comment out non- english leagues
  • add score to match
  • delete stack
  • script to push results
  • script to fetch results
  • abstract table intialisation for clarity
  • script to delete teams
  • fetch outputs
  • script to push teams
  • script to fetch teams
  • script to fetch results
  • json stack
  • yaml stack
  • test deployment
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment