CODEWITHSUNDEEP
pythonsqlitesqlalchemy
xyzzyrz
xyzzyrz

Reputation: 16416

sqlalchemy query object over sqlite3 returning Nones

I'm using sqlalchemy 0.6.6 and sqlite 3.6.22 on Python 2.6. When I do:

In [1]: for i in ses.query(UserSnapshot):
   ...:     print i
   ...:     if i is None: break
   ...:

I see:

...
<twopt.UserSnapshot object at 0x86a52f50>
<twopt.UserSnapshot object at 0x86a55050>
<twopt.UserSnapshot object at 0x86a55110>
<twopt.UserSnapshot object at 0x86a551d0>
<twopt.UserSnapshot object at 0x86a55290>
<twopt.UserSnapshot object at 0x86a55350>
None

My schema:

class User(Base):
  __tablename__ = 'user' 
  id = Column(Integer, primary_key=True)
  blob = Column(LargeBinary, nullable=False)
  since = Column(DateTime, nullable=False)

class UserSnapshot(Base):
  __tablename__ = 'user_snapshot'
  id = Column(Integer, primary_key=True)
  uid = Column(Integer, ForeignKey(User.id), nullable=False)
  blob = Column(LargeBinary, nullable=False)
  time = Column(DateTime, nullable=False)

Short of dumping my entire DB, any hints as to what might cause None to be returned? I searched the documentation but couldn't find any leads. Thanks.

Upvotes: 0

Views: 155

Answers (1)

xyzzyrz
xyzzyrz

Reputation: 16416

Oy...this turned out to be due to my own fiddling with the DB outside of sqlalchemy, where I swapped out the user_snapshot table with a copy that had

id int primary key

instead of

id integer primary key

Apparently sqlite treats these differently:

sqlite> create table a (a integer primary key, b integer);
sqlite> insert into a (b) values (0);
sqlite> select * from a;
1|0
sqlite> create table b (a int primary key, b integer);
sqlite> insert into b (b) values (0);
sqlite> select * from b;
|0

The result was that many of my rows had NULL id's.

I couldn't find in http://www.sqlite.org/autoinc.html or http://www.sqlite.org/datatype3.html any mention of this peculiar distinguishing behavior. Anyway, that's that.

Upvotes: 1

Related Questions