Avoid several json.loads from MySQL database while Reading

Question

I'm a newbie to programming in python and would like to set up a MySQL database with flask on pythonanywhere.com. I execute the Reading/Writing process from/to the database using Marshmallow-sqlalchemy. Right now, I'm a bit staggered about the following complicated reading process:

@app.route('/getElement')
def getElement():
    idU1=["abc","def","ghi"]
    newTest=Test(idU1=json.dumps(idU1))
    db.session.add(newTest)
    db.session.commit()
    entryString = test_schema.dumps(Test.query.with_entities(Test.idU1).filter_by(idm=1).all())  #Browser views [{"idU1": "[\"abc\", \"def\", \"ghi\"]"}]
    entryList = json.loads(entryString)
    entryDict = entryList[0] #Browser views {"idU1": "[\"abc\", \"def\", \"ghi\"]"}
    valueString = entryDict['idU1']
    valueList = json.loads(valueString)
    result =  valueList[2]
    return json.dumps(result) #Browser views "ghi", which again should be loaded for processing

That's how I set up my file:

app = Flask(__name__)
app.config["DEBUG"] = True

SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}".format(
    username="Ehrismann",
    password="abcdefgh",
    hostname="Ehrismann.mysql.pythonanywhere-services.com",
    databasename="Ehrismann$default",
)

app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI  # connection specs
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299  # don't care
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)  # actually make connection
ma = Marshmallow(app)  # instantiate MarshmallowObject


class Test(db.Model):  # new Table: comment

    __tablename__ = "test"
    idm = db.Column(db.Integer, primary_key=True)  # new Column
    idU1=db.Column(db.String(100), nullable=False)

class TestSchema(ma.ModelSchema):
    class Meta:
        model=Test

test_schema = TestSchema(many=True)

So, any ideas on how to simplify my code?

Answer 1

There are two obvious improvements that can be made here:

• There is no need to serialise the query result; inside a function you use the object returned by the query, and only serialise to json when necessary, for example when data is to be sent across the network.
• There is no need to fetch all rows in the query if you are only going to operate on the first row.

@app.route('/getElement')
def getElement():
    idU1=["abc","def","ghi"]
    newTest=Test(idU1=json.dumps(idU1))
    db.session.add(newTest)
    db.session.commit()

    first_result = Test.query.with_entities(Test.idU1).filter_by(idm=1).first()
    entryDict = json.loads(first_result)
    valueString = entry_dict['idU1']
    valueList = json.loads(valueString)
    result =  valueList[2]
    return json.dumps(result)

MySQL has a json column type; using this instead of a string column would avoud the need for calling json.loads on the query result.