如何使用Go / GORM在没有预定义结构的情况下打印SELECT查询的输出?

huangapple go评论82阅读模式
英文:

How to use Go / GORM to print SELECT query output without pre-defined struct

问题

我正在使用Go开发一个API,该API连接到MySQL数据库执行一些查询操作。我正在使用GORM进行数据库操作。但是我在打印没有列名的表的SELECT查询结果时遇到了问题。

我的使用场景是,在我不知道表的列名和类型的情况下,需要在多个表上运行查询。因此,我无法预先为当前和未来可能添加的所有表定义一个结构体。

有没有一种方法可以在没有预定义的结构体的情况下打印/保存SELECT查询的结果?

我尝试使用空结构体,但没有帮助。

附注:我是Go的初学者

    type Testing struct{}

	var test Testing
	dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v", myds.DBuser, myds.DBpassword, myds.DBhost, myds.DBport, myds.DBname)
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		fmt.Println(err)
	}
	tx := db.Raw(query).Scan(&test)
	if tx.Error != nil {
		fmt.Println(tx.Error)
	}
	fmt.Println(test)
英文:

I am developing an API using Go which connects to MySQL database for some query execution. Am using GORM for database operations. But am stuck at printing the SELECT query output for the tables which I don't have the column names.

My use case is that, I need to run the query on multiple tables where I don't have an idea about what their column names and types are. And so I cannot pre-define a struct for all the current and future tables which might get added.

Is there a way to print/save the SELECT query output without a pre-defined struct ?

I tried do some using empty struct but it didn't help me.

P.S: Am a beginner in Go

    type Testing struct{}

	var test Testing
	dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v", myds.DBuser, myds.DBpassword, myds.DBhost, myds.DBport, myds.DBname)
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		fmt.Println(err)
	}
	tx := db.Raw(query).Scan(&test)
	if tx.Error != nil {
		fmt.Println(tx.Error)
	}
	fmt.Println(test)

答案1

得分: 0

你可以使用一个匿名结构体。

假设你有一个结构体

type User struct{
  FirstName string
  LastName string
}

查询语句如下:

SELECT CONCAT(first_name,last_name) AS full_name from users;

注意新的列 full_name

你可以简单地这样做:

var fullName = struct{FullName string}{}

注意我使用了帕斯卡命名法,并且FullName必须是字段名。两个大写字母之间将表示一个下划线_。字段是公开的,因此可以在外部访问。

full_name(查询) = FullName(字段)

将这个fullName对象作为一个桶传递给Scan函数,它应该可以工作。

db.Raw(query).Scan(&fullName)

编辑

你的查询应该有一些结果,对吗?让我假设你有:

column_one,column_two... column_n

现在,要获取所有列的数据或者选择的列(如果你想要的话),你只需要在匿名结构体中定义具有特定名称的字段。在我们的例子中:

struct{ColumnOne,ColumnTwo,..ColumnN interface{}}{}

注意,我使用了interface{},你可以根据列返回的数据类型使用相应的类型。

英文:

You can use an anonymous struct

Let's say you have a struct:

type User struct{
  FirstName string
  LastName string
}

Query:

SELECT CONCAT(first_name,last_name) AS full_name from users;

Notice the new column full_name

you can simply do

var fullName = struct{FullName string}{}

Notice how I use pascal case & FullName has to be the field name
A capital letter in between will represent a _
Field is public so it can be accessed outside.

full_name(query) = FullName(field)

pass this fullName object as a bucket to your Scan and it should work.

db.Raw(query).Scan(&fullName)

EDIT:

Your query will have some result right?
Let me assume that you have

column_one,column_two... column_n

Now, to get the data from all the columns or selected ones if you want, you simply have to define fields (in anonymous struct) with specific names. In our case:

struct{ColumnOne,ColumnTwo,..ColumnN interface{}}{}

P.S. I have used interface{}, you can use types depending on the data your column returns.

答案2

得分: 0

使用带有接口的映射类型对我起作用。这帮助我在没有预定义结构或列名的情况下保存了SELECT查询的结果。

dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v", myds.DBuser, myds.DBpassword, myds.DBhost, myds.DBport, myds.DBname)

db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
    fmt.Println(err)
}

var result []map[string]interface{}

tx := db.Raw(query).Scan(&result)
if tx.Error != nil {
    fmt.Println(tx.Error)
    return
}
bytes, _ := json.Marshal(result)
fmt.Println(string(bytes))
英文:

It worked for me by using a map type with interface. This helped me to save the SELECT query results without pre-defined struct or the column names.

    dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v", myds.DBuser, myds.DBpassword, myds.DBhost, myds.DBport, myds.DBname)

    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		fmt.Println(err)
	}

	var result []map[string]interface{}

	tx := db.Raw(query).Scan(&result)
	if tx.Error != nil {
		fmt.Println(tx.Error)
		return
	}
	bytes, _ := json.Marshal(result)
	fmt.Println(string(bytes))

</details>



huangapple
  • 本文由 发表于 2022年7月19日 19:35:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/73036158.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定