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

如何一次使用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)
        }
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 Bibtex4Word 引用中文文献
  • ¥20 用opencv c/c++ 转换成灰度图,然后做一下直方图均衡,输出mp4文件
  • ¥20 matlab中的双层数值积分
  • ¥50 服务器打印水晶报表问题
  • ¥30 gradle环境下javafx项目如何使用druid连接池
  • ¥15 服务器打印水晶报表问题
  • ¥18 深度学习tensorflow1,ssdv1,coco数据集训练一个模型
  • ¥100 关于注册表摄像头和麦克风的问题
  • ¥30 代码本地运行正常,但是TOMCAT部署时闪退
  • ¥15 关于#python#的问题