DML 寫入資料

使用 DML 寫入資料。

深入探索

如需包含這個程式碼範例的詳細說明文件，請參閱下列文章：

程式碼範例

C++

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。

void DmlGettingStartedInsert(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;

  auto commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto insert = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES"
                " (12, 'Melissa', 'Garcia'),"
                " (13, 'Russell', 'Morales'),"
                " (14, 'Jacqueline', 'Long'),"
                " (15, 'Dylan', 'Shaw')"));
        if (!insert) return std::move(insert).status();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Insert was successful [spanner_dml_getting_started_insert]\n";
}

C#

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class WriteUsingDmlCoreAsyncSample
{
    public async Task<int> WriteUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
            "INSERT Singers (SingerId, FirstName, LastName) VALUES "
               + "(12, 'Melissa', 'Garcia'), "
               + "(13, 'Russell', 'Morales'), "
               + "(14, 'Jacqueline', 'Long'), "
               + "(15, 'Dylan', 'Shaw')");
        int rowCount = await cmd.ExecuteNonQueryAsync();

        Console.WriteLine($"{rowCount} row(s) inserted...");
        return rowCount;
    }
}

Go

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func WriteDataWithDml(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Add 4 rows in one statement.
	// The database/sql driver supports positional query parameters.
	res, err := db.ExecContext(ctx,
		"INSERT INTO Singers (SingerId, FirstName, LastName) "+
			"VALUES (?, ?, ?), (?, ?, ?), "+
			"       (?, ?, ?), (?, ?, ?)",
		12, "Melissa", "Garcia",
		13, "Russel", "Morales",
		14, "Jacqueline", "Long",
		15, "Dylan", "Shaw")
	if err != nil {
		return err
	}
	c, err := res.RowsAffected()
	if err != nil {
		return err
	}
	fmt.Fprintf(w, "%v records inserted\n", c)

	return nil
}

Java

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

class WriteDataWithDml {
  static class Singer {
    private final long singerId;
    private final String firstName;
    private final String lastName;

    Singer(final long id, final String first, final String last) {
      this.singerId = id;
      this.firstName = first;
      this.lastName = last;
    }
  }

  static void writeDataWithDml(String host, int port, String database) throws SQLException {
    String connectionUrl = String.format("jdbc:postgresql://%s:%d/%s", host, port, database);
    try (Connection connection = DriverManager.getConnection(connectionUrl)) {
      // Add 4 rows in one statement.
      // JDBC always uses '?' as a parameter placeholder.
      try (PreparedStatement preparedStatement =
          connection.prepareStatement(
              "INSERT INTO singers (singer_id, first_name, last_name) VALUES "
                  + "(?, ?, ?), "
                  + "(?, ?, ?), "
                  + "(?, ?, ?), "
                  + "(?, ?, ?)")) {

        final List<Singer> singers =
            Arrays.asList(
                new Singer(/* SingerId= */ 12L, "Melissa", "Garcia"),
                new Singer(/* SingerId= */ 13L, "Russel", "Morales"),
                new Singer(/* SingerId= */ 14L, "Jacqueline", "Long"),
                new Singer(/* SingerId= */ 15L, "Dylan", "Shaw"));

        // Note that JDBC parameters start at index 1.
        int paramIndex = 0;
        for (Singer singer : singers) {
          preparedStatement.setLong(++paramIndex, singer.singerId);
          preparedStatement.setString(++paramIndex, singer.firstName);
          preparedStatement.setString(++paramIndex, singer.lastName);
        }

        int updateCount = preparedStatement.executeUpdate();
        System.out.printf("%d records inserted.\n", updateCount);
      }
    }
  }
}

Node.js

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

try {
  await database.runTransactionAsync(async transaction => {
    const [rowCount] = await transaction.runUpdate({
      sql: `INSERT Singers (SingerId, FirstName, LastName) VALUES
      (12, 'Melissa', 'Garcia'),
      (13, 'Russell', 'Morales'),
      (14, 'Jacqueline', 'Long'),
      (15, 'Dylan', 'Shaw')`,
    });
    console.log(`${rowCount} records inserted.`);
    await transaction.commit();
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  await database.close();
}

Node.js

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。

import { Client } from 'pg';

async function writeDataWithDml(host: string, port: number, database: string): Promise<void> {
  const connection = new Client({
    host: host,
    port: port,
    database: database,
  });
  await connection.connect();

  const result = await connection.query("INSERT INTO singers (singer_id, first_name, last_name) " +
      "VALUES ($1, $2, $3), ($4, $5, $6), " +
      "       ($7, $8, $9), ($10, $11, $12)",
       [12, "Melissa", "Garcia",
        13, "Russel", "Morales",
        14, "Jacqueline", "Long",
        15, "Dylan", "Shaw"])
  console.log(`${result.rowCount} records inserted`);

  // Close the connection.
  await connection.end();
}

PHP

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。

function write_data_with_dml(string $host, string $port, string $database): void
{
    $dsn = sprintf("pgsql:host=%s;port=%s;dbname=%s", $host, $port, $database);
    $connection = new PDO($dsn);

    $sql = "INSERT INTO singers (singer_id, first_name, last_name)"
                        ." VALUES (?, ?, ?), (?, ?, ?), "
                        ."        (?, ?, ?), (?, ?, ?)";
    $statement = $connection->prepare($sql);
    $statement->execute([
        12, "Melissa", "Garcia",
        13, "Russel", "Morales",
        14, "Jacqueline", "Long",
        15, "Dylan", "Shaw"
    ]);
    printf("%d records inserted\n", $statement->rowCount());

    $statement = null;
    $connection = null;
}

Python

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。

import string
import psycopg


def write_data_with_dml(host: string, port: int, database: string):
    with psycopg.connect("host={host} port={port} dbname={database} "
                         "sslmode=disable".format(host=host,
                                                  port=port,
                                                  database=database)) as conn:
        conn.autocommit = True
        with conn.cursor() as cur:
            cur.execute("INSERT INTO singers (singer_id, first_name, last_name)"
                        " VALUES (%s, %s, %s), (%s, %s, %s), "
                        "        (%s, %s, %s), (%s, %s, %s)",
                        (12, "Melissa", "Garcia",
                         13, "Russel", "Morales",
                         14, "Jacqueline", "Long",
                         15, "Dylan", "Shaw",))
            print("%d records inserted" % cur.rowcount)

Ruby

如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱「Spanner 用戶端程式庫」。

如要向 Spanner 進行驗證，請設定應用程式預設憑證。詳情請參閱「為本機開發環境設定驗證機制」。

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id
row_count = 0

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES
     (12, 'Melissa', 'Garcia'),
     (13, 'Russell', 'Morales'),
     (14, 'Jacqueline', 'Long'),
     (15, 'Dylan', 'Shaw'),
     (16, 'Billie', 'Eillish'),
     (17, 'Judy', 'Garland'),
     (18, 'Taylor', 'Swift'),
     (19, 'Miley', 'Cyrus'),
     (20, 'Michael', 'Jackson'),
     (21, 'Ariana', 'Grande'),
     (22, 'Elvis', 'Presley'),
     (23, 'Kanye', 'West'),
     (24, 'Lady', 'Gaga'),
     (25, 'Nick', 'Jonas')"
  )
end

puts "#{row_count} records inserted."

Rust

use google_cloud_spanner::client::DatabaseClient;
use google_cloud_spanner::statement::Statement;

pub async fn sample(client: &DatabaseClient) -> anyhow::Result<()> {
    let runner = client.read_write_transaction().build().await?;

    runner
        .run(async |transaction| {
            let sql = r#"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES
                       (12, 'Melissa', 'Garcia'),
                       (13, 'Russell', 'Morales'),
                       (14, 'Jacqueline', 'Long'),
                       (15, 'Dylan', 'Shaw')"#;
            let statement = Statement::builder(sql).build();
            let row_count = transaction.execute_update(statement).await?;
            println!("{row_count} records inserted.");
            Ok(())
        })
        .await?;

    Ok(())
}

後續步驟

如要搜尋及篩選其他 Google Cloud 產品的程式碼範例，請參閱Google Cloud 範例瀏覽工具。