0

I am stuck with a query in a function. here is my code: def action(changePin, action): pins = Pins.query.all() changePin = int(changePin) deviceName = Pins.query.filter_by(pin=changePin, name) if action == "on": GPIO.output(changePin, GPIO.HIGH) print("turned ", deviceName , " on") if action =="off": GPIO.output(changePin, GPIO.LOW) print("turned ", deviceName , " off") for pin in pins: db.session.commit() The error for this is 

 File "<stdin>", line 4
SyntaxError: positional argument follows keyword argument

In line 4 I want to find the name of the pin relating to the pin "changePin", this is adapted code from a tutorial, here is the origional code where a dictionary holds the pin information not a database, code:

deviceName = pins[changePin]['name']

I have tried numerous different ways but none work, here is a list of the different versions of line 4:

deviceName = Pins.query.filter_by(changePin=pin).name
deviceName = Pins.query.filter_by(changePin, name=name)
deviceName = Pins.query.filter_by(Pins.pin=changePin, Pins.Name)
deviceName = Pins.query(Pins.pin=changePin, Pins.Name)
deviceName = Pins.query(**changePin, Pins.name)
deviceName = Pins.query(**changePin)
deviceName = db.session.filter_by(Pins.changePin)
deviceName = db.session(Pins).filter_by(pin=changePin)

and many other variations, I have read the sqlalchemy docs and the flask docs, but I have not seen any comparisons, I have looked at and tried this; flask sqlalchemy query with keyword as variable but got this;

Traceback (most recent call last): File "<stdin>", line 1, in <module> File "<stdin>", line 4, in action TypeError: BaseQuery object argument after ** must be a mapping, not int this is my models.py code;

class Pins(db.Model):
id = db.Column(db.Integer, primary_key=True)
pin = db.Column(db.Integer, index=True, unique=True)
name = db.Column(db.String(64))
upDown = db.Column(db.String(4))
state = db.Column(db.String(9))

def __repr__(self):
    return '<Valves {}>'.format(self.pin)

Querying with function on Flask-SQLAlchemy model gives BaseQuery object is not callable error

Dear Menno Thank you for comming back to me I have followed your advice and it works!!!

rows = Pins.query.filter_by(pin=changePin).all()
deviceName = rows[0].name

I don't understand how the "rows[0].name" part works but it does, thank you thank you

help regards Paul

pascale
  • 145
  • 2
  • 13

1 Answers1

1

A filter_by expression selects rows, not fields. From the documentation: "apply the given filtering criterion to a copy of this Query, using keyword expressions.". Name is not a keyword expression, just a name.

Also, after building the query, you have to run it. You do that by calling all() on it:

deviceName = Pins.query.filter_by(pin=changePin).all()

Letś also change the name of the outcome:

rows = Pins.query.filter_by(pin=changePin).all()

Calling that returns a list with zero or more rows, hence the name. rows[0] (the first row) is what you want. After you have a row, a field becomes an attribute of the row: rows[0].name. You can also use one() to get a unique row.

If you still need that: To limit the columns that are returned use load only cols

Menno Hölscher
  • 567
  • 1
  • 3
  • 9
  • Thank you so much for answering!! I have not understood your reply, but I have looked at the "load only cols" and changed my code to ' deviceName = db.session.query(Pins).options(load_only("state")) ' there are no error messages but this prints "turned SELECT pins.id AS pins_id, pins.state AS pins_state FROM pins off " the pin changes state but I want the associated name to be printed. I do not understand enough to know how to express myself in the question!! – pascale Mar 02 '19 at 22:19
  • What is printed is the query that SQLAlchemy wants to send to the database. Superhandy for debugging, just not what you want ;=). I applied my recipe to your original code. Hope this makes it clearer. – Menno Hölscher Mar 03 '19 at 20:18
  • Dear Menno, Thank you for clarifying your first answer, as you can see I have posted my new code into My question and it works. I did not understand that the '.all() ' calls the query, and in the second part rows[0].name works for all rows in the database not just the first. Finally does this mean that the results of "rows = Pins.query.filter_by(pin=changePin).all()" is a dictionary? Kind regards Paul – pascale Mar 03 '19 at 22:17
  • From the SQLAlchemy documentation: "Return the results represented by this Query as a list.". So no, not a dictionary, but a list. – Menno Hölscher Mar 04 '19 at 12:11