Faheem Mitha wrote:
>
> Hi,
>
> The following script is then followed by its output, and
> finally by the
> table output.
>
> I don't get what is going on here. Yes, I should commit the
> session, and
> the table is empty as expected, but why does the id keep
> incrementing on
> successive runs, and where is this table living, if not in
> the db? I'd
> expect to see the id stay at 1. Also, I'd expect to see something in
> session.dirty. Deleting the table resets the counter back to 1, so it
> looks like it is using the table in some way, but as already
> stated, the
> table shows as empty via a select * command.
>
> If anyone can clarify what is going on here and satisfy my
> curiosity, I'd
> appreciate it. Please CC me on any reply. Thanks.
>
>
> Regards, Faheem.
>
> ***************************************************************
> oddity.py
> ***************************************************************
>
> from sqlalchemy import *
> from sqlalchemy.orm import mapper, relation, sessionmaker
>
> def add_obj(session, obj):
> """ Check if object primary key exists in db. If so,exit, else
> add.
> """
> from sqlalchemy import and_
> from sqlalchemy.orm import object_mapper
> mapper = object_mapper(obj)
> pid = mapper.primary_key_from_instance(obj)
> criterion = and_(*(col == val for col, val in
> zip(mapper.primary_key,
> mapper.primary_key_from_instance(obj))))
> if session.query(obj.__class__).filter(criterion).count() > 0:
> print "%s object with id %s is already in
> db."%(type(obj).__name__, pid)
> exit
> else:
> session.add(obj)
>
> metadata = MetaData()
>
> mytest_table = Table(
> 'mytest', metadata,
> Column('id', Integer, primary_key=True),
> )
>
> class MyTest(object):
> def __init__(self):
> pass
>
> mapper(MyTest, mytest_table)
>
> dbstring = "postgres://username:pas...@localhost:5432/oddity"
> db = create_engine(dbstring)
> metadata.bind = db
> metadata.create_all()
> conn = db.connect()
>
> Session = sessionmaker()
> session = Session()
> t1 = MyTest()
> add_obj(session, t1)
> print session.query(MyTest).count()
>
> stmt = mytest_table.select()
> for row in stmt.execute():
> print row
>
> stmt = select([mytest_table.c.id])
> print "anno statement is %s\n"%stmt
> for row in stmt.execute():
> print row
>
> print "session.dirty is %s"%session.dirty
>
> #session.commit()
> #session.flush()
> #conn.close()
>
> *************************************
> script output
> *************************************
> $ python oddity.py
> 1
> (1,)
> anno statement is SELECT mytest.id
> FROM mytest
>
> (1,)
> session.dirty is IdentitySet([])
> $ python oddity.py
> 1
> (2,)
> anno statement is SELECT mytest.id
> FROM mytest
>
> (2,)
> session.dirty is IdentitySet([])
>
> ************************************
> table output
> ************************************
> oddity=# select * from mytest;
> id
> ----
> (0 rows)
>
I've never used postgres, but I believe auto-incrementing counters are
implemented using database sequences. I think these are incremented
outside of a transaction - this ensures that two seperate database
connections using the sequence at the same time will get distinct
values. So although you aren't commiting your transaction, the sequence
still advances.
I guess the sequence must be associated with the table, so when you drop
the table it destroys the sequence as well (I don't know if this is SA
behaviour or PG behaviour).
session.dirty only contains objects that have been loaded from the DB
and subsequently modified. You don't ever actually modify your object,
so it shouldn't appear in session.dirty. (It should appear in
session.new though)
Hope that helps,
Simon
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
