douye4051
2018-08-24 07:27
浏览 1.0k
已采纳

如何一次使用GORM读取一行SQLite数据库

I have the following code

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func getDatabaseHandle(dbpath string) (*sql.DB, error) {
    database, err := sql.Open("sqlite3", dbpath)
    if err != nil {
        log.Printf("Failed to create the handle")
        return nil, err
    }
    if err = database.Ping(); err != nil {
        fmt.Printf("Failed to keep connection alive")
        return nil, err
    }
    return database, nil
}

func getAllRows(database *sql.DB, table string) {
    query := fmt.Sprintf("SELECT User, AppName FROM %s LIMIT 10", table)
    rows, err := database.Query(query)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var app string
        rows.Scan(&id, &app)
        fmt.Println(id, app)
    }
}

func main() {
    db, err := getDatabaseHandle("./gozo.db")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    getAllRows(db, "timesheet")
}

This is against a SQLite database which has following columns

id, User, Matter, AppName, AppDesc, Duration, Type, Timestamp

The above code word perfectly. But there are two problems

  1. I need to declare variables for each of the column that I intend to use inside the for loop that does for rows.Next() which is annoying and can't be made modular easily.

  2. I need to use an ORM tool for database portability and all.

Hence I tied GORM and here is my code

type Timesheet struct {
    id        int
    User      int
    Matter    int
    AppName   string
    AppDesc   string
    Duration  int64
    Type      string
    Timestamp string
}

// TableName -- Sets the table name
func (ts Timesheet) TableName() string {
    return "timesheet"
}

func main() {
    db, err := gorm.Open("sqlite3", "./gozo.db")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    var ts []Timesheet
    db.Find(&ts).Limit(5)
    fmt.Println(ts)
}

But this doesn't give me data properly instead it gave me all values 0. Moreover this is not using each row scanning iteration so that I can wrap this go concurrency while performing some other related operation. This seems to be pulling all the data and that is also wrong. Please let me know how get scan each row in a loop and get the right data using GORM.

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doushun4666 2018-08-27 06:56
    已采纳

    I have found an answer. The GORM documentation was not so verbose but the flowing could be the answer.

    package main
    
    import (
        "fmt"
    
        "github.com/jinzhu/gorm"
        _ "github.com/jinzhu/gorm/dialects/sqlite"
    )
    
    // Product -- Represents a product
    type Product struct {
        gorm.Model
        Code  string
        Price uint
    }
    
    // TableName setting the table name
    func (Product) TableName() string {
        return "allProducts"
    }
    
    func main() {
        db, err := gorm.Open("sqlite3", "test.db")
        if err != nil {
            panic("failed to connect database")
        }
        defer db.Close()
        var product Product
        rows, err := db.Model(&Product{}).Rows()
        defer rows.Close()
        if err != nil {
            panic(err)
        }
        for rows.Next() {
            db.ScanRows(rows, &product)
            fmt.Println(product)
        }
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题