Post JSON to database with Python SQLAlchemy

Question

I am trying to add JSON data posted from client to my database, the code I've tried with:

@app.route('/postjson', methods=['POST'])
def postEventViewer():
    print(request.is_json)

    content = request.get_json()
    items = []
    for item in content:
        items.append(item['Values'])
        items.append(item['Count'])
        items.append(item['Group'])
        items.append(item['Name'])

        values = item['Values']
        count = item['Count']
        group = item['Group']
        name = item['Name']

        engine.execute('INSERT INTO EventViewer (Values, Count, Group, Name) VALUES (:value, :count, :group, :name)', value=values, count=count, group=group, name=name)

This gives me the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "Values": syntax error

But using the same code with less parameters in a equivalent database works fine and adds to the database just as expected:

engine.execute('INSERT INTO person (name, balance) VALUES (:name, :balance)', name=name, balance=count)

What am I doing wrong here? Thanks!

Jaldhi Mehta · Accepted Answer · 2020-01-10 08:56:22Z

This error is because Values is a reserved keyword in sql. You need to wrap it in double-quotes to consider it as the column name.

EDIT

You have one more keyword used as a column name which is Group. You need to wrap it as well in double-quotes.

Try the below query.

@app.route('/postjson', methods=['POST'])
def postEventViewer():
    print(request.is_json)

    content = request.get_json()
    items = []
    for item in content:
        items.append(item['Values'])
        items.append(item['Count'])
        items.append(item['Group'])
        items.append(item['Name'])

        values = item['Values']
        count = item['Count']
        group = item['Group']
        name = item['Name']

        engine.execute('INSERT INTO EventViewer ("Values", Count, "Group", Name) VALUES (:value, :count, :group, :name)', value=values, count=count, group=group, name=name)

Collectives™ on Stack Overflow

Post JSON to database with Python SQLAlchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related