[sqlalchemy] Re: Firebird - called a stored procedure which returns a value

[Werner F. Bruhin]

Figured out how to run the called procedure.

sql = db.sa.text(select trans_value from t(:totrans, :username), 
bindparams=[db.sa.bindparam('totrans', 'some text 
orig'),
db.sa.bindparam('username', me.name)])
result = session.execute(sql).fetchone()
print result
(u'some text orig auf Deutsch',)


But still stuck on how to make this an additional column using 
declarative base and obtaining some text orig from another column in 
the same table.

Is this at all possible?

I would like to use this to resolve i18n texts which are stored in tables.

Werner



--~--~-~--~~~---~--~~
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: Declarative base and Adjacency List Relationships

[Werner F. Bruhin]

Hi,

maxi wrote:
 Hi,

 I´ve just using sqlalchemy 0.5.1 with python 2.6 and turbogers, but I
 found a little problem trying to configurate adjacency relationship
 with declarative base.


 My object class is something like this:

 class QueryGroup(DeclarativeBase):
 __tablename__ = 'queries_group'

 qry_grp_id = Column(Smallinteger, primary_key=True)
 qry_grp_desc = Column(Unicode(20), nullable=False)
 parent_id = relation('QueryGroup', backref='parent')

 When I try to generate my tables, I get an error like this:

 sqlalchemy.exc.ArgumentError: Could not determine join condition
 between parent/
 child tables on relation QueryGroup.parent_id.  Specify a
 'primaryjoin' expressi
 on.  If this is a many-to-many relation, 'secondaryjoin' is needed as
 well.

 How can I specify the correct statement?

 Using traditional mapper approach, I can do:

 queries_group = Table(...)

 mapper(QueryGroup, queries_group, properties={
 'children': relation(QueryGroup, cascade=all,
 backref=backref(parent, remote_side=
 [queries_group.c.qry_grp_id]))
 }
 )


 Can I do the same using declarative style? How?
   
I use declarative like this to do the above.

class Lang(Base):
__table__ = sa.Table(u'lang', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('gen_lang_id'), 
primary_key=True, nullable=False),
sa.Column(u'lang', sa.String(length=5, convert_unicode=False)),
)


class Users(Base):
__table__ = sa.Table(u'users', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('gen_users_id'), 
primary_key=True, nullable=False),
sa.Column(u'name', sa.String(length=20, convert_unicode=False)),
sa.Column(u'fk_lang', sa.Integer(), sa.ForeignKey(u'lang.id')),
)
   
lang = sao.relation(Lang)

could also be:
lang = sao.relation(Lang, backref='users')

Werner

 Thanks in advance.
 ---
 Maxi.
















 

   


--~--~-~--~~~---~--~~
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: Firebird - called a stored procedure which returns a value

[Werner F. Bruhin]

Michael Bayer wrote:
 Werner F. Bruhin wrote:
   
 Figured out how to run the called procedure.

 sql = db.sa.text(select trans_value from t(:totrans, :username),
 bindparams=[db.sa.bindparam('totrans', 'some text
 orig'),
 db.sa.bindparam('username', me.name)])
 result = session.execute(sql).fetchone()
 print result
 (u'some text orig auf Deutsch',)


 But still stuck on how to make this an additional column using
 declarative base and obtaining some text orig from another column in
 the same table.

 Is this at all possible?
 

 easiest would be a plain python method that calls the appropriate SQL. 
   
Michael thanks for these tips, I think I am there.

However I am still getting the feeling that you think this is not a good 
way to go and I wonder if there is something which will bite me down the 
road.

Some additional explanations:
On the tables which are multi language I would like to be able to do 
queries and just get the translation returned from the stored procedure, 
that is why I like to do the mapped column.
 alternatively, if you really want to use a mapped column, the only
 advantage is the inline SQL aspect of it, i.e. performance.  you'd
 construct a select() from the above and pass the dependent columns in -
 using the third example in
 http://www.sqlalchemy.org/docs/05/sqlexpression.html#functions , something
 like

 select([column('x1')]).select_from(func.t(mytable.c.totrans,
 mytable.c.username))

 then map it using column_property().
   
Now I have this in my model:

def getCurrentUser():
return cUser

class Sample(Base):
__table__ = sa.Table(u'sample', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('gen_sample_id'), 
primary_key=True, nullable=False),
sa.Column(u'name', sa.String(length=256, convert_unicode=False)),
   
)

name_trans = 
sao.column_property(sa.select([sasql.column('trans_value')]).
select_from(sa.func.t(__table__.c.name, 
getCurrentUser)))

And doing a query, e.g. like this:
sample = session.query(db.Sample).one()
print sample

Gives me:
Sample(id=1, name=u'some text orig', name_trans=u'some text orig auf 
Deutsch')

Perfect!

Will do some more testing on this, but I think I am pretty close, if not 
already there.

Thanks again!
Werner

--~--~-~--~~~---~--~~
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] Firebird - called a stored procedure which returns a value

[Werner F. Bruhin]

I can't figure quit figure this out.

In an FB admin tool I can do:
select * from T('some text orig', 'wbruhin');
or
execute procedure T('some text orig', 'wbruhin');

And they return:
a varchar some text orig auf Deutsch


The following runs without error:
...
me = session.query(db.Users).get(1)
cur = engine.raw_connection().cursor()
result = cur.callproc('t', ('some text orig', me.name))

but result just contains ('some text orig', me.name)

What is the correct way to run a stored procedure with Firebird and SA?

And could I define a stored procedure in a mapper, to create an
additional column, i.e. something like the above in a mapper, i.e.:

class Sample(Base):
__table__ = sa.Table(u'sample', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('gen_sample_id'),
primary_key=True, nullable=False),
sa.Column(u'name', sa.String(length=256, convert_unicode=False)),
'name_trans': column_property(
select([func.t(sample_table.c.name, me.name)]).
label('address_count')
)

Thanks for any hints.
Werner


--~--~-~--~~~---~--~~
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: how does a class instance find its values

[Werner F. Bruhin]

Robert,

robert rottermann wrote:
...
 however I would like to change the assignMyselfToCompany method in a way that 
 I
 could achieve the same with:

 product = tblProducts_kl()
 product.assignMyselfToCompany(company_id)
   
Unless you do some more things in your assignMyselfToCompany I think you 
can just do:

product = tblProducts_kl()
company.productRelation = product
or probably more likely this as I assume a company can have more then 
one product.
company.productRelation.append(product)

And SA doe the magic for you.

See:
http://www.sqlalchemy.org/docs/05/ormtutorial.html#adding-new-objects

particularly Working with Related Objects

Hope this helps
Werner



--~--~-~--~~~---~--~~
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: Q: fetch value of autoincrement column

[Werner F. Bruhin]

Adrian von Bidder wrote:
 On Friday 22 May 2009 23.00:05 Werner F. Bruhin wrote:
   
 What do you want to do with the autoincrement column?  Often these are
 used for primary keys, which in turn get used as foreign keys.
 

 I want to use the id as filename; the table will cache some info that comes 
 from the file.  Using it as a filename directly has several advantages:
  * I don't have to care about a unique filename, since the db already takes 
 care of that.
  * I don't have to create an additional column with the filename.

 Obviously, I can just first create the file with a temp filename, but I'd 
 like to avoid this additional step if it's possible.  Less code == fewer 
 bugs.  And I hoped that using the id from the beginning would be easily 
 possible.
   
I have a similar use case (for images and xml files), I use the wine 
name and vintage (easier for the user if ever they look for a particular 
image in the file system and want to use it outside my app) and primary 
key (just to make sure that it is unique), but in my case I can do it 
after having flush'ed.
 Right now, I'm undecided if I should just drop portability and fetch the 
 next value from the mytable_id_seq that postgres has generated for me with 
 the autoincrement columns (from what I understand, even if I declare the 
 column autoincrement, if I set the id manually it will Just Work(tm) like it 
 should 
Based on Michael's post you can keep partial portability by manually 
getting it using the Sequence.

I was intriged and wanted to figure out how to get at the sequence 
defined in the model.

Is the following really the only way to get at it?  I am using 
ORM/declarative.

seq = sa.Sequence(l.__table__.c.langid.default.name)

nextid = engine.execute(seq)

Werner


--~--~-~--~~~---~--~~
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: Q: fetch value of autoincrement column

[Werner F. Bruhin]

Adrian,

Adrian von Bidder wrote:
 Hi,

 Is it possible to fetch the values of an autoincrement field without 
 flushing the object to the DB?

 (In postgres, I obviously can manually fetch nextval of the automatically 
 generated sequence, but I lose the portability that way ...)

 Why?

 Because I need the id to generate data that will be filled into some (non-
 null) columns of the table row.  So I can't flush since I'll get an 
 IntegrityError about non-null columns, and I can't fill those columns 
 without knowing the id that's going to be assigned.

 (Yes, I can use dummy values, then flush(), and then update the row before 
 committing.  But that's not exactly elegant...)
   
This is one of the beauties of SQLA which it took me a while to catch on to.

I use SQLA ORM (declarative) and you can just do:

lang = session.query(db.Language).get(1)

ca = db.Country_Ls()
ca.language = lang
ca.name = 'some country'

reg = db.Region_Ls()
reg.language = lang
reg.name = 'some region'

reg.country_ls = ca

session.add(ca)
session.add(reg)

print ca
print reg

session.flush()

print '==='
print 'flushed'
print '==='
print ca
print ca.countryid
print ''
print 'region'
print reg
print reg.fk_countryid

Which gives me this output and as you can see the actual primary key and 
foreign key are only known after I do a flush, but it is not needed to 
add etc.

Country_Ls(language=Language(created=datetime.date(2009, 5, 22), 
langid=1, locales=u'en  ', name=u'English', 
updated=datetime.date(2009, 5, 22)), name='some country')
Region_Ls(country_ls=Country_Ls(language=Language(created=datetime.date(2009, 
5, 22), langid=1, locales=u'en  ', name=u'English', 
updated=datetime.date(2009, 5, 22)), name='some country'), 
language=Language(created=datetime.date(2009, 5, 22), langid=1, 
locales=u'en  ', name=u'English', updated=datetime.date(2009, 5, 
22)), name='some region')
===
flushed
===
Country_Ls(centralkey=None, countryid=241, 
created=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000), fk_langid=1, 
id=None, language=Language(created=datetime.date(2009, 5, 22), langid=1, 
locales=u'en  ', name=u'English', updated=datetime.date(2009, 5, 
22)), name='some country', shortname=None, 
updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000))
241

region
Region_Ls(centralkey=None, country_ls=Country_Ls(centralkey=None, 
countryid=241, created=datetime.datetime(2009, 5, 22, 13, 22, 48, 
826000), fk_langid=1, id=None, 
language=Language(created=datetime.date(2009, 5, 22), langid=1, 
locales=u'en  ', name=u'English', updated=datetime.date(2009, 5, 
22)), name='some country', shortname=None, 
updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000)), 
created=datetime.datetime(2009, 5, 22, 13, 22, 48, 829000), 
fk_countryid=241, fk_langid=1, id=None, 
language=Language(created=datetime.date(2009, 5, 22), langid=1, 
locales=u'en  ', name=u'English', updated=datetime.date(2009, 5, 
22)), name='some region', regionid=214, shortname=None, 
updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 829000))
241

Hope this helps
Werner


--~--~-~--~~~---~--~~
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: how to transaction like this

[Werner F. Bruhin]

Hi,

manman wrote:
 yes,i known,but how do i do that?  if not commit parent,how to get the
 parent id?
   
flush should be enough, but I think you could also do:

assuming you have a relation parent

c.parent.append(p)

or the other way round

p.children.append(c)

Werner


--~--~-~--~~~---~--~~
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: puzzling outerjoin in the mapper

[Werner F. Bruhin]

Michael Bayer wrote:
 You need to set allow_null_pks=true on your mapper.  This will be on  
 by default in 06.
   
I am running into the same problem, but I use declarative.

After some search and looking at mapper.py I figured out I could do this:

db.Vcbook.__mapper__.allow_null_pks = True

But I did not like using __mapper__, so nearly just asked but then gave 
the search option another go on the doc and found:

__mapper_args__ = {'allow_null_pks': True}

Now, if I would want to do this not in my model.py but just in the code 
section where I need this would using the __mapper__ be the only way 
to do this or is there a cleaner way, e.g.

db.Vcbook.Getmapper().allow_null_pks = True or similar?

Werner


--~--~-~--~~~---~--~~
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: How to use mappers in a wxPython app?

[Werner F. Bruhin]

Opus wrote:
 Hello,

 I'm a sqlalchemy newbie and I'm trying to get a handle on how and
 where to do the mappers in my app.

 The app consists of a frame  split window  tree control  flat
 notebook.

 The idea is that when you click on a node in the tree, the record is
 opened in a new tab in the flat notebook.

 I decided to try and use sqlalchemy orm to handle all the db
 operations but I'm having a bit of trouble.

 I can get it to open up and disply the first record that I select from
 the tree but if I try to open a second (different) record I get the
 following error from sqlalchemy:

   File c:\program files\python25\lib\site-packages\sqlalchemy-0.5.2-
 py2.5.egg\sqlalchemy\orm\mapper.py, line 351, in
 _configure_class_instrumentation
 self.class_)
 sqlalchemy.exc.ArgumentError: Class 'class 'Gui.Person.Person''
 already has a primary mapper defined. Use non_primary=True to create a
 non primary Mapper.  clear_mappers() will remove *all* current mappers
 from all classes.


 In my main app I connect to the db, create a session, set up the
 metadata and load all the table metadata.

 The main app then sets up the frame which proceeds to fill my tree.

 When the user clicks on a node a notebook page is created which then
 maps the class to the table, reads the record and displays it. That
 works fine until I try to create a new instance of the page with a
 different record.

 In brief the question is, where in a gui app should I do the
 mapping?
   
I use SA with wxPython for my new release of a shareware application to 
manage wine.

I have currently all the SA stuff in model.py (using SA declarative), 
this file is imported in the different files where I need access to the 
data (i.e. import model as db).

In wx.App.OnInit I connect to the db and create a session and keep a 
reference, then in any model I need access to the data I do again the 
import model as db and use wx.GetApp() to get to my session.

In other words my mappers etc are all defined in the model.py file.

I hope this helps
Werner


--~--~-~--~~~---~--~~
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: Correct way of moving relation

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 oh, duh.  do it like this:

 for purchase in list(aItem.purchase):
   purchase.cbbottle = bItem

 I leave it to you as an exercise why this is the case.
   
aItem.purchase is an instrumented list and as such is mutable, is that 
the right conclusion?

Thanks for your help
Werner

--~--~-~--~~~---~--~~
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: Getting ForeignKey Before Commit

[Werner F. Bruhin]

n00b wrote:
 why don't you work off the las/previous committed rec id?

 On Jan 29, 4:05 am, Dejan Mayo dejan.m...@gmail.com wrote:
   
 Hi,

 My code is like that:

 try:
 for some_val in some_values:
 rec = SomeModel()
 rec.some_val = some_val
 session.save(rec)
 session.commit()
 except:
 session.rollback()

 For each record that I'm creating, I need to send an email right
 after. And each email includes some data about these records,
 especially the rec.id. Everything looks fine but the problem is
 getting the rec.id.

 I've tried:

 try:
 for some_val in some_values:
 rec = SomeModel()
 rec.some_val = some_val
 session.save(rec)
 email(rec=rec) # rec doesn't have id yet so it doesn't work
 
what about doing:
session.flush()
here?

Werner

--~--~-~--~~~---~--~~
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] Correct way of moving relation

[Werner F. Bruhin]

I have some items which are related but I need to change it so they 
related to another item.

Before getting myself in a mess (as I need to do this for a bunch of 
tables) I wanted to check if the following approach is fine.

I am using SA 0.5, ORM and declarative and the model is:

class Cbbottle(Base):
__table__ = sa.Table(u'cbbottle', metadata,
sa.Column(u'cbbottleid', sa.Integer(), 
sa.Sequence('gen_cbbottle_cbbottleid'), primary_key=True, nullable=False),
etc
)

purchase = sao.relation('Purchase', cascade=all, delete, 
delete-orphan, backref='cbbottle')

aItem = session.query(db.Cbbottle).get(keyno)
bItem = session.query(db.Cbbottle).get(anotherkeyno)

for purchase in aItem.purchase:
purchase.cbbottle = bItem

session.commit()

At this point I expect that aItem has no more purchase relations and 
they are all related to bItem.

Werner

--~--~-~--~~~---~--~~
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: Correct way of moving relation

[Werner F. Bruhin]

Werner F. Bruhin wrote:
 I have some items which are related but I need to change it so they 
 related to another item.

 Before getting myself in a mess (as I need to do this for a bunch of 
 tables) I wanted to check if the following approach is fine.

 I am using SA 0.5, ORM and declarative and the model is:

 class Cbbottle(Base):
 __table__ = sa.Table(u'cbbottle', metadata,
 sa.Column(u'cbbottleid', sa.Integer(), 
 sa.Sequence('gen_cbbottle_cbbottleid'), primary_key=True, nullable=False),
 etc
 )

 purchase = sao.relation('Purchase', cascade=all, delete, 
 delete-orphan, backref='cbbottle')

 aItem = session.query(db.Cbbottle).get(keyno)
 bItem = session.query(db.Cbbottle).get(anotherkeyno)

 for purchase in aItem.purchase:
 purchase.cbbottle = bItem

 session.commit()

 At this point I expect that aItem has no more purchase relations and 
 they are all related to bItem.

   
I had a go at it and get this error on a few tables:
c:\python25\lib\site-packages\sqlalchemy-0.5.2-py2.5.egg\sqlalchemy\orm\properties.py:711:
 
SAWarning: On Bottag.bothist, delete-orphan cascade is not supported on 
a many-to-many or many-to-one relationship when single_parent is not 
set.   Set single_parent=True on the relation().
  self._determine_direction()

Changing the relations then makes it run without error but there is one 
record being missed.

I changed it slightly:
aItem = session.query(db.Cbbottle).get(keyno)
bItem = session.query(db.Cbbottle).get(anotherkeyno)

for purchase in aItem.purchase:
purchase.cbbottle = bItem
purchase.fk_cbbottleid = bItem.cbbottleid

session.commit()

But for some reason one record is still not reassigned.  So, what I do 
is obviously not quit right.

Werner


--~--~-~--~~~---~--~~
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: Correct way of moving relation

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:

...

 there's no need to reassign the FK column yourself and the original  
 pattern you're using is correct.  that only one item in the list is  
 the exception suggests something else is changing its state again  
 further down the road.
   
Thanks for the quick reply, will setup an isolated test script and see 
if I can figure out what I am doing wrong.

Werner

--~--~-~--~~~---~--~~
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: Correct way of moving relation

[Werner F. Bruhin]

Michael,

I run the following script and initially had the either my application 
and/or the IB Expert database tool (for Firebird SQL v 2.1) open at the 
same time.  Now the following tests are done without any other task 
accessing the database.

script:
engine = db.sa.create_engine(dburl, encoding='utf8', echo=False)
Session = db.sao.sessionmaker()
Session.configure(bind=engine)
session = Session()

keyA = 174
keyB = 175

itemB = session.query(db.Cbbottle).get(keyB)
print 'before move from B to A'
print 'itemB id: %s' % itemB.cbbottleid

for purch in itemB.purchase:
print 'purchasid: %s' % purch.purchaseid
print 'fk_cbbottleid: %s' % purch.fk_cbbottleid

session.flush()

itemA = session.query(db.Cbbottle).get(keyA)
itemB = session.query(db.Cbbottle).get(keyB)

print 'start to move from B to A'
print 'itemA id: %s' % itemA.cbbottleid

for purch in itemB.purchase:
print 'purchasid: %s' % purch.purchaseid
print 'fk_cbbottleid: %s' % purch.fk_cbbottleid
purch.cbbottle = itemA
   
session.commit()

itemA = session.query(db.Cbbottle).get(keyA)
print 'after move from B to A'
print 'itemA id: %s' % itemA.cbbottleid

for purch in itemA.purchase:
print 'purchasid: %s' % purch.purchaseid
print 'fk_cbbottleid: %s' % purch.fk_cbbottleid

The following is the output, note that purchasid 80 is not being moved.
before move from B to A
itemB id: 175
purchasid: 79
fk_cbbottleid: 175
purchasid: 80
fk_cbbottleid: 175
purchasid: 81
fk_cbbottleid: 175

start to move from B to A
itemA id: 174
purchasid: 79
fk_cbbottleid: 175
purchasid: 81
fk_cbbottleid: 175

after move from B to A
itemA id: 174
purchasid: 79
fk_cbbottleid: 174
purchasid: 81
fk_cbbottleid: 174

Without doing other tasks on the database I run the same script again, 
and get this output (80 is now moved).
before move from B to A
itemB id: 175
purchasid: 80
fk_cbbottleid: 175

start to move from B to A
itemA id: 174
purchasid: 80
fk_cbbottleid: 175

after move from B to A
itemA id: 174
purchasid: 79
fk_cbbottleid: 174
purchasid: 80
fk_cbbottleid: 174
purchasid: 81
fk_cbbottleid: 174

If I run the script again nothing is moved (which is obviously correct) 
and all shows under fk_cbbottleid 174, now I change the keyA and keyB 
variable and reverse the values as follows:
keyA = 175
keyB = 174

Now I get this and again 80 is not moved.
before move from B to A
itemB id: 174
purchasid: 79
fk_cbbottleid: 174
purchasid: 80
fk_cbbottleid: 174
purchasid: 81
fk_cbbottleid: 174

start to move from B to A
itemA id: 175
purchasid: 79
fk_cbbottleid: 174
purchasid: 81
fk_cbbottleid: 174

after move from B to A
itemA id: 175
purchasid: 79
fk_cbbottleid: 175
purchasid: 81
fk_cbbottleid: 175

I can repeat this again and again and it is always 80 which does not 
move the first time.

Any ideas on what I can do to find out what is causing this row not to 
move would be very much appreciated.

Werner

--~--~-~--~~~---~--~~
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: Correct way of moving relation

[Werner F. Bruhin]

Michael Bayer wrote:

..
 right there, purchasid 80 is not even in the list of items anymore.   
 This is basically iterate the list, 80 is there, then flush(), then 80  
 is not there.  this is all before anything has been moved.   so  
 either the flush() does something, or just the move of item #79  
 affects something with #80.  try it without the flush(), and  
 alternatively try iterating through the list a second time without  
 moving the items, see if 80 disappears.   try without the delete- 
 orphan too, perhaps thats triggering an event that is problematic.
   
Tried it with removing the flush() and not using the delete-orphan and 
still the same behavior.

I then added two more purchase records and now a pattern starts to show, 
i.e. every second one does not get moved.

The slightly updated script (added a print itemB.purchase and removed 
the flush) and the output:

itemA = session.query(db.Cbbottle).get(keyA)
itemB = session.query(db.Cbbottle).get(keyB)

print 'start to move from B to A'
print 'itemA id: %s' % itemA.cbbottleid
print 'no purch: %s' % len(itemB.purchase)
print itemB.purchase

itemB = session.query(db.Cbbottle).get(keyB)

for purch in itemB.purchase:
print 'purchasid: %s' % purch.purchaseid
print 'fk_cbbottleid: %s' % purch.fk_cbbottleid
purch.cbbottle = itemA
   
session.commit()

itemA = session.query(db.Cbbottle).get(keyA)
print 'after move from B to A'
print 'itemA id: %s' % itemA.cbbottleid

for purch in itemA.purchase:
print 'purchasid: %s' % purch.purchaseid
print 'fk_cbbottleid: %s' % purch.fk_cbbottleid

before move from B to A
itemB id: 174
purchasid: 79
fk_cbbottleid: 174
purchasid: 80
fk_cbbottleid: 174
purchasid: 81
fk_cbbottleid: 174
purchasid: 82
fk_cbbottleid: 174
purchasid: 83
fk_cbbottleid: 174
start to move from B to A
itemA id: 175
no purch: 5
[Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, 
fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, 
purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=79, 
purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), 
purchasevalue=Decimal(0), quantity=1, remarks=None, transferid=None, 
updated=datetime.date(2009, 1, 29)), 
Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, 
fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, 
purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=80, 
purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), 
purchasevalue=Decimal(0), quantity=1, remarks=None, transferid=None, 
updated=datetime.date(2009, 1, 29)), 
Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, 
fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, 
purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=81, 
purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), 
purchasevalue=Decimal(0), quantity=5, remarks=None, transferid=None, 
updated=datetime.date(2009, 1, 29)), 
Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, 
fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, 
purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=82, 
purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), 
purchasevalue=Decimal(0), quantity=2, remarks=None, transferid=None, 
updated=datetime.date(2009, 1, 29)), 
Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, 
fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, 
purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=83, 
purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), 
purchasevalue=Decimal(0), quantity=3, remarks=None, transferid=None, 
updated=datetime.date(2009, 1, 29))]
purchasid: 79
fk_cbbottleid: 174
purchasid: 81
fk_cbbottleid: 174
purchasid: 83
fk_cbbottleid: 174
after move from B to A
itemA id: 175
purchasid: 79
fk_cbbottleid: 175
purchasid: 81
fk_cbbottleid: 175
purchasid: 83
fk_cbbottleid: 175

My work around is iterate over the purchase relation and get the primary 
keys, then do a query on the purchase table and move them.

Following script does the trick but it is not quit as nice.

keyA = 174
keyB = 175

itemB = session.query(db.Cbbottle).get(keyB)
print 'before move from B to A'
print 'itemB id: %s' % itemB.cbbottleid
allPurch = []
for purch in itemB.purchase:
allPurch.append(purch.purchaseid)

print 'allPurch: %s' % allPurch

itemA = session.query(db.Cbbottle).get(keyA)
itemB = session.query(db.Cbbottle).get(keyB)

print 'start to move from B to A'
print 'itemA id: %s' % itemA.cbbottleid
print 'no purch: %s' % len(itemB.purchase)

for purchId in allPurch:
purch = session.query(db.Purchase).get(purchId)
print 'purchasid: %s' % purch.purchaseid
print 'fk_cbbottleid: %s' % purch.fk_cbbottleid
purch.cbbottle = itemA
   
session.commit()

itemA = session.query(db.Cbbottle).get(keyA)
print 'after move from B to A'
print 'itemA id: %s' % itemA.cbbottleid

for purch in itemA.purchase:
print 'purchasid: %s' % purch.purchaseid
print 'fk_cbbottleid: %s' % purch.fk_cbbottleid


Werner

[sqlalchemy] Re: INSERT ... ON DUPLICATE KEY UPDATE

[Werner F. Bruhin]

camlost wrote:
 hello,
 i'm trying to use sqlalchemy 0.5.1 with python 2.5.4 on windows. and
 mysql 5.1.

 the task is simple: to keep fresh information about our servers.

 i can get a list of server names from AD, get some info about them and
 insert them into DB using Session.add().

 if i run the script for the first time, it works fine.
 however, the next run fails: sqlalchemy.exc.IntegrityError:
 (IntegrityError) (1062, Duplicate entry...

 the table looks like this:
 'id', 'int(11)', 'PRIMARY KEY', 'auto_increment'
 'name', 'varchar(16)', 'UNIQUE'
 'ip_address', 'varchar(16)'
 ... (some other columns which are not important)

 if i want to keep the data up to date, i need to update them if they
 already exist in DB.
 if i would use plain (literal) sql, i could execute insert in $SUBJ.
 but i would like to use sqlalchemy's native solution if it's possible.
 however, i don't know how to do this.

 can anyone help me?
   
What about something like this:

# get by primary key, you could also use a where clause
item = session.query(db.YourClass).get(131312)

if item:
# do whatever to update
print item
else:
# create a new one
newItem = db.YourClass()
print newItem
session.add(newItem)

# commit
session.commit()

 thanks

 c.

 


   


--~--~-~--~~~---~--~~
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: how to walk through a query result

[Werner F. Bruhin]

Michael,

Thanks for the quick reply.

Michael Bayer wrote:
 On Jan 11, 2009, at 4:51 PM, Werner F. Bruhin wrote:

   
 I can not find the answer looking through the 0.5 doc for my problem.

 query =  
 session.query(db.SomeClass).filter(something).order_by(anorder)

 # get the first row
 row = query.first()

 # display a dialog to the user
 # one of the possible actions is to press a button which should show  
 the
 next row in the query
 row = query.???()

 How could I use fetchone() on this?  Or is there another/correcter  
 way
 to do this type of thing ins SA?
 


 the query itself is an iterator, so you could say result =  
 iter(query); row = result.next() .

 now, if you are looking for the results from the *server* to be non- 
 buffered, that requires something more (namely yield_per()), but it  
 has a lot of caveats so I would not use it unless you absolutely know  
 what you're doing.
   
Just for the archive, I ended up doing something along these lines:

if self.searchInit:
 whatever is needed to setup the searchQuery
self.searchResult = iter(self.searchQuery)
try:
row = self.searchResult.next()
except StopIteration:
... do whatever if nothing is found
else:
try:
row = self.searchResult.next()
except StopIteration:
 do whatever when there are no more rows

Michael, thanks again for excellent support.
Werner

 


   


--~--~-~--~~~---~--~~
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] how to walk through a query result

[Werner F. Bruhin]

I can not find the answer looking through the 0.5 doc for my problem.

query = session.query(db.SomeClass).filter(something).order_by(anorder)

# get the first row
row = query.first()

# display a dialog to the user
# one of the possible actions is to press a button which should show the 
next row in the query
row = query.???()

How could I use fetchone() on this?  Or is there another/correcter way 
to do this type of thing ins SA?

Werner

--~--~-~--~~~---~--~~
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: SQLAlchemy 0.5rc4 Released

[Werner F. Bruhin]

Shawn,

Shawn Church wrote:


 On Sat, Nov 15, 2008 at 7:55 AM, Michael Bayer 
 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:


 easy_install remains a mystery to me in general.  Try easy_install
 sqlalchemy==0.5.0rc4 , or otherwise yeah delete out your dev versions.

 I use easy_install --upgrade sqlalchemy:
I do something similar in a script, i.e.:
easy_install.main(['--upgrade', '-Zmaxd', sitePKG, egg])

And egg contains sqlalchemy.

But after I had installed rc3 I easy_installed from svn 
(http://svn.sqlalchemy.org/sqlalchemy/trunk) and then when rc4 came out 
and I wanted to do the upgrade I got again a dev version 
(sqlalchemy-0.5.0rc4dev_r0).  I just deleted the dev version and then 
did the upgrade and that worked fine.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: SQLAlchemy 0.5rc4 Released

[Werner F. Bruhin]

I just easy_install'ed it and got sqlalchemy-0.5.0rc4dev_r0-py2.5.egg

Is this the correct one or did my getting the SVN version the other day 
causes a problem?

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: 0.5.0rc3 - kinterbasdb.ProgrammingError: (0, 'Invalid cursor state. The cursor must be open to perform this operation.')

[Werner F. Bruhin]

Michael Bayer wrote:
 On Nov 10, 2008, at 12:10 PM, Werner F. Bruhin wrote:

   
 Michael,

 Michael Bayer wrote:
 
 I know what this is and it should be working in r5280.   I don't have
 access to firebird here so we weren't able to run the tests on it
 before rc3 was out.

   
Installed sa from svn/trunk and the problem is solved.

...
 P.S.
 What is involved to get SA r5280 onto my MS Vista machine to test?  Is
 this just a SVN checkout or are there some additional build/compile  
 steps?
 

 you can do easy_install http://svn.sqlalchemy.org/sqlalchemy/trunk .
   
Just for the archive, needed to do the following to be able to get the 
trunk version.
- needed to install a command line svn tool (used this one: 
http://www.sliksvn.com/en/download)
- got an exception that log is not defined, so had to add the 
following line to setuptools-0.6c8\setuptools\command\sdist.py
from distutils import log

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: 0.5.0rc3 - kinterbasdb.ProgrammingError: (0, 'Invalid cursor state. The cursor must be open to perform this operation.')

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 I know what this is and it should be working in r5280.   I don't have  
 access to firebird here so we weren't able to run the tests on it  
 before rc3 was out.
   
Thanks for the quick reply.

Looking at the changes doc these will be included in rc4 - any idea when 
this will come out?

Werner

P.S.
What is involved to get SA r5280 onto my MS Vista machine to test?  Is 
this just a SVN checkout or are there some additional build/compile steps?


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] 0.5.0rc3 - kinterbasdb.ProgrammingError: (0, 'Invalid cursor state. The cursor must be open to perform this operation.')

[Werner F. Bruhin]

I am getting sometimes the following exception with rc3 which I did not 
see with rc2 when I do something like this:


engine = sa.create_engine(dburl, encoding='utf8', echo=False)
# connect to the database
##connection = engine.connect()
Session = sao.sessionmaker()
Session.configure(bind=engine)
##Session.configure(bind=connection)
session = Session()
query = session.query(db.Preferences).get(1)
lang = session.query(db.Language).get(2)
query.language = lang
session.commit()

Am I doing which I should not, which now causes this or ...?

Best regards
Werner

Following the traceback and part of my model.

Traceback (most recent call last):
  File saTest.py, line 56, in module
session.commit()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 670, in commit
self.transaction.commit()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 375, in commit
self._prepare_impl()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 359, in _prepare_impl
self.session.flush()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 1354, in flush
self._flush(objects)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 1424, in _flush
flush_context.execute()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 260, in execute
UOWExecutor().execute(self, tasks)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 723, in execute
self.execute_save_steps(trans, task)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 738, in execute_save_steps
self.save_objects(trans, task)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 729, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\orm\mapper.py,
 
line 1318, in _save_obj
rows += c.rowcount
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\engine\base.py,
 
line 1397, in rowcount
return self.context.get_rowcount()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\engine\default.py,
 
line 279, in get_rowcount
return self.cursor.rowcount
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc3-py2.5.egg\sqlalchemy\pool.py,
 
line 466, in __getattr__
return getattr(self.cursor, key)
kinterbasdb.ProgrammingError: (0, 'Invalid cursor state.  The cursor 
must be open to perform this operation.')

The relevant part of my model are:
class BaseExt(object):
def __repr__(self):
return %s(%s) % (
 (self.__class__.__name__),
 ', '.join([%s=%r % (key, getattr(self, key))
for key in sorted(self.__dict__.keys())
if not key.startswith('_')]))

Base = sad.declarative_base(cls=BaseExt)
metadata = Base.metadata


class Language(Base):
__table__ = sa.Table(u'language', metadata,
sa.Column(u'langid', sa.Integer(), 
sa.Sequence('gen_language_langid'), primary_key=True, nullable=False),
sa.Column(u'name', sa.String(length=50, convert_unicode=False), 
nullable=False),
sa.Column(u'locales', sa.String(length=2, convert_unicode=False)),
sa.Column(u'created', sa.Date()),
sa.Column(u'updated', sa.Date()),
)

class Preferences(Base):
__table__ = sa.Table(u'preferences', metadata,
sa.Column(u'prefid', sa.Integer(), 
sa.Sequence('gen_preferences_prefid'), primary_key=True, nullable=False),
...
sa.Column(u'fk_langid', sa.Integer(), 
sa.ForeignKey(u'language.langid'), nullable=False),
...
)

cellar = sao.relation(Cellar)
language = sao.relation(Language)
reason_ls = sao.relation(Reason_Ls)
displayformats = sao.relation(Displayformats)
measure_ls = sao.relation(Measure_Ls)
ingr_ls = sao.relation(Ingr_Ls)
tastingsys = sao.relation(Tastingsys)
imagetype_ls = sao.relation(Imagetype_Ls, primaryjoin=
('Preferences.fk_imagetypeid==Imagetype_Ls.imagetypeid'))
filters = sao.relation(Filters)
ratingtype_ls = sao.relation(Ratingtype_Ls)
container_ls = sao.relation(Container_Ls)
imagetype_ls_rec = sao.relation(Imagetype_Ls, primaryjoin=
('Preferences.rec_fk_imagetypeid==Imagetype_Ls.imagetypeid'))



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 

[sqlalchemy] Re: connect data base

[Werner F. Bruhin]

nano wrote:
 hello,
 I'm new to sqlalchemy and database
 I just installed postgres http://doc.fedora-fr.org/wiki/
 Installation_et_configuration_de_PostgreSQL,
 and I have a problem connecting to my database
 with a script sqlalchemy
 the error is   File/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.0-
 py2.4.egg/sqlalchemy/engine/strategies.py, line 77, in connect raise
 exceptions.DBAPIError.instance(None, None, e)
 sqlalchemy.exceptions.OperationalError: (OperationalError) FATAL:
 Ident authentication failed for user postgres None None.
 Thanks.
   
No expert in SA, but if you just start with it I would suggest going 
with version 5, i.e. 0.5rc2 just came out the other day.

Have you installed psycopg2?  The DB-API you need for PostGres.

Showing a little of your script might also help.

You might also find answers in the SA doc, which is pretty good (and I 
always forget to look into it too ;-) ).

http://www.sqlalchemy.org/docs/05/intro.html

I would also look at declarative, I found it easier to define my db model.

http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_declarative

Hope this helps
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Limit to 500 records after particular date.

[Werner F. Bruhin]

Heston,

Heston James wrote:
 Hi,

 Thanks for the response, that gave me a good foot in the door to this.
 I've now appened my existing query with.

 .order_by('myobject.created')[:1000]
   
Just tried this on a simple example:

query = query.limit(500)


Quote from doc for 0.5rc1:

*def limit(/self/, /limit/)*


Apply a LIMIT to the query and return the newly resulting Query.

*def offset(/self/, /offset/)*

Apply an OFFSET to the query and return the newly resulting Query.

*def slice(/self/, /start/, /stop/)*


apply LIMIT/OFFSET to the Query based on a range and return the newly 
resulting Query.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Limit to 500 records after particular date.

[Werner F. Bruhin]

Heston,

Heston James wrote:
 Werner,

 Thank you for your response, very kind of you. This looks to be more
 what I'm looking for, after a quick test it seems that it is now
 applying the limit at the SQL level which is definitly a good thing.

 Where abouts in the documentation did you find that? Look here:
 http://www.sqlalchemy.org/docs/05/ormtutorial.html is seems to
 reccomend the same method as reccomended by the first repsonder, is
 that a fault in the docs perhaps?
   
It is from the api doc, see here:
http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_query.html

When I search for things on the doc page I mostly click first on the 
link in the top right One page, unless I know which section contains 
what I am looking for.

 The next challegne I've noticed with using Limit() is that it doesnt
 appear to return an array of objects, but instead, a query object, so
 when I try and perform an evaluation on it like so:

 if len(the_objects):

 I get an error which states:

 TypeError: object of type 'Query' has no len()

 Why is this? Does using Limit() mean that we're returning query
 objects instead of the array of objects I was getting before?
   
Yes (now take my answer with a grain of salt, I am by no means an SA 
expert, nor even an advanced user).

In the 0.5 version you can do things like:

query = session.query(db.Mytable)
query = query.order_by()
query = query.limit(3)
... etc

but then you need to tell it to actually get the data, with

.first(), .one(), .all()

or iterate over it, e.g.

for aninstance in query:
print aninstance

Hope this helps, and if I said something wrong I hope someone corrects me.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Replacing a Firebird view with orm.query.join

[Werner F. Bruhin]

I have a view in an FB db with basically does this

CREATE VIEW VCBOOK(
CB_CELLARBOOKID,
CBV_CBVINTAGEID,
CBB_CBBOTTLEID
)
AS
select cb.cellarbookid, cbv.cbvintageid, cbb.cbbottleid,
from cellarbook cb
left outer join cbvintage cbv on cb.cellarbookid = cbv.fk_cellarbookid
left outer join cbbottle cbb on cbv.cbvintageid = cbb.fk_cbvintageid

The actual view has many more columns but I think the above is enough to 
show what I would like to accomplish.

In the application I then use it e.g. like this:
query(db.Vcbook).all()

Now as the cbv_cbvintageid and cbb_cbbottleid can be Null I get None back.

I thought lets get rid of this view and just use joins, but I can not 
figure it out.

Doing this:
wine = session.query(db.Cellarbook).outerjoin([db.Cbvintage, 
db.Cbbottle]).all()

Only gives me 5 rows, i.e. only the one from db.Cellarbook.

Can anyone push me in the right direction on this?

Werner
P.S.
I am on 0.5rc1

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Replacing a Firebird view with orm.query.join

[Werner F. Bruhin]

Still not there, but another question/problem below:

Werner F. Bruhin wrote:
 I have a view in an FB db with basically does this

 CREATE VIEW VCBOOK(
 CB_CELLARBOOKID,
 CBV_CBVINTAGEID,
 CBB_CBBOTTLEID
 )
 AS
 select cb.cellarbookid, cbv.cbvintageid, cbb.cbbottleid,
 from cellarbook cb
 left outer join cbvintage cbv on cb.cellarbookid = cbv.fk_cellarbookid
 left outer join cbbottle cbb on cbv.cbvintageid = cbb.fk_cbvintageid

 The actual view has many more columns but I think the above is enough to 
 show what I would like to accomplish.

 In the application I then use it e.g. like this:
 query(db.Vcbook).all()

 Now as the cbv_cbvintageid and cbb_cbbottleid can be Null I get None back.

 I thought lets get rid of this view and just use joins, but I can not 
 figure it out.

 Doing this:
 wine = session.query(db.Cellarbook).outerjoin([db.Cbvintage, 
 db.Cbbottle]).all()

 Only gives me 5 rows, i.e. only the one from db.Cellarbook.

 Can anyone push me in the right direction on this?

 Werner
 P.S.
 I am on 0.5rc1
   
j1 = sao.outerjoin(db.Cellarbook, db.Cbvintage)
print j1

j2 = sao.outerjoin(db.Cbvintage, db.Cbbottle)
print j2

j3 = sao.outerjoin(db.Cellarbook, db.Cbvintage).outerjoin(db.Cbvintage, 
db.Cbbottle)
print j3

j1 and j2 produce a join clause, but on j3 I get the following exception:

Traceback (most recent call last):
  File saTest.py, line 66, in module
print j3
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
 
line 1157, in __str__
return unicode(self.compile()).encode('ascii', 'backslashreplace')
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
 
line 1153, in compile
compiler.compile()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
 
line 175, in compile
self.string = self.process(self.statement)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
 
line 183, in process
return meth(obj, **kwargs)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
 
line 597, in visit_join
self.process(join.right, asfrom=True) +  ON  + 
self.process(join.onclause))
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
 
line 181, in process
meth = getattr(self, visit_%s % obj.__visit_name__, None)
AttributeError: type object 'Cbbottle' has no attribute '__visit_name__'

What is the best/most efficient way of doing multiple joins with SA.orm?

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Replacing a Firebird view with orm.query.join

[Werner F. Bruhin]

Werner F. Bruhin wrote:
 ...
   
 j3 = sao.outerjoin(db.Cellarbook, db.Cbvintage).outerjoin(db.Cbvintage, 
 db.Cbbottle)
 print j3

 j1 and j2 produce a join clause, but on j3 I get the following exception:
   
I different exception if I actually try to use j3:

I do this:
wine = session.query(db.Cellarbook)
j3 = sao.outerjoin(db.Cellarbook, db.Cbvintage).outerjoin(db.Cbvintage, 
db.Cbbottle)
wine = wine.select_from(j3)

Then I get this:
Traceback (most recent call last):
  File saTest.py, line 76, in module
print wine
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\orm\query.py,
 
line 1565, in __str__
return str(self._compile_context().statement)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
 
line 1157, in __str__
return unicode(self.compile()).encode('ascii', 'backslashreplace')
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
 
line 1153, in compile
compiler.compile()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
 
line 175, in compile
self.string = self.process(self.statement)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
 
line 183, in process
return meth(obj, **kwargs)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
 
line 498, in visit_select
correlate_froms = set(sql._from_objects(*froms))
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
 
line 886, in _from_objects
return itertools.chain(*[element._get_from_objects(**kwargs) for 
element in elements])
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
 
line 2412, in _get_from_objects
return [self] + self.onclause._get_from_objects(**modifiers) + 
self.left._get_from_objects(**modifiers) + 
self.right._get_from_objects(**modifiers)
AttributeError: type object 'Cbbottle' has no attribute '_get_from_objects'

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Replacing a Firebird view with orm.query.join

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 ...
 if CellarBook, Cbvintage, etc. are mapped classes, the join and  
 outerjoin functions you must be using are from sqlalchemy.orm import  
 join, outerjoin.   those are aware of ORM mapped classes whereas  
 sqlalchemy.sql.expression.join/outerjoin are not.

 You can use the outerjoin() attached to Qeury for the whole thing, i.e.:

 query(Class1).outerjoin(Class2, Class3)

 if the ON condition is required:

 query(Class1).outerjoin((Class2, Class1.foo==Class2.bar), (Class3,  
 Class3.bar==Class2.foo))
   
O.K.  that looks easy, and I tried this before but I don't get the 
result I am looking for.

wines = session.query(db.Cellarbook).outerjoin(db.Cbvintage, db.Cbbottle)

print wines  # if I use this sql select in my db ide I get 8 rows

for wine in wines.all():
print wine
print '\n'

If I use the generated SQL I get 8 rows, but in my for loop above I only 
get 5.

i.e. I get the following: (note the integer at the end is the 
dbCellarbook.primarykey, so this duplicated rows have more then one 
row in cbvintage and possible in cbbottle).
Glen Elgin Virgin OakGlen Elgin Virgin Oak141
Ardbeg RenaissanceArdbeg Renaissance142
Ch. St. GeorgesCh. St. Georges144
Ch. St. GeorgesCh. St. Georges144
Ch. St. GeorgesCh. St. Georges144
Goldwater EsslinGoldwater Esslin, Merlot145
Goldwater EsslinGoldwater Esslin, Merlot145
Goldwater ZellGoldwater Zell146

Maybe I am asking the question incorrectly.

In other words:
db.Cellarbook (a wine)
- relates (oneToMany) to db.Cbvintage (zero or more vintages)
- which in turn relates (oneToMany) to db.Cbbottle (zero or more bottle 
sizes)

What do I need to do to get the 8 rows in my for loop?  Can I do this 
with just a query or do I need to look into other things.

As always thanks a lot for your help
Werner




--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Replacing a Firebird view with orm.query.join

[Werner F. Bruhin]

Michael Bayer wrote:
...
 Now, if you actually want to get back objects for the outerjoins, youd  
 say somehting like:

 sess.query(SomeClass, SomeOtherClass, SomeThirdClass)
   
On Firebird when I do this I get a cross join (according to the Helen 
Borrie book) which in my case gives me 280 rows instead of the 8 :-( .
 this will disable the uniquing logic used for a single class and  
 return tuples containining instances of the above three classes,  
 exactly corresponding to the full result of the outerjoin.  Primary  
 keys which aren't fulfilled for the joined classes will correspoind to  
 an object value of None.
   
I got what I call a work around.

I keep the Firebird view and to a outerjoin on it.  Gives me the result 
I want but I can not get rid of the view - not a big deal.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Replacing a Firebird view with orm.query.join

[Werner F. Bruhin]

Michael Bayer wrote:
 On Sep 12, 2008, at 12:49 PM, Werner F. Bruhin wrote:

   
 Michael Bayer wrote:
 ...
 
 Now, if you actually want to get back objects for the outerjoins,  
 youd
 say somehting like:

 sess.query(SomeClass, SomeOtherClass, SomeThirdClass)

   
 On Firebird when I do this I get a cross join (according to the  
 Helen
 Borrie book) which in my case gives me 280 rows instead of the 8 :-( .
 

 what is the SQL being emitted ?
   
SELECT cellarbook.winenames AS cellarbook_winenames, 
cellarbook.namesandvar AS cellarbook_namesandvar, 
cellarbook.cellarbookid AS cellarbook_cellarbookid, cellarbook.winename 
AS cellarbook_winename, cellarbook.winename2 AS cellarbook_winename2, 
cellarbook.barrique AS cellarbook_barrique, cellarbook.externalref AS 
cellarbook_externalref, cellarbook.created AS cellarbook_created, 
cellarbook.updated AS cellarbook_updated, cellarbook.fk_countryid AS 
cellarbook_fk_countryid, cellarbook.fk_regionid AS 
cellarbook_fk_regionid, cellarbook.fk_subregionid AS 
cellarbook_fk_subregionid, cellarbook.fk_vineyardid AS 
cellarbook_fk_vineyardid, cellarbook.fk_supplierid AS 
cellarbook_fk_supplierid, cellarbook.fk_producerid AS 
cellarbook_fk_producerid, cellarbook.fk_distillerid AS 
cellarbook_fk_distillerid, cellarbook.fk_qualityid AS 
cellarbook_fk_qualityid, cellarbook.fk_drinktypeid AS 
cellarbook_fk_drinktypeid, cellarbook.notes AS cellarbook_notes, 
cellarbook.fk_winefamid AS cellarbook_fk_winefamid, cellarbook.variety 
AS cellarbook_variety, cbvintage.cbvintageid AS cbvintage_cbvintageid, 
cbvintage.vintage AS cbvintage_vintage, cbvintage.created AS 
cbvintage_created, cbvintage.updated AS cbvintage_updated, 
cbvintage.notes AS cbvintage_notes, cbvintage.alcohol AS 
cbvintage_alcohol, cbvintage.avgscore AS cbvintage_avgscore, 
cbvintage.avgscore2 AS cbvintage_avgscore2, cbvintage.fk_cellarbookid AS 
cbvintage_fk_cellarbookid, cbvintage.fk_wineinfoid AS 
cbvintage_fk_wineinfoid, cbvintage.fk_spirinfoid AS 
cbvintage_fk_spirinfoid, cbbottle.quantityonhand AS 
cbbottle_quantityonhand, cbbottle.purchasevalueonhand AS 
cbbottle_purchasevalueonhand, cbbottle.currentvalueonhand AS 
cbbottle_currentvalueonhand, cbbottle.cbbottleid AS cbbottle_cbbottleid, 
cbbottle.maturityfirst AS cbbottle_maturityfirst, cbbottle.maturitybest 
AS cbbottle_maturitybest, cbbottle.maturitypast AS 
cbbottle_maturitypast, cbbottle.storagelocation AS 
cbbottle_storagelocation, cbbottle.quantitypurchased AS 
cbbottle_quantitypurchased, cbbottle.quantityconsumed AS 
cbbottle_quantityconsumed, cbbottle.lastpurchaseprice AS 
cbbottle_lastpurchaseprice, cbbottle.avgpurchaseprice AS 
cbbottle_avgpurchaseprice, cbbottle.currentvalue AS 
cbbottle_currentvalue, cbbottle.isactive AS cbbottle_isactive, 
cbbottle.printlabel AS cbbottle_printlabel, cbbottle.remarks AS 
cbbottle_remarks, cbbottle.created AS cbbottle_created, cbbottle.updated 
AS cbbottle_updated, cbbottle.fk_cbvintageid AS cbbottle_fk_cbvintageid, 
cbbottle.fk_containerid AS cbbottle_fk_containerid, cbbottle.fk_cellarid 
AS cbbottle_fk_cellarid, cbbottle.fk_sealtypeid AS 
cbbottle_fk_sealtypeid, cbbottle.barcode AS cbbottle_barcode, 
cbbottle.minqoh AS cbbottle_minqoh, cbbottle.inetrefcode AS 
cbbottle_inetrefcode, cbbottle.inetrefsource AS cbbottle_inetrefsource
FROM cellarbook, cbvintage, cbbottle

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Replacing a Firebird view with orm.query.join

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 ...
 OK, more specifically, this is how to do the query:

 sess.query(SomeClass, SomeOtherClass,  
 SomeThirdClass).outerjoin((SomeOtherClass,  
 SomeClass.foo==SomeOtherClass.bar), (SomeThirdClass,  
 SomeOtherClass.foo==SomeThirdClass.bar))

 if firebird can't do OUTER JOIN, then that's a different story.
   
That did the trick and is also a lot faster then using the view which is 
even better.

Actually it was a little simplar as SA figured out the onclause, i.e.:
wines = session.query(db.Cellarbook, db.Cbvintage, 
db.Cbbottle).outerjoin(db.Cbvintage).outerjoin(db.Cbbottle)

Thanks for being patient with me
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Passive delete problem

[Werner F. Bruhin]

I have a problem deleting a Consevent instance and get the exception 
shown further below.

Relevant part of my model:
class Consevent(Base):
__table__ = sa.Table(u'consevent', metadata,
sa.Column(u'conseventid', sa.Integer(), 
sa.Sequence('gen_consevent_conseventid'), primary_key=True, nullable=False),
sa.Column(u'description', sa.String(length=25, convert_unicode=False)),
sa.Column(u'conseventdate', sa.DateTime(timezone=False)),
sa.Column(u'fk_reasonid', sa.Integer(), 
sa.ForeignKey(u'reason_ls.reasonid')),
sa.Column(u'created', sa.Date()),
sa.Column(u'updated', sa.Date()),
sa.Column(u'notes', sa.TEXT(length=32000, convert_unicode=True)),
)

reason_ls = sao.relation(Reason_Ls)
conseventgl = sao.relation('Conseventgl', cascade=all, delete, 
delete-orphan, backref='conseventgl')
conseventit = sao.relation('Conseventit', cascade=all, delete, 
delete-orphan, backref='conseventit')
conseventmu = sao.relation('Conseventmu', cascade=all, delete, 
delete-orphan, backref='consevent')
vconseventmu = sao.relation('Vconseventmu', backref='consevent', 
passive_deletes=True)

class Vconseventmu(Base):
# note that this is a view in the Firebird SQL db
__table__ = sa.Table(u'vconseventmu', metadata,
sa.Column(u'conseventmuid', sa.Integer(), 
sa.ForeignKey(u'conseventmu.conseventmuid'), primary_key=True),
sa.Column(u'name', sa.String(length=50, convert_unicode=False)),
sa.Column(u'itemtype', sa.String(length=1, convert_unicode=False)),
sa.Column(u'description', sa.String(length=50, convert_unicode=False)),
sa.Column(u'created', sa.Date()),
sa.Column(u'updated', sa.Date()),
sa.Column(u'fk_conseventid', sa.Integer(), 
sa.ForeignKey(u'consevent.conseventid')),
sa.Column(u'fk_ingrid', sa.Integer(), sa.ForeignKey(u'ingr_ls.ingrid')),
sa.Column(u'fk_recipeid', sa.Integer(), 
sa.ForeignKey(u'recipe.recipeid')),
sa.Column(u'fk_courseid', sa.Integer(), 
sa.ForeignKey(u'course_ls.courseid')),
sa.Column(u'fk_cbbottleid', sa.Integer(), 
sa.ForeignKey(u'cbbottle.cbbottleid')),
)

ingr_ls = sao.relation(Ingr_Ls)
recipe = sao.relation(Recipe)
course_ls = sao.relation(Course_Ls)
cbbottle = sao.relation(Cbbottle)


To delete a consevent I do:
for dbitem in self.dbItem.conseventit:
# clear bottag table foreign key for consumption
utils.BottleTagsClearConsumption(self.Getds(), 
dbitem.consumption)
  
self.Getds().flush()   

self.Getds().delete(self.dbItem)

self.dbItem is an instance of Consevent.

Can anyone see what I am doing wrong.

Werner


 Mon Sep 01 16:36:24 2008  Version: 3.0.416.1   wxV: 2.8.8.1 
(msw-unicode) *
Traceback (most recent call last):
  File C:\Dev\twcb\Program\dialogconsevent.py, line 752, in OnDeleteButton
if self.DeleteConsEvent():
  File C:\Dev\twcb\Program\dialogconsevent.py, line 797, in 
DeleteConsEvent
self.Getds().commit()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 663, in commit
self.transaction.commit()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 376, in commit
self._prepare_impl()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 360, in _prepare_impl
self.session.flush()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\session.py,
 
line 1409, in flush
flush_context.execute()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 265, in execute
UOWExecutor().execute(self, tasks)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 753, in execute
self.execute_save_steps(trans, task)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 768, in execute_save_steps
self.save_objects(trans, task)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 759, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\orm\mapper.py,
 
line 1182, in _save_obj
c = connection.execute(statement.values(value_params), params)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\engine\base.py,
 
line 848, in execute
return Connection.executors[c](self, object, multiparams, params)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg\sqlalchemy\engine\base.py,
 
line 899, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect, 
column_keys=keys, inline=len(params)  1), distilled_params=params)
  File 

[sqlalchemy] Re: Passive delete problem

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 passive_deletes doesnt prevent SA from operating upon rows which are  
 currently represented in the Session, only from loading in not-already- 
 loaded rows from the databse in order to ensure they are all detached  
 ro removed.   Instead, set viewonly=True on Consevent.vconseventmu.
   
You are amazing!  Just sitting there waiting for questions ;-)

That works great.

Thanks
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Mike wrote:



 I found the issue. For some reason, SqlAlchemy is passing a unicode
 string to a varchar field in SQL Server, which is causing an error to
 be raised. If I explicitly set the value to an integer or a string, it
 works fine. I'm not sure how it's getting cast to unicode, but I think
 I can work around this.
   
How is your model defined?  Maybe the column is incorrectly defined in 
the model or if you use autoload then maybe there is a problem with the 
autoload code.

I would also work around it, but provide some more details here in a new 
thread as it might point to a problem in SA.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Mike wrote:
 Hi,

 I am working on a wxPython application that saves data to various
 tables in our MS SQL Server 2000. I connect to one table and get data
 using a session. This works great. I then do a session.close() and
 then a conn.close() where conn = engine.connect().  This seems to work
 as expected as well. The problem happens when I connect to the second
 table. I get the data from the second table just fine, but when I try
 to update it using my 2nd session object, I get the following error:

 Traceback (most recent call last):
   File \\debianis\loginscript$\PythonPackages\Development\Timesheet_sa
 \ts_worksheet_MT.py, line 689, in onClose
 session.flush()
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\session.py, line 789, in flush
 self.uow.flush(self, objects)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 233, in flush
 flush_context.execute()
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 445, in execute
 UOWExecutor().execute(self, tasks)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 930, in execute
 self.execute_save_steps(trans, task)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 945, in execute_save_steps
 self.save_objects(trans, task)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 936, in save_objects
 task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\mapper.py, line 1144, in _save_obj
 c = connection.execute(statement.values(value_params), params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 844, in execute
 return Connection.executors[c](self, object, multiparams, params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 895, in execute_clauseelement
 return self._execute_compiled(elem.compile(dialect=self.dialect,
 column_keys=keys, inline=len(params)  1), distilled_params=params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 907, in _execute_compiled
 self.__execute_raw(context)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 916, in __execute_raw
 self._cursor_execute(context.cursor, context.statement,
 context.parameters[0], context=context)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 960, in _cursor_execute
 self._handle_dbapi_exception(e, statement, parameters, cursor)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 942, in _handle_dbapi_exception
 raise exceptions.DBAPIError.instance(statement, parameters, e,
 connection_invalidated=is_disconnect)
 sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error:
 None 'UPDATE [tbl_Acct_Prefs] SET pref_value=%(pref_value)s WHERE
 [tbl_Acct_Prefs].[empID] = %(tbl_Acct_Prefs_empID)s AND
 [tbl_Acct_Prefs].pref_name = %
 (tbl_Acct_Prefs_pref_name)s' {'pref_value': u'4',
 'tbl_Acct_Prefs_pref_name': 'last_payPeriod', 'tbl_Acct_Prefs_empID':
 258}


 To get the data, I do the following:

 pref = session.query(Acct_Prefs).filter_by(empID=self.emp_id,
 pref_name='last_payPeriod').first()

 Then I change a value like this:

 pref.pref_value = someValue

 Finally, I do a session.flush() and session.commit(). I've tried using
 just a commit(), but I get the same error. Since I can run this second
 session by itself in IDLE, I think the 1st session or connection or
 something is somehow messing up the second one. Any ideas on what is
 going on would be great. I'm still pretty green with SA, by the way.

 I am developing on Windows XP, Python 2.5.2 and using SqlAlchemy
 0.4.7. I can upgrade if you guys think that's the issue.
   
I am no expert in SA, but I am converting my application over to it.

Why are you closing the session?

You can work with many tables and do all your queries all in the one 
session.

self.session = Session()
pref = self.session.query(db.Preferences).get(1)
# change pref here
self.session.commit()

cellar = self.session.query(db.Cellar).get(1)
# change cellar here
self.session.commit()

etc etc

I think in most wxPython application you would create the session in 
wx.App.OnInit and close it in wx.App.OnExit.

Should you package your application with py2exe you should probably 
include this in your OnExit method:

self.session.close_all()
self.engine.dispose()


 Thanks,

 Mike

 


   


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Sent this a bit to quickly

Mike wrote:
 Hi,

 I am working on a wxPython application that saves data to various
 tables in our MS SQL Server 2000. I connect to one table and get data
 using a session. This works great. I then do a session.close() and
 then a conn.close() where conn = engine.connect().  This seems to work
 as expected as well. The problem happens when I connect to the second
 table. I get the data from the second table just fine, but when I try
 to update it using my 2nd session object, I get the following error:

 Traceback (most recent call last):
   File \\debianis\loginscript$\PythonPackages\Development\Timesheet_sa
 \ts_worksheet_MT.py, line 689, in onClose
 session.flush()
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\session.py, line 789, in flush
 self.uow.flush(self, objects)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 233, in flush
 flush_context.execute()
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 445, in execute
 UOWExecutor().execute(self, tasks)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 930, in execute
 self.execute_save_steps(trans, task)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 945, in execute_save_steps
 self.save_objects(trans, task)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 936, in save_objects
 task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\mapper.py, line 1144, in _save_obj
 c = connection.execute(statement.values(value_params), params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 844, in execute
 return Connection.executors[c](self, object, multiparams, params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 895, in execute_clauseelement
 return self._execute_compiled(elem.compile(dialect=self.dialect,
 column_keys=keys, inline=len(params)  1), distilled_params=params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 907, in _execute_compiled
 self.__execute_raw(context)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 916, in __execute_raw
 self._cursor_execute(context.cursor, context.statement,
 context.parameters[0], context=context)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 960, in _cursor_execute
 self._handle_dbapi_exception(e, statement, parameters, cursor)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 942, in _handle_dbapi_exception
 raise exceptions.DBAPIError.instance(statement, parameters, e,
 connection_invalidated=is_disconnect)
 sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error:
 None 'UPDATE [tbl_Acct_Prefs] SET pref_value=%(pref_value)s WHERE
 [tbl_Acct_Prefs].[empID] = %(tbl_Acct_Prefs_empID)s AND
 [tbl_Acct_Prefs].pref_name = %
 (tbl_Acct_Prefs_pref_name)s' {'pref_value': u'4',
 'tbl_Acct_Prefs_pref_name': 'last_payPeriod', 'tbl_Acct_Prefs_empID':
 258}


 To get the data, I do the following:

 pref = session.query(Acct_Prefs).filter_by(empID=self.emp_id,
 pref_name='last_payPeriod').first()

 Then I change a value like this:

 pref.pref_value = someValue

 Finally, I do a session.flush() and session.commit(). I've tried using
 just a commit(), but I get the same error. Since I can run this second
 session by itself in IDLE, I think the 1st session or connection or
 something is somehow messing up the second one. Any ideas on what is
 going on would be great. I'm still pretty green with SA, by the way.

 I am developing on Windows XP, Python 2.5.2 and using SqlAlchemy
 0.4.7. I can upgrade if you guys think that's the issue.
   
I am no expert in SA, but I am converting my application over to it.

Why are you closing the session?

You can work with many tables and do all your queries all in the one
session.

self.session = Session()
pref = self.session.query(db.Preferences).get(1)
# change pref here
self.session.commit()

cellar = self.session.query(db.Cellar).get(1)
# change cellar here
self.session.commit()

etc etc

I think in most wxPython application you would create the session in
wx.App.OnInit and close it in wx.App.OnExit.

Should you package your application with py2exe you should probably
include this in your OnExit method:

self.session.close_all()
self.engine.dispose()

I was seeing that my application would hang on closing if I did not do 
this.  I am using Firebird SQL, it might be something to do 

[sqlalchemy] Re: Storing UTC Dates

[Werner F. Bruhin]

Heston,

Heston James - Cold Beans wrote:

 Hello Guys,

 This might seem like a bit of a naive question but I’m looking for 
 your advice. Being from the UK we operate on Daylight Savings Time 
 which gives us a one hour offset on times for a few months of the year.

 I currently have a DateTime column which is declared like so:

 created = Column(DateTime, default=func.now())

 modified = Column(DateTime, default=func.now(), onupdate=func.now())

 Which generally works very well, when I create a record it inserts the 
 current locale time into the column, however, it stores the datetime 
 with DST applied too it. As I use the datetime at a later point for 
 posting over web services I really need to store the UTC version of 
 now() in the database, without DST applied to it.

 How can I modify the above column definition to do this? Can I simply 
 use something instead of func.now()? I was given the advise to use 
 func.now() by someone but not really sure what it returns, is it a 
 datetime.datetime object? Or a time tuple?

 Or is there a parameter I can pass to Column() or DateTime() which 
 will ensure it uses the UTC format of the date when creating and 
 modifying records?

IIUC func.now is a database function.

You should be able to use datetime instead i.e.:

created = Column(DateTime, default=datetime.datetime.utcnow)

modified = Column(DateTime, default=datetime.datetime.utcnow, 
onupdate=datetime.datetime.utcnow)


Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Mike wrote:

...

 Does this work for multiple databases? This particular program I am
 working on will be connecting to 2 or 3 databases and a table or three
 in each of those. I'm pretty sure I have to create separate engines
 for each db and probably bind separate sessions for those.
   
I don't think so, using sessions and engines I would think you have to 
have one per database.

You probably need to explain a bit more what you are doing with these 
databases, i.e. are you moving data from one to the other, or are they 
independent databases or ..

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Mike wrote:
 Hi,

 Here's what I'm doing. I have a timesheet application written in
 wxPython. It works, but I think it would work better if I changed the
 database calls into SA calls. There are 3 databases. I created one and
 the other two are pre-existing. The one I created is the one I store
 all the user entered data into. I read from the official accounting
 database and I read from a couple of views in the third database. The
 two databases I read from are for checking purposes to make sure that
 I am doing the calculations correctly in my program and also for
 authentication and certain bits of user data, such as employee number,
 name and such.

 Anyway, I dumped the close calls I had and put them in the OnExit
 method, which is something I've never used before. I'm not sure that I
 have that set up right, but at this point it doesn't matter. I am
 still receiving the same error.

 If I take all of my SA setup out of the wxPython code and stick it in
 my own module, it works. Here's what that looks like:


 code

 import ts_info
 from db_tables import Acct_Prefs, TimeEntries
 from sqlalchemy import Table
 from sqlalchemy.orm import mapper, sessionmaker

 # Connect to the database
 print 'connecting to MCISAccounting DB...'
 conn, engine, meta = ts_info.setupDB('acct')

 # Load the tables
 print 'loading tables...'
 entry_table = Table('tbl_TimeEntries', meta, autoload=True)
 prefs_table = Table('tbl_Acct_Prefs', meta, autoload=True)

 # Map the tables
 print 'mapping tables...'
 mapper(TimeEntries, entry_table)
 mapper(Acct_Prefs, prefs_table)

 # Create a session object
 print 'creating session...'
 Session = sessionmaker(bind=engine)
 session = Session()

 pref = self.session.query(Acct_Prefs).filter_by(empID=self.emp_id,
 pref_name='last_payPeriod').first()
 pref.pref_value = SomeValue
 self.session.commit()

 /code

 For some weird reason, if I do those last three lines in one of my
 wxPython methods, I get an error. I know it has to be something really
 stupid, but I'm just not seeing it...

 Mike

   
I did a small test based on the test/demo project I am working on.

Just duplicated the database and changed the application to read from 
one database and show a list of countries and to read/update from a 
second database.  I can view both without problem and update the 2nd db 
without getting an error.

The way I set them up is:
class BoaApp(wx.App):
def OnInit(self):
   
self.ConnectDb()
   
self.main = demoFrame2db.create(None)
self.main.Show()
self.SetTopWindow(self.main)
return True
   
def OnExit(self):
self.session.close_all()
self.engine.dispose()
   
def ConnectDb(self):
# db 1
database = u'C:/Dev/BoaTest04/dbsampleSAnew/database.sqldb'
dburl = sa.engine.url.URL('sqlite', username=None, 
password=None, host=None, port=None, database=database)
self.engine = sa.create_engine(dburl, encoding='utf8', echo=False)
Session = sao.sessionmaker()
Session.configure(bind=self.engine)
self.session = Session()
   
# db 2
database2 = u'C:/Dev/BoaTest04/dbsampleSAnew/database2.sqldb'
dburl = sa.engine.url.URL('sqlite', username=None, 
password=None, host=None, port=None, database=database2)
self.engine2 = sa.create_engine(dburl, encoding='utf8', echo=False)
Session2 = sao.sessionmaker()
Session2.configure(bind=self.engine2)
self.session2 = Session2()
   
def Getds(self):
return self.session

def Getds2(self):
return self.session2

In my primary frame I then do:
   self.theList.SetSession(wx.GetApp().Getds())
self.theList.InitObjectListView()
self.theList.Bind(wx.EVT_LIST_ITEM_SELECTED, self.OnItemSelected)

# a list from a 2 db
self.theList2.SetSession(wx.GetApp().Getds2())
self.theList2.InitObjectListView()
 
when selecting items from theList I display some details and I can 
update these details and commit.

Maybe this helps, but maybe it just causes more confusion?

Werner



 On Aug 28, 10:29 am, Werner F. Bruhin [EMAIL PROTECTED] wrote:
   
 Mike,

 Mike wrote:

 ...

 
 Does this work for multiple databases? This particular program I am
 working on will be connecting to 2 or 3 databases and a table or three
 in each of those. I'm pretty sure I have to create separate engines
 for each db and probably bind separate sessions for those.
   
 I don't think so, using sessions and engines I would think you have to
 have one per database.

 You probably need to explain a bit more what you are doing with these
 databases, i.e. are you moving data from one to the other, or are they
 independent databases or ..

 Werner
 
 


   


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email

[sqlalchemy] Re: db model - default and onupdate problems

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 On Aug 24, 2008, at 6:42 AM, Werner F. Bruhin wrote:

   
 I have this in my model:

sa.Column(u'created', sa.Date(), default=sa.func.now()),
sa.Column(u'updated', sa.Date(), onupdate=datetime.datetime.now),

 But the dates don't get updated when I do:

 langtable = [
 INSERT INTO LANGUAGE (LANGID, NAME, LOCALES) VALUES (1, 'English',
 'en'),
  etc
 ]
 

 the onupdates and such related to a Table only take effect when you  
 use the Table object itself to generate the INSERT statement, not a  
 plain string.  If you want the database to always do something when an  
 INSERT happens regardless of its source, you'd have to configure a  
 trigger in your database (which sqlite does support).
   
Thanks for the quick reply.

I started working on a wxPython demo/sample app using SA and want to use 
sqlite for it (to keep the dependencies down), so will just have to 
change the demo data loading to use table objects.

Werner

 


   


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] db model - default and onupdate problems

[Werner F. Bruhin]

I have this in my model:

sa.Column(u'created', sa.Date(), default=sa.func.now()),
sa.Column(u'updated', sa.Date(), onupdate=datetime.datetime.now),

But the dates don't get updated when I do:

langtable = [
INSERT INTO LANGUAGE (LANGID, NAME, LOCALES) VALUES (1, 'English', 
'en'),
 etc
]

conn = engine.connect()

for row in langtable:
conn.execute(row)
   
for lang in session.query(db.Language).all():
print lang

Gives me:
Language(created=None, langid=1, locales=u'en', name=u'English', 
updated=None)

I am doing this with sqlite database.

What am I doing wrong here?

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: SavePoint question

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 ...
 if FB didn't raise an error when you said begin_nested() then i think
 SAVEPOINT is working.  Any number of SAVEPOINTS are still all
 contained within the larger transaction, though.   If you want u3 to
 be committed regardless of the transaction, you'd have to use a
 different Session on its own transaction.
   
Thanks for the quick reply.

I initially had this but that caused me problems when I wanted to access 
data from that other session.

Would I use merging for this? I.e. something along these lines?

session1
 do whatever
open a wxPython dialog
- uses session2
 does whatever
on dialog close:  newobject = session1.merge(anObjectFromSession2)

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] SavePoint question

[Werner F. Bruhin]

I have a similar case to what is shown in the doc, here my slightly 
different usecase, see the commit for u3 and a final rollback.

Session = sessionmaker()
sess = Session()
sess.add(u1)
sess.add(u2)

sess.begin_nested() # establish a savepoint
sess.add(u3)  # in my case this is data I would like to keep regardless if 
later a rollback is done.
sess.commit()  # or rollback affecting u3, does not affect u1 or u2

sess.rollback() # I guessed/hoped that this would only affect u1 and u2, 
however it rolls back also u3


I am on Firebird SQL 2.1.

Should this work as I am hoping it is (in which case I will need to track it 
down further in my program), or is SAVEPOINT
not supported by FB?

Best regards
Werner



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] How to totally close down db connection?

[Werner F. Bruhin]

Within my application (FB SQL) I use kinterbasdb.services to do backup
and restore operations.

No problem with the backup, but the restore is not working as SA seems
to hang on to a connection.

I do basically this to connect:
self.engine = db.sa.create_engine(dburl, encoding='utf8',
echo=False)
self.Session = db.sao.sessionmaker()
self.Session.configure(bind=self.engine)
self.ds = self.Session()


Then this to close:
self.ds.close()
self.Session.close_all()
del self.ds
del self.Session
del self.engine

Do backup and/or restore and then reconnect to the database.

However at this point if I pause the execution in the debugger (Boa) and
check with e.g. IBExpert I see that the db connection from SA is still open.

I guess it has to do with the connection pool, is there anyway to tell
the pool to close all connections or what other options do I have?

Werner

P.S.
This is with SA 0.5.beta1.


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to totally close down db connection?

[Werner F. Bruhin]

Werner F. Bruhin wrote:
 Within my application (FB SQL) I use kinterbasdb.services to do backup
 and restore operations.

 No problem with the backup, but the restore is not working as SA seems
 to hang on to a connection.

 I do basically this to connect:
 self.engine = db.sa.create_engine(dburl, encoding='utf8',
 echo=False)
 self.Session = db.sao.sessionmaker()
 self.Session.configure(bind=self.engine)
 self.ds = self.Session()


 Then this to close:
 self.ds.close()
 self.Session.close_all()
 del self.ds
 del self.Session
 del self.engine

 Do backup and/or restore and then reconnect to the database.

 However at this point if I pause the execution in the debugger (Boa) and
 check with e.g. IBExpert I see that the db connection from SA is still open.

 I guess it has to do with the connection pool, is there anyway to tell
 the pool to close all connections or what other options do I have?

 Werner

 P.S.
 This is with SA 0.5.beta1.


   
After searching through the doc and doing some googling and trial and 
error I added an engine.dispose() to the closing routine and that seems 
to do the trick.  Is this the correct approach?

New version of closing db:
self.ds.close()
self.Session.close_all()
self.engine.dispose()   # added this
del self.ds
del self.Session
del self.engine


Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] read only column with declaritive

[Werner F. Bruhin]

Michael had helped me in the past to get read only columns defined like 
this:

## old model non declarative
##class Quality(OrmObject):
##def comboname(self):
##return self._comboname
##comboname = property(comboname)
##
##quality = sao.mapper(Quality, quality_table,
##properties={
##'_comboname': quality_table.c.comboname,
##'comboname': sao.synonym('_comboname'),

I tried to translate this based on the declarative doc (BTW, there is a 
typo on synonym_for it is shown as synonyn_for, i.e. an n instead of 
the m and came up with this:
class Quality(Base):
__table__ = sa.Table(u'quality', metadata,
...
sa.Column(u'comboname', sa.String(length=63, convert_unicode=False), 
server_default=),
...
)

# read only columns
_comboname = sao.column_property(__table__.c.comboname)
@sad.synonym_for('_comboname')
@property
def comboname(self):
return self._comboname

When reading from Quality I do not see the comboname column but I also 
do not get any error.

Would appreciate if someone can put me right.

Best regards
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Exception when changing to declarative

[Werner F. Bruhin]

I am changing my model to using declarative.

I am getting an exception bool' object has no attribute 
'__visit_name__' (full exception below) which was relatively difficult 
for me to trace down.

The bool exception I get when I change this:
vrecingrwfit = sao.relation('Vrecingrwfit', primaryjoin=
(__table__.c.recipeid=='vrecingrwfit.fk_recipeid'), 
passive_deletes=True)
to this:
vrecingrwfit = sao.relation('Vrecingrwfit', primaryjoin=
('Recipe.recipeid'=='vrecingrwfit.fk_recipeid'), 
passive_deletes=True)

It happens as I did not define the primaryjoin correctly.
Correcting it to one string, i.e. removing the middle quotes fixed it:
vrecingrwfit = sao.relation('Vrecingrwfit', primaryjoin=
('Recipe.recipeid==vrecingrwfit.fk_recipeid'), 
passive_deletes=True)

May I suggest to change/improve the exception, i.e. give some pointer 
where in the model the problem is.

Werner


Traceback (most recent call last):
  File saTest.py, line 42, in module
x = session.query(dbmin.Prefminimal).get(1)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\session.py,
 
line 894, in query
return self._query_cls(entities, self, **kwargs)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\query.py,
 
line 97, in __init__
self.__setup_aliasizers(self._entities)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\query.py,
 
line 111, in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity, 
ent.entity_name)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\util.py,
 
line 398, in _entity_info
mapper = class_mapper(entity, entity_name, compile)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\util.py,
 
line 488, in class_mapper
mapper = mapper.compile()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\mapper.py,
 
line 370, in compile
mapper.__initialize_properties()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\mapper.py,
 
line 391, in __initialize_properties
prop.init(key, self)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\interfaces.py,
 
line 378, in init
self.do_init()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\properties.py,
 
line 510, in do_init
self._determine_synchronize_pairs()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\properties.py,
 
line 605, in _determine_synchronize_pairs
eq_pairs = criterion_as_pairs(self.primaryjoin, 
consider_as_foreign_keys=self._foreign_keys, any_operator=self.viewonly)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\sql\util.py,
 
line 268, in criterion_as_pairs
visitors.traverse(expression, {}, {'binary':visit_binary})
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\sql\visitors.py,
 
line 123, in traverse
return traverse_using(iterate(obj, opts), obj, visitors)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\sql\visitors.py,
 
line 115, in traverse_using
meth = visitors.get(target.__visit_name__, None)
AttributeError: 'bool' object has no attribute '__visit_name__'




--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: read only column with declaritive - resolved

[Werner F. Bruhin]

It looks like I just confused myself, in my test case I got None 
returned and I thought it was a model definition issue but it was me 
leaving one of the columns used for the computed column at None.

So if I do just the following the read only column (FB SQL computed 
column) are working for me.

class Quality(Base):
__table__ = sa.Table(u'quality', metadata,
...
sa.Column(u'comboname', sa.String(length=63, convert_unicode=False), 
server_default=),
...
)

# read only columns
comboname = sao.column_property(__table__.c.comboname)


Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: accessing fields by name

[Werner F. Bruhin]

Alex,

alex bodnaru wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1


 hello friends,

 as opposed to a table.query() that returns a list of records of that table, 
 with
 fields accessible as record attributes, a select() statement returns a list of
 tuples with the values of the fields in the virtual record.

 i'd like to access the fields by their column name or label, at least the non
 calculated or labelled ones. it would suffice to be able to retrieve a list 
 with
 the labels of the fields in the resulted tuples.

 could you point me in the right direction?
   
No expert, so take this with a grain of salt.

q = db.Quality.__table__.select()
print q
print dir(q)
print q.columns

q.columns is giving the column names as a list

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: problem with server_default (and/or sa.PassiveDefault in 0.5.beta1

[Werner F. Bruhin]

Svilen and Michael,

Thanks for all the pointers.  Will look into this all and read up some 
more on declarative (I like its approach, having things together) and do 
some more test scripts for my application.

Werner

Michael Bayer wrote:
 that __repr__ is pretty tortured too; a typical ORM-agnostic approach  
 is:

  def __repr__(self):
  return %s(%s) % (
  (self.__class__.__name__),
  ', '.join([%s=%r % (key, getattr(self, key))
 for key in sorted(self.__dict__.keys())
 if not key.startswith('_')]))



 


   


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: problem with server_default (and/or sa.PassiveDefault in 0.5.beta1

[Werner F. Bruhin]

I did some more research on this.

If I change back to 0.4.3, i.e. change my model to use:
##pkg_resources.require(sqlalchemy) # get latest version
pkg_resources.require(sqlalchemy==0.4.3) # use a specific version

Then I can debug my code, i.e. my application starts.

So, I changed it back to 0.5.beta1 and tried to find out were the 
problem starts in my code, it is on the following line.

self.prefs = self.ds.query(db.prefminimal).get(1)

prefminimal is a redefinition of the preferences table, i.e. it 
only maps columns which are present in the preferences table regardless 
of the version of my application.

The model for it is:
metadataMin = sa.MetaData()
prefminimal_table = sa.Table(u'preferences', metadataMin,
sa.Column(u'prefid', sa.Integer(), 
sa.Sequence('gen_preferences_prefid'), primary_key=True, nullable=False),
sa.Column(u'fk_langid', sa.Integer(), 
sa.ForeignKey(u'language.langid'), nullable=False),
sa.Column(u'dbstructure', sa.Integer()),
sa.Column(u'dbdata', sa.Integer()),
)


class Prefminimal(OrmObject):
pass


prefminimal = sao.mapper(Prefminimal, prefminimal_table,
properties={
})

All other tables (including the full version of preferences) are using 
metadata = sa.MetaData(), instead of the metadataMin I use for the 
prefminimal definition.

I got this working some month ago, but maybe this is not the right way 
to go about it and it bits me now?

Werner



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: problem with server_default (and/or sa.PassiveDefault in 0.5.beta1

[Werner F. Bruhin]

Werner F. Bruhin wrote:
 I did some more research on this.

 If I change back to 0.4.3, i.e. change my model to use:
 ##pkg_resources.require(sqlalchemy) # get latest version
 pkg_resources.require(sqlalchemy==0.4.3) # use a specific version

 Then I can debug my code, i.e. my application starts.

 So, I changed it back to 0.5.beta1 and tried to find out were the 
 problem starts in my code, it is on the following line.

 self.prefs = self.ds.query(db.prefminimal).get(1)
   
I think it has nothing to do with the prefminimal, it happens on 
whatever is the first query on whatever table.

I just run a small test script with the idle debugger and I see the same 
exception.

If I run it in Idle with the debugger but use SA 0.4.3 then it runs o.k.

I hope this info helps.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: problem with server_default (and/or sa.PassiveDefault in 0.5.beta1

[Werner F. Bruhin]

Michael Bayer wrote:
 On Jun 18, 2008, at 4:05 AM, Werner F. Bruhin wrote:

   
 All other tables (including the full version of preferences) are using
 metadata = sa.MetaData(), instead of the metadataMin I use for the
 prefminimal definition.

 I got this working some month ago, but maybe this is not the right way
 to go about it and it bits me now?

 

 using two separate MetaData objects, if I correctly understand that's  
 what you're doing, is not going to work at all if that Table has any  
 ForeignKey objects (which it does).   I dont see the use case for two  
 distinct MDs.
   
What I need to do at start up of the application is to check a couple of 
columns in table preferences to check what version the database is, 
then if it is an older version I do the upgrade of the database 
(metadata etc) and update the columns in the table.

I can not read all the columns of the table as there might be columns 
defined for this new release which are not present until the upgrade is 
finished.

Would something like this work for me session.query(Preferences.col1, 
Preferences.col2) and have columns defined in the model which are not 
yet present in the database?

If the query approach does not work what alternatives would I have?

Werner


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] problem with server_default (and/or sa.PassiveDefault in 0.5.beta1

[Werner F. Bruhin]

I just got beta1 and I run into a problem with the following:

sa.Column(u'consumedvalue', sa.Numeric(precision=18, length=2, 
asdecimal=True), sa.PassiveDefault()),

consumedvalue is a readonly column (i.e. computed by Firebird SQL) and I 
am getting the following exception when I try to debug (in Boa), the 
same happens if I change the above to use server_default=.

Do I need to change something else in the model.  For each of these type 
of columns I also have the following:

class Consumption(object):
def consumedvalue(self):
return self._consumedvalue
consumedvalue = property(consumedvalue)

Werner



Traceback (most recent call last):
  File C:\Python25\Lib\site-packages\boa\Debugger\IsolatedDebugger.py, 
line 823, in run
Bdb.run(self, cmd, globals, locals)
  File C:\Python25\lib\bdb.py, line 366, in run
exec cmd in globals, locals
  File string, line 1, in module
  File C:\Dev\twcb\Program\twcb.py, line 1108, in module
main()
  File C:\Dev\twcb\Program\twcb.py, line 1104, in main
appl = BoaApp()
  File C:\Python25\Lib\site-packages\wx-2.8-msw-unicode\wx\_core.py, 
line 7912, in __init__
self._BootstrapApp()
  File C:\Python25\Lib\site-packages\wx-2.8-msw-unicode\wx\_core.py, 
line 7487, in _BootstrapApp
return _core_.PyApp__BootstrapApp(*args, **kwargs)
  File C:\Dev\twcb\Program\twcb.py, line 266, in OnInit
self.prefs = self.ds.query(db.prefminimal).get(1)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\session.py,
 
line 894, in query
return self._query_cls(entities, self, **kwargs)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\query.py,
 
line 97, in __init__
self.__setup_aliasizers(self._entities)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\query.py,
 
line 111, in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity, 
ent.entity_name)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\util.py,
 
line 401, in _entity_info
mapper = entity.compile()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\mapper.py,
 
line 370, in compile
mapper.__initialize_properties()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\mapper.py,
 
line 391, in __initialize_properties
prop.init(key, self)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\interfaces.py,
 
line 378, in init
self.do_init()
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\properties.py,
 
line 193, in do_init
strategies.DefaultColumnLoader(self)._register_attribute(None, None, 
False, comparator_callable, proxy_property=self.descriptor)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\strategies.py,
 
line 37, in _register_attribute
proxy_property=proxy_property
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 88, in register_attribute
return attributes.register_attribute(class_, key, *args, **kwargs)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\attributes.py,
 
line 1407, in register_attribute
descriptor = proxy_type(key, proxy_property, comparator, parententity)
  File 
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\attributes.py,
 
line 144, in __init__
self.descriptor = self.user_prop = descriptor
AttributeError: can't set attribute


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: problem with server_default (and/or sa.PassiveDefault in 0.5.beta1

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:

 On Jun 17, 1:06 pm, Werner F. Bruhin [EMAIL PROTECTED] wrote:
   
 I just got beta1 and I run into a problem with the following:

 sa.Column(u'consumedvalue', sa.Numeric(precision=18, length=2,
 asdecimal=True), sa.PassiveDefault()),

 consumedvalue is a readonly column (i.e. computed by Firebird SQL) and I
 am getting the following exception when I try to debug (in Boa), the
 same happens if I change the above to use server_default=.

 Do I need to change something else in the model.  For each of these type
 of columns I also have the following:

 class Consumption(object):
 def consumedvalue(self):
 return self._consumedvalue
 consumedvalue = property(consumedvalue)
 

 the mapper will automatically map the consumedvalue column to the
 consumedvalue attribute on the class.  The descriptor you've placed
 there conflicts with it.  You need to tell the mapper about the
 _consumedvalue name you're using, which is illustrated here:
 http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_mapper_overriding
   
I already have this (see below), sorry forgot to mention this before.  
Note that my model worked under 0.4.3 and it does in 0.5beta1, i.e. I 
can insert a record into Consumption.  I only get the exception shown in 
the previous email when I try to run it through the debugger (to check 
something unrelated to SA).

Werner

consumption = sao.mapper(Consumption, consumption_table,
properties={
'reason_ls': sao.relation(Reason_Ls),
'rating': sao.relation(Rating),
'cbbottle': sao.relation(Cbbottle),
'cellar': sao.relation(Cellar),
'_consumedvalue': consumption_table.c.consumedvalue,
'consumedvalue': sao.synonym('_consumedvalue'),
})


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: app not closing when py2exe'd it

[Werner F. Bruhin]

Werner F. Bruhin wrote:
 Werner F. Bruhin wrote:
   
 I am having a problem with my app not closing correctly when I py2exe'd it.

 Trying to track it down but with not much look so far and as sqlalchemy 
 is one of the big changes I did in this version of the app (i.e. moved 
 from another ORM to sqlalchemy) I wonder if there are some things I have 
 to watch out for.

 As I suspected SA I make sure that on close all my connections are 
 closed and for good measure I also del my session and the engine.

 Is there anything else I should watch out for? 

 Is there some way I can check that I really closed all my connections?

 Is SA using threads?  If yes, do I need to do something special to 
 ensure that they are all closed/finished?
   
 
 I resolved the issue by adding a del self.ds (which is my session)

 So, now I do something along these lines:

 self.engine = db.sa.create_engine(dburl, encoding='utf8', echo=False)
 self.Session = db.sao.sessionmaker(autoflush=True, transactional=True)
 self.Session.configure(bind=self.engine)
 self.ds = self.Session()
 ...
 self.ds.Close()
 del self.ds # if I don't do this app does not close
 del self.Session
 del self.engine

 I am using SA 0.4.3 and the engine is Firebird.

 Is this an indication that I am not closing something correctly, if yes 
 what could it be.

 BTW, I also tried self.Session.close_all()

 While the del solves my issue I am a bit nervous that I am using SA 
 somehow incorrectly.
   
Had a discussion on this problem on the wxPython list with Andrea, he 
uses SA 0.4.6 with SQLite and does not see any issue when his py2exe'd 
application when it closes.

This confirms to me that either I am doing something wrong or the 
Firebird SQL backend in SA is for some reason not closing correctly.

Can anyone give me some hints on what I should look for/at to figure out 
what is causing the hang on a normal application shutdown.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] app not closing when py2exe'd it

[Werner F. Bruhin]

I am having a problem with my app not closing correctly when I py2exe'd it.

Trying to track it down but with not much look so far and as sqlalchemy 
is one of the big changes I did in this version of the app (i.e. moved 
from another ORM to sqlalchemy) I wonder if there are some things I have 
to watch out for.

As I suspected SA I make sure that on close all my connections are 
closed and for good measure I also del my session and the engine.

Is there anything else I should watch out for? 

Is there some way I can check that I really closed all my connections?

Is SA using threads?  If yes, do I need to do something special to 
ensure that they are all closed/finished?

Appreciate any hints or tips.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: refresh(object)

[Werner F. Bruhin]

Micheal,

Michael Bayer wrote:
 ...

 you need the refresh() to cascade onto the objects present in the  
 vconseventmu collection, so set  'vconseventmu' as  
 sao.relation(Vconseventmu, cascade=save-update, merge, refresh- 
 expire).
   
Thanks that did the trick.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: refresh(object)

[Werner F. Bruhin]

Michael,

Thanks for the quick reply, I needed a bit of time to be able to get 
some more data on this.

Michael Bayer wrote:
 ...
 uh it depends on what object.otherviewofsubobject is...is that a  
 relation() ?  a python descriptor ?  if the latter, how does it  
 work ?  where does it get context from ?   seems like this is a simple  
 issue of issuing a SELECT on a connection outside of the current  
 transaction.
   
They are both relation.  Ok, here some test code showing the problem, I 
hope this will allow you to give me some pointers.

consevent = session.query(db.consevent).get(1)
conseventMU = consevent.conseventmu
conseventMUview = consevent.vconseventmu
print 'conseventmu'
for item in conseventMU:
print item
print 'conseventmu-VIEW'
for item in conseventMUview:
print item


consItem = session.query(db.conseventmu).get(5)
consItem.description = uSouris d'ageau

session.flush()
session.refresh(consevent)
##consevent = session.query(db.consevent).get(1)  # this does not work 
either
conseventMU = consevent.conseventmu
conseventMUview = consevent.vconseventmu
print 'conseventmu'
for item in conseventMU:
print item
print 'conseventmu-VIEW'
for item in conseventMUview:
print item

Sample output - before update:

conseventmu
...
Conseventmu(conseventmuid=5, description=uSouris d'agneau 
aaa23355bbbxx, created=datetime.date(2008, 5, 7), 
updated=datetime.date(2008, 5, 7), fk_conseventid=1, fk_ingrid=None, 
fk_recipeid=2, fk_courseid=6, fk_cbbottleid=None)
...
conseventmu-VIEW
...
Vconseventmu(conseventmuid=5, name=u'Foie Gras aux Epices', 
itemtype=u'R   ', description=uSouris d'agneau aaa23355bbbxx, 
created=datetime.date(2008, 5, 7), updated=datetime.date(2008, 5, 7), 
fk_conseventid=1, fk_ingrid=None, fk_recipeid=2, fk_courseid=6, 
fk_cbbottleid=None)
...

Sample output - after update and flush:
conseventmu
...
Conseventmu(conseventmuid=5, description=uSouris d'ageau, 
created=datetime.date(2008, 5, 7), updated=datetime.date(2008, 5, 7), 
fk_conseventid=1, fk_ingrid=None, fk_recipeid=2, fk_courseid=6, 
fk_cbbottleid=None)
...
conseventmu-VIEW
...
Vconseventmu(conseventmuid=5, name=u'Foie Gras aux Epices', 
itemtype=u'R   ', description=uSouris d'agneau aaa23355bbbxx, 
created=datetime.date(2008, 5, 7), updated=datetime.date(2008, 5, 7), 
fk_conseventid=1, fk_ingrid=None, fk_recipeid=2, fk_courseid=6, 
fk_cbbottleid=None)
...

Some of my model (I hope I provide the relevant parts you need to be 
able to help me)- keep in mind that Vconseventmu is a FB view and not a 
real table (this is used mainly to show items in listctrl's in my 
wxPython application - there are no updates to it).

consevent = sao.mapper(Consevent, consevent_table,
properties={
...
'conseventmu': sao.relation(Conseventmu),
'vconseventmu': sao.relation(Vconseventmu) #, 
enable_typechecks=False),
})

conseventmu = sao.mapper(Conseventmu, conseventmu_table,
properties={
...
'recipe': sao.relation(Recipe),
'ingr_ls': sao.relation(Ingr_Ls),
'consevent': sao.relation(Consevent),
})

vconseventmu = sao.mapper(Vconseventmu, vconseventmu_table,
properties={
...
'ingr_ls': sao.relation(Ingr_Ls),
'recipe': sao.relation(Recipe),
'consevent': sao.relation(Consevent),
})

vconseventmu_table = sa.Table(u'vconseventmu', metadata,
sa.Column(u'conseventmuid', sa.Integer(), 
sa.ForeignKey(u'conseventmu.conseventmuid'), primary_key=True), # fake
sa.Column(u'name', sa.String(length=50, convert_unicode=False)),
sa.Column(u'itemtype', sa.String(length=1, convert_unicode=False)),
sa.Column(u'description', sa.String(length=50, convert_unicode=False)),
sa.Column(u'created', sa.Date()),
sa.Column(u'updated', sa.Date()),
sa.Column(u'fk_conseventid', sa.Integer(), 
sa.ForeignKey(u'consevent.conseventid')),
sa.Column(u'fk_ingrid', sa.Integer(), sa.ForeignKey(u'ingr_ls.ingrid')),
sa.Column(u'fk_recipeid', sa.Integer(), 
sa.ForeignKey(u'recipe.recipeid')),
sa.Column(u'fk_courseid', sa.Integer(), 
sa.ForeignKey(u'course_ls.courseid')),
sa.Column(u'fk_cbbottleid', sa.Integer(), 
sa.ForeignKey(u'cbbottle.cbbottleid')),
)

consevent_table = sa.Table(u'consevent', metadata,
sa.Column(u'conseventid', sa.Integer(), 
sa.Sequence('gen_consevent_conseventid'), primary_key=True, nullable=False),
sa.Column(u'description', sa.String(length=25, convert_unicode=False)),
sa.Column(u'conseventdate', sa.DateTime(timezone=False)),
sa.Column(u'fk_reasonid', sa.Integer(), 
sa.ForeignKey(u'reason_ls.reasonid')),
sa.Column(u'created', sa.Date()),
sa.Column(u'updated', sa.Date()),
sa.Column(u'notes', sa.TEXT(length=32000, convert_unicode=True)),
)

DDL for FB view:
CREATE VIEW VCONSEVENTMU(
CONSEVENTMUID,
NAME,
ITEMTYPE,
DESCRIPTION,
CREATED,
UPDATED,
FK_CONSEVENTID,
FK_INGRID,
FK_RECIPEID,
FK_COURSEID,
FK_CBBOTTLEID)
AS
select item.conseventmuid,
   

[sqlalchemy] refresh(object)

[Werner F. Bruhin]

I have a problem with refresh, doing something along these lines:

object.subobject
object.otherviewofsubobject  (this is actually a Firebird SQL view - 
which I use for read only access)

do some update to subobject
session.flush()

refresh(object)

object.subobject
object.otherviewofsubobject  - at this point this one does not show the 
update done to subobject

I replaced the session.flush() with a session.commit() to see if the 
data makes it to the database correctly, which it does.

Is this just a got you with me trying to keep using db views or can I 
make SA give me the correct data for the view by doing some additional step.

Any hints would be very much appreciated.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Entity name None - solved

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 On Feb 11, 2008, at 12:02 PM, Werner F. Bruhin wrote:

   
 Thanks again for you quick reply.

 I had a case problem, my string was containing the mapper name instead
 of the object name.

 

 not sure if you're referring to your emailthe error basically  
 means no mapper() is set up for the object in question.
   
Your first reply pointed me in the right direction, i.e. I found out 
that I was not working with the object I thought I was working as I used 
the incorrect name (see below).

container_lm = sao.mapper(.
class Container_Lm(Object):

Thanks again for your help and best regards
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Entity name None

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
...
 the entity name part of the message there is only trying to identify  
 which mapper the given object is mapped to.  but in this case it looks  
 like you are saving an actual Mapper object (when you should be  
 sending one of your application's object instances), so thats the  
 error, i.e.

 m = mapper(...)

 sess.save(m)  # -- error !
   
Thanks again for you quick reply.

I had a case problem, my string was containing the mapper name instead 
of the object name.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] How to check that orm object is the same

[Werner F. Bruhin]

In my wxPython GUI I get an error if I reselect the same object and then 
issue a commit.

I get the exception:
InvalidRequestError: Object 'Tastingsys(tastingsysid=5, ..., 
xmltype=u'GEN100')' is already attached to session '114990800' (this is 
'63582576')

I would like to do something like:
ormobject._instance_key == ormobject._instance_key

Above seems to work for me but I don't like using _instance_key as it 
is declared privat, what is a cleaner way of doing this check?

Werner


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Handling of currency values

[Werner F. Bruhin]

I am converting an existing Firebird DB over to use sqlalchemy (0.4.0) 
and I can't figure out how to define the model for currency values.

In the DB they are defined as numeric(18,2) default 0 and in the model I 
do e.g.

sa.Column(u'cbb_currentvalue', 
sa.Numeric(precision=16,length=2,asdecimal=True)),

However I don't get the trailing zero in my wxPython application, i.e. 
when I debug it I get:

Decimal(26.2) or Decimal(0)

I would have expected:

Decimal(26.20) or Decimal(0.00)

What am I missing?

Appreciate any hints on this
Werner



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: FYI: SQLAutocode 0.4.1 and 0.5 released

[Werner F. Bruhin]

Hi Gerhard,

Gerhard Haering wrote:
 On Tue, 13 Nov 2007 06:00:25 -0800, Simon Pamies [EMAIL PROTECTED] wrote:

   
 [...]
 

   
 I'm very pleased to announce the release of SQLAutocode 0.4.1 and 0.5.
 

   
 This tool enables SQLAlchemy users to automagically generate python
 

   
 code from an existing database layout [...]
 



 Could you perhaps explain the advantages/differences to autoload=True
I understand that autoload=True has a pretty high overhead.  The script 
actually uses autoload=True but then generates a script for your 
model/schema which you import and then use, so you only have the 
overhead once.
  or perhaps SqlSoup?
   
Don't know how SqlSoup works.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Firebird engine - support for embedded version

[Werner F. Bruhin]

I would like to change SA to support the embedded version of Firebird SQL.

I am close but the process does not terminate correctly (i.e. I have to 
kill it), so I am obviously missing something.

I made the following change to firebird.py (SA 0.4.0 final):

def create_connect_args(self, url):
opts = url.translate_connect_args(username='user')
if opts.get('port'):
opts['host'] = %s/%s % (opts['host'], opts['port'])
del opts['port']
opts.update(url.query)
print opts
print opts['host']
if opts['host'] == 'embedded':
del opts['host']
print opts

type_conv = opts.pop('type_conv', self.type_conv)


Then running the following:
import sqlalchemy as sa
import sqlalchemy.orm as sao

import model as db
import utils

database = u'C:\\Dev\\twcb\\Data\\twcb3.fdb'
host = 'embedded'
fileurl = str(database.replace('\\', '/'))
url = 'firebird://USERNAME:[EMAIL PROTECTED]/%s' % (host, fileurl)
dburl = sa.engine.url.make_url(url)

engine = sa.create_engine(dburl, encoding='utf8', echo=False)
Session = sao.sessionmaker(autoflush=True, transactional=True)
Session.configure(bind=engine)
session = Session()

dbItemConsumption = session.query(db.Consumption).load(63)
print dbItemConsumption.consumptionid
print dbItemConsumption.consumedvalue
print dbItemConsumption.updated
session.close()

Gives me the correct output, i.e.:

{'host': 'embedded', 'password': 'pw', 'user': 'USERNAME', 'database': 
'C:/Dev/twcb/Data/twcb3.fdb'}
embedded
{'password': 'pw', 'user': 'USERNAME', 'database': 
'C:/Dev/twcb/Data/twcb3.fdb'}
63
7.5
2007-11-09

However the process hangs and I have to manually kill it.


Can anyone help me pinpoint what else needs to be changed?

If this works correctly would a patch be accepted?

Best regards
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Firebird engine - support for embedded version

[Werner F. Bruhin]

Werner F. Bruhin wrote:
 I would like to change SA to support the embedded version of Firebird SQL.

 I am close but the process does not terminate correctly (i.e. I have to 
 kill it), so I am obviously missing something.
   
Just noticed that I can also use this:

dburl = sa.engine.url.URL('firebird', username='USERNAME', 
password='pw', database=fileurl)

Which means firebird.py does not need to be patched, however I still see 
the same problem that the process hangs.

Best regards
Werner

P.S.
To use the embedded engine one has to install FB files into the 
kinterbasdb folder, I can provide more details or anyone wanting to try 
it can follow the FB embedded install instructions provided in the FB 
release guides.

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Firebird engine - support for embedded version

[Werner F. Bruhin]

Werner F. Bruhin wrote:
 Werner F. Bruhin wrote:
   
 I would like to change SA to support the embedded version of Firebird SQL.

 I am close but the process does not terminate correctly (i.e. I have to 
 kill it), so I am obviously missing something.
   
 
 Just noticed that I can also use this:

 dburl = sa.engine.url.URL('firebird', username='USERNAME', 
 password='pw', database=fileurl)

 Which means firebird.py does not need to be patched, however I still see 
 the same problem that the process hangs.

   
As the version of FB is 2.1beta I wondered if maybe there is an issue 
with it, so I just did a test with kinterbasdb directly (no SA), but 
that works correctly and the program terminates/closes correctly too.

Best regards
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Firebird engine - support for embedded version

[Werner F. Bruhin]

Hi Florent,

Florent Aide wrote:
 On Nov 12, 2007 12:57 PM, Werner F. Bruhin [EMAIL PROTECTED] wrote:
   
 Which means firebird.py does not need to be patched, however I still see
 the same problem that the process hangs.


   
 As the version of FB is 2.1beta I wondered if maybe there is an issue
 with it, so I just did a test with kinterbasdb directly (no SA), but
 that works correctly and the program terminates/closes correctly too.
 

 I use FB + SA 0.4 on a daily basis without problems.
 For this I use kinterbasdb-3.2 in embeded mode on windows with python 2.5.
   
My setup is just about the same.

- Windows Vista
- kinterbasdb 3.2 with a patch from Pavel Cisar to __init__.py to make 
it work with FB 2.1beta
- Python 2.5 (r25:51908, Sep 19 2006, 09:52:17)
 If you need to check anything specific you can ask me I'll look into
 my setup. For me it just works.
   
If I connect to the db with just kinterbasdb I see no problem.

If I use SA 0.4 final I connect without problem but when the script 
finishes it hangs.

How do you build the connection string when you want to connect to an 
embedded engine?  Do you have a sample?

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)

[Werner F. Bruhin]

Mike,

Michael Bayer wrote:
 ...
 oh sorry, I misread the source code in SA earlier...for an INSERT, we  
 are going to insert None for all columns that are blank but dont  
 have a default.  so your two options here are to put another  
 PassiveDefault on the column:

 Column(u'consumedvalue',  
 Numeric(precision=18,length=2,asdecimal=True), PassiveDefault()),
   
That did the trick.

Thanks a lot
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: data inserted by db trigger is not returned when I re-query the row

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 On Nov 6, 2007, at 12:20 PM, Werner F. Bruhin wrote:

   
 I insert a raw into a table and then retrieve again but columns which
 are filled by a db trigger don't return the updated values.

 The following is a code snippet and I wonder what I am missing.

 engine = sa.create_engine(url, encoding='utf8', echo=False)
 Session = sao.sessionmaker(autoflush=True, transactional=True)
 Session.configure(bind=engine)
 session = Session()
 botlot = db.Bottaglot()
 session.save(botlot)
 session.commit()

 print 'org'
 print botlot.bottaglotid
 print botlot.updated

 botlot2 = session.query(db.Bottaglot).get(botlot.bottaglotid)
 print 'reloaded'
 print botlot2.bottaglotid
 print botlot2.updated

 Both columns updated will show None instead of at least for  
 botlot2 it
 should show the current date which was inserted into that column by  
 a db
 trigger.

 

 set a PassiveDefault on the triggered column.  that will indicate to  
 the mapper that it should post-fetch the value after an insert. note  
 that if the trigger is on a primary key column, it wont work since we  
 need primary key values in order to post-fetch.
   
PassiveDefault is great to know.

However I still have a problem with the following.

In a program I do something like this:
botlot3 = session.query(db.Bottaglot).get(39)

Then some other user and/or application changes data (I faked this by 
setting a debugger break point and used the db admin tool to change some 
data and committed it) in the database and commits, then when I do this:
botlot4 = session.query(db.Bottaglot).get(39)

I expected to get the data from the database, however SA gets it from 
the session (I set echo=True) and I don't see a select being done 
between the first statement and the second.

Searching in the documentation I see that it is documented that get is 
NOT querying the database if the key is present, however I haven't found 
how I can do a primary key query so that SA goes to the database. 

I'll keep searching in the doc, but would still appreciate any hints.

Thanks in advance
Werner




 


   


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)

[Werner F. Bruhin]

Mike,

Michael Bayer wrote:
 On Nov 7, 2007, at 12:32 PM, Werner F. Bruhin wrote:

   
 Can you point out what I did wrong, please.

 

 id have to see a larger example, seems like something is marking the  
 column as modified when it should not be.
   
I put a little test case together, which does very little but I get the 
exception.

engine = sa.create_engine(url, encoding='utf8', echo=True)
Session = sao.sessionmaker(autoflush=True, transactional=True)
Session.configure(bind=engine)
session = Session()

dbItemConsumption = db.Consumption()
dbItemConsumption.fk_cbbottleid = 33
session.save(dbItemConsumption)

session.flush
session.commit()

Here the echo output and the exception is:
2007-11-07 18:52:53,438 INFO sqlalchemy.engine.base.Engine.0x..d0 BEGIN
2007-11-07 18:52:53,460 INFO sqlalchemy.engine.base.Engine.0x..d0 SELECT 
gen_id(gen_consumption_consumptionid, 1) FROM rdb$database
2007-11-07 18:52:53,460 INFO sqlalchemy.engine.base.Engine.0x..d0 None
2007-11-07 18:52:53,463 INFO sqlalchemy.engine.base.Engine.0x..d0 INSERT 
INTO consumption (consumptionid, quantity, unitprice, consumedvalue, 
remarks, consumed, maturityfirst, maturitybest, maturitypast, created, 
updated, fk_reasonid, fk_cbbottleid, transferid, fk_cellarid, 
fk_ratingid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2007-11-07 18:52:53,464 INFO sqlalchemy.engine.base.Engine.0x..d0 [58, 
None, None, None, None, None, None, None, None, None, None, None, 33, 
None, None, None]
2007-11-07 18:52:53,466 INFO sqlalchemy.engine.base.Engine.0x..d0 ROLLBACK

Traceback (most recent call last):
  File saTest.py, line 27, in module
session.commit()
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\session.py,
 
line 483, in commit
self.transaction = self.transaction.commit()
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\session.py,
 
line 210, in commit
self.session.flush()
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\session.py,
 
line 681, in flush
self.uow.flush(self, objects)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 216, in flush
flush_context.execute()
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 432, in execute
UOWExecutor().execute(self, head)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1051, in execute
self.execute_save_steps(trans, task)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1070, in execute_save_steps
self.execute_childtasks(trans, task, False)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1088, in execute_childtasks
self.execute(trans, child, isdelete)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1051, in execute
self.execute_save_steps(trans, task)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1070, in execute_save_steps
self.execute_childtasks(trans, task, False)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1088, in execute_childtasks
self.execute(trans, child, isdelete)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1051, in execute
self.execute_save_steps(trans, task)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1070, in execute_save_steps
self.execute_childtasks(trans, task, False)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1088, in execute_childtasks
self.execute(trans, child, isdelete)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1051, in execute
self.execute_save_steps(trans, task)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1070, in execute_save_steps
self.execute_childtasks(trans, task, False)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1088, in execute_childtasks
self.execute(trans, child, isdelete)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1051, in execute
self.execute_save_steps(trans, task)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1070, in execute_save_steps
self.execute_childtasks(trans, task, False)
  File 
c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py,
 
line 1088, in execute_childtasks
self.execute(trans, child, isdelete)
  File 
c:\python25\lib\site-packages\SQLAlchemy

[sqlalchemy] Re: data inserted by db trigger is not returned when I re-query the row - solved

[Werner F. Bruhin]

Werner F. Bruhin wrote:
 Michael,

 Michael Bayer wrote:
   
 On Nov 6, 2007, at 12:20 PM, Werner F. Bruhin wrote:

   
 
 I insert a raw into a table and then retrieve again but columns which
 are filled by a db trigger don't return the updated values.

 The following is a code snippet and I wonder what I am missing.

 engine = sa.create_engine(url, encoding='utf8', echo=False)
 Session = sao.sessionmaker(autoflush=True, transactional=True)
 Session.configure(bind=engine)
 session = Session()
 botlot = db.Bottaglot()
 session.save(botlot)
 session.commit()

 print 'org'
 print botlot.bottaglotid
 print botlot.updated

 botlot2 = session.query(db.Bottaglot).get(botlot.bottaglotid)
 print 'reloaded'
 print botlot2.bottaglotid
 print botlot2.updated

 Both columns updated will show None instead of at least for  
 botlot2 it
 should show the current date which was inserted into that column by  
 a db
 trigger.

 
   
 set a PassiveDefault on the triggered column.  that will indicate to  
 the mapper that it should post-fetch the value after an insert. note  
 that if the trigger is on a primary key column, it wont work since we  
 need primary key values in order to post-fetch.
   
 
 PassiveDefault is great to know.

 However I still have a problem with the following.

 In a program I do something like this:
 botlot3 = session.query(db.Bottaglot).get(39)

 Then some other user and/or application changes data (I faked this by 
 setting a debugger break point and used the db admin tool to change some 
 data and committed it) in the database and commits, then when I do this:
 botlot4 = session.query(db.Bottaglot).get(39)
   
Instead of using .get( I ended up using refresh, i.e. something like 
this:

session.refresh(botlot3)

Which re-queried the database and gave the values of the columns which 
were completed by the db trigger.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)

[Werner F. Bruhin]

Mike,

Michael Bayer wrote:
 On Nov 7, 2007, at 10:23 AM, Werner F. Bruhin wrote:

   
 How can I define a column in the table as read-only?

 I have some computed by columns, e.g.:
 consumedvalue  computed by (quantity*unitprice)

 These columns can not be updated, otherwise I get the following  
 exception:
 ProgrammingError: (ProgrammingError) (-151, 'isc_dsql_prepare: \n
 attempted update of read-only column')

 

 read-only behavior is a class-level thing, so something like this:

 class MyClass(object):
  def mycol(self):
  return self._mycol
  mycol = property(mycol)

 mapper(MyClass, mytable, properties={
 '_mycol':mytable.c.mycol,
 'mycol':synonym('_mycol')
 })


 Theres a ticket in trac which will make the above configuration  
 slightly less verbose in a future release.
   
I don't have many of these, so no big deal that it is verbose.

However I must not do something wrong in transposing the above as I 
still get the error.

I must be a bit dense on this, here is what I have done:

consumption_table = sa.Table(u'consumption', metadata,
sa.Column(u'consumptionid', sa.Integer(), 
sa.Sequence('gen_consumption_consumptionid'), primary_key=True, 
nullable=False),
sa.Column(u'quantity', sa.Integer()),
sa.Column(u'unitprice', 
sa.Numeric(precision=18,length=2,asdecimal=True)),
sa.Column(u'consumedvalue', 
sa.Numeric(precision=18,length=2,asdecimal=True)),

)

class Consumption(object):
def consumedvalue(self):
return self._consumedvalue
consumedvalue = property(consumedvalue)
pass

consumption = sao.mapper(Consumption, consumption_table,
properties={
...
'cellar': sao.relation(Cellar, backref='consumption'),
'_consumedvalue': consumption_table.c.consumedvalue,
'consumedvalue': sao.synonym('_consumedvalue'),
})

Can you point out what I did wrong, please.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] How to define a column as read-only (e.g. computed by column in Firebird)

[Werner F. Bruhin]

How can I define a column in the table as read-only?

I have some computed by columns, e.g.:
consumedvalue  computed by (quantity*unitprice)

These columns can not be updated, otherwise I get the following exception:
ProgrammingError: (ProgrammingError) (-151, 'isc_dsql_prepare: \n  
attempted update of read-only column')

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: data inserted by db trigger is not returned when I re-query the row

[Werner F. Bruhin]

Mike,

Michael Bayer wrote:
 On Nov 7, 2007, at 5:19 AM, Werner F. Bruhin wrote:

   
 PassiveDefault is great to know.

 However I still have a problem with the following.

 In a program I do something like this:
 botlot3 = session.query(db.Bottaglot).get(39)

 Then some other user and/or application changes data (I faked this by
 setting a debugger break point and used the db admin tool to change  
 some
 data and committed it) in the database and commits, then when I do  
 this:
 botlot4 = session.query(db.Bottaglot).get(39)

 I expected to get the data from the database, however SA gets it from
 the session (I set echo=True) and I don't see a select being done
 between the first statement and the second.

 Searching in the documentation I see that it is documented that  
 get is
 NOT querying the database if the key is present, however I haven't  
 found
 how I can do a primary key query so that SA goes to the database.

 I'll keep searching in the doc, but would still appreciate any hints.

 

 Werner -

 theres three approaches which can be used individually or together for  
 this type of thing.

 1. When running the session within a transaction, either using  
 session.begin() or creating your session with transactional=True, you  
 let the database's normal transactional behavior handle transaction  
 isolation issues (reading one value, making a change based on that  
 value and commiting, but then someone else changed in the middle, is a  
 transaction isolation issue).  I would recommend running within a  
 transaction at the very least.

 2. If youd like to explicitly place a lock on the row, you can say  
 session.query(Foo).with_lockmode('update').get(39).  This will use a  
 SELECT..FOR UPDATE in order to fetch the row and will then explicitly  
 lock the row against concurrent access, until the next UPDATE occurs  
 or the transaction is completed/rolled back.  This is known as  
 pessimistic locking, since it assumes that a contention issue will  
 occur and prevents against it from happening. SELECT..FOR UPDATE  
 should be run in a transaction.

 3. Alternatively, optimistic locking can be used by setting the  
 version_id_col option on your mapper()  (i.e. mapper(,  
 version_id_col=mytable.c.version_col) );  you add an integer valued  
 column to your table which you reference via this setting.  The mapper  
 will place increasing numbers within the column upon each change to  
 the row.  When a flush() occurs, the mapper updates the row based not  
 only on the primary key columns but also on the expected version  
 number, and if no row was located in the update, it throws a  
 ConcurrencyError.   this is known as optimistic since it doesn't  
 actually prevent the contention issue from happening, but instead when  
 it does happen knows enough to abort the transaction.

 The Query can also fetch data from the database without using the  
 cache - if you say query.load(39) it will fetch the row from the  
 database unconditionally and re-populate the existing object if  
 already present, but note that this removes any changes you've made to  
 the object.  However, this is not much of a guarantee of anything,  
 since if you load() the object, make changes, then flush, theres still  
 the possibility that the concurrent changes occured in between the  
 load/flush steps.   So the pessimistic/optimistic locking approaches  
 are better solutions if concurrency issues are expected.

 For slower concurrency issues, such as you arent concerned about  
 concurrency within a small period of time and are instead concerned  
 about two users changing some data five minutes apart, I would note  
 that an individual Session is typically meant for a single set of  
 operations, then its closed.  Holding onto a Session for a long time,  
 across web requests, etc., is not really its primary usage model.  Its  
 more like something you check out, do some things with it, then  
 check it back in.

   
Thanks for the detailed response.  As mentioned earlier I found 
refresh which did the trick for this problem, but the query.load is 
really what I was looking for.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] data inserted by db trigger is not returned when I re-query the row

[Werner F. Bruhin]

I insert a raw into a table and then retrieve again but columns which 
are filled by a db trigger don't return the updated values.

The following is a code snippet and I wonder what I am missing.

engine = sa.create_engine(url, encoding='utf8', echo=False)
Session = sao.sessionmaker(autoflush=True, transactional=True)
Session.configure(bind=engine)
session = Session()
botlot = db.Bottaglot()
session.save(botlot)
session.commit()

print 'org'
print botlot.bottaglotid
print botlot.updated

botlot2 = session.query(db.Bottaglot).get(botlot.bottaglotid)
print 'reloaded'
print botlot2.bottaglotid
print botlot2.updated

Both columns updated will show None instead of at least for botlot2 it 
should show the current date which was inserted into that column by a db 
trigger.

If then run this:
engine = sa.create_engine(url, encoding='utf8', echo=False)
Session = sao.sessionmaker(autoflush=True, transactional=True)
Session.configure(bind=engine)
session = Session()
botlot3 = session.query(db.Bottaglot).get(39)
print 'reloaded'
print botlot3.bottaglotid
print botlot3.updated

At this point I get the value for the column updated I expected.

What am I missing?

Werner

P.S.
This is with SA 0.4 final and Firebird SQL 2.1beta

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Error determining primary and/or secondary join for relationship

[Werner F. Bruhin]

Werner F. Bruhin wrote:
...
My problem was that I had two foreign key columns which related to the 
same table.

Initially I didn't see that hint in the exception message.

So changing my mapper to include a primaryjoin as follows solved the 
problem.

preferences = sao.mapper(Preferences, preferences_table,
properties={
'language': sao.relation(Language, backref='preferences'),
'imagetype_ls': sao.relation(Imagetype_ls, primaryjoin=

(preferences_table.c.fk_imagetypeid==Imagetype_ls.c.imagetypeid)),
'imagetype_ls2': sao.relation(Imagetype_ls, primaryjoin=

(preferences_table.c.rec_fk_imagetypeid==Imagetype_ls.c.imagetypeid)),
})

Werner

 


   


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Error determining primary and/or secondary join for relationship

[Werner F. Bruhin]

I am getting the following exception, but I can't figure out what I 
specified incorrectly.  I have other relations setup which work fine.  
Appreciate if someone can put me right.

File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\properties.py, 
line 402, in _determine_joins
raise exceptions.ArgumentError(Error determining primary and/or 
secondary join for relationship '%s'. If the underlying error cannot be 
corrected, you should specify the 'primaryjoin' (and 'secondaryjoin', if 
there is an association table )

When I do this:
print session.query(model.Preferences).get(1)

The relevant part (I think) of the model is the following:
imagetype_ls_table = sa.Table(u'imagetype_ls', metadata,
sa.Column(u'id', sa.Integer()),
sa.Column(u'imagetypeid', sa.Integer(), primary_key=True, 
nullable=False),
sa.Column(u'name', sa.String(length=30, convert_unicode=False)),
sa.Column(u'shortname', sa.String(length=10, convert_unicode=False)),
sa.Column(u'created', sa.Date()),
sa.Column(u'updated', sa.Date()),
sa.Column(u'fk_langid', sa.Integer()),
sa.Column(u'centralkey', sa.Integer()),
)

class Imagetype_Ls(OrmObject):
pass

preferences_table = sa.Table(u'preferences', metadata,
sa.Column(u'prefid', sa.Integer(), primary_key=True, nullable=False),
...
sa.Column(u'fk_langid', sa.Integer(), 
sa.ForeignKey(u'language.langid'), nullable=False),
...
sa.Column(u'fk_imagetypeid', sa.Integer(), 
sa.ForeignKey(u'imagetype_ls.imagetypeid')),
sa.Column(u'rec_fk_imagetypeid', sa.Integer(), 
sa.ForeignKey(u'imagetype_ls.imagetypeid')),
...
sa.Column(u'bottletagsassign', sa.String(length=1, 
convert_unicode=False)),
)

class Preferences(OrmObject):
pass

preferences = sao.mapper(Preferences, preferences_table,
properties={
'language': sao.relation(Language),
'imagetype_ls': sao.relation(Imagetype_Ls)
})

Thanks in advance
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: SQLalchemy coding style

[Werner F. Bruhin]

McA wrote:
 Hi all,

 I'm intersted in using sqlalchemy and started to read the manuals.
 I didn't find a hint for my question, so I'm asking here. I hope it's
 not too annoying.

 Most code examples in the documentation use something like this
   
 from sqlalchemy. import 
   

   
I am just experimenting with sqlalchemy, but I am pretty sure to start 
using it soon, I wondered about this too, to me it was confusing to look 
at code and not easily able to see where things come from.

Having seen the wxPython project move to use the namespace as of 2.6 
(with I think two transition releases), I have done all the tests so far 
doing the following type of import and corresponding code.  This has not 
caused any problems so far and to while a little bit more typing makes 
the code more readable, especially for newbies.

But I wonder why this is not done by everyone, i.e. what are the reasons 
not to do it this way?

import sqlalchemy as sa
import sqlalchemy.orm as sao

metadata = sa.MetaData()

cbbottle_table = sa.Table( u'cbbottle', metadata,
sa.Column( u'cbbottleid', sa.Integer(), 
sa.Sequence('gen_cbbottle_cbbottleid'),
primary_key= True, nullable= False),
sa.Column( u'maturityfirst', sa.Integer()),
sa.Column( u'maturitybest', sa.Integer()),
sa.Column( u'maturitypast', sa.Integer()),
...
sa.Column( u'fk_cbvintageid', sa.Integer(), 
sa.ForeignKey(u'cbvintage.cbvintageid')),
)

Werner
 My question is: Is this the good/proper way to import the sqlalchemy
 stuff.
 I'm concerned about polluting the current namespace. I could assume
 that
 class names like 'Table' are too common to reserve them for the
 sqlalchemy
 classes.

 What would you recommend? How are the gurus out there using
 sqlalchemy?

 Thanks in advance.

 Best regards
 Andreas Mock


 


   


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Connecting to a Microsoft Access Database

[Werner F. Bruhin]

Eddie,

Eddie wrote:
 Thank your Paul (and Expo!)

 Just curious but where would I look to find out that I needed to use
 access instead of mssql?
   
You might want to check the documentation:
http://www.sqlalchemy.org/docs/04

More specifically:
http://www.sqlalchemy.org/docs/04/documentation.html#dbengine

And then there are some notes per engine here (check the end of this 
page for Acess):
http://www.sqlalchemy.org/trac/wiki/DatabaseNotes

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: FYI: AutoCode moved to a new repository

[Werner F. Bruhin]

Simon,

Simon Pamies wrote:
 Hi,

 although I said I would start on Friday if there are no objections, I
 couldn't longer resist to revamp autocode and so I moved it to google
 code.

 Please have a look at http://code.google.com/p/sqlautocode/ for the
 changes and the current structure.

 I also removed some contents from 
 http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode
 to reflect the move.

 I performed a release under LGPL. Hope that this is ok and fits into
 the sqlalchemy environment.

 If s/o has objections and/or wants to add information/data/code feel
 free to contact me.

 Simon Pamies
   
I had changed one of the other versions to handle Firebird and got it to 
work for my purposes, but did some hacks which were not for public 
consumption.

If you or someone else can help me working the hacks out then maybe 
Firebird could be supported by this new autocode.

I did the test with sqlalchemy 0.4beta6, which seems to require some 
changes to autocode.

Traceback 1:
Traceback (most recent call last):
  File autocode.py, line 140, in module
c.type = autoloader.coltypes[ c.type.__class__ ]()
KeyError: class 'sqlalchemy.types.Integer'

I don't know how to fix the above.

Traceback 2:
Traceback (most recent call last):
  File autocode.py, line 61, in module
dburl = engine.url.make_url(url)
NameError: name 'engine' is not defined

Just commenting the line dburl = engine.url.make_url(url) is fine as 
the dburl is not used.

Traceback 3:
Traceback (most recent call last):
  File autocode.py, line 71, in module
metadata = BoundMetaData(db)
NameError: name 'BoundMetaData' is not defined

Just change:
metadata = BoundMetaData(db)
to:
metadata = MetaData(db)

Traceback 4:
Traceback (most recent call last):
  File formatter.py, line 75, in module
sql._TextClause.__repr__ = textclause_repr
NameError: name 'sql' is not defined

I don't know how to correct this, but as FB doesn't use schema I did get 
around it by just commenting the block Monkey patching sqlalchemy repr 
functions

Question for FB:
What should be returned to the variable sqltext for indexes in the 
following code?

for name,tbl_name,sqltext in db.execute( me.sql4indexes):

The output for a table then looks like this:

I18N_CURRLANG   
  
= 
Table('I18N_CURRLANG

', 
MetaData(Engine(firebird://SYSDBA:[EMAIL 
PROTECTED]/c:/dev/twcb/data/i18n.fdb)), 
Column(u'id', Integer(), primary_key=True, nullable=False), 
Column(u'user_name', String(length=80,convert_unicode=False)), 
Column(u'lang', String(length=20,convert_unicode=False), 
nullable=False), schema=None)

It would be nice to add maybe a pretty print option so it would look 
more like this:

I18N_CURRLANG  = Table('I18N_CURRLANG',
  
MetaData(Engine(firebird://USER:[EMAIL PROTECTED]/c:/dev/twcb/data/i18n.fdb)),
  Column(u'id', Integer(), primary_key=True, nullable=False),
  Column(u'user_name', 
String(length=80,convert_unicode=False)),
  Column(u'lang', String(length=20,convert_unicode=False), 
nullable=False), schema=None)

Another option would be nice to replace:
  
MetaData(Engine(firebird://USER:[EMAIL PROTECTED]/c:/dev/twcb/data/i18n.fdb)),

with just:
  metadata

I.e. just the variable for metadata, so that one can assign an engine at 
run time.

Another thing is the table names, but I believe this is an FB issue, 
they should be .strip()'ed and I also do a .lower() on them, so they 
look nicer (in my tests this does not cause any problems).

When these things are worked out I'll send you the little bit of code 
need in loader.py for FB.

Best regards
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: engine.execute

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:
 On Oct 10, 2007, at 5:56 AM, Werner F. Bruhin wrote:

   
 Looking at the doc for 0.4 I see that I should be able to do this:

 result = engine.execute(select username from users)
 ... do something with the result
 result.close()

 But I am getting the following exception.  Note that I am using  
 Firebird
 SQL and the firebird.py is the one patched by Roger.

 Am I doing something wrong or is this not supported in 0.4?

 BTW, I would like to use this within a custom type (class
 MyType(types.TypeDecorator): to do the I18N translation similar to  
 what
 Karsten suggested in another thread.

 

 it looks like you are trying to execute SQL inside of a TypeEngine's  
 convert_result_value() method.  the argument passed to that is  
 currently a Dialect, not an engine (also, issuing SQL inside of  
 convert_result_value(), not such a great idea in general...)
   
Yes, I was calling a Stored Procedure to get a translation for the 
original value of that column.

Could you give me some hints on how I could do the following in SA.

- define a column as I18N (actual value is a varchar)
- when the column is read take the value from the table column, do a 
lookup with it in the i18n translation table with a stored procedure and 
either return what was found or the original value
- the column should ideally be read only
- to be able to maintain the column I was thinking of setting up a 
second model for the same table where the i18n column would be defined 
as varchar to allow to set the value to be translated

I would really appreciate if you could push me in the right direction on 
this.

Thanks and best regards
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: engine.execute

[Werner F. Bruhin]

Michael,

Michael Bayer wrote:

First of all, thanks for this response.
 i'd embed the SP call explicitly in the select():

 select([table.c.id, table.c.foo, table.c.bar, func.convert_I18n 
 (table.c.data).label('data')]).where(...)

 if using the ORM, set up the column explicitly:

 mapper(SomeClass, sometable, properties={
   'data':column_property(func.convert_I18n(table.c.data).label('data'))
 })
   
I want to use the ORM.

My first go at this did not work, it might have to do with 
FB/kinterbasdb as it expects the stored procedures to be called in one 
of these ways.

cur.execute(select output1, output2 from the_proc(?, ?), (input1, input2))

cur.callproc(the_proc, (input1, input2))

I'll spend some more time on this tomorrow.
 this way you dont add any statement overhead to the type conversion  
 (embedding in the TypeEngine means, number of I18N columns * total  
 number of rows number of SQL executions).

 If I were doing this I also might use TypeEngine for the conversion  
 but I'd move the I18N function into Python.
   
Pity that the above is not reading When I am going to .. :-) .

Best regards
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] engine.execute

[Werner F. Bruhin]

Looking at the doc for 0.4 I see that I should be able to do this:

result = engine.execute(select username from users)
... do something with the result
result.close()

But I am getting the following exception.  Note that I am using Firebird 
SQL and the firebird.py is the one patched by Roger.

Am I doing something wrong or is this not supported in 0.4?

BTW, I would like to use this within a custom type (class 
MyType(types.TypeDecorator): to do the I18N translation similar to what 
Karsten suggested in another thread.

Best regards
Werner

Traceback (most recent call last):
  File sai18Test.py, line 15, in module
for entry in session.query(model.Test_i18n):
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\query.py, line 
619, in __iter__
return self._execute_and_instances(context)
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\query.py, line 
624, in _execute_and_instances
return iter(self.instances(result, querycontext=querycontext))
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\query.py, line 
680, in instances
self.select_mapper._instance(context, row, result)
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\mapper.py, 
line 1436, in _instance
self.populate_instance(context, instance, row, **flags)
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\mapper.py, 
line 1517, in populate_instance
p(instance, row, ispostselect=ispostselect, isnew=isnew, **flags)
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\strategies.py, 
line 77, in new_execute
instance.__dict__[self.key] = row[self.columns[0]]
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\engine\base.py, 
line 1580, in __getitem__
return self.__parent._get_col(self.__row, key)
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\engine\base.py, 
line 1386, in _get_col
return rec[1](row[rec[2]])
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\types.py, line 46, 
in process
return self.convert_result_value(value, dialect)
  File C:\Dev\BoaTest04\sqlalchemy\modelTest.py, line 16, in 
convert_result_value
result = engine.execute(select output1 from t(?, ?), ('some new 
text here ddd', 'wbruhin'))
AttributeError: 'FBDialect' object has no attribute 'execute'


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] i10n of data

[Werner F. Bruhin]

Hi,

I wonder if there is a standard in how to deal with i10n data in tables 
with SA, I have search google and the documentation but without any success.

For example one has a table for countries were at least the name has to 
be translated from e.g. Germany to Deutschland etc., with a Default 
Language if an entry is not found for a particular translation.

My shareware app is currently supporting this but the current 
implementation is single user (won't bore you with how I am doing it), 
what I would like a multi user solution for this.

I wonder if there is some way of defining this with SA.orm, i.e. have 
the following two tables and then some magic definition for the 
country table to get the appropriate translation from the country_l 
table based on the user selected language and a default language if not 
translation is found.

country_table = sa.Table( u'country', metadata,
sa.Column( u'countryid', sa.Integer(), 
sa.Sequence('gen_country_countryid'), primary_key=True, nullable=False),
sa.Column( u'dialcode', sa.String(length=10, 
convert_unicode=False)),
sa.Column( u'is2code', sa.String(length=2, 
convert_unicode=False)),
sa.Column( u'un3code', sa.String(length=3, 
convert_unicode=False)),
sa.Column( u'website1', sa.String(length=150, 
convert_unicode=False)),
sa.Column( u'website2', sa.String(length=150, 
convert_unicode=False)),
sa.Column( u'created', sa.Date()),
sa.Column( u'updated', sa.Date())
)

country_l_table = sa.Table( u'country_l', metadata,
sa.Column( u'id', sa.Integer(), 
sa.Sequence('gen_country_l_id'), primary_key= True, nullable= False),
sa.Column( u'fk_countryid', sa.Integer(), 
sa.ForeignKey(u'country.countryid'), nullable= False),
sa.Column( u'fk_langid', sa.Integer(),  
sa.ForeignKey(u'language.langid'), nullable= False),
sa.Column( u'name', sa.String(length=50, 
convert_unicode=False), nullable= False),
sa.Column( u'created', sa.Date()),
sa.Column( u'updated', sa.Date())
)

Any hints or tips on how to do something like this will be very welcome.
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Database views

[Werner F. Bruhin]

In my existing Firebird database I have some views defined.  In the 
model I thought I could just go ahead and define them basically like 
tables but I get an exception:

Traceback (most recent call last):
  File saTest.py, line 4, in module
import modelTest as model
  File C:\Dev\BoaTest04\sqlalchemy\modelTest.py, line 237, in module
vcbook = sao.mapper(Vcbook, vcbook_table)
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\__init__.py, 
line 516, in mapper
return Mapper(class_, local_table, *args, **params)
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\mapper.py, 
line 152, in __init__
self._compile_tables()
  File c:\downloaded 
software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\mapper.py, 
line 414, in _compile_tables
raise exceptions.ArgumentError(Could not assemble any primary key 
columns for mapped table '%s' % (self.mapped_table.name))
sqlalchemy.exceptions.ArgumentError: Could not assemble any primary key 
columns for mapped table 'vcbook'

In my case the views are always used in read-only mode, changes are made 
to the actual table.

|Is there someway I can define these views.  I was able to work around 
the above exception by defining a primary key column or columns 
(compound key) but I have one or two views which don't really have a 
primary key.

Appreciate any hints/tips.
Werner
|

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Database views

[Werner F. Bruhin]

Kevin,

Kevin Cole wrote:
 Hi,

 I had the same question here last week (although I was using
 PostgreSQL instead of Firebird).
   
Sorry for the noise then, I should have checked the on-line archive, 
only subscribed a while here, so my local archive didn't show anything.
 Does Firebird offer a sequence/serial/auto-number data type?  If so,
 you could just add that to your views.
Yes it has, in older version it was called a generator, but as of 2.0 (I 
think) and can use gen_id or NEXT VALUE FOR sequence.
   In other words, just have the
 views artificially generate a unique number to satisfy SQLAlchemy,
 Elixir, TurboGears, etc.  Then make sure you never really use that key
 for anything.  (Others vetoed the idea because they were concerned
 that at some future date I might want to update something... But, if
 like me, you're fairly confident of the read-only nature, I think it
 would be fine.)
   
I'll consider this for the views where I don't have a unique key and/or 
can't define a composite key with what I already have in there.

Thanks
Werner

 On 10/5/07, Werner F. Bruhin [EMAIL PROTECTED] wrote:
   
 In my existing Firebird database I have some views defined.  In the
 model I thought I could just go ahead and define them basically like
 tables but I get an exception:

 In my case the views are always used in read-only mode, changes are made
 to the actual table.

 |Is there someway I can define these views.  I was able to work around
 the above exception by defining a primary key column or columns
 (compound key) but I have one or two views which don't really have a
 primary key.

 Appreciate any hints/tips.
 

   


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] blob column - how to define in model

[Werner F. Bruhin]

I have just started to work with sqlalchemy and there is one thing I 
can't figure out.

I am using Firebird SQL 2.0 and kinterbasdb 3.2, and I have some blob 
columns defined as:

NOTES   MEMO /* MEMO = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,

CREATE DOMAIN MEMO AS
BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET ISO8859_1;

The call to create_engine includes  encoding='utf-8', but any blob 
column is returned as a string instead of Unicode.

varchar columns are returned as Unicode.

I am working on an existing database, so I adapted the script from on 
this page http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode and 
it generated the model for the notes column as sa.Column( u'notes', 
sa.TEXT(length=None,convert_unicode=False)).

So, first I thought it had to do with the convert_unicode param but the 
same is used on the varchar fields which work fine for me.

Can anyone point out what I am doing wrong.
Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Firebird - column types

[Werner F. Bruhin]

Scanning firebird.py I noticed that:

 FBText returns BLOB SUB_TYPE 2

Shouldn't that be BLOB SUB_TYPE 1 or BLOB SUB_TYPE TEXT

and FBBinary returns BLOB SUB_TYPE 1

Shouldn't that be BLOB SUB_TYPE 0

See Helen's FB Book on page 182 and/or page 78 of the IB 6 Data 
Definition Guide (DataDef.pdf).

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: blob column - how to define in model

[Werner F. Bruhin]

Michael Bayer wrote:
 On Oct 4, 2007, at 5:07 AM, Werner F. Bruhin wrote:

   
 I have just started to work with sqlalchemy and there is one thing I
 can't figure out.

 I am using Firebird SQL 2.0 and kinterbasdb 3.2, and I have some blob
 columns defined as:

 NOTES   MEMO /* MEMO = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,

 CREATE DOMAIN MEMO AS
 BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET ISO8859_1;

 The call to create_engine includes  encoding='utf-8', but any blob
 column is returned as a string instead of Unicode.

 varchar columns are returned as Unicode.

 I am working on an existing database, so I adapted the script from on
 this page http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode  
 and
 it generated the model for the notes column as sa.Column( u'notes',
 sa.TEXT(length=None,convert_unicode=False)).

 So, first I thought it had to do with the convert_unicode param but  
 the
 same is used on the varchar fields which work fine for me.

 Can anyone point out what I am doing wrong.
 

 you should have the convert_unicode=True param set on the TEXT  
 column.  that will apply the engine's encoding of utf-8 to the  
 encoded strings returned from the database.  if you have VARCHAR  
 fields which dont have this flag set, but they are still coming back  
 as unicode, then its probably a product of kinterbasdb doing it (i  
 dont use firebird over here).
   
I have done some more research on this.  kinterbasdb the way it is 
configured by default in firebird.py (type_conv=200) will do the 
conversion from any encoding used within the database to the encoding 
defined on the connection.  I guess this is why it works for the varchar 
and char fields.  There is some stuff implemented in kinterbasdb to do 
the same for blob fields but due to some FB limitation it is currently 
not activated by default in the Dynamic Type Translation.

See the thread:
http://sourceforge.net/forum/forum.php?thread_id=1299756forum_id=30917

I wonder if this stuff could be used?  I am willing to give it a try but 
don't know how to access the kinterbasdb.connection when using sqlalchemy.

def blobInputDTT(x): 
if isinstance(x, unicode): 
return x.encode('UTF-8') 
else: 
return x 
 
kinterbasdb.connection.set_type_trans_in({'BLOB': blobInputDTT})
kinterbasdb.connection.set_type_trans_out({'BLOB': {'mode': 
'materialize', 'treat_subtype_text_as_text': True}})

Could this be done on the engine instance?

Werner



 


   


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---