doumindang2416
2016-11-02 11:50
浏览 150

为什么此sqlx的copyin语句挂起?

I've written a toy app to experiment with using Postgresql through sqlx. I got a mass insert working using

pq.CopyIn

as content of a prepared statement

stmt, _ := tx.Preparex(pq.CopyIn(tablename, column, column, ...)

I would then proceed to add rows to the mass insert I'm creating.

tx.Exec(..., ..., ...)

then finally execute the prepared statement

stmt.Exec()

This worked perfectly before, but now I've come back to it and try and execute this code, it hangs on the

stmt.Exec

Am I missing something in my code or is this all to do with the Database Engine, being unresponsive.

Here's my full code for this.

package main

import (
    _ "database/sql"
    "fmt"
    "log"
    "encoding/json"
    "io/ioutil"
    "os"

    "github.com/jmoiron/sqlx"
    "github.com/lib/pq"
)

var schema = `
    CREATE TABLE IF NOT EXISTS contact (
        id Serial,
        first_name text,
        last_name text,
        email text
);`

type Contact struct {
    Id int            `json:"-"`
    First_name string `json:"first_name"`
    Last_name string  `json:"last_name"`
    Email string      `json:"email"`
}

type Contacts struct {
    Contacts []Contact `json:"contacts"`
}

func (c *Contacts) createFromJSON(json_str []byte) error {
    b := []byte(json_str)
    err := json.Unmarshal(b, &c)
    if err != nil {
        log.Fatal(err)
    }

    return err
}

func (c *Contacts) save(db *sqlx.DB) error {
    tx := db.MustBegin()

    stmt, _ := tx.Preparex(pq.CopyIn("contact", "first_name", "last_name", "email"))

    for _, contact := range c.Contacts {
        tx.Exec(contact.First_name, contact.Last_name, contact.Email)

    }

    _, err := stmt.Exec()

    if err != nil {
        log.Fatal(err)
        return err
    }
    err = stmt.Close()
    if err != nil {
        log.Fatal(err)
        return err
    }

    tx.Commit()

    return nil
}

func connect() (*sqlx.DB, error) {
    db, err := sqlx.Connect("postgres", "user=pqgotest dbname=pqgotest sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }

    return db, err
}


func createTables(db *sqlx.DB) {
    db.MustExec(schema)
}


func main() {
    db, err := connect()
    if err != nil {
        os.Exit(1)
    }

    createTables(db)
    contactsJson, e := ioutil.ReadFile("./contacts.json")
    if e != nil {
        fmt.Printf("File error: %v
", e)
        os.Exit(1)
    }

    tx := db.MustBegin()
    tx.MustExec("DELETE FROM contact")
    tx.Commit()

    contacts := new(Contacts)

    contacts.createFromJSON(contactsJson)

    contacts.save(db)

    people := new(Contacts)
    db.Select(people.Contacts, "SELECT * FROM contact ORDER BY email,id ASC")

    for _, contact := range people.Contacts {
        contact_json, err := json.Marshal(contact)
        if err != nil {
            log.Fatal(err)
            os.Exit(1)
        }
        fmt.Printf("%s
", contact_json)
    }


}

I could include the contents of the contacts.json file as well, if that will help.

UPDATE

Yes it was obvious in the end. I was creating a statement from tx,

stmt, _ := tx.Preparex(pq.CopyIn(tablename, column, column, ...)

and further additions to this should be to stmt

stmt.Exec(..., ..., ...)

Also another error not directly related to the question is where I insert an array of contacts into the Contacts field of the struct Contacts

people := new(Contacts)
db.Select(people.Contacts, "SELECT * FROM contact ORDER BY email,id ASC")

should be passing a pointer to the Select method of db of the Contacts array field of Contacts, like so

db.Select(&people.Contacts, "SELECT * FROM contact ORDER BY email,id ASC")

In case people try and run this code later and wonder why it's not printing the results to the console.

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

1条回答 默认 最新

  • doufuhao0566 2016-11-02 17:49
    已采纳

    From Bulk imports part in https://godoc.org/github.com/lib/pq, it should be

    stmt.Exec(contact.First_name, contact.Last_name, contact.Email)
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题