Golang sqlite3数据库输出到变量

Question

I have just recently started faffing with golang. I taught myself php & jquery pretty easily & by creating a project to do so.

I am trying the same atm to teach myself golang, but I have gotten to a point now which either im missing the point or just searching incorrectly.

....

Ok so what I am trying to do is make a IRC bot. The one function listens to channel chatter & if certain commands are picked up it then will add relevant information to a sqlite3 database.

The problem is that one of the commands queries the database & will return multiple rows, which then need to be passed back to the original function & outputted into the IRC channel.

Where I am getting stuck is returning the queries output to the original function since it is multiple rows of data

Im importing the below libraries fmt net/textproto regexp strings os database/sql _ github.com/mattn/go-sqlite3

func getLineup() {
    // Open Database
    db, err := sql.Open("sqlite3", "./database.db")
    if err != nil {
            fmt.Println(err)
            os.Exit(1)
    }
    defer db.Close()

    // Prepare Query
    statement, err := db.Prepare("SELECT team, player FROM lineup ORDER BY team DESC;")
    if err != nil {
            fmt.Println(err)
            os.Exit(1)
    }

    // Execute Statement
    rows, err := statement.Query()
    defer rows.Close()


    fmt.Println("Lineup:")

    for rows.Next() {
            var team string
            var player string
            rows.Scan(&team, &player)
            fmt.Printf("%v %v
", team, player)
    }

}

So I can print it, but I need to pass it to another function which is where im lost

================================UPDATE====================================

Ok so here is my full code... @evanmcdonnal With the updated code you gave above I now get the error bot.go:70: cannot use p (type Player) as type *Player in append

package main

import (
    "fmt"
    "net/textproto"
    "regexp"
    "log"
    "strings"
    "database/sql"
    // SQLite3
    _ "github.com/mattn/go-sqlite3"
)


type PrivMsg struct {
    nick, channel, text string
}

var (
    conn *textproto.Conn
    err  error

    ping    = regexp.MustCompile("^PING :([a-zA-Z0-9\\.]+)$")
    motd    = regexp.MustCompile(":End of /MOTD command\\.$")
    privmsg = regexp.MustCompile("^:([a-zA-Z0-9`_\\-]+)![a-zA-Z0-9/\\\\\\.\\-]+@[a-zA-Z0-9/\\\\\\.\\-]+ PRIVMSG (#[a-zA-Z0-9]+) :(.*)$")
)


func talk(channel, msg string) {
    conn.Cmd("PRIVMSG " + channel + " :" + msg)
}


func handlePing(auth string) {
    conn.Cmd("PONG :" + auth)
    fmt.Printf("PONG :%s
", auth)
}

type Player struct {
    TeamName string
    PlayerName string
}

func getLineup() {
    // Open Database
    db, err := sql.Open("sqlite3", "./database.db")
    if err != nil {
        log.Fatal(err)
    }

    // Prepare Query
    statement, err := db.Prepare("SELECT team, player FROM lineup ORDER BY team DESC;")
    if err != nil {
        log.Fatal(err)
    }

    // Execute Statement
    rows, err := statement.Query()
    defer rows.Close()


// Output Code
    var Players []*Player
    for rows.Next() {
        p := &Player{}
        if err := rows.Scan(p.TeamName, p.PlayerName); err != nil{
            log.Fatal(err)
        }
        //Players = append(Players, p)
        return p.TeamName, p.PlayerName
    }
    // pass Players to next function/return it whatever

    fmt.Println(Players)
}




func handlePrivmsg(pm *PrivMsg) {
    if strings.Contains(pm.text, "!add t") {
        talk(pm.channel, pm.nick + " added to Terrorists")
        saveLineup("T", pm.nick)
    }
    if strings.Contains(pm.text, "!add ct") {
        talk(pm.channel, pm.nick + " added to Counter-Terrorists")
        saveLineup("CT", pm.nick)
    }
    if strings.Contains(pm.text, "!rem") {
        talk(pm.channel, pm.nick + " has been removed from the current lineup")
    }
    if strings.Contains(pm.text, "!votemap") {
        talk(pm.channel, pm.nick + " map vote code")
    }
    if strings.Contains(pm.text, "!moveme") {
        talk(pm.channel, pm.nick + " has been moved to Counter-Terrorists")
    }
    if strings.Contains(pm.text, "!teams") {
        getLineup()
        //fmt.Println(*tpList)
        talk(pm.channel, pm.nick + " will show the current teams")
    }
    if strings.Contains(pm.text, "!add ct") {
        talk(pm.channel, pm.nick + " added to Counter-Terrorists")
    }
    if strings.Contains(pm.text, "pug-bot") {
        talk(pm.channel, "Hello, " + pm.nick + "!")
    }
}


func saveLineup(Team, Player string) {

    // Open Database
    db, err := sql.Open("sqlite3", "./database.db")
    if err != nil {
        //log.Fatal(err)
        fmt.Printf("%s", err)
    }

    // Get Current Lineup
    rows, err := db.Query("SELECT team, player FROM lineup WHERE player = ?;", Player)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    if Player == Player {
        fmt.Println("You have already added yourself")
    } else {
        // Insert new Player
        db.Exec(
            "INSERT INTO lineup (team, player) VALUES (?, ?);",
            Team,
            Player,
        )
    }

}


func handleMotd() {
    conn.Cmd("JOIN #ircchannel")
    fmt.Println("JOIN #ircchannel")
}


func parseLine(line string) {
    // Channel activity
    if match := privmsg.FindStringSubmatch(line); match != nil {
        pm := new(PrivMsg)
        pm.nick, pm.channel, pm.text = match[1], match[2], match[3]
        handlePrivmsg(pm)
        return
    }

    // Server PING
    if match := ping.FindStringSubmatch(line); match != nil {
        handlePing(match[1])
        return
    }

    // End of MOTD (successful login to IRC server)
    if match := motd.FindString(line); match != "" {
        handleMotd()
        return
    }
}

func main() {
    conn, err = textproto.Dial("tcp", "irc.server.org:6667")
    if err != nil {
        fmt.Printf("%s", err)
        return
    }

    conn.Cmd("NICK pug-bot
USER pug-bot 8 * :pAsSwOrD")

    for {
        text, err := conn.ReadLine()
        if err != nil {
            fmt.Printf("%s", err)
            return
        }

        go parseLine(text)

        fmt.Println(text)
    }


}

Essentially I would like to pass the result of the sql query back into the talk(pm.channel, pm.nick + " SQL QUERY RESULT") irc talk section

Answer 1

I think what you're failing to understand is how to model the data in your Go app (after it's returned from the db). The variables you're using to store the values you read are only scoped for the loop and are singletons, how would you return a collection? Well by using a collection of course!

So here are the two options I would consider;

1) just concat everything into one big string. If you're going to dump the output to the console soon after his and you don't have any real processing to do, it's probably simplest to just declare a string before the loop, keep your scan as it is, append the results into the string in some common format like comma delimited then pass that to the function/write it to IRC or whatever.

2) use an actual collection. In any serious program you're probably going to do more with data coming from a db than print just print it. A more realistic implementation would be to create Player struct which has teamName and playerName fields. Before the loop you'd initialize a slice of Players or an array if you know how many results will be coming back. In the loop you would create a new instance and add it to the slice with the append function. After the loop you pass the slice/array to whatever needs to use the data next.

Here are couple samples. Note that these are both untested and I am not considering performance. For example if performance were a concern you should probably be using something like this for string concatenation How to efficiently concatenate strings in Go?

type Player struct {
    TeamName string
    PlayerName string
}

var Players []*Player
for rows.Next() {
        p := &Player{}
        if err := rows.Scan(p.TeamName, p.PlayerName); err != nil{
             // handle error
        }
        Players = append(Players, p)
}
// pass Players to next function/return it whatever


// simpler less robust option 1

lineUp := ""
for rows.Next() {
            var team string
            var player string
            rows.Scan(&team, &player)
            lineUp += team + ":" + player "
"
    }
return lineUp