Python/ PonyORM /MySQL - Query using multiple URL Args

Question

I have a URL https://example.com?p1=nai&p2=tex&p3=mak

I am using PONY ORM + Flask, on MYSQL. I would like to get all rows that match the URL parameters. The p1, p2 and p3 are fields on the table and can vary depending on user input

In MySQL I would have something like done [SELECT * FROM Table Name WHERE p1=nai&p2=tex]

Below my PONY/Flask code

query = select(r for r in Route)
for key,value in args.items():
    query = query.where("lambda r: r.%s == '%s'" %(key, value))
    print(r.id)

My query above just returns a match for the first parameter. i.e if p1 = nai then it will return all where p1= nai and ignore the rest. What am I missing? NB: Don't worry about SQL Injection

Alexander Kozlovsky · Accepted Answer · 2018-04-19 18:03:04Z

1

If you want to filter arguments with PonyORM by exact match, you can specify keyword arguments to filter or where methods:

kwargs = {'name': 'John', 'age': 20}
query = Route.select().where(**kwargs)

for more complex queries it is possible to use getattr:

for name, value in kwargs.items():
    query = query.where(lambda r: getattr(r, name) == value)

My query above just returns a match for the first parameter.

I suspect it is caused by some bug in your code, maybe you assign new query to query2 and then use previous query instead of query2.

answered Apr 19, 2018 at 18:03

Alexander Kozlovsky

4,8791 gold badge24 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joost · Accepted Answer · 2018-04-19 17:33:07Z

You can use the request arguments together with the **kwargs functionality python has. Lets pretend we want to look for someone in our userdatabase named bob, which is 22 years old:

http://127.0.0.1:5000/?name=bob&age=22

You can convert the request arguments to a dictionary like this:

argumentdict = request.args.to_dict()

You can then query our users using the kwargs

users = User.query.filter_by(**argumentdict).all()

which is the same thing as:

users = User.query.filter_by(name='bob', age='22').all()

which will then put all users with the name bob and the age 22 in a list.

Fully working snippet:

from flask import Flask, render_template, request, render_template_string
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    age = db.Column(db.Integer)

db.create_all()

for name, age in [['Bob', 45], ['John', 55], ['Bob', 22]]:
    user = User()
    user.name = name
    user.age = age
    db.session.add(user)

db.session.commit()

@app.route("/")
def index():
    argumentdict = request.args.to_dict()
    users = User.query.filter_by(**argumentdict).all()
    print(users)

app.run()

with this query: http://127.0.0.1:5000/?name=bob&age=22, will print:

[<User 3>]

It will give you errors though, when using it with invalid request arguments (name=bob&age=22&country=russia), so you have to take that into account.

EDIT: I completely missed the PONY ORM bit, sorry about that. I don't know anything about it, but if you can query in the same way as in SQL-Alchemy (column1=value1, column2=value2, ...) it should work with this example as well. I'll leave it here anyways because it's still useful for those who use SQL-Alchemy.

John Kitonyo · Accepted Answer · 2018-04-20 02:33:47Z

0

Heads Up

 kwargs = {'name': 'John', 'age': 20})
    query = Register.select().where(**kwargs)
    resp= []
    for r in query:
        resp.append({"id":r.id,.,...............)
    return jsonify(resp)

The above answer by Alexander is correct but will only return 1 response. You might have to create a python array and append the multiple values in the query result.

answered Apr 20, 2018 at 2:33

John Kitonyo

2,4691 gold badge18 silver badges18 bronze badges

Collectives™ on Stack Overflow

Python/ PonyORM /MySQL - Query using multiple URL Args

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related