[sqlalchemy] Re: Create tables within a transaction
Ok. I'll do some testing against other engines when I get a chance.
Thanks for helping.
Cheers
Chris Miles
On Feb 6, 2:36 pm, Michael Bayer wrote:
> sqlite doesn't include CREATE TABLE statements within the scope of a
> transaction. I think that's a relatively rare behavior only seen in
> Postgres, in fact - I dont think Oracle or MySQL have that behavior,
> for example.
>
> On Feb 5, 2009, at 10:01 PM, Chris Miles wrote:
>
>
>
> > That did the trick, thanks.
>
> > Well, actually, it did the trick for PostgreSQL but sqlite isn't
> > rolling back. The SA logs show the same commands are being sent to
> > both. Here's an example:
>
> > $ rm test1.sqlite
> > $ python sa_create_table_transaction_test.py
> > 2009-02-06 13:39:29,006 INFO sqlalchemy.engine.base.Engine.0x..d0
> > BEGIN
> > 2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0
> > CREATE TABLE table1 (
> > col1 INTEGER
> > )
>
> > 2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> > 2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0
> > CREATE TABLE table2 (
> > col1 INTEGER
> > )
>
> > 2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> > 2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0
> > CREATE TABLE table2 (
> > col1 INTEGER
> > )
>
> > 2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> > 2009-02-06 13:39:29,027 INFO sqlalchemy.engine.base.Engine.0x..d0
> > ROLLBACK
> > $ sqlite3 test1.sqlite
> > Loading resources from /Users/chris/.sqliterc
> > SQLite version 3.4.0
> > Enter ".help" for instructions
> > sqlite> .tables
> > table1 table2
> > sqlite>
>
> > And the updated test script:
>
> > #
> > import sqlalchemy as sa
>
> > engine = sa.create_engine('sqlite:///test1.sqlite')
> > #engine = sa.create_engine('postgres://localhost/test1')
> > engine.echo = True
> > metadata = sa.MetaData()
>
> > table1 = sa.Table("table1", metadata,
> > sa.Column('col1', sa.types.Integer,),
> > )
> > table2 = sa.Table("table2", metadata,
> > sa.Column('col1', sa.types.Integer,),
> > )
> > table3 = sa.Table("table3", metadata,
> > sa.Column('col1', sa.types.Integer,),
> > )
>
> > metadata.bind = engine
> > connection = engine.connect()
> > trans = connection.begin()
> > try:
> > table1.create(bind=connection)
> > table2.create(bind=connection)
> > table2.create(bind=connection)
> > table3.create(bind=connection)
> > trans.commit()
> > except:
> > trans.rollback()
> > #
>
> > Cheers,
> > Chris
>
> > On Feb 6, 2:08 am, Michael Bayer wrote:
> >> create() and create_all() take a "bind" argument which can be an
> >> engine or connection. you want the connection in this case.
>
> >> On Feb 5, 2009, at 5:27 AM, Chris Miles wrote:
> >>> I can't find away to link table.create() with the existing
> >>> transaction.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Create tables within a transaction
sqlite doesn't include CREATE TABLE statements within the scope of a
transaction. I think that's a relatively rare behavior only seen in
Postgres, in fact - I dont think Oracle or MySQL have that behavior,
for example.
On Feb 5, 2009, at 10:01 PM, Chris Miles wrote:
>
> That did the trick, thanks.
>
> Well, actually, it did the trick for PostgreSQL but sqlite isn't
> rolling back. The SA logs show the same commands are being sent to
> both. Here's an example:
>
> $ rm test1.sqlite
> $ python sa_create_table_transaction_test.py
> 2009-02-06 13:39:29,006 INFO sqlalchemy.engine.base.Engine.0x..d0
> BEGIN
> 2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0
> CREATE TABLE table1 (
>col1 INTEGER
> )
>
>
> 2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> 2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0
> CREATE TABLE table2 (
>col1 INTEGER
> )
>
>
> 2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> 2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0
> CREATE TABLE table2 (
>col1 INTEGER
> )
>
>
> 2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> 2009-02-06 13:39:29,027 INFO sqlalchemy.engine.base.Engine.0x..d0
> ROLLBACK
> $ sqlite3 test1.sqlite
> Loading resources from /Users/chris/.sqliterc
> SQLite version 3.4.0
> Enter ".help" for instructions
> sqlite> .tables
> table1 table2
> sqlite>
>
>
> And the updated test script:
>
> #
> import sqlalchemy as sa
>
> engine = sa.create_engine('sqlite:///test1.sqlite')
> #engine = sa.create_engine('postgres://localhost/test1')
> engine.echo = True
> metadata = sa.MetaData()
>
> table1 = sa.Table("table1", metadata,
>sa.Column('col1', sa.types.Integer,),
> )
> table2 = sa.Table("table2", metadata,
>sa.Column('col1', sa.types.Integer,),
> )
> table3 = sa.Table("table3", metadata,
>sa.Column('col1', sa.types.Integer,),
> )
>
> metadata.bind = engine
> connection = engine.connect()
> trans = connection.begin()
> try:
>table1.create(bind=connection)
>table2.create(bind=connection)
>table2.create(bind=connection)
>table3.create(bind=connection)
>trans.commit()
> except:
>trans.rollback()
> #
>
> Cheers,
> Chris
>
>
> On Feb 6, 2:08 am, Michael Bayer wrote:
>> create() and create_all() take a "bind" argument which can be an
>> engine or connection. you want the connection in this case.
>>
>> On Feb 5, 2009, at 5:27 AM, Chris Miles wrote:
>>> I can't find away to link table.create() with the existing
>>> transaction.
>
> >
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Create tables within a transaction
That did the trick, thanks.
Well, actually, it did the trick for PostgreSQL but sqlite isn't
rolling back. The SA logs show the same commands are being sent to
both. Here's an example:
$ rm test1.sqlite
$ python sa_create_table_transaction_test.py
2009-02-06 13:39:29,006 INFO sqlalchemy.engine.base.Engine.0x..d0
BEGIN
2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table1 (
col1 INTEGER
)
2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table2 (
col1 INTEGER
)
2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table2 (
col1 INTEGER
)
2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,027 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
$ sqlite3 test1.sqlite
Loading resources from /Users/chris/.sqliterc
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .tables
table1 table2
sqlite>
And the updated test script:
#
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///test1.sqlite')
#engine = sa.create_engine('postgres://localhost/test1')
engine.echo = True
metadata = sa.MetaData()
table1 = sa.Table("table1", metadata,
sa.Column('col1', sa.types.Integer,),
)
table2 = sa.Table("table2", metadata,
sa.Column('col1', sa.types.Integer,),
)
table3 = sa.Table("table3", metadata,
sa.Column('col1', sa.types.Integer,),
)
metadata.bind = engine
connection = engine.connect()
trans = connection.begin()
try:
table1.create(bind=connection)
table2.create(bind=connection)
table2.create(bind=connection)
table3.create(bind=connection)
trans.commit()
except:
trans.rollback()
#
Cheers,
Chris
On Feb 6, 2:08 am, Michael Bayer wrote:
> create() and create_all() take a "bind" argument which can be an
> engine or connection. you want the connection in this case.
>
> On Feb 5, 2009, at 5:27 AM, Chris Miles wrote:
> > I can't find away to link table.create() with the existing
> > transaction.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Missing FROM clause on a one-to-many declarative_base mapping
that's your flush() process flushing something pending in the
session. say session.flush() to see it happen. the error means
you've removed a child object from a parent, which would result in a
primary key that is also a foreign key being nulled out.
On Feb 5, 2009, at 8:51 PM, Gloria W wrote:
>
> OK, a new problem on the same model:
>
> I try this in my unit test:
>
> memberProfile = self.session.query(MemberProfile).filter
> (MemberProfile.memberID.in_(memberid)).order_by
> (MemberProfile.memberID).filter(MemberProfile.city == 'Jamaica').all()
>
> and I get this error:
>
> Traceback (most recent call last):
> File "MemberInfo.py", line 187, in ?
>print jsonize(x.GET(memberid=81087,fields={'city':'brooklyn',
> 'phonehome': '3473653052'}).data)
> File "MemberInfo.py", line 70, in GET
>memberProfile = memberProfile.order_by(MemberProfile.memberID).all
> ()
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/query.py", line 998, in all
>return list(self)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/query.py", line 1087, in __iter__
>self.session._autoflush()
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/session.py", line 912, in _autoflush
>self.flush()
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/session.py", line 1354, in flush
>self._flush(objects)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/session.py", line 1424, in _flush
>flush_context.execute()
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/unitofwork.py", line 260, in execute
>UOWExecutor().execute(self, tasks)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/unitofwork.py", line 723, in execute
>self.execute_save_steps(trans, task)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/unitofwork.py", line 740, in execute_save_steps
>self.execute_dependencies(trans, task)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/unitofwork.py", line 749, in execute_dependencies
>self.execute_dependency(trans, dep, False)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/unitofwork.py", line 735, in execute_dependency
>dep.execute(trans, isdelete)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/unitofwork.py", line 702, in execute
>delete=delete)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/dependency.py", line 332, in process_dependencies
>self._synchronize(state, child, None, False, uowcommit)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/dependency.py", line 375, in _synchronize
>sync.clear(state, self.parent, self.prop.synchronize_pairs)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/sync.py", line 28, in clear
>raise AssertionError("Dependency rule tried to blank-out primary
> key column '%s' on instance '%s'" % (r, mapperutil.state_str(dest)))
> AssertionError: Dependency rule tried to blank-out primary key column
> 'member_profiles.memberID' on instance ' 0x-488616d4>'
>
>
> which seems to happen on any filter() option I use. Things work fine
> without the filter() option.
>
> Thanks again!
> Gloria
> >
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Missing FROM clause on a one-to-many declarative_base mapping
OK, a new problem on the same model:
I try this in my unit test:
memberProfile = self.session.query(MemberProfile).filter
(MemberProfile.memberID.in_(memberid)).order_by
(MemberProfile.memberID).filter(MemberProfile.city == 'Jamaica').all()
and I get this error:
Traceback (most recent call last):
File "MemberInfo.py", line 187, in ?
print jsonize(x.GET(memberid=81087,fields={'city':'brooklyn',
'phonehome': '3473653052'}).data)
File "MemberInfo.py", line 70, in GET
memberProfile = memberProfile.order_by(MemberProfile.memberID).all
()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 998, in all
return list(self)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 1087, in __iter__
self.session._autoflush()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/session.py", line 912, in _autoflush
self.flush()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/session.py", line 1354, in flush
self._flush(objects)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/session.py", line 1424, in _flush
flush_context.execute()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/unitofwork.py", line 260, in execute
UOWExecutor().execute(self, tasks)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/unitofwork.py", line 723, in execute
self.execute_save_steps(trans, task)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/unitofwork.py", line 740, in execute_save_steps
self.execute_dependencies(trans, task)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/unitofwork.py", line 749, in execute_dependencies
self.execute_dependency(trans, dep, False)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/unitofwork.py", line 735, in execute_dependency
dep.execute(trans, isdelete)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/unitofwork.py", line 702, in execute
delete=delete)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/dependency.py", line 332, in process_dependencies
self._synchronize(state, child, None, False, uowcommit)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/dependency.py", line 375, in _synchronize
sync.clear(state, self.parent, self.prop.synchronize_pairs)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/sync.py", line 28, in clear
raise AssertionError("Dependency rule tried to blank-out primary
key column '%s' on instance '%s'" % (r, mapperutil.state_str(dest)))
AssertionError: Dependency rule tried to blank-out primary key column
'member_profiles.memberID' on instance ''
which seems to happen on any filter() option I use. Things work fine
without the filter() option.
Thanks again!
Gloria
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Missing FROM clause on a one-to-many declarative_base mapping
Wow, awesome, it works, thank you! ~G~ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: cpython jdbc
> I was wondering if anyone was aware of a JDBC DBAPI module for > cpython. Interesting idea, and could be a killer feature for SA 0.6+ if it could be made to work Jpype could perhaps do the job: http://jpype.sourceforge.net/ There's been at least some activity with accessing JDBC drivers from CPython using it, though I don't know of anything recent: http://mail.python.org/pipermail/python-list/2006-April/377434.html Good luck and report back what you find! --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Missing FROM clause on a one-to-many declarative_base mapping
you're ordering the Member and Gender relation()s by a column in the
parent table, which is producing the error.The order_by expression
should be local to the Member or Gender entity.
On Feb 5, 2009, at 11:54 AM, Gloria W wrote:
>
> Hi All,
> I have three classes, all using the same declarative_base() instance,
> as follows:
>
>
> In a config file:
>
> global Base
> Base = None
>
> def initBase():
> global Base
> if not Base:
>Base = declarative_base()
>
> return Base
>
> ~~~
> First class:
> ~~~
> sys.path.append('../config')
> import config
>
> Base = config.initBase()
>
> class Member(Base):
> __tablename__ = 'members'
>
> memberID = Column(Integer, primary_key=True)
> question = Column(String)
> answer = Column(String)
> lockoutflag = Column(Boolean)
> deleteflag = Column(Boolean)
> firstname = Column(String)
> lastname = Column(String)
> middleinitial = Column(String)
> alert = Column(String)
> activeflag = Column(Boolean)
> crmleadID = Column(String)
> password = Column(String,name='pass')
> insuranceflag = Column(Boolean)
> applicationstatusID = Column(Integer)
> identifier = Column(String)
> email = Column(String)
>
> def __init__(self, member_id, question, answer, lockoutflag,
> deleteflag, etc..
> def __repr__(self):
>return "
> ~~~
> Second:
> ~~~
> sys.path.append('../config')
> import config
>
> Base = config.initBase()
>
> class Gender(Base):
> __tablename__ = 'member_gender'
> genderID = Column(Integer, primary_key=True)
> gender = Column(String,name='description')
> gender_activeflag = Column(Boolean,name='activeflag')
> gender_orderindex = Column(Float,name='orderindex')
>
> def __init__(self, genderID, description, activeflag, orderindex):
>self.genderID = genderID
>self.gender = description
>self.gender_activeflag = activeflag
>self.gender_orderindex = orderindex
>
> def __repr__(self):
>return "" % (self.genderID,
> self.gender,
> self.activeflag, self.orderindex)
>
> ~
> Third Class, using foreign keys and relations into the first two
> classes:
> ~
> sys.path.append('../config')
> import config
>
> from Members import *
> from Gender import *
>
> Base = config.initBase()
>
> class MemberProfile(Base):
> __tablename__ = 'member_profiles'
>
> memberID = Column(Integer, ForeignKey('members.memberID'),
> primary_key=True)
> SSN = Column(String)
> DOB = Column(Date)
> industryID = Column(Integer)
> primarysectorID = Column(Integer)
> address1 = Column(String)
> address2 = Column(String)
> city = Column(String)
> state = Column(String)
> zip = Column(String)
> howhearID = Column(Integer)
> affiliationID = Column(Integer)
> incomeID = Column(Integer)
> worksituationID = Column(Integer)
> currentinsuranceID = Column(Integer)
> genderID = Column(Integer,ForeignKey('member_gender.genderID'))
> referemail = Column(String)
> occupation = Column(String)
> phonehome = Column(String)
> phonework = Column(String)
> phonecell = Column(String)
> phonefax = Column(String)
> occupationID = Column(Integer)
> occupationother = Column(String)
> billing_address1 = Column(String)
> billing_address2 = Column(String)
> billing_city = Column(String)
> billing_state = Column(String)
> billing_zip = Column(String)
>
> member = relation(Member,lazy=False,backref=backref
> ('members',order_by=memberID),cascade="all, delete")
>
> gender = relation(Gender,lazy=False,backref=backref
> ('member_gender'),order_by=genderID)
>
>
> def __init__(self, memberID, SSN=None, etc...
>
>
>
>
> My unit test is here:
>
> Base = config.initBase()
> metdata = Base.metadata
> engine = create_engine(config.db_conn)
> Session = sessionmaker(bind=engine)
> session = Session()
>
> memberProfile = session.query(MemberProfile).filter_by
> (memberID=81087).first()
> print "\nOriginal record:"
> print memberProfile.__dict__
>
>
> The error I get is here:
>
>
> python MemberProfiles.py
> Traceback (most recent call last):
> File "MemberProfiles.py", line 108, in ?
>memberProfile = session.query(MemberProfile).filter_by
> (memberID=81087).first()
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/query.py", line 1027, in first
>ret = list(self[0:1])
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/query.py", line 952, in __getitem__
>return list(res)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/query.py", line 1088, in __iter__
>return self._execute_and_instances(context)
> File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
> sqlalchemy/orm/
[sqlalchemy] Missing FROM clause on a one-to-many declarative_base mapping
Hi All,
I have three classes, all using the same declarative_base() instance,
as follows:
In a config file:
global Base
Base = None
def initBase():
global Base
if not Base:
Base = declarative_base()
return Base
~~~
First class:
~~~
sys.path.append('../config')
import config
Base = config.initBase()
class Member(Base):
__tablename__ = 'members'
memberID = Column(Integer, primary_key=True)
question = Column(String)
answer = Column(String)
lockoutflag = Column(Boolean)
deleteflag = Column(Boolean)
firstname = Column(String)
lastname = Column(String)
middleinitial = Column(String)
alert = Column(String)
activeflag = Column(Boolean)
crmleadID = Column(String)
password = Column(String,name='pass')
insuranceflag = Column(Boolean)
applicationstatusID = Column(Integer)
identifier = Column(String)
email = Column(String)
def __init__(self, member_id, question, answer, lockoutflag,
deleteflag, etc..
def __repr__(self):
return "" % (self.genderID,
self.gender,
self.activeflag, self.orderindex)
~
Third Class, using foreign keys and relations into the first two
classes:
~
sys.path.append('../config')
import config
from Members import *
from Gender import *
Base = config.initBase()
class MemberProfile(Base):
__tablename__ = 'member_profiles'
memberID = Column(Integer, ForeignKey('members.memberID'),
primary_key=True)
SSN = Column(String)
DOB = Column(Date)
industryID = Column(Integer)
primarysectorID = Column(Integer)
address1 = Column(String)
address2 = Column(String)
city = Column(String)
state = Column(String)
zip = Column(String)
howhearID = Column(Integer)
affiliationID = Column(Integer)
incomeID = Column(Integer)
worksituationID = Column(Integer)
currentinsuranceID = Column(Integer)
genderID = Column(Integer,ForeignKey('member_gender.genderID'))
referemail = Column(String)
occupation = Column(String)
phonehome = Column(String)
phonework = Column(String)
phonecell = Column(String)
phonefax = Column(String)
occupationID = Column(Integer)
occupationother = Column(String)
billing_address1 = Column(String)
billing_address2 = Column(String)
billing_city = Column(String)
billing_state = Column(String)
billing_zip = Column(String)
member = relation(Member,lazy=False,backref=backref
('members',order_by=memberID),cascade="all, delete")
gender = relation(Gender,lazy=False,backref=backref
('member_gender'),order_by=genderID)
def __init__(self, memberID, SSN=None, etc...
My unit test is here:
Base = config.initBase()
metdata = Base.metadata
engine = create_engine(config.db_conn)
Session = sessionmaker(bind=engine)
session = Session()
memberProfile = session.query(MemberProfile).filter_by
(memberID=81087).first()
print "\nOriginal record:"
print memberProfile.__dict__
The error I get is here:
python MemberProfiles.py
Traceback (most recent call last):
File "MemberProfiles.py", line 108, in ?
memberProfile = session.query(MemberProfile).filter_by
(memberID=81087).first()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 1027, in first
ret = list(self[0:1])
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 952, in __getitem__
return list(res)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 1088, in __iter__
return self._execute_and_instances(context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 1091, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none(),
_state=self._refresh_state)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/session.py", line 749, in execute
return self.__connection(engine, close_with_result=True).execute(
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/engine/base.py", line 806, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/engine/base.py", line 856, in execute_clauseelement
return self.__execute_context(context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/engine/base.py", line 878, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/engine/base.py", line 927, in _curs
[sqlalchemy] Missing FROM clause on a one-to-many declarative_base mapping
Hi All,
I have three classes, all using the same declarative_base() instance,
as follows:
In a config file:
global Base
Base = None
def initBase():
global Base
if not Base:
Base = declarative_base()
return Base
~~~
First class:
~~~
sys.path.append('../config')
import config
Base = config.initBase()
class Member(Base):
__tablename__ = 'members'
memberID = Column(Integer, primary_key=True)
question = Column(String)
answer = Column(String)
lockoutflag = Column(Boolean)
deleteflag = Column(Boolean)
firstname = Column(String)
lastname = Column(String)
middleinitial = Column(String)
alert = Column(String)
activeflag = Column(Boolean)
crmleadID = Column(String)
password = Column(String,name='pass')
insuranceflag = Column(Boolean)
applicationstatusID = Column(Integer)
identifier = Column(String)
email = Column(String)
def __init__(self, member_id, question, answer, lockoutflag,
deleteflag, etc..
def __repr__(self):
return "" % (self.genderID,
self.gender,
self.activeflag, self.orderindex)
~
Third Class, using foreign keys and relations into the first two
classes:
~
sys.path.append('../config')
import config
from Members import *
from Gender import *
Base = config.initBase()
class MemberProfile(Base):
__tablename__ = 'member_profiles'
memberID = Column(Integer, ForeignKey('members.memberID'),
primary_key=True)
SSN = Column(String)
DOB = Column(Date)
industryID = Column(Integer)
primarysectorID = Column(Integer)
address1 = Column(String)
address2 = Column(String)
city = Column(String)
state = Column(String)
zip = Column(String)
howhearID = Column(Integer)
affiliationID = Column(Integer)
incomeID = Column(Integer)
worksituationID = Column(Integer)
currentinsuranceID = Column(Integer)
genderID = Column(Integer,ForeignKey('member_gender.genderID'))
referemail = Column(String)
occupation = Column(String)
phonehome = Column(String)
phonework = Column(String)
phonecell = Column(String)
phonefax = Column(String)
occupationID = Column(Integer)
occupationother = Column(String)
billing_address1 = Column(String)
billing_address2 = Column(String)
billing_city = Column(String)
billing_state = Column(String)
billing_zip = Column(String)
member = relation(Member,lazy=False,backref=backref
('members',order_by=memberID),cascade="all, delete")
gender = relation(Gender,lazy=False,backref=backref
('member_gender'),order_by=genderID)
def __init__(self, memberID, SSN=None, etc...
My unit test is here:
Base = config.initBase()
metdata = Base.metadata
engine = create_engine(config.db_conn)
Session = sessionmaker(bind=engine)
session = Session()
memberProfile = session.query(MemberProfile).filter_by
(memberID=81087).first()
print "\nOriginal record:"
print memberProfile.__dict__
The error I get is here:
python MemberProfiles.py
Traceback (most recent call last):
File "MemberProfiles.py", line 108, in ?
memberProfile = session.query(MemberProfile).filter_by
(memberID=81087).first()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 1027, in first
ret = list(self[0:1])
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 952, in __getitem__
return list(res)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 1088, in __iter__
return self._execute_and_instances(context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py", line 1091, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none(),
_state=self._refresh_state)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/session.py", line 749, in execute
return self.__connection(engine, close_with_result=True).execute(
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/engine/base.py", line 806, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/engine/base.py", line 856, in execute_clauseelement
return self.__execute_context(context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/engine/base.py", line 878, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/engine/base.py", line 927, in _curs
[sqlalchemy] Re: Outer joins with ORM and single table polymorphism
After some experimenting I am able to generate the correct query
by two methods
(1) build query from ORM classes with ORM session.query()
(2) build query from underlying tables with sql expressions
I like the ORM based method better, because the code does not need
to know which columns are involved in the join, and does seem to be
a little cleaner.
I do have a couple of questions about this though:
(1) I can't figure out how to access columns from the RowTuple result
by name. I can only get values by absolute index row[0],row[1], etc.
(2) Are subquery aliases always anonymous or can I control the name?
The table based query satisfies all requirements for getting data. The
only issue is that the code now has more embedded information about
the
relations between tables.
Generated SQL here
http://dpaste.com/hold/116982/
Here is a complete test program; I am using version 0.5.2
http://dpaste.com/hold/116995/
Mapped classes based query
# set up subqueries for outer joins
S1 = session.query(SRC1).subquery()
S2 = session.query(SRC2).subquery()
S3 = session.query(SRC3).subquery()
# build up the query
query = session.query(Job.title, Step.name).join(Step)
query = query.outerjoin([S1,S2,S3])
query = query.add_column(S1.c.value)
query = query.add_column(S2.c.value)
query = query.add_column(S3.c.value)
query = query.with_labels()
# select relevant job
query = query.filter(Job.id==2)
for r in query:
print r # how to get named access to columns? r[0],r[1],... works
# r.job_title, etc. doesn't
Table based query
# get access to underlying tables
J = class_mapper(Job).mapped_table
S = class_mapper(Step).mapped_table
I = class_mapper(Input).mapped_table
# set up some alias names for outerjoins
S1 = select([I], I.c.kind=='SRC1')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S1')
S2 = select([I], I.c.kind=='SRC2')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S2')
S3 = select([I], I.c.kind=='SRC3')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S3')
# build a query
query = J.join(S)
query = query.outerjoin(S1, S.c.id==S1.c.step_id)
query = query.outerjoin(S2, S.c.id==S2.c.step_id)
query = query.outerjoin(S3, S.c.id==S3.c.step_id)
query = query.select(use_labels=True)
query = query.with_only_columns([J.c.title,S.c.name,
S1.c.value,S2.c.value,S3.c.value])
# select rows of interest
query = query.where(J.c.id==1)
for r in conn.execute(query):
print r.job_title, r.step_name, r.S1_value, r.S2_value, r.S3_value
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Missing UPDATE on Parent
Hi guys, I managed to model a device scanner based on SA 0.5.2.
Everything works fine on the first run, but when i rerun a scan to
refresh data (specially Client.scantime) i do not see any UPDATE
statement called on clients table.
What's wrong with my design?
Thanks in advance
Massimo
Follow my prog code (more or less)
==
Table definition:
devices = Table('devices', metadata,
Column('id', Integer, primary_key=True),
Column('type', String(10), nullable=False),
Column('hostname', String(20), index=True),
Column('ip', String(15), index=True, unique=True),
Column('scantime', DateTime, default=datetime.now,
onupdate=datetime.now),
Column('sn', String(20)),
Column('model', String(20)),
Column('version', String(20)),
Column('os_id', Integer, ForeignKey('os.id'))
)
clients = Table('clients', metadata,
Column('device_id', Integer, ForeignKey('devices.id'),
primary_key=True),
Column('sidib_id', Integer, ForeignKey('sidib.id')),
Column('last_logon', String(50))
)
servers = Table('servers', metadata,
Column('device_id', Integer, ForeignKey('devices.id'),
primary_key=True),
Column('uptime', String(5))
)
cpu = Table('cpu', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('description', String(50)),
Column('clock_speed', Integer),
Column('socket', String(50)),
UniqueConstraint('name', 'clock_speed', 'description', 'socket',
name='uix_cpu')
)
cpu_device = Table('cpu_device', metadata,
Column('id', Integer, primary_key=True),
Column('device_id', Integer, ForeignKey('devices.id')),
Column('cpu_id', Integer, ForeignKey('cpu.id'))
)
Mapping definition:
#! /usr/bin/env python2.5
# Filename: models.py
'''
Define model and mapping to DB
'''
import logging
import commands
from actionsimport *
from database import *
from sqlalchemy.orm import relation, reconstructor
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.ext.associationproxyimport AssociationProxy
__all__ = ['Client', 'Server']
logger = logging.getLogger(__name__)
def _get_or_create(klass, dic):
logger.debug('_get_or_create(klass=%s, dic=%s)' % (klass, dic))
try:
ret = Session.query(klass).filter_by(**dic).one()
except NoResultFound:
ret = klass(**dic)
logger.debug('Returning %s' % ret)
return ret
class _Mapper(object):
def __init__(self, field, action, class_=None, list=True):
self.field = field
self.class_ = class_
self.action = action
self.list = list
def __repr__(self):
return '''<_Mapper:
\tfield:%s
\tclass_:%s
\taction:%s
\tlist:%s>\n''' % (self.field, self.class_,
self.action, self.list)
class CpuAssociation(object):
def __init__(self, cpu):
self.cpu = cpu
class Device(object):
def __init__(self, ip):
logger.debug('Device.__init__')
self.ip = ip
def isAlive(self):
return commands.ping(self.ip)
def scan(self):
if self.isAlive():
logger.info('Host %s is alive. Start scanning.' % self.ip)
logger.debug('Host %s has %s actions' % (self.ip,
self.actions))
for mapper in self.actions:
func = getattr(commands, mapper.action.command)
funcRet = func(self, mapper.action.param)
logger.debug('Function %s() returned %s' %
(mapper.action.command, funcRet))
if funcRet not in (None, '', []):
if hasattr(mapper.action, 'callback'):
funcRet = mapper.action.callback(funcRet) #
Chiamo l eventuale callback impostata dall azione
if mapper.class_ is not None:
if mapper.list:
funcRet = [_get_or_create(mapper.class_,
ret) for ret in funcRet]
else:
funcRet = _get_or_create(mapper.class_,
funcRet)
setattr(self, mapper.field, funcRet)
logger.debug('Added %s to %s' % (funcRet,
mapper.field))
class Client(Device):
def __init__(self, ip, username, password):
logger.debug('Client.__init__')
Device.__init__(self, ip)
self.username = username
self.password = password
self.setActions()
@reconstructor
def init_on_load(self):
logger.debug('<%s> Client.init_on_load' % self.ip)
self.setActions()
def setActions(self):
self.actions = []
self.actions.append(_Mapper('hostname', GetHostname))
self.actions.append(_Mapper('last_logon', GetLastLogon))
self.actions.append(_Mapper('cpu', GetCPU, Cpu))
cpu = AssociationProxy('cpu_
[sqlalchemy] Re: Can polymorphic_identity be a range of values ?
polymorphic_identity is intended to link to the "class" of an entity in a one-to-one fashion, so using a date type for this column would not be an appropriate usage.We have eventual plans to support polymorphic_identity supplied by a function but that feature is currently not implemented. You might consider looking into MapperExtension.create_instance() for this use case. On Feb 5, 2009, at 10:24 AM, Toby Bradshaw wrote: > > Hi, > > So I (think I) understand that that polymorphic_on and > polymorphic_identity can be used to determine which class is > instantiated for a query result row. Is there any way I can use a > range > of values for polymorphic_identity to map to the same class ? Of > specific interest to me right now are date ranges and single-table > inheritance. > > e.g. I have two classes: Employee and CurrentEmployee. Both are mapped > to the same table, employee, but Employee.query() should return every > record in the table and CurrentEmployee() should return the same > subset > as Employee.query(Employee.start_date >= today), for example. > > It really is just a convenience thing so not a show stopper but > would be > nice to know if this kind of thing is straightforwardly possible. > > -- > t o b e > -- > A truly clever developer will create code so easy to understand that > a less than average developer could debug it. > > > > --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Can polymorphic_identity be a range of values ?
Hi, So I (think I) understand that that polymorphic_on and polymorphic_identity can be used to determine which class is instantiated for a query result row. Is there any way I can use a range of values for polymorphic_identity to map to the same class ? Of specific interest to me right now are date ranges and single-table inheritance. e.g. I have two classes: Employee and CurrentEmployee. Both are mapped to the same table, employee, but Employee.query() should return every record in the table and CurrentEmployee() should return the same subset as Employee.query(Employee.start_date >= today), for example. It really is just a convenience thing so not a show stopper but would be nice to know if this kind of thing is straightforwardly possible. -- t o b e -- A truly clever developer will create code so easy to understand that a less than average developer could debug it. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Error: attribute refresh operation cannot proceed
On Feb 5, 2009, at 9:04 AM, pwaern wrote: > What I would like to be able to is to keep on working with detached > objects like the user object in the code above , in a manner where the > objects attribute values are the same as they were when the session > was closed, i.e. without further database queries (and certainly > without exceptions!). If there was some way to ensure that all > database attributes on the object were loaded but not modifiable, it > could solve the problem. In my current application I use the > get_session extension to return the ScopedSession instance in order to > ensure that I can access attributes but I would prefer that the > objects were properly detached but still funcionable. you'd have to call refresh() or otherwise hit an attribute on every object before detached from a session.Alternatively, you could set expire_on_commit=False on your sessionmaker() so that the commit() operation leaves the current in-memory state in place. The latter option would be why your test program works in 0.4, there wasnt any expire_on_commit behavior. . --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Create tables within a transaction
create() and create_all() take a "bind" argument which can be an
engine or connection. you want the connection in this case.
On Feb 5, 2009, at 5:27 AM, Chris Miles wrote:
>
> I notice that a table create (and drop/etc) is always followed by an
> implicit commit. Is it possible to suppress the commit or force SA to
> create multiple tables in one transaction so that if any fail they can
> all be rolled back?
>
> Here's some code to demonstrate what I want. In this example, the
> table creation fails half way through, but leaves 2 tables created. I
> want the CREATEs rolled back if any of them fail.
>
>
> import sqlalchemy as sa
> engine = sa.create_engine('sqlite:///test1.sqlite')
> engine.echo = True
> metadata = sa.MetaData()
>
> table1 = sa.Table("table1", metadata,
>sa.Column('col1', sa.types.Integer,),
> )
> table2 = sa.Table("table2", metadata,
>sa.Column('col1', sa.types.Integer,),
> )
> table3 = sa.Table("table3", metadata,
>sa.Column('col1', sa.types.Integer,),
> )
>
> metadata.bind = engine
> connection = engine.connect()
> trans = connection.begin()
> try:
>table1.create()
>table2.create()
>table2.create()
>table3.create()
>trans.commit()
> except:
>trans.rollback()
>
>
> I can't find away to link table.create() with the existing
> transaction.
>
> Note: I don't want to just use create_all(). This is part of an in-
> house schema version control system.
>
> Note 2: If I create tables from SQL (sending "CREATE TABLE ..."
> strings) within the transaction then I get the desired behaviour.
> However, I'd prefer to use table.create() for convenience (i.e. across
> multiple engines).
>
> Cheers,
> Chris Miles
>
>
> >
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Error: attribute refresh operation cannot proceed
I have also experienced problems with this exception.Here is a simple
example:
import sqlalchemy
import sqlalchemy.orm as orm
engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
metadata = sqlalchemy.MetaData()
users_table = sqlalchemy.Table('users', metadata,
sqlalchemy.Column('id', sqlalchemy.Integer,
primary_key=True),
sqlalchemy.Column('name', sqlalchemy.String),
sqlalchemy.Column('fullname', sqlalchemy.String),
sqlalchemy.Column('password', sqlalchemy.String)
)
metadata.create_all(engine)
metadata.bind = engine
class User(object):
pass
orm.mapper(User, users_table)
MySession = orm.sessionmaker(autoflush=True, autocommit=False)
session = MySession()
user = User()
user.name = "ed"
user.fullname = "Ed Jones"
user.password = "password"
session.add(user)
session.commit()
session.close()
print user.name
The last print statement fails with the "attribute refresh operation
cannot proceed" exception with the 0.5.2 version. The corresponent
code for 0.4.5 works fine.
What I would like to be able to is to keep on working with detached
objects like the user object in the code above , in a manner where the
objects attribute values are the same as they were when the session
was closed, i.e. without further database queries (and certainly
without exceptions!). If there was some way to ensure that all
database attributes on the object were loaded but not modifiable, it
could solve the problem. In my current application I use the
get_session extension to return the ScopedSession instance in order to
ensure that I can access attributes but I would prefer that the
objects were properly detached but still funcionable.
Any ideas as to what I could do?
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Create tables within a transaction
I notice that a table create (and drop/etc) is always followed by an
implicit commit. Is it possible to suppress the commit or force SA to
create multiple tables in one transaction so that if any fail they can
all be rolled back?
Here's some code to demonstrate what I want. In this example, the
table creation fails half way through, but leaves 2 tables created. I
want the CREATEs rolled back if any of them fail.
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///test1.sqlite')
engine.echo = True
metadata = sa.MetaData()
table1 = sa.Table("table1", metadata,
sa.Column('col1', sa.types.Integer,),
)
table2 = sa.Table("table2", metadata,
sa.Column('col1', sa.types.Integer,),
)
table3 = sa.Table("table3", metadata,
sa.Column('col1', sa.types.Integer,),
)
metadata.bind = engine
connection = engine.connect()
trans = connection.begin()
try:
table1.create()
table2.create()
table2.create()
table3.create()
trans.commit()
except:
trans.rollback()
I can't find away to link table.create() with the existing
transaction.
Note: I don't want to just use create_all(). This is part of an in-
house schema version control system.
Note 2: If I create tables from SQL (sending "CREATE TABLE ..."
strings) within the transaction then I get the desired behaviour.
However, I'd prefer to use table.create() for convenience (i.e. across
multiple engines).
Cheers,
Chris Miles
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
