Google Flex App Engine not writing to Cloud SQL database

Question

EDIT: Solved, see bottom

I am working on a project that receives data from a Meraki Scanning API and writes that JSON data to a Cloud SQL database. The code originally wrote fine to a local MySQL database, but I've tried about 800 different configurations and tweaks and nothing seems to be working. I have a connection between Flex App Engine and Cloud SQL, the database and tables are there, but no writing/INSERT statements are being run. This is a Flask app using SQLAlchemy (NOT Flask_SQLAlchemy)

Meraki Scanning API docs for reference: https://documentation.meraki.com/MR/Monitoring_and_Reporting/Scanning_API

from api import app
from sqlalchemy import create_engine
from datetime import datetime, timedelta


engine = create_engine(app.config.get('database_uri'))
class Events(object)
@staticmethod
def add(event):
    connection = engine.connect()
    for observation in event["observations"]:
        try:
            connection.execute("""INSERT INTO events (
                apMac,
                apTags,
                apFloors,
                clientMac,
                ipv4,
                ipv6,
                seenTime,
                seenEpoch,
                ssid,
                rssi,
                manufacturer,
                os,
                lat,
                lng,
                unc,
                x,
                y
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )""", (
                event.get("apMac", None),
                ",".join(event.get("apTags", [])),
                ",".join(event.get("apFloors", [])),
                observation.get("clientMac", None),
                observation.get("ipv4").strip("/") if observation.get("ipv4") is not None else None,
                observation.get("ipv6", None),
                observation.get("seenTime", None),
                observation.get("seenEpoch", None),
                observation.get("ssid", None),
                observation.get("rssi", None),
                observation.get("manufacturer", None),
                observation.get("os", None),
                observation.get("location", {}).get("lat", None),
                observation.get("location", {}).get("lng", None),
                observation.get("location", {}).get("unc", None),
                observation.get("location", {}).get("x", None) if observation.get("location", {}).get("x", None) != [] else None,
                observation.get("location", {}).get("y", None) if observation.get("location", {}).get("y", None) != [] else None,
            ))
        except:
            pass

    connection.close()
    return "Added"

That is the Event class with the function that adds to it. This is the other script that deals with providing a validator so Meraki will begin to POST JSON data to the page:

from flask import request
from api import app
from api.models import Events


@app.route('/', methods=['GET'])
def events_get():
    return str(app.config.get("validator"))


@app.route('/', methods=['POST'])
def events_post():
    data = request.json
    if data["secret"] != app.config.get("secret"):
        return
    Events.add(data["data"])
    return "."

Database URI is set as (I have tried with just mysql as well as mysql+pymysql also):

mysql+mysqldb://<user>:<pw>@/<database>?unix_socket=/cloudsql/<instance connection name>

I am receiving POSTs but no data on Cloud SQL. Any help is greatly appreciated. Thank you.

SOLUTION: I had to revert to MySQL 5.6 on Cloud SQL. That combined with ALLOW_INVALID_DATES flag for MySQL fixed this and kept the time as a DateTime.

Answer 1

Try to enable SQLAlchemy logging as suggested here:

import logging

logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

Now, second thing I see is that you catch ALL exceptions and do NOTHING. It's considered a bad practice in general. Apart from that it might give you a clue why you're not getting any data inserted. Remove try...except or add exception logging to see what's going on - might help you here.

Finally, you can try to use transactional approach, not sure. Example from SQLAlchemy connection docs:

trans = connection.begin()
try:
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), col1=7, col2='this is some data')
    trans.commit()
except:
    trans.rollback()
    raise

UPDATE: Also, not sure if your syntax execute("INSERT .... VALUES (%s, %s, ...), (value1, value2)) works, but if you said it did for local server, then you probably can ignore this one.

What I see in official docs is
engine.execute("insert into users values (?, ?)", 1, "john"), i.e. question marks ? instead of %s and unwound arguments, not a list.

UPDATE2: ALso make sure Events.add is actually called, put prints everywhere to see values. In your case maybe it's secret value mismatch.