I will start by saying that this is not my database, I did not design or create it, I am simply connecting to it to write an application, do not judge me on the use of non unique indexing!
I am connecting to a MSSQL database using the FreeTDS driver (version 8.0). When I run a query from SQLA (on two different machines) I get 72 rows, however, when I query from visual studio (on a windows machine) I get the correct result of 165 rows, here is my code.
class OrderLine(BaseRO): __tablename__ = 'orderline' ol_orderno = Column(Integer, ForeignKey('orderhead.oh_orderno'), primary_key=True) ol_linestatus = Column(Integer) ol_reqdate = Column(Date) ol_statusdate = Column(Date) ol_stocktype = Column(String) statuss = [40, 60] orders = DBSessionRO.query(OrderLine).\ filter(OrderLine.ol_reqdate == date_today).\ filter(OrderLine.ol_stocktype == 5).\ filter(OrderLine.ol_linestatus.in_(statuss)).all() len(orders) 72 # This generates this sql.. SELECT orderline.ol_orderno AS orderline_ol_orderno, orderline.ol_linestatus AS ol_linestatus, orderline.ol_reqdate AS orderline_ol_reqdate, orderline.ol_statusdate AS orderline_ol_statusdate, orderline.ol_stocktype AS orderline_ol_stocktype FROM orderline WHERE orderline.ol_reqdate = 2015-05-11 AND orderline.ol_stocktype = 5 AND orderline.ol_linestatus IN (40, 60)
Now I am fairly sure what the problem is, the column ol_orderno is not unique so when the following gets generated...
sqlalchemy generates SELECT orderline.ol_orderno AS orderline_ol_orderno
It is overwriting all previous instances of the same ol_orderno, I have noticed this once before but assumed it was a database error as it occurred during a testing phase, is there any way to combat this whilst still using the orm with declarative base?