SQLAlchemy composite primary key IntegrityError on insert

Question

I'm trying to create a composite primary key with SQLAlchemy however when adding data it's telling me that the columns are not unique but, as a pair, I'm sure that they are.

Is there something wrong with my syntax?

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
import dataset


Base = declarative_base()

class Work(Base):
    __tablename__ = 'Work'
    id = Column(String(25),primary_key=True)
    user = Column(String(10),primary_key=False)
    date = Column(Integer, primary_key=False)
    time = Column(Integer, primary_key=False)
    ticket = Column(String(10), primary_key=True)
    updated = Column(Integer, primary_key=False)
    timestamp = Column(Integer, primary_key=False)

engine = create_engine('sqlite:///work_items.db', pool_recycle=3600)
Base.metadata.create_all(engine)

I've defined the id and ticket columns as primary keys True, which is how I'm supposed to do it according to the docs - I just can't seem to figure out what's causing this issue.

I know that I could simply define the id column as a string composed of a concatenation of id+ticket, but I thought it would be better to the composite primary key feature because that's what the feature's for!

EDIT: It has been suggested that another question regarding defining a foreign key constraint on a composite primary key serves as an answer to this question. However, it does not: my database has only one table, and therefore no foreign key relationships. Despite that, I am still encountering an error:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) columns id, ticket are not unique

EDIT2: this is the error I'm getting:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) columns id, ticket are not unique 
[SQL: u'INSERT INTO "Work" (id, user, date, time, ticket, updated, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?)'] 
[parameters: (u'108-4900', u'kiba', u'1451390400000', u'30', u'S-1527', u'1452863269208', 1458724496050.0)]

Here's the thing: there's only one ticket with that name, and that ticket itself has only one id... so I'm really scratching my head here

EDIT3:

try:
    table['Work'].insert(dict(user=work_item.authorLogin,
    date=work_item.date,
    time=work_item.duration,
    id=work_item.id,
    ticket=issue.id,
    updated=issue.updated,
    timestamp=time.time()*1000.0))
except exc.SQLAlchemyError:
    print 'error'
    print work_item.authorLogin
    print work_item.date
    print work_item.duration
    print work_item.id
    print issue.id
    print issue.updated

Possible duplicate of [Relations on composite keys using sqlalchemy](http://stackoverflow.com/questions/7504753/relations-on-composite-keys-using-sqlalchemy) — Sameer Mirji, Mar 23 '16 at 10:10
@SameerMirji - thank you for providing that as a possible answer, however my database has only one table and no foreign keys, so the answer therein cannot solve my problem. — Charon, Mar 23 '16 at 10:17
Pretty sure your error is from having duplicate rows inserted for your composite key not the key name. If you inserted `[(1,2),(1,2)]` into the table under the same composite key you would see the same error as the entry already existed. — Padraic Cunningham, Mar 23 '16 at 10:27
@PadraicCunningham - that's what the error would imply... but it doesn't seem that way from my end. Please see edit2 — Charon, Mar 23 '16 at 10:52
I still think that is what is happening, the best thing to do is catch the error sqlite3.IntegrityError, keep a record of what caused the error and do a query on the db to see if it exists, then you will know for sure. — Padraic Cunningham, Mar 23 '16 at 10:56
@PadraicCunningham - Just fyi I tried running it again with error catching and no errors appeared... which is annoying. Obviously the bug lurks elsewhere in my code. — Charon, Mar 23 '16 at 13:22
I wrote the code to print the data that was going to be written to the DB if an issue arose.... which I presume would suffice to debug? — Charon, Mar 23 '16 at 13:30
What is the output from `except sqlite3.IntegrityError as e:print(e.message)` — Padraic Cunningham, Mar 23 '16 at 13:43
@PadraicCunningham - it didn't throw any errors this time... thank you for spending so much time on this - I have no idea why it's behaving so erratically. I'm dl'ing the data from an API so my only explanation at this point is that the source is changing whilst I'm dl'ing from it, but even that is unlikely. — Charon, Mar 23 '16 at 14:10
Now that I'm not using the `try` `except` block, the issue is reappearing..... — Charon, Mar 23 '16 at 14:44
@PadraicCunningham - when I tried to catch, the error simply did not appear. Here it is when I scripted it without a `catch` - http://pastebin.com/Tfkesruw - the thing is, I think I've found the bug now: I was dl'ing data from an API and sorting by time updated. However the amount of data was too large and so the query was broken up into blocks of data. The underlying data was changing, so when sorting by updated it meant some duplicate data was sent. I fixed it by changing the `table.insert` command into `table.upsert`. — Charon, Mar 23 '16 at 15:08
That makes sense, especially as there were inconsistent errors — Padraic Cunningham, Mar 23 '16 at 15:11
@PadraicCunningham - Yes. Even though the errors seemed to crop up randomly beforehand, I'm satisfied that my fix is the right one.I should say again, thank you so much for taking the time to help me. Pointing me in the direction of querying the DB helped me to uncover what had happened. — Charon, Mar 23 '16 at 15:14
No worries, glad you got it sorted, I have been there myself and it can be painful. — Padraic Cunningham, Mar 23 '16 at 15:15

SQLAlchemy composite primary key IntegrityError on insert

0 Answers0