使用参数将数据插入Spanner

问题描述

我想将变量传递给我的SQL插入语句.Spanner似乎使用以下语法来指定变量@param.如 https://cloud.google.com/spanner/docs/lexical中所述#query-parameters .我是Python的新手，所以我认为我不太了解语法，因为它会引发错误找不到绑定参数:messageid".相同的代码将使用硬编码值正确执行.

I want to pass variables to my SQL insert statement. Spanner seems to use the following syntax to specify a variable @param. As described in https://cloud.google.com/spanner/docs/lexical#query-parameters. I am new to Python so I don't think I am quite understand the syntax because it throws the error "No parameter found for binding: messageid" The same code executes correctly with hard coded values.

这是我的代码:

messageid = str(uuid.uuid4())
sender = newmsg['Sender']
recipient = newmsg['Recipient']
message = newmsg['Message']

def insert_message(transaction):
    self.logger.debug("Inside insert_with_dml")
    row_ct = transaction.execute_update(
        "INSERT MESSAGE_STORE (MessageId, Message, MessageRecipient, MessageSender) "
        " VALUES (@messageid, @message, @recipient, @sender)"
    )

更新:如果有人感兴趣，这是工作代码

Update: Here is the working code if anyone is interested

def insertmessage(self, newmsg):
            messageid = str(uuid.uuid4())
            sender = newmsg['Sender']
            recipient = newmsg['Recipient']
            message = newmsg['Message']

            data_type = param_types.Struct([
                    param_types.StructField('Id', param_types.STRING),
                    param_types.StructField('Msg', param_types.STRING),
                    param_types.StructField('Requestor', param_types.STRING),
                    param_types.StructField('MsgRecipient', param_types.STRING)
            ])

            data_values = (messageid, message, recipient, sender)

            def insert_message(transaction):
                self.logger.debug("Inside insert_with_dml")
                row_ct = transaction.execute_update(
                    "INSERT MESSAGE_STORE (MessageId, Message, MessageRecipient, MessageSender) "
                        "VALUES (@values.Id, @values.Msg, @values.Requestor, @values.MsgRecipient)",
                        params={'values' : data_values},
                        param_types={'values' : data_type}
            )

                self.logger.debug("{} record(s) inserted.".format(row_ct))

            try:
                self.client.run_in_transaction(insert_message)
            except Exception as e:
                self.logger.debug(e)

            self.logger.debug("Before Output")
            output = "{ 'Message Id':" +  messageid +", 'Result Code': '1' }"
            return output

推荐答案

请参见在SQL查询中将STRUCT对象用作绑定参数

这篇关于使用参数将数据插入Spanner的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！