How to implement SQLite using Golang

With the development of the Internet, data processing is becoming more and more important. Among them, relational database is a necessary part of many projects, and SQLite is a lightweight relational database that is widely used in various database-related applications. Golang, as a programming language with efficient execution speed and concise syntax style, has also received more and more attention. This article mainly introduces how to use Golang to implement SQLite.

1. Introduction to SQLite

SQLite is an open source lightweight relational database that supports multiple operating systems. It is designed to be embedded, that is, it can be embedded into other applications as an internal data storage engine, or it can run as a stand-alone database server. In Golang, we can access SQLite database by using go-sqlite3.

2. Install go-sqlite3

Before installing go-sqlite3, you need to install the SQLite database first, which can be downloaded from the official website (https://www.sqlite.org/download.html) . Environment variables need to be set during the installation process to facilitate access to SQLite in Golang.

Next, install go-sqlite3 through the go get command:

go get github.com/mattn/go-sqlite3

3. Establish a database connection

Before using golang to operate SQLite, you first need to establish a connection with it . The following is a simple example of establishing a SQLite database connection:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, err := sql.Open("sqlite3", "./test.db")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()

    // 测试连接是否成功
    err = db.Ping()
    if err != nil {
        fmt.Println(err)
        return
    }

    fmt.Println("database connected")
}

We established a SQLite database connection named test.db in the program through the sql.Open function. It should be noted here that the connection created using this function is a lightweight connection, so the connection needs to be closed explicitly after the function returns.

You can test whether the connection is successful through the db.Ping function. If successful, "database connected" will be printed.

4. Operation of the database

After establishing the database connection, the next step is various database operations. Below are some examples of common database operations.

1. Create data table

In SQLite, you can use SQL statements to create data tables. The following is a simple example of creating a data table:

_, err = db.Exec(`
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        age INTEGER,
        gender INTEGER
    )
`)
if err != nil {
    fmt.Printf("create table failed: %v\n", err)
}

In the code, we use the db.Exec function to execute the SQL statement that creates the data table. The return value of this function is nil after successful execution, otherwise an error message of type error is returned.

2. Inserting data

Inserting data is also a very common operation. The following is an example of inserting data:

res, err := db.Exec("INSERT INTO users(name, age, gender) VALUES (?, ?, ?)", "张三", 18, 1)
if err != nil {
    fmt.Printf("insert data failed: %v\n", err)
}

lastInsertId, _ := res.LastInsertId() // 获取自增长ID
fmt.Printf("last insert id: %d\n", lastInsertId)

In the code, we use the db.Exec function to execute a simple SQL statement to insert a piece of data into the data table. in? It is a placeholder, indicating that the actual data needs to be replaced by the placeholder when executing the SQL statement. If the execution is successful, the db.Exec function will return a Result type value, which contains the last self-increasing ID of the data.

3. Querying data

Querying data is also a very common operation. The following is a simple example of querying data:

rows, err := db.Query("SELECT id, name, age, gender FROM users WHERE age > ?", 18)
if err != nil {
    fmt.Printf("query data failed: %v\n", err)
    return
}

defer rows.Close()

for rows.Next() {
    var id int
    var name string
    var age int
    var gender int

    err := rows.Scan(&id, &name, &age, &gender)
    if err != nil {
        fmt.Printf("get data failed: %v\n", err)
        return
    }

    fmt.Printf("%d\t%s\t%d\t%d\n", id, name, age, gender)
}

In the code, we A simple query SQL statement was executed using the db.Query function to obtain all data with an age greater than 18 years old, and each piece of data was mapped to a variable through the Scan function.

4. Summary

This article briefly introduces how to use Golang to operate SQLite database. Although SQLite's functions are not as good as other large relational databases, it is also very suitable for use in some small projects. Combined with Golang's efficient execution speed and concise syntax style, various database operations can be quickly implemented, making our projects more efficient.

The above is the detailed content of How to implement SQLite using Golang. For more information, please follow other related articles on the PHP Chinese website!