Python Spanner Client API INSERT DML Doesn't Insert data or return error

Question

I have a class that allows me to reuse the spanner Client connection. I am trying to do a basic insert with DML and am unable to accomplish this within my class. I can insert data using the command line:

cloud spanner databases execute-sql queue --instance=sandbox --sql="INSERT MESSAGE_STORE (MessageId,Message,MessageRecipient,MessageSender) VALUES ( 'id','hello spanner','fred','bob')"

However, when I try to do the equivalent using the python client libraries it doesn't insert a row or even throw an error. I have debug set to true so that shouldn't be an issue. I come from a C/C++ background and am new to python so the error may lie there, I'm not sure.

Here is my class code:

class DataStore():

def __init__(self):

        self.logger = logging.getLogger('manager.sub')
        loghandler = logging.StreamHandler(sys.stdout)
        loghandler.setFormatter(logging.Formatter(
        '%(asctime)s - %(name)s - %(levelname)s - %(message)s'))
         self.logger.addHandler(loghandler)
         self.logger.setLevel(logging.DEBUG)

        client = spanner.Client()
        self.instance = client.instance(INSTANCE_ID)
        database = self.instance.database(DATABASE_ID)
        self.client = database

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

    # Values are hardcoded for now until I can get it to work

    def insert_message(transaction):
            row_ct = transaction.execute_update(
                "INSERT MESSAGE_STORE (MessageId, Message, MessageRecipient, MessageSender) "
            " VALUES ('id','hello spanner' , 'fred' , 'bob') "
            )

            print("{} record(s) inserted.".format(row_ct))

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

    # Hardcoded for now until I can actually get the data inserted
    output = "{ 'Message Id':" +  str(messageid) +", 'Result Code': '1' }"
    return output

Answer 1

Your issue appears to be due to Python's strict requirement for proper spacing and indenting. Update the insert_message function to be structured like the following:

def insert_message(transaction):
    row_ct = transaction.execute_update(
        "INSERT MESSAGE_STORE (MessageId, Message, MessageRecipient, MessageSender) "
        " VALUES ('id', 'hello spanner', 'fred', 'bob')"
    )

    print("{} record(s) inserted.".format(row_ct))

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