Hello, I am using SQLAlchemy 0.9.4 with Python 2.6.6 on RHEL 6.5 with Oracle 11.2.0.3.
My simple application queries an LDAP directory to find particular records and then reflects the information into an Oracle table. The application is designed to keep the database table version of the information in sync with the LDAP directory as the system of record. Here is some pseudocode that shows the basic outline of what I am doing: class AdminGroup(Base): __tablename__ = 'admin_groups' id = Column(Integer, Sequence('seq_admin_groups_id'), primary_key = True) dn = Column(String) name = Column(String) display_name = Column(String) primary_mail = Column(String) def __repr__(self): return "<AdminGroup(dn='%s',name='%s',display_name='%s',primary_mail='%s')>" % ( self.dn, self.name, self.display_name, self.primary_mail) db_engine = create_engine(...) SessionClass = sessionmaker(bind = db_engine) # loop every N minutes session = SessionClass() ldap_groups = ldap_connection.search_ext_s(...) for dn, attr_dict in ldap_groups: # see if group is represented in database table admin_group = session.query(AdminGroup).filter(AdminGroup.dn == "%s" % dn).first() if admin_group: # group exists so see if any updates needed else: # create the group in the database table admin_group = AdminGroup(dn = dn, name = name, ...) session.add(admin_group) # done with all groups so commit and sleep until next iteration session.commit() Note that the class AdminGroup() is written to use the sequence seq_admin_groups_id to generate the primary key. I created the sequence in the Oracle database using sqlplus and the command: CREATE SEQUENCE seq_admin_groups_id START WITH 1 INCREMENT BY 1; COMMIT; I then ran the Python code and it performed as I expected, creating roughly 500 rows in the table. It continued to run fine for a few days, creating and deleting rows now and then. After a few days the code began throwing this exception: 2014-05-30 16:32:24,337 ERROR Master: Caught database exception while provisioning: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occuring prematurely) (IntegrityError) ORA-00001: unique constraint (MYSCHEMA.SYS_C0015706) violated 'INSERT INTO admin_groups (id, dn, name, display_name, primary_mail) VALUES (seq_admin_groups_id.nextval, :dn, :name, :display_name, :primary_mail) RETURNING admin_groups.id INTO :ret_0' {'dn': 'cn=somename,ou=groups,dc=bc,dc=edu', 'primary_mail': 'somen...@my.edu', 'display_name': 'somename', 'name': 'somename', 'ret_0': <cx_Oracle.NUMBER with value None>} I investigated and found that the sequence seq_admin_groups_id was now at the value 68 after having been used previously to insert rows with IDs in the 500s. I stopped the code and used sqlplus to change the sequence back to a value in the high 500s. I then restarted the code and the exception no longer occurred. I am unable to explain how the sequence seq_admin_groups_id went from in the 500s and working fine to suddenly being 68. The only place in the Python code where the sequence is used explicitly is in the definition of the AdminGroup() class. I would be grateful for any insights on how the sequence might have become out of sync or anything I can change in the code to prevent it from happening again. Thank you for your consideration. Scott P.S. I should add that I am using cx_Oracle and the connection string oracle+cx_oracle://user:password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=somehost)(PORT=1521)))(CONNECT_DATA=(SID=MYSID))) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.