Help us understand the problem. What is going on with this article?

Golangのデータベース操作を雑多にまとめる

本当に雑多に走り書き、DBはsqlite3を用いる。

テーブルの作成

func main() {
    DbConnection,  _ := sql.Open("sqlite3", "./data.sql")
    defer DbConnection.Close()
    cmd := `CREATE TABLE IF NOT EXISTS person(
                name STRING,
                age INT)`
    _, err := DbConnection.Exec(cmd)
    if err != nil {
        log.Fatalln(err)
    }
}

テーブルに挿入

func main() {
    DbConnection := sql.Open("sqlite3", "./data.sql")
    defer DbConnection.Close()
    cmd := "INSERT INTO person (name age) VALUES (?, ?)"
    _, err := DbConnection.Exec(cmd, "Nancy", 24)
    if err != nil {
        log.Fatalln(err)
    }
}

テーブルを更新

func main() {
    DbConnection := sql.Open("sqlite3", "./data.sql")
    defer DbConnection.Close()
    cmd := "UPDATE person SET age = ? WHERE user_id = ?"
    _, err := DbConnection.Exec(cmd, "1234)
    if err != nil {
        log.Fatalln(err)
    }
}

テーブルからMultiple Selectし、結果をPerson構造体のスライスに詰める

DbConnection.Queryを使い、結果をrows.Next()でループを回す

type Person struct {
    Name string
    Age int
}

func main() {
    DbConnection := sql.Open("sqlite3", "./data.sql")
    defer DbConnection.Close()

    cmd := "SELECT * FROM person WHERE"
    rows, _ := DbConnection.Query(cmd)

    var persons []Person
    for rows.Next() {
        var p Person
        err := rows.Scan(&p.Name, &p.Age)
        if err != nil {
            log.Println(err)
        }
        persons = append(persons, p)
    }
}

テーブルからSingle Selectし、結果をPerson構造体にする

DbConnection.QueryRowを使う。

func main() {
    DbConnection := sql.Open("sqlite3", "./data.sql")
    defer DbConnection.Close()

    cmd := "SELECT * FROM person WHERE age = ?"
    row := DbConnection.QueryRow(cmd, 28)

    var Person p
    err := row.Scan(&p.Name, &p.Age)

    if err != nil {
        // 条件を満たすものが見つからない場合
        if err == sql.ErrNoRows {
            log.Println("No row")
        // そういうわけではないがエラー
        log.Println(err)
    }
}

テーブルからデータを削除

func main() {
    DbConnection := sql.Open("sqlite3", "./data.sql")
    defer DbConnection.Close()

    cmd := "DELETE FROM person WHERE user_id = ?"
    row := DbConnection.Exec(cmd, 1234)

    if err != nil {
        log.Fatalln(err)
    }
}

クエリインジェクション対策

  • valueは?でエスケープする
  • ただし、table名は?で挿入できず、%sをplaceholderとする
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした