[sqlalchemy] Re: SQL to sqlalchemy help
Thanks it worked.
I am fairly new to SQLalchemy and this will be my first code. i was
wondering can i map the tables using a mapper to a class and then
somehow use python swap function to swap values ??
On Mar 1, 6:51 pm, Michael Bayer wrote:
> On Mar 1, 2011, at 9:14 PM, eddy wrote:
>
> > Hi All,
>
> > I have been trying to covert this sql query to sqlalchemy one for
> > hours now with no luck. any help will be appreciated
>
> > "update table1 set columnValue=(case when columnValue=A then B when
> > columnValue=B then A end) where columnValue in (A,B);"
>
> > It is just swapping the columnValue where its A it set B and where
> > its B it set A
>
> update uses values() for the SET part
> (docs:http://www.sqlalchemy.org/docs/core/tutorial.html#inserts-and-updates),
> case is a little weird it takes a list of when/then pairs (docs +
> exampleshttp://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cas...)
>
> from sqlalchemy.sql import table, column, case
>
> table1 = table('t1', column('columnValue'))
>
> print table1.update().\
> where(table1.c.columnValue.in_(['A', 'B'])).\
> values(columnValue=case([
> (table1.c.columnValue=='A', 'B'),
> (table1.c.columnValue=='B', 'A')
> ]
> ))
>
> > I tried
> > query=update([table1.c.columnValue],case([table1.c.columnValue==A, B],
> > [table1.c.columnValue==B, A]), where(table1.c.columnValue==A or
> > table1.c.columnValue==B))
>
> > ???
>
> > --
> > 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
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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.
Re: [sqlalchemy] Selecting columns with dots in their names
thats a behavior there for the purposes of SQLite that's been removed and made specific to the SQLite dialect in 0.7. On Mar 2, 2011, at 6:18 PM, Stefan Urbanek wrote: > Hi, > > I have a table with column names that contain dots, like > "category.name" or "category.desc". When I do: > >stmt = table.select(whereclause = condition) >cursor = connection.execute(stmt) >print cursor.keys() > > I will get just [ ... "name", "desc" ] - nothing before the dot > '.'. > > When I try: > >row = cursor.fetchone() >for (key, value) in row.items(): >... > > It fails with an exception on the 'for' statement, that there are > duplicate column names - like "name" or "desc". There are not > duplicates, because they are called "something.name" and > "otherthing.name". > > Is there any option to be passed to the select() to return full column > names? > > Regards, > > Stefan > > -- > 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. > -- 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: Selecting columns with dots in their names
I apologize for replying to my own post, just found out that it works as expected with the latest fresh release 0.7b2 -downloaded and installed manually. it does not work with the version installed by easy_install, i think it was 0.6.6. On Mar 3, 12:18 am, Stefan Urbanek wrote: > Hi, > > I have a table with column names that contain dots, like > "category.name" or "category.desc". When I do: > > stmt = table.select(whereclause = condition) > cursor = connection.execute(stmt) > print cursor.keys() > > I will get just [ ... "name", "desc" ] - nothing before the dot > '.'. > > When I try: > > row = cursor.fetchone() > for (key, value) in row.items(): > ... > > It fails with an exception on the 'for' statement, that there are > duplicate column names - like "name" or "desc". There are not > duplicates, because they are called "something.name" and > "otherthing.name". > > Is there any option to be passed to the select() to return full column > names? > > Regards, > > Stefan -- 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] Selecting columns with dots in their names
Hi, I have a table with column names that contain dots, like "category.name" or "category.desc". When I do: stmt = table.select(whereclause = condition) cursor = connection.execute(stmt) print cursor.keys() I will get just [ ... "name", "desc" ] - nothing before the dot '.'. When I try: row = cursor.fetchone() for (key, value) in row.items(): ... It fails with an exception on the 'for' statement, that there are duplicate column names - like "name" or "desc". There are not duplicates, because they are called "something.name" and "otherthing.name". Is there any option to be passed to the select() to return full column names? Regards, Stefan -- 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.
Re: [sqlalchemy] support for SEQUENCE keyword in firebird
On Mar 2, 2011, at 5:32 PM, Michael Bayer wrote: > > On Mar 2, 2011, at 5:13 PM, Michael Bayer wrote: > >> Sequence() is a DDL construct, not a SQL construct, so you don't embed it >> into an INSERT statement directly, nor can you assign it to an attribute on >> a mapped object (as would appear to be the case here). It is a marker >> applied to a Column() object to note the column's generator for default >> values. >> >> The usage of Sequence in table metadata is described at: >> >> http://www.sqlalchemy.org/docs/core/schema.html#defining-sequences >> >> When using the ORM in particular, the Sequence needs to be used in this way >> so that SQLAlchemy has a means of acquiring the newly generated identifier. >> On Firebird, when the Table metadata has a Sequence associated, the gen_id() >> function is embedded inline and the new identifier is acquired via >> RETURNING. This applies to all insert() constructs generated against the >> Table object, not just those used by the ORM. >> >> Another option is to explicitly execute the sequence object ahead of time - >> a Sequence can be passed to the execute() method of any >> engine/connection/Session to invoke its next id.This is not the best >> pattern in conjunction with inserts: >> >> myobject.id = Session.execute(my_sequence_object) > > continuing, not the best pattern because, its two separate executions instead > of one and is more verbose in any case. and, I'd add we can make it actually work the way you thought it did, by letting you say myobject.id = sequence.nextval(), we will see if we can get that into 0.7.0. > > > > >> >> >> >> >> >> On Mar 2, 2011, at 3:10 PM, bigt wrote: >> >>> my Firebird database has some Sequence values defined as >>> >>> CREATE SEQUENCE S_org; >>> >>> with an appropriate trigger to set a value. >>> >>> RECREATE TRIGGER TBI_org FOR org >>> ACTIVE BEFORE INSERT POSITION 0 >>> AS BEGIN >>> IF (NEW.Id IS NULL) THEN >>>NEW.Id = NEXT VALUE FOR S_org; >>> END^ >>> >>> >>> Using reflection in sqlalchemy i get an error when trying to store an >>> entry in the ORG table. >>> >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", >>> line 1392, in flush >>> self._flush(objects) >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", >>> line 1473, in _flush >>> flush_context.execute() >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ >>> unitofwork.py", line 302, in execute >>> rec.execute(self) >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ >>> unitofwork.py", line 446, in execute >>> uow >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", >>> line 1884, in _save_obj >>> execute(statement, params) >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> line 1191, in execute >>> params) >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> line 1271, in _execute_clauseelement >>> return self.__execute_context(context) >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> line 1302, in __execute_context >>> context.parameters[0], context=context) >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> line 1401, in _cursor_execute >>> context) >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> line 1394, in _cursor_execute >>> context) >>> File "/usr/lib/python2.7/site-packages/sqlalchemy/dialects/firebird/ >>> base.py", line 680, in do_execute >>> cursor.execute(statement, parameters or []) >>> InterfaceError: (InterfaceError) (0L, "Error while attempting to >>> convert object of type to >>> database-internal numeric type for storage in field [name not known at >>> this stage of query execution]. The invalid input object is: >>> Sequence(u's_org', start=1, increment=1, optional=False)") 'INSERT >>> INTO org (id, name, contact, dept, vatnr, country, city, street, >>> building, postcode, phone, fax, email) VALUES >>> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' (Sequence(u's_org', start=1, >>> increment=1, optional=False), u'xyz', None, None, None, u'CH', None, >>> None, None, None, None, None, None) >>> >>> >>> It appears that Sqlalchemy does not process correctly the SEQUENCE >>> keyword in recent versions of Firebird >>> >>> -- >>> 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. >>> >> >> -- >> 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
Re: [sqlalchemy] Re: More autocommit and exception-handling questions
On Mar 2, 2011, at 5:33 PM, Romy wrote: > On Mar 2, 6:50 am, Michael Bayer wrote: >>> WRT your 5 insert example, what's wrong w/ explicitly marking those >>> single logical units inside a BEGIN ... COMMIT while running >>> autocommit ? >> >> If they are truly unrelated things, then yes there's nothing logically wrong >> with them being in separate transactions. >> [snip] >> Then there's just the basic nature of what using a transaction means. Your >> third operation fails, the request throws an error. What ever you changed >> in the first two operations succeeds and remains permanent. A lot of apps >> are not OK with that, certainly not any I write. > > I think you misunderstood what I meant. I was referring to wrapping > multiple statements in a single BEGIN / COMMIT, when necessary while > running autocommit. oh - well yes, the "real" use case for "autocommit" is that your application or framework is doing its own begin() and a rollback()/commit() at the end. "autocommit=True" allows the begin() to be denoted by the framework instead of upon first usage by the Session. -- 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: More autocommit and exception-handling questions
On Mar 2, 6:50 am, Michael Bayer wrote: > > WRT your 5 insert example, what's wrong w/ explicitly marking those > > single logical units inside a BEGIN ... COMMIT while running > > autocommit ? > > If they are truly unrelated things, then yes there's nothing logically wrong > with them being in separate transactions. > [snip] > Then there's just the basic nature of what using a transaction means. Your > third operation fails, the request throws an error. What ever you changed in > the first two operations succeeds and remains permanent. A lot of apps are > not OK with that, certainly not any I write. I think you misunderstood what I meant. I was referring to wrapping multiple statements in a single BEGIN / COMMIT, when necessary while running autocommit. -- 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.
Re: [sqlalchemy] support for SEQUENCE keyword in firebird
On Mar 2, 2011, at 5:13 PM, Michael Bayer wrote: > Sequence() is a DDL construct, not a SQL construct, so you don't embed it > into an INSERT statement directly, nor can you assign it to an attribute on a > mapped object (as would appear to be the case here). It is a marker applied > to a Column() object to note the column's generator for default values. > > The usage of Sequence in table metadata is described at: > > http://www.sqlalchemy.org/docs/core/schema.html#defining-sequences > > When using the ORM in particular, the Sequence needs to be used in this way > so that SQLAlchemy has a means of acquiring the newly generated identifier. > On Firebird, when the Table metadata has a Sequence associated, the gen_id() > function is embedded inline and the new identifier is acquired via RETURNING. > This applies to all insert() constructs generated against the Table object, > not just those used by the ORM. > > Another option is to explicitly execute the sequence object ahead of time - a > Sequence can be passed to the execute() method of any > engine/connection/Session to invoke its next id.This is not the best > pattern in conjunction with inserts: > > myobject.id = Session.execute(my_sequence_object) continuing, not the best pattern because, its two separate executions instead of one and is more verbose in any case. > > > > > > On Mar 2, 2011, at 3:10 PM, bigt wrote: > >> my Firebird database has some Sequence values defined as >> >> CREATE SEQUENCE S_org; >> >> with an appropriate trigger to set a value. >> >> RECREATE TRIGGER TBI_org FOR org >> ACTIVE BEFORE INSERT POSITION 0 >> AS BEGIN >> IF (NEW.Id IS NULL) THEN >> NEW.Id = NEXT VALUE FOR S_org; >> END^ >> >> >> Using reflection in sqlalchemy i get an error when trying to store an >> entry in the ORG table. >> >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", >> line 1392, in flush >> self._flush(objects) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", >> line 1473, in _flush >> flush_context.execute() >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ >> unitofwork.py", line 302, in execute >> rec.execute(self) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ >> unitofwork.py", line 446, in execute >> uow >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", >> line 1884, in _save_obj >> execute(statement, params) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1191, in execute >> params) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1271, in _execute_clauseelement >> return self.__execute_context(context) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1302, in __execute_context >> context.parameters[0], context=context) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1401, in _cursor_execute >> context) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1394, in _cursor_execute >> context) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/dialects/firebird/ >> base.py", line 680, in do_execute >> cursor.execute(statement, parameters or []) >> InterfaceError: (InterfaceError) (0L, "Error while attempting to >> convert object of type to >> database-internal numeric type for storage in field [name not known at >> this stage of query execution]. The invalid input object is: >> Sequence(u's_org', start=1, increment=1, optional=False)") 'INSERT >> INTO org (id, name, contact, dept, vatnr, country, city, street, >> building, postcode, phone, fax, email) VALUES >> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' (Sequence(u's_org', start=1, >> increment=1, optional=False), u'xyz', None, None, None, u'CH', None, >> None, None, None, None, None, None) >> >> >> It appears that Sqlalchemy does not process correctly the SEQUENCE >> keyword in recent versions of Firebird >> >> -- >> 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. >> > > -- > 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. > -- 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,
Re: [sqlalchemy] support for SEQUENCE keyword in firebird
Sequence() is a DDL construct, not a SQL construct, so you don't embed it into an INSERT statement directly, nor can you assign it to an attribute on a mapped object (as would appear to be the case here). It is a marker applied to a Column() object to note the column's generator for default values. The usage of Sequence in table metadata is described at: http://www.sqlalchemy.org/docs/core/schema.html#defining-sequences When using the ORM in particular, the Sequence needs to be used in this way so that SQLAlchemy has a means of acquiring the newly generated identifier. On Firebird, when the Table metadata has a Sequence associated, the gen_id() function is embedded inline and the new identifier is acquired via RETURNING. This applies to all insert() constructs generated against the Table object, not just those used by the ORM. Another option is to explicitly execute the sequence object ahead of time - a Sequence can be passed to the execute() method of any engine/connection/Session to invoke its next id.This is not the best pattern in conjunction with inserts: myobject.id = Session.execute(my_sequence_object) On Mar 2, 2011, at 3:10 PM, bigt wrote: > my Firebird database has some Sequence values defined as > > CREATE SEQUENCE S_org; > > with an appropriate trigger to set a value. > > RECREATE TRIGGER TBI_org FOR org > ACTIVE BEFORE INSERT POSITION 0 > AS BEGIN >IF (NEW.Id IS NULL) THEN > NEW.Id = NEXT VALUE FOR S_org; >END^ > > > Using reflection in sqlalchemy i get an error when trying to store an > entry in the ORG table. > > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", > line 1392, in flush >self._flush(objects) > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", > line 1473, in _flush >flush_context.execute() > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ > unitofwork.py", line 302, in execute >rec.execute(self) > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ > unitofwork.py", line 446, in execute >uow > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", > line 1884, in _save_obj >execute(statement, params) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1191, in execute >params) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1271, in _execute_clauseelement >return self.__execute_context(context) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1302, in __execute_context >context.parameters[0], context=context) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1401, in _cursor_execute >context) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1394, in _cursor_execute >context) > File "/usr/lib/python2.7/site-packages/sqlalchemy/dialects/firebird/ > base.py", line 680, in do_execute >cursor.execute(statement, parameters or []) > InterfaceError: (InterfaceError) (0L, "Error while attempting to > convert object of type to > database-internal numeric type for storage in field [name not known at > this stage of query execution]. The invalid input object is: > Sequence(u's_org', start=1, increment=1, optional=False)") 'INSERT > INTO org (id, name, contact, dept, vatnr, country, city, street, > building, postcode, phone, fax, email) VALUES > (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' (Sequence(u's_org', start=1, > increment=1, optional=False), u'xyz', None, None, None, u'CH', None, > None, None, None, None, None, None) > > > It appears that Sqlalchemy does not process correctly the SEQUENCE > keyword in recent versions of Firebird > > -- > 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. > -- 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] support for SEQUENCE keyword in firebird
my Firebird database has some Sequence values defined as CREATE SEQUENCE S_org; with an appropriate trigger to set a value. RECREATE TRIGGER TBI_org FOR org ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.Id IS NULL) THEN NEW.Id = NEXT VALUE FOR S_org; END^ Using reflection in sqlalchemy i get an error when trying to store an entry in the ORG table. File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1392, in flush self._flush(objects) File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1473, in _flush flush_context.execute() File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ unitofwork.py", line 302, in execute rec.execute(self) File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ unitofwork.py", line 446, in execute uow File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1884, in _save_obj execute(statement, params) File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1191, in execute params) File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1271, in _execute_clauseelement return self.__execute_context(context) File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1302, in __execute_context context.parameters[0], context=context) File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1401, in _cursor_execute context) File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1394, in _cursor_execute context) File "/usr/lib/python2.7/site-packages/sqlalchemy/dialects/firebird/ base.py", line 680, in do_execute cursor.execute(statement, parameters or []) InterfaceError: (InterfaceError) (0L, "Error while attempting to convert object of type to database-internal numeric type for storage in field [name not known at this stage of query execution]. The invalid input object is: Sequence(u's_org', start=1, increment=1, optional=False)") 'INSERT INTO org (id, name, contact, dept, vatnr, country, city, street, building, postcode, phone, fax, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' (Sequence(u's_org', start=1, increment=1, optional=False), u'xyz', None, None, None, u'CH', None, None, None, None, None, None, None) It appears that Sqlalchemy does not process correctly the SEQUENCE keyword in recent versions of Firebird -- 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.
Re: [sqlalchemy] Re: Query a value that is a relationship
I see... I'll work something out.
Thank you Mr. Bayer!!
2011/3/1 Michael Bayer :
>
> On Mar 1, 2011, at 5:50 PM, Hector Blanco wrote:
>
> Hello everyone:
>
> Let's say I have a class "User" and a class "UserGroup". One user can
> belong to one userGroup, an a userGroup can contain several users
> (pretty typical structure). It's a simple relationship I got modeled
> like:
>
> class UserGroup(declarativeBase):
> """Represents a group of users with the same features"""
> __tablename__ = "user_groups"
>
> id = Column("id", Integer, primary_key=True)
> name = Column("name", String(50))
> users = relationship("User", order_by=lambda:User.userName,
> cascade="all, delete", collection_class=set)
>
> class User(declarativeBase):
> """Represents a user"""
> __tablename__ = "users"
>
> id = Column("id", Integer, primary_key=True)
> firstName = Column("first_name", String(50))
> lastName = Column("last_name", String(50))
> email = Column("email", String(60))
> userName = Column("user_name", String(50), unique=True,
> nullable=False)
> password = Column("password", String(64), nullable=False)
> userGroupId = Column("user_group_id", Integer,
> ForeignKey("user_groups.id"))
>
> userGroup = relationship("UserGroup", uselist=False)
>
> I am working in a tool that accepts generic queries, and, basically, I
> can do something like:
>
> session.query(User.User).filter(User.User.id > 3).values("userName")
>
> And get tuples with a .userName field with all the userNames of the
> users whose id is > 3
>
> But if I try:
> session.query(User.User).filter(User.User.id > 3).values("userGroup")
>
> well yes values() accepts only scalar columns (and also you should pass the
> attribute, not a string, guess the docs aren't crystal clear on that).
>
>
> So here's the question:
>
> Is there any way of getting the "userGroup" value somehow "starting"
> (or querying) User objects? (or what would be the best way, if there
> are many ways)
>
> typically the columns you're retrieving are the thing you're "starting"
> from:
>
> query(UserGroup).join(UserGroup.users).filter(User.id > 3).all()
> if you have a lot more join going on and really need a certain entity in the
> left, you can say:
> query(UserGroup).select_from(User).join(User.userGroup).filter(User.id >
> 3).all()
> There's a ticket somewhere to allow query() to also accept a relationship()
> attribute that is specifically many-to-one, but that's just a small
> syntactic convenience. query() in general accepts entities and column
> expressions only.
>
> --
> 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.
>
--
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.
Re: [sqlalchemy] Re: More autocommit and exception-handling questions
On Mar 1, 2011, at 11:41 PM, Romy wrote: > On Mar 1, 2:29 am, Michael Bayer wrote: >> On Mar 1, 2011, at 1:42 AM, Romy wrote: >> >>> Getting some conflicting advice on autocommit and wrapping the request >>> in a try/except block on the Tornado mailing list, was wondering what >>> your thoughts are on the issues brought up in the following message >>> and its replies: >> >>> http://groups.google.com/group/python-tornado/msg/d06a7e244fc9fe29 >> >> I have never worked with async servers so I don't have much wisdom on the >> best usage patterns with relational databases, I think the suggestion to >> wrap individual queries in a try/except defeats the one of the purposes of >> using a transactional, relational database. It shouldn't be an exotic need >> to treat several SQL statements in succession as part of a single logical >> series of operations, that series of operations linked to the scope of a >> single HTTP request. It's of course optional, though I'd like to think I >> emit four INSERT statements in a request, then the fifth one fails, I can >> roll the whole thing back. Similarly that I can emit SELECT statements that >> will share the same isolated environment of one transaction, won't release >> row locks before I'm done, etc. > > Don't let the async nature throw you -- my code is 99% synchronous and > in the async calls I can easily handle the database behavior manually. > > WRT your 5 insert example, what's wrong w/ explicitly marking those > single logical units inside a BEGIN ... COMMIT while running > autocommit ? If they are truly unrelated things, then yes there's nothing logically wrong with them being in separate transactions. The commit and/or rollback has overhead, as well as within SQLAlchemy itself there's overhead to the demarcation of transactions. If you have expire_on_commit=True, then the work involved between statements is greater as the Session can't even return to you any data from the previous transaction, it all has to be reloaded. Then there's just the basic nature of what using a transaction means. Your third operation fails, the request throws an error. What ever you changed in the first two operations succeeds and remains permanent. A lot of apps are not OK with that, certainly not any I write. > Same goes for wrapping SELECTs and anything else you > might need. I've found there to be nothing awkward about this approach > when I had autocommit on. Yeah everyone that's used MySQL for all these years with MyISAM tables, the default, has this behavior, there's no transactions.Its not awkward at all and is extremely common. Just not the way I like to write applications, I prefer what my requests do are contained within transactions and don't leave side effects if they fail. SQLAlchemy around version 0.4/0.5 was very specifically aimed at the latter use case. > > How do you feel about the effects on locking as it pertains to > elongated transactions ? I would never call the span of a single web request an "elongated transaction". As long as you're using an MVCC concurrency model, locking should only be down to rows that have been updated or deleted and should not be adding latency anywhere that it's not appropriate. > It looks like the more strict the isolation > mode, the more this hurts concurrency. Even at the repeatable-read > defaults this could lead to SELECTs issuing write-locks for as long as > they're in a transaction. PG's default is "Read committed". SELECTs don't lock. > > -- > 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. > -- 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: Setting column value that changes everyday
Hi, Thanks for the reply. I too have noticed I can not store numbers starting with 0 while number is integer. May be I'll make that column string :) On Mar 2, 12:33 am, "Sergey V." wrote: > > Do you need to store expiry_code? seeing as it is a function of > > last_con and the current date. > > Second that. I would also point out that phone number probably > shouldn't be an integer - how would you store phone numbers which > start with 0, for example? > > I'd rather make it a String. -- 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: Setting column value that changes everyday
Hi, Thanks for the reply. This definitely looks good but my problem is i want to save the data into the database as a column and I need to show this message to user. I'm using formalchemy to show the data and this way, formalchemy grid view is throwing Attribute error on expiry_code. May be I'm doing something wrong, I'm very new to sqlalchemy. On Mar 1, 12:46 pm, GHZ wrote: > Hi, > > Do you need to store expiry_code? seeing as it is a function of > last_con and the current date. > > class PhNumber(Base): > __tablename__ = 'ph_numbers' > ph_no = Column(Integer, nullable=False) > last_con = Column(DateTime, nullable=False) > > @property > def expiry_code(self): > msg = 'Expired 3 months' > now = datetime.datetime.now() > if now > (self.last_con - 90): > return msg > return 'Not Applicable' > > If the column needs to be queried from outside sqlalchemy, then you > could put the logic in a database function (depending upon what > database you are using). > > On Mar 1, 12:52 pm, dalia wrote: > > > Hi, > > > I have a table of phone numbers which has 3 columns named - > > > 1. ph_no Integer not null > > 2. last_contacted Datetime not null > > 3. expiry_code Text() > > > The behaviour of the table should be - When the last_contacted column > > has a date which is 3 months older, the expiry_code column should have > > the value 'number expired'. I'm not sure how this can be done using > > declarative method. I did the following - > > > class PhNumber(Base): > > __tablename__ = 'ph_numbers' > > ph_no = Column(Integer, nullable=False) > > last_con = Column(DateTime, nullable=False) > > expiry_code = Column(Text(), default=mydefault, > > onupdate=mydefault) > > > def mydefault(context): > > msg = 'Expired 3 months' > > now = datetime.datetime.now() > > if now > (context.current_parameters['last_con'] - 90): > > return msg > > return 'Not Applicable' > > > mydefault function calculates if the value in last_con column is > > greater than 3 months of today's date, it stores 'Expired 3 months' in > > expiry_code. But this happens only when a new insert or update occurs > > in this table. > > > I want the value in expiry_code to be changed even without any update/ > > insert operations on the table. Whenever the table is selected, the > > updated value should be shown. Is this possible in SQLAlchemy? Please > > let me know. -- 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.
