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?

Not directly related to your question: variable and function names should follow the `lower_case_with_underscores` style. — AMC, Dec 13 '19 at 22:16
I camelCase like this guy all day long. So agree to disagree @AlexanderCécile — Daniel Skovli, Dec 13 '19 at 22:18
@itypewithmyhands Agree to disagree with PEP 8 and the vast majority of libraries, including [Flask itself](https://flask.palletsprojects.com/en/1.1.x/styleguide/)? It’s not illegal to do otherwise, but these style guidelines exist for a reason, no? — AMC, Dec 13 '19 at 22:37
Well, honestly I have to confess I should decide for one style either way. To use test_schema and the camelCase variables in one file should, of course, bei improved. — Mxrsxpxxl, Dec 13 '19 at 22:40
@AlexanderCécile I also don't like newlines for curly braces in methods for `c#`. Perhaps I'm just a lost cause. Anyway, @Mxrsxpxxl is right, they should definitely pick a style and stick to it :) — Daniel Skovli, Dec 13 '19 at 23:46
@itypewithmyhands Newlines where? Do you mean those ones after the signature, on a new line? — AMC, Dec 14 '19 at 00:00
In order to get a good readability, I changed some names afterwards. Should be valueList instead of testdef. — Mxrsxpxxl, Dec 14 '19 at 13:52

score 0 · Answer 1 · answered Dec 14 '19 at 14:44

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.

Unfortunately the first() method did not return a useful result, so therefore I decided to use .all(), which should not return another result, as the requested values are all unique. — Mxrsxpxxl, Dec 14 '19 at 17:13

Avoid several json.loads from MySQL database while Reading

1 Answers1