1

I want to execute this query in SQLAlchemy

SELECT customer_name FROM Orders
WHERE order_id = 101 AND order_date BETWEEN '2018-07-01' AND '2018-07-31';

here is my Model:

class Orders(db.Model):
    id = db.Column(db.Integer,primary_key=True)
    order_id = db.Column(db.Integer,nullable=False)
    customer_name = db.Column(db.String(100),nullable=False)
    order_date = db.Column(db.String(100),nullable=False)
    remarks = db.Column(db.String(100),nullable=False)
Hamza Awan
  • 99
  • 1
  • 3
  • 9
  • What issues are you having that prevent you from doing that? Have you read for example https://stackoverflow.com/questions/8895208/sqlalchemy-how-to-filter-date-field? – Ilja Everilä Nov 05 '18 at 16:39
  • @IljaEverilä: thanks for that link, I've duplicated the post to there. – Martijn Pieters Nov 05 '18 at 16:40
  • sorry, i also want selected values in where clause. how can i execute this, SELECT * FROM Orders WHERE OrderID = 10248 AND OrderDate BETWEEN #01/07/1996# AND #31/07/1996#; in SQLAlchemy. – Hamza Awan Nov 05 '18 at 16:48
  • i have read this but my problem is little bit different. – Hamza Awan Nov 05 '18 at 16:50

1 Answers1

18

Just call the column.between() method, and pass in both dates (strings here, since you defined the column with db.String(100)). Pass the result of the call to the Query.filter() method.

Assuming you are using the Flask SQLAlchemy model as base:

Orders.query.filter(Orders.order_date.between('2018-07-01', '2018-07-31'))

As long as you use ISO8601 formatted strings as dates this'll work, but it'd be better if you used db.Date() as the column type, and then use Python's datetime.date() objects to do the comparison.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • how can i execute this, SELECT * FROM Orders WHERE OrderID = 10248 AND OrderDate BETWEEN #01/07/1996# AND #31/07/1996#; in SQLAlchemy. – Hamza Awan Nov 05 '18 at 16:46
  • @HamzaAwan: this is getting to be rather basic; just add another `filter()` to the query for the order id. – Martijn Pieters Nov 05 '18 at 22:40
  • Orders.query.filter(Orders.order_date.between('2018-07-01', '2018-07-31')).filter(orde_id = 101), you mean like this? – Hamza Awan Nov 06 '18 at 04:09
  • @HamzaAwan: `.filter()` takes clauses built by column expressions such as `Orders.order_id == 101`. You can use `.filter_by()` which takes column names as keyword arguments, so `Orders.query.filter(Orders.order_date.between('2018-07-01', '2018-07-31')).filter?by(order_id=101)` works too. `filter_by()` is limited to equality clauses. – Martijn Pieters Nov 06 '18 at 10:30
  • 1
    @HamzaAwan: see the [*Querying* section of the SQLAlchemy ORM tutorial](https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying), highly recommended reading! – Martijn Pieters Nov 06 '18 at 10:31
  • thanks Martin it's working. – Hamza Awan Nov 06 '18 at 14:50