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?
