Reputation: 8809
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?
Upvotes: 3
Views: 2506
Reputation: 7033
Yes and No:
No if you want to keep this database schema - SQLalchemy has no way to tell the objects apart if they cannot be IDentified - this is no primary key, at least not a complete one. - You really don't want to update rows, when you don't know which row will be updated.
Yes, if you want to try the following: add an auto-incrementing ID column to the table and to the primary key. Once you've done this, every row (in ORM: every object) is uniquely identifiable and thus usable with SQLalchemy.
Upvotes: 0
Reputation: 9696
If there's any combination of columns in your database that is unique,
you could use these as a composite primary key in SQLAlchemy.
To so, simply add those columns to a composite primary key, via primary_key=True
.
class OrderLine(BaseRO):
__tablename__ = 'orderline'
ol_orderno = Column(Integer, ForeignKey('orderhead.oh_orderno'),
primary_key=True)
ol_linestatus = Column(Integer, primary_key=True)
ol_reqdate = Column(Date)
ol_statusdate = Column(Date)
ol_stocktype = Column(String)
This SQLAlchemy would e.g. treat all rows with different combinations of order_no, linestatus
as distinct objects.
Upvotes: 4