英文:
Handling inserts into a database with varying and large number of columns in golang
问题
所以我有一个使用golang编写的程序，它从kafka队列中读取JSON数据，将其转换为普通的扁平语法，并将其推送到数据库表中。该表有200多个列，如果数据不包含某个列，应该插入一个空值。
目前，我可以通过手动形成INSERT语句（包含当前字段）来实现这一点，但问题在于数据库参数，我如何将参数传递给db.Query？
要填充的数据库是PostgreSQL。
英文:
So I have golang program that reads a JSON off a kafka queue, makes it into normal flat syntax and pushes to a database table. The table is has (200+) columns, if the data doesn't contain a column, a null should be inserted.
Currently I can do that by forming the INSERT statement manually(with the present fields), problem comes with database arguments, how do I pass the argument to db.Query ?
The database to be populated is postgresql.
答案1
得分: 3
假设如果未设置列值，则列值将默认为null，下面是一种方法。
创建一个将列名映射到interface{}值的映射表：
args := make(map[string]interface{})
设置你感兴趣的列值：
args["col_x"] = ...
args["col_y"] = ...
准备SQL语句：
sql = "insert into tab ( xxx ) values ( yyy )"
其中xxx是列名列表，yyy是值替换字符的列表，每个值对应一个字符，具体根据数据库而定，例如？
最后，将args展开为可变参数，执行查询：
db.Exec(sql, args...)
英文:
Assuming column values will default to null if not set, here's one approach.
Create a map of column names to interface{} values:
args := make(map[string]interface{})
Set the column values you're interested in:
args["col_x"] = . . .
args["col_y"] = . . .
Prepare the sql statement:
sql = "insert into tab ( xxx ) values ( yyy )"
where xxx is the list of column names, and yyy is the list of value replacement characters, one for each value, specific to the database, eg, ?
Finally execute the query, expanding args as a variadic parameter:
db.Exec(sql, args...)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论