dataset comparsion not working as expected

[RafTim]

Suppose a Simple table

Footable: id | amount | used | ... ...

The query I want is simple:

SELECT * FROM Footable WHERE amount > used;

I thought I'm able to do this on a Table object:

with dataset.connect("...") as db:
    table = db["Footable"]
    results = table.find(amount: {">": 'used'})

But I get all entries

[pudo]

Hey @RafTim - I've reworked the documentation for how the advanced queries actually work (the old docstring was just plain wrong), see: https://dataset.readthedocs.io/en/latest/queries.html - can you perhaps be a tester for this? It should answer your question.

[luator]

The current documentation (table.find((column, operator, value))) is not working for me, it raises the following exception:

sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of type <class 'tuple'> instead

After looking at the code, I figured out that it works, when I use table._generate_clause instead of passing a tuple. I guess for the expected behaviour, this should happen internally for tuple arguments.

Minimal example for reproducing the issue:

import dataset

db = dataset.connect("sqlite:///:memory:")

table = db['sometable']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34))

print(table.find_one(age=37))  # good

print(table.find_one(("age", ">", 35)))  # exception

print(table.find_one(table._generate_clause("age", ">", 35)))  # good

I am using version 1.4.3 (installed via pip).

[garyrob]

This really needs to be fixed!! The feature simply doesn't work as documented.

The current documentation (table.find((column, operator, value))) is not working for me, it raises the following exception:

sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of type <class 'tuple'> instead

After looking at the code, I figured out that it works, when I use table._generate_clause instead of passing a tuple. I guess for the expected behaviour, this should happen internally for tuple arguments.

Minimal example for reproducing the issue:

import dataset

db = dataset.connect("sqlite:///:memory:")

table = db['sometable']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34))

print(table.find_one(age=37))  # good

print(table.find_one(("age", ">", 35)))  # exception

print(table.find_one(table._generate_clause("age", ">", 35)))  # good

I am using version 1.4.3 (installed via pip).