Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
3
Help us understand the problem. What is going on with this article?
@kudojp

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

More than 1 year has passed since last update.

本当に雑多に走り書き、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とする
3
Help us understand the problem. What is going on with this article?
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.
Sign Up
If you already have a Qiita account Login
3
Help us understand the problem. What is going on with this article?