Re: [sqlalchemy] obtaining * field when more than 1 table
On 2011-9-15 18:58, RVince wrote: Suppose I wish to do something like: Session.query(Files.original_name, MSPResponse.* because MSPResponse table has so many fields, and I want to get them all. How do I do this given that I am also picking field(s) from other tables ? Thanks RVince You can use the power of python: Session.query(Files.original_name, *[c.name for c in MSPResponse.__table__.c]) Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- 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 more than one entity from a subquery
I have seen that it is possible to get an entity from a subquery with the aliased(entity,statement) construct. Is there also a way to get more than one entity from a subquery, for example 2? Cheers Adrian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/2pEAnubaBukJ. 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] different behavior with schema qualified tables in sqlite since 0.7.x
Something's changed with sqlite since 0.6. Once a sqlite engine is
created to a physical file the usual workaround for schema change does
not work. It works correctly on sqlite in memory and even when
reattaching from memory to a file.
from sqlalchemy import Column, Sequence, create_engine, Integer
from sqlalchemy.ext.declarative import declarative_base
import os
Base = declarative_base()
metadata = Base.metadata
class A(Base):
__tablename__ = 'A'
__table_args__ = {'schema':'S'}
id = Column(u'ID', Integer, Sequence('A_PK'), primary_key=True)
e = create_engine('sqlite://', echo=True)
e.execute(attach database 'test.db' as S;)
metadata.bind = e
metadata.create_all() # all good
e = create_engine('sqlite:///test.db', echo=True)
e.execute(attach database 'test.db' as S;)
metadata.bind = e
metadata.create_all() # OperationalError: (OperationalError) unknown
database S 'PRAGMA S.table_info(A)' ()
os.remove('test.db')
# the work-around I came up with
e = create_engine('sqlite://', echo=True)
e.execute(attach database 'test.db' as S;)
metadata.bind = e
metadata.create_all() # works fine again, tables were created in file
Previously (ver. 0.6), the OperationalError would not occur. Neither
SQLite nor pysqlite version have changed.
--
Victor Olex
http://linkedin.com/in/victorolex
http://twitter.com/agilevic
--
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] obtaining * field when more than 1 table
Suppose I wish to do something like: Session.query(Files.original_name, MSPResponse.* because MSPResponse table has so many fields, and I want to get them all. How do I do this given that I am also picking field(s) from other tables ? Thanks RVince Session.query(Files.original_name, MSPResponse) should work. You get result rows with 2 items: a scalar for original_name and a MSPResponse object. You can then process the result like this: for row in result: ### access Files.original_name as row.original_name ### access MSPResponse as row[1] -- Mike Conley -- 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] different behavior with schema qualified tables in sqlite since 0.7.x
OK what's changed with SQLite is that file-based databases no longer use a pool
- NullPool is used by default.So below, if you want some kind of scope to
remain for the life of the Engine you'd need to switch it back to
SingletonThreadPool - however, what would be better is to procure a Connection
from the Engine, then do everything with the Connection (i.e.
metadata.create_all(conn)). Since if you really want some scope declared on a
single SQLite connection that's what you'd need to do in any case.
On Sep 16, 2011, at 11:37 AM, Victor Olex wrote:
Something's changed with sqlite since 0.6. Once a sqlite engine is
created to a physical file the usual workaround for schema change does
not work. It works correctly on sqlite in memory and even when
reattaching from memory to a file.
from sqlalchemy import Column, Sequence, create_engine, Integer
from sqlalchemy.ext.declarative import declarative_base
import os
Base = declarative_base()
metadata = Base.metadata
class A(Base):
__tablename__ = 'A'
__table_args__ = {'schema':'S'}
id = Column(u'ID', Integer, Sequence('A_PK'), primary_key=True)
e = create_engine('sqlite://', echo=True)
e.execute(attach database 'test.db' as S;)
metadata.bind = e
metadata.create_all() # all good
e = create_engine('sqlite:///test.db', echo=True)
e.execute(attach database 'test.db' as S;)
metadata.bind = e
metadata.create_all() # OperationalError: (OperationalError) unknown
database S 'PRAGMA S.table_info(A)' ()
os.remove('test.db')
# the work-around I came up with
e = create_engine('sqlite://', echo=True)
e.execute(attach database 'test.db' as S;)
metadata.bind = e
metadata.create_all() # works fine again, tables were created in file
Previously (ver. 0.6), the OperationalError would not occur. Neither
SQLite nor pysqlite version have changed.
--
Victor Olex
http://linkedin.com/in/victorolex
http://twitter.com/agilevic
--
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] Selecting more than one entity from a subquery
On Sep 16, 2011, at 5:28 AM, Adrian wrote: I have seen that it is possible to get an entity from a subquery with the aliased(entity,statement) construct. Is there also a way to get more than one entity from a subquery, for example 2? You can of course have a subquery that represents multiple entities internally, and then the subquery itself has a .c. attribute which you can use to construct further statements in terms of the columns of the entity. Otherwise it seems like you'd be asking for myalias.Entity1.foo, myalias.Entity2.bar ? If that's what you mean, I think for the moment you'd need to stick with a pattern like myalias.c.entity1_foo, myalias.c.entity2_bar, where myalias is a Core Alias construct, rather than an ORM alias. Feel free to send an example of what you're looking for if more info is needed. -- 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] Many-to-One relationship where each side has different typed keys?
I have a table of survey responces which each correspond to a particular student, identified by an integer student_id. The database which stores that student's data, however, has their id in a VARCHAR2 column. The data in that column is really just an int, but I can't change the Oracle database. I've tried to relate these two tables using: SurveyResponse.student = relationship(Person, primaryjoin=Person.person_id==SurveyResponse.student_id, foreign_keys=[SurveyResponse.student_id] ) But I get this error when I try to retrieve the student attribute from a SurveyResponse instance: ORA-01722: invalid number I can't use a ForeignKey object, because these two tables are in different databases. That's why I've got the relationship defined so explicitly. So, is there any way to tell SQLAlchemy to convert the int foreign key on the SurveyResponse table into a VARCHAR2 to match the primary key on the Person table? Or is there some other way to make this relationship work? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/o5UECbf9O_4J. 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] Many-to-One relationship where each side has different typed keys?
You're a lifesaver! That method works perfectly for me. Since the Oracle side is read-only, I didn't need to use the StringAsInt class, but the rest of it worked like a charm. I look forward to the eventual resolution of that ticket, as this process really is quite convoluted. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/3BM7mK7djsAJ. 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.
