CODEWITHSUNDEEP
pythonsqlalchemy
crooksey
crooksey

Reputation: 8809

SQLAlchemy not playing nicely with non unique primary keys

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

Answers (2)

knitti
knitti

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

sebastian
sebastian

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

Related Questions