Use ExecuteStatement with an AWS SDK or CLI - Amazon DynamoDB

Use ExecuteStatement with an AWS SDK or CLI

The following code examples show how to use ExecuteStatement.

Action examples are code excerpts from larger programs and must be run in context. You can see this action in context in the following code example:

.NET
AWS SDK for .NET
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

Use an INSERT statement to add an item.

/// <summary> /// Inserts a single movie into the movies table. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="movieTitle">The title of the movie to insert.</param> /// <param name="year">The year that the movie was released.</param> /// <returns>A Boolean value that indicates the success or failure of /// the INSERT operation.</returns> public static async Task<bool> InsertSingleMovie(string tableName, string movieTitle, int year) { string insertBatch = $"INSERT INTO {tableName} VALUE {{'title': ?, 'year': ?}}"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = insertBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; }

Use a SELECT statement to get an item.

/// <summary> /// Uses a PartiQL SELECT statement to retrieve a single movie from the /// movie database. /// </summary> /// <param name="tableName">The name of the movie table.</param> /// <param name="movieTitle">The title of the movie to retrieve.</param> /// <returns>A list of movie data. If no movie matches the supplied /// title, the list is empty.</returns> public static async Task<List<Dictionary<string, AttributeValue>>> GetSingleMovie(string tableName, string movieTitle) { string selectSingle = $"SELECT * FROM {tableName} WHERE title = ?"; var parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, }; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = selectSingle, Parameters = parameters, }); return response.Items; }

Use a SELECT statement to get a list of items.

/// <summary> /// Retrieve multiple movies by year using a SELECT statement. /// </summary> /// <param name="tableName">The name of the movie table.</param> /// <param name="year">The year the movies were released.</param> /// <returns></returns> public static async Task<List<Dictionary<string, AttributeValue>>> GetMovies(string tableName, int year) { string selectSingle = $"SELECT * FROM {tableName} WHERE year = ?"; var parameters = new List<AttributeValue> { new AttributeValue { N = year.ToString() }, }; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = selectSingle, Parameters = parameters, }); return response.Items; }

Use an UPDATE statement to update an item.

/// <summary> /// Updates a single movie in the table, adding information for the /// producer. /// </summary> /// <param name="tableName">the name of the table.</param> /// <param name="producer">The name of the producer.</param> /// <param name="movieTitle">The movie title.</param> /// <param name="year">The year the movie was released.</param> /// <returns>A Boolean value that indicates the success of the /// UPDATE operation.</returns> public static async Task<bool> UpdateSingleMovie(string tableName, string producer, string movieTitle, int year) { string insertSingle = $"UPDATE {tableName} SET Producer=? WHERE title = ? AND year = ?"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = insertSingle, Parameters = new List<AttributeValue> { new AttributeValue { S = producer }, new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; }

Use a DELETE statement to delete a single movie.

/// <summary> /// Deletes a single movie from the table. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="movieTitle">The title of the movie to delete.</param> /// <param name="year">The year that the movie was released.</param> /// <returns>A Boolean value that indicates the success of the /// DELETE operation.</returns> public static async Task<bool> DeleteSingleMovie(string tableName, string movieTitle, int year) { var deleteSingle = $"DELETE FROM {tableName} WHERE title = ? AND year = ?"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = deleteSingle, Parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; }
C++
SDK for C++
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

Use an INSERT statement to add an item.

Aws::DynamoDB::DynamoDBClient dynamoClient(clientConfiguration); // 2. Add a new movie using an "Insert" statement. (ExecuteStatement) Aws::String title; float rating; int year; Aws::String plot; { title = askQuestion( "Enter the title of a movie you want to add to the table: "); year = askQuestionForInt("What year was it released? "); rating = askQuestionForFloatRange("On a scale of 1 - 10, how do you rate it? ", 1, 10); plot = askQuestion("Summarize the plot for me: "); Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "INSERT INTO \"" << MOVIE_TABLE_NAME << "\" VALUE {'" << TITLE_KEY << "': ?, '" << YEAR_KEY << "': ?, '" << INFO_KEY << "': ?}"; request.SetStatement(sqlStream.str()); // Create the parameter attributes. Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); Aws::DynamoDB::Model::AttributeValue infoMapAttribute; std::shared_ptr<Aws::DynamoDB::Model::AttributeValue> ratingAttribute = Aws::MakeShared<Aws::DynamoDB::Model::AttributeValue>( ALLOCATION_TAG.c_str()); ratingAttribute->SetN(rating); infoMapAttribute.AddMEntry(RATING_KEY, ratingAttribute); std::shared_ptr<Aws::DynamoDB::Model::AttributeValue> plotAttribute = Aws::MakeShared<Aws::DynamoDB::Model::AttributeValue>( ALLOCATION_TAG.c_str()); plotAttribute->SetS(plot); infoMapAttribute.AddMEntry(PLOT_KEY, plotAttribute); attributes.push_back(infoMapAttribute); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to add a movie: " << outcome.GetError().GetMessage() << std::endl; return false; } }

Use a SELECT statement to get an item.

// 3. Get the data for the movie using a "Select" statement. (ExecuteStatement) { Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "SELECT * FROM \"" << MOVIE_TABLE_NAME << "\" WHERE " << TITLE_KEY << "=? and " << YEAR_KEY << "=?"; request.SetStatement(sqlStream.str()); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to retrieve movie information: " << outcome.GetError().GetMessage() << std::endl; return false; } else { // Print the retrieved movie information. const Aws::DynamoDB::Model::ExecuteStatementResult &result = outcome.GetResult(); const Aws::Vector<Aws::Map<Aws::String, Aws::DynamoDB::Model::AttributeValue>> &items = result.GetItems(); if (items.size() == 1) { printMovieInfo(items[0]); } else { std::cerr << "Error: " << items.size() << " movies were retrieved. " << " There should be only one movie." << std::endl; } } }

Use an UPDATE statement to update an item.

// 4. Update the data for the movie using an "Update" statement. (ExecuteStatement) { rating = askQuestionForFloatRange( Aws::String("\nLet's update your movie.\nYou rated it ") + std::to_string(rating) + ", what new rating would you give it? ", 1, 10); Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "UPDATE \"" << MOVIE_TABLE_NAME << "\" SET " << INFO_KEY << "." << RATING_KEY << "=? WHERE " << TITLE_KEY << "=? AND " << YEAR_KEY << "=?"; request.SetStatement(sqlStream.str()); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(rating)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to update a movie: " << outcome.GetError().GetMessage(); return false; } }

Use a DELETE statement to delete an item.

// 6. Delete the movie using a "Delete" statement. (ExecuteStatement) { Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "DELETE FROM \"" << MOVIE_TABLE_NAME << "\" WHERE " << TITLE_KEY << "=? and " << YEAR_KEY << "=?"; request.SetStatement(sqlStream.str()); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to delete the movie: " << outcome.GetError().GetMessage() << std::endl; return false; } }
Go
SDK for Go V2
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

Use an INSERT statement to add an item.

// AddMovie runs a PartiQL INSERT statement to add a movie to the DynamoDB table. func (runner PartiQLRunner) AddMovie(movie Movie) error { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year, movie.Info}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("INSERT INTO \"%v\" VALUE {'title': ?, 'year': ?, 'info': ?}", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't insert an item with PartiQL. Here's why: %v\n", err) } return err }

Use a SELECT statement to get an item.

// GetMovie runs a PartiQL SELECT statement to get a movie from the DynamoDB table by // title and year. func (runner PartiQLRunner) GetMovie(title string, year int) (Movie, error) { var movie Movie params, err := attributevalue.MarshalList([]interface{}{title, year}) if err != nil { panic(err) } response, err := runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("SELECT * FROM \"%v\" WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't get info about %v. Here's why: %v\n", title, err) } else { err = attributevalue.UnmarshalMap(response.Items[0], &movie) if err != nil { log.Printf("Couldn't unmarshal response. Here's why: %v\n", err) } } return movie, err }

Use a SELECT statement to get a list of items and project the results.

// GetAllMovies runs a PartiQL SELECT statement to get all movies from the DynamoDB table. // pageSize is not typically required and is used to show how to paginate the results. // The results are projected to return only the title and rating of each movie. func (runner PartiQLRunner) GetAllMovies(pageSize int32) ([]map[string]interface{}, error) { var output []map[string]interface{} var response *dynamodb.ExecuteStatementOutput var err error var nextToken *string for moreData := true; moreData; { response, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("SELECT title, info.rating FROM \"%v\"", runner.TableName)), Limit: aws.Int32(pageSize), NextToken: nextToken, }) if err != nil { log.Printf("Couldn't get movies. Here's why: %v\n", err) moreData = false } else { var pageOutput []map[string]interface{} err = attributevalue.UnmarshalListOfMaps(response.Items, &pageOutput) if err != nil { log.Printf("Couldn't unmarshal response. Here's why: %v\n", err) } else { log.Printf("Got a page of length %v.\n", len(response.Items)) output = append(output, pageOutput...) } nextToken = response.NextToken moreData = nextToken != nil } } return output, err }

Use an UPDATE statement to update an item.

// UpdateMovie runs a PartiQL UPDATE statement to update the rating of a movie that // already exists in the DynamoDB table. func (runner PartiQLRunner) UpdateMovie(movie Movie, rating float64) error { params, err := attributevalue.MarshalList([]interface{}{rating, movie.Title, movie.Year}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("UPDATE \"%v\" SET info.rating=? WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't update movie %v. Here's why: %v\n", movie.Title, err) } return err }

Use a DELETE statement to delete an item.

// DeleteMovie runs a PartiQL DELETE statement to remove a movie from the DynamoDB table. func (runner PartiQLRunner) DeleteMovie(movie Movie) error { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("DELETE FROM \"%v\" WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't delete %v from the table. Here's why: %v\n", movie.Title, err) } return err }

Define a Movie struct that is used in this example.

// Movie encapsulates data about a movie. Title and Year are the composite primary key // of the movie in Amazon DynamoDB. Title is the sort key, Year is the partition key, // and Info is additional data. type Movie struct { Title string `dynamodbav:"title"` Year int `dynamodbav:"year"` Info map[string]interface{} `dynamodbav:"info"` } // GetKey returns the composite primary key of the movie in a format that can be // sent to DynamoDB. func (movie Movie) GetKey() map[string]types.AttributeValue { title, err := attributevalue.Marshal(movie.Title) if err != nil { panic(err) } year, err := attributevalue.Marshal(movie.Year) if err != nil { panic(err) } return map[string]types.AttributeValue{"title": title, "year": year} } // String returns the title, year, rating, and plot of a movie, formatted for the example. func (movie Movie) String() string { return fmt.Sprintf("%v\n\tReleased: %v\n\tRating: %v\n\tPlot: %v\n", movie.Title, movie.Year, movie.Info["rating"], movie.Info["plot"]) }
JavaScript
SDK for JavaScript (v3)
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

Create an item using PartiQL.

import { DynamoDBClient } from "@aws-sdk/client-dynamodb"; import { ExecuteStatementCommand, DynamoDBDocumentClient, } from "@aws-sdk/lib-dynamodb"; const client = new DynamoDBClient({}); const docClient = DynamoDBDocumentClient.from(client); export const main = async () => { const command = new ExecuteStatementCommand({ Statement: `INSERT INTO Flowers value {'Name':?}`, Parameters: ["Rose"], }); const response = await docClient.send(command); console.log(response); return response; };

Get an item using PartiQL.

import { DynamoDBClient } from "@aws-sdk/client-dynamodb"; import { ExecuteStatementCommand, DynamoDBDocumentClient, } from "@aws-sdk/lib-dynamodb"; const client = new DynamoDBClient({}); const docClient = DynamoDBDocumentClient.from(client); export const main = async () => { const command = new ExecuteStatementCommand({ Statement: "SELECT * FROM CloudTypes WHERE IsStorm=?", Parameters: [false], ConsistentRead: true, }); const response = await docClient.send(command); console.log(response); return response; };

Update an item using PartiQL.

import { DynamoDBClient } from "@aws-sdk/client-dynamodb"; import { ExecuteStatementCommand, DynamoDBDocumentClient, } from "@aws-sdk/lib-dynamodb"; const client = new DynamoDBClient({}); const docClient = DynamoDBDocumentClient.from(client); export const main = async () => { const command = new ExecuteStatementCommand({ Statement: "UPDATE EyeColors SET IsRecessive=? where Color=?", Parameters: [true, "blue"], }); const response = await docClient.send(command); console.log(response); return response; };

Delete an item using PartiQL.

import { DynamoDBClient } from "@aws-sdk/client-dynamodb"; import { ExecuteStatementCommand, DynamoDBDocumentClient, } from "@aws-sdk/lib-dynamodb"; const client = new DynamoDBClient({}); const docClient = DynamoDBDocumentClient.from(client); export const main = async () => { const command = new ExecuteStatementCommand({ Statement: "DELETE FROM PaintColors where Name=?", Parameters: ["Purple"], }); const response = await docClient.send(command); console.log(response); return response; };
  • For API details, see ExecuteStatement in AWS SDK for JavaScript API Reference.

PHP
SDK for PHP
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

public function insertItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ 'Statement' => "$statement", 'Parameters' => $parameters, ]); } public function getItemByPartiQL(string $tableName, array $key): Result { list($statement, $parameters) = $this->buildStatementAndParameters("SELECT", $tableName, $key['Item']); return $this->dynamoDbClient->executeStatement([ 'Parameters' => $parameters, 'Statement' => $statement, ]); } public function updateItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ 'Statement' => $statement, 'Parameters' => $parameters, ]); } public function deleteItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ 'Statement' => $statement, 'Parameters' => $parameters, ]); }
Python
SDK for Python (Boto3)
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

class PartiQLWrapper: """ Encapsulates a DynamoDB resource to run PartiQL statements. """ def __init__(self, dyn_resource): """ :param dyn_resource: A Boto3 DynamoDB resource. """ self.dyn_resource = dyn_resource def run_partiql(self, statement, params): """ Runs a PartiQL statement. A Boto3 resource is used even though `execute_statement` is called on the underlying `client` object because the resource transforms input and output from plain old Python objects (POPOs) to the DynamoDB format. If you create the client directly, you must do these transforms yourself. :param statement: The PartiQL statement. :param params: The list of PartiQL parameters. These are applied to the statement in the order they are listed. :return: The items returned from the statement, if any. """ try: output = self.dyn_resource.meta.client.execute_statement( Statement=statement, Parameters=params ) except ClientError as err: if err.response["Error"]["Code"] == "ResourceNotFoundException": logger.error( "Couldn't execute PartiQL '%s' because the table does not exist.", statement, ) else: logger.error( "Couldn't execute PartiQL '%s'. Here's why: %s: %s", statement, err.response["Error"]["Code"], err.response["Error"]["Message"], ) raise else: return output
  • For API details, see ExecuteStatement in AWS SDK for Python (Boto3) API Reference.

Ruby
SDK for Ruby
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

Select a single item using PartiQL.

class DynamoDBPartiQLSingle attr_reader :dynamo_resource attr_reader :table def initialize(table_name) client = Aws::DynamoDB::Client.new(region: "us-east-1") @dynamodb = Aws::DynamoDB::Resource.new(client: client) @table = @dynamodb.table(table_name) end # Gets a single record from a table using PartiQL. # Note: To perform more fine-grained selects, # use the Client.query instance method instead. # # @param title [String] The title of the movie to search. # @return [Aws::DynamoDB::Types::ExecuteStatementOutput] def select_item_by_title(title) request = { statement: "SELECT * FROM \"#{@table.name}\" WHERE title=?", parameters: [title] } @dynamodb.client.execute_statement(request) end

Update a single item using PartiQL.

class DynamoDBPartiQLSingle attr_reader :dynamo_resource attr_reader :table def initialize(table_name) client = Aws::DynamoDB::Client.new(region: "us-east-1") @dynamodb = Aws::DynamoDB::Resource.new(client: client) @table = @dynamodb.table(table_name) end # Updates a single record from a table using PartiQL. # # @param title [String] The title of the movie to update. # @param year [Integer] The year the movie was released. # @param rating [Float] The new rating to assign the title. # @return [Aws::DynamoDB::Types::ExecuteStatementOutput] def update_rating_by_title(title, year, rating) request = { statement: "UPDATE \"#{@table.name}\" SET info.rating=? WHERE title=? and year=?", parameters: [{ "N": rating }, title, year] } @dynamodb.client.execute_statement(request) end

Add a single item using PartiQL.

class DynamoDBPartiQLSingle attr_reader :dynamo_resource attr_reader :table def initialize(table_name) client = Aws::DynamoDB::Client.new(region: "us-east-1") @dynamodb = Aws::DynamoDB::Resource.new(client: client) @table = @dynamodb.table(table_name) end # Adds a single record to a table using PartiQL. # # @param title [String] The title of the movie to update. # @param year [Integer] The year the movie was released. # @param plot [String] The plot of the movie. # @param rating [Float] The new rating to assign the title. # @return [Aws::DynamoDB::Types::ExecuteStatementOutput] def insert_item(title, year, plot, rating) request = { statement: "INSERT INTO \"#{@table.name}\" VALUE {'title': ?, 'year': ?, 'info': ?}", parameters: [title, year, {'plot': plot, 'rating': rating}] } @dynamodb.client.execute_statement(request) end

Delete a single item using PartiQL.

class DynamoDBPartiQLSingle attr_reader :dynamo_resource attr_reader :table def initialize(table_name) client = Aws::DynamoDB::Client.new(region: "us-east-1") @dynamodb = Aws::DynamoDB::Resource.new(client: client) @table = @dynamodb.table(table_name) end # Deletes a single record from a table using PartiQL. # # @param title [String] The title of the movie to update. # @param year [Integer] The year the movie was released. # @return [Aws::DynamoDB::Types::ExecuteStatementOutput] def delete_item_by_title(title, year) request = { statement: "DELETE FROM \"#{@table.name}\" WHERE title=? and year=?", parameters: [title, year] } @dynamodb.client.execute_statement(request) end

For a complete list of AWS SDK developer guides and code examples, see Using DynamoDB with an AWS SDK. This topic also includes information about getting started and details about previous SDK versions.