CODEWITHSUNDEEP
pythonmysqlflaskponyorm
John Kitonyo
John Kitonyo

Reputation: 2419

Python/ PonyORM /MySQL - Query using multiple URL Args

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

Upvotes: 1

Views: 317

Answers (3)

John Kitonyo
John Kitonyo

Reputation: 2419

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.

Upvotes: 0

Alexander Kozlovsky
Alexander Kozlovsky

Reputation: 4849

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.

Upvotes: 1

Joost
Joost

Reputation: 3729

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.

Upvotes: 0

Related Questions