[sqlalchemy] 'ThreadLocalMetaData' support for 'schema'
Having to support different PostgreSQL schemas per web request and finding my current approach of setting the PostgreSQL schema search path a bit convoluted, when I try to use longer lived sessions in unit tests for convenience, because the session starts using a new database connection after a commit, I came across 'sqlalchemy.schema.ThreadLocalMetaData' which would seem to be an elegant solution for the problem if it supported a different 'schema' per thread as it does a different 'bind'. My question is: would it be feasible and does it make sense to 'schema' support to 'sqlalchemy.schema.ThreadLocalMetaData' for cases such as the one I described above? Thanks in advance for any feedback on this. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Self-referencing augmented class
Hi!
I've got this setup:
cs = sqlite:///:memory:
sa_engine = create_engine(cs)
Base = declarative_base()
class Person(Base):
__abstract__ = True
id = Column(Integer, primary_key=True)
name = Column(String(30))
class Employee(Person):
__tablename__ = 'employee'
manager_id = Column(Integer, ForeignKey('employee.id'))
manager = relationship('Employee', primaryjoin=(manager_id==Person.id),
remote_side=Person.id) #many-to-one
comment = Column(String(100))
Base.metadata.drop_all(sa_engine)
Base.metadata.create_all(sa_engine)
Session = sessionmaker(bind=sa_engine)
s = Session()
e1 = Employee(name='John Smith') #sqlalchemy.exc.CompileError: Cannot
compile Column object until it's 'name' is assigned.
What gives? Thanks!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] 'ThreadLocalMetaData' support for 'schema'
we will be supporting this as a connection execution option in http://www.sqlalchemy.org/trac/ticket/2685, so the usage will be like: conn = connection.execution_options(default_schema=someschema) s = Session(bind=conn) work with Session or Connection for now the easiest approach is to set the search path per connection/session/whatever: s = Session() s.execute(set search path to my_schema, public) if you bind your Session to a Connection as above, it will be used repeatedly for new transactions so the commit() won't be a problem. Another approach, if you're Session centric, is to set it in the after_begin Session event: from sqlalchemy import event @event.listens_for(Session, after_begin) def after_begin(session, trans, conn): conn.execute(set search path to my_schema, public) On Apr 7, 2013, at 6:09 AM, Pedro Romano pmcn...@gmail.com wrote: Having to support different PostgreSQL schemas per web request and finding my current approach of setting the PostgreSQL schema search path a bit convoluted, when I try to use longer lived sessions in unit tests for convenience, because the session starts using a new database connection after a commit, I came across 'sqlalchemy.schema.ThreadLocalMetaData' which would seem to be an elegant solution for the problem if it supported a different 'schema' per thread as it does a different 'bind'. My question is: would it be feasible and does it make sense to 'schema' support to 'sqlalchemy.schema.ThreadLocalMetaData' for cases such as the one I described above? Thanks in advance for any feedback on this. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] ORM Join with Group By
Thank you very much for your effort in putting together a complete and
working example.
Much appreciated,
Thijs
On Sat, Apr 6, 2013, at 18:10, Audrius Kažukauskas wrote:
Hi, Thijs,
On Thu, 2013-04-04 at 21:36:56 +0200, Thijs Engels wrote:
Assume a database with two tables; sessions and events. Sessions has a
primary key id and some additional information. Events has a primary key
which consists of session_id (foreign key to Sessions) and event_id
which is using a sequence. This table contains an additional column
which contains the value I am interested in.
SESSIONS
- SESSION_ID [PK]
- DATE
EVENTS
- SESSION_ID [PK + FK]
- EVENT_ID [PK]
- CODE
Each session has multiple events, what I am interested in is the code of
the last event per session (event with highest event id).
I managed to come up with the SQL which will do this:
SELECT
SSN.SESSION_ID,
SSN.DATE
FROM
SESSIONS SSN
LEFT JOIN
(SELECT
EVT.SESSION_ID,
EVT.CODE
FROM
EVENTS EVT
INNER JOIN
(SELECT
SESSION_ID,
MAX(EVENT_ID) AS EVENT_ID
FROM
EVENTS
GROUP BY
SESSION_ID) LAST_EVENT
ON
AEL.SESSION_ID = LAST_EVENT.SESSION_ID AND
AEL.EVENT_ID = LAST_EVENT.EVENT_ID) EVENT
ON
EVENT.SESSION_ID = SSN.SESSION_ID
ORDER BY
SSN.SESSION_ID;
Hence initially getting the max event_id, which is then joined with the
events table to extract the code, which in the end is join with the
sessions table.
I tried to transform this query to SQLAlchemy, but am getting stuck.
Looking at the various examples as found in the documentation I gathered
that I should start with the creation of a sub-query:
stmt = oracle.query(Event.session_id,
func.max(Event.id).label(last_event)).group_by(Event.session_id).subquery()
But I can not figure out how to use this sub-query in the two remaining
joins...
You were on correct path, what you're missing is the fact that result of
.subquery() is an object which can be used like a Table in another
query. So to access last_event in your stmt subquery, you would write
it like this:
stmt.c.last_event
Following is the declaration of two tables (using declarative extension)
and your full query:
class Session(Base):
__tablename__ = 'sessions'
session_id = Column(Integer, primary_key=True)
date = Column(DateTime())
class Event(Base):
__tablename__ = 'events'
session_id = Column(Integer, ForeignKey('sessions.session_id'),
primary_key=True)
event_id = Column(Integer, primary_key=True)
code = Column(Integer)
last_event = db_session.query(
Event.session_id,
func.max(Event.event_id).label('event_id')
).group_by(Event.session_id).subquery()
event = db_session.query(
Event.session_id,
Event.code
).join(last_event, and_(
Event.session_id == last_event.c.session_id,
Event.event_id == last_event.c.event_id
)).subquery()
q = db_session.query(
Session,
event.c.code
).outerjoin(event).order_by(Session.session_id)
--
Audrius Kažukauskas
http://neutrino.lt/
Email had 1 attachment:
+ Attachment2
1k (application/pgp-signature)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Self-referencing augmented class
On Apr 7, 2013, at 8:27 AM, Alexey Vihorev viho...@gmail.com wrote:
Hi!
I’ve got this setup:
cs = sqlite:///:memory:
sa_engine = create_engine(cs)
Base = declarative_base()
class Person(Base):
__abstract__ = True
id = Column(Integer, primary_key=True)
name = Column(String(30))
class Employee(Person):
__tablename__ = 'employee'
manager_id = Column(Integer, ForeignKey('employee.id'))
manager = relationship('Employee', primaryjoin=(manager_id==Person.id),
remote_side=Person.id) #many-to-one
comment = Column(String(100))
Base.metadata.drop_all(sa_engine)
Base.metadata.create_all(sa_engine)
Session = sessionmaker(bind=sa_engine)
s = Session()
e1 = Employee(name='John Smith') #sqlalchemy.exc.CompileError: Cannot compile
Column object until it's 'name' is assigned.
What gives? Thanks!
that Column object assigned to Person.id is not actually associated with any
Table; a copy of it has been made and associated with the employee table.
This hasn't happened yet when you declare your Employee class, so that
primaryjoin and remote_side needs to be as a string:
relationship(Employee, primaryjoin=Employee.manager_id==Employee.id,
remote_side=Employee.id)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] 'ThreadLocalMetaData' support for 'schema'
Thanks for the reply Michael. I had already done some experimentation with the 'Session.after_begin' event, so I think I'll revisit that for now, but #2685 looks like a very elegant solution, looking forward to that. --Pedro. On Sunday, 7 April 2013 15:44:55 UTC+1, Michael Bayer wrote: we will be supporting this as a connection execution option in http://www.sqlalchemy.org/trac/ticket/2685, so the usage will be like: conn = connection.execution_options(default_schema=someschema) s = Session(bind=conn) work with Session or Connection for now the easiest approach is to set the search path per connection/session/whatever: s = Session() s.execute(set search path to my_schema, public) if you bind your Session to a Connection as above, it will be used repeatedly for new transactions so the commit() won't be a problem. Another approach, if you're Session centric, is to set it in the after_begin Session event: from sqlalchemy import event @event.listens_for(Session, after_begin) def after_begin(session, trans, conn): conn.execute(set search path to my_schema, public) On Apr 7, 2013, at 6:09 AM, Pedro Romano pmc...@gmail.com javascript: wrote: Having to support different PostgreSQL schemas per web request and finding my current approach of setting the PostgreSQL schema search path a bit convoluted, when I try to use longer lived sessions in unit tests for convenience, because the session starts using a new database connection after a commit, I came across 'sqlalchemy.schema.ThreadLocalMetaData' which would seem to be an elegant solution for the problem if it supported a different 'schema' per thread as it does a different 'bind'. My question is: would it be feasible and does it make sense to 'schema' support to 'sqlalchemy.schema.ThreadLocalMetaData' for cases such as the one I described above? Thanks in advance for any feedback on this. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] composite ForeignKeyConstraint with a constant, non-column member?
I'm using SQLAlchemy on an existing database, with some one-many with the many row connecting to a many-many department/status system. The main fields are something like this: Parent.id Parent.mainStatus Child.id Child.parent_link Child.department_id Child.status_id Department.id DepartmentAssoc.id DepartmentAssoc.department_id DepartmentAssoc.status_id DepartmentAssoc.seqOrder Status.id Status.whatever Each Parent can have multiple Child rows, for different departments. Each department can have a specific sequenced subset of Status rows, so there's a DepartmentAssoc table that provides this. Child has a status value, and does a composite foreign key relationship (department_id, status_id) through DepartmentAssoc to the status system. Everything connects and works fine, and SQLA has those slick AssociationProxy things that make it even better than the original. There's a couple different Parent/Child type setups like this that share that same association setup. Unfortunately, there's also one weird parent variant that also has a naked status code mainStatus, which implicitly belongs to a specific department (call it #310). The PHP never did complete links with this, but I'd like to provide the same sort of foreign key/association linkage to the status system that the various Child objects have. But since there is no department field in Parent I can't specify that half of the composite. I assume there isn't any way of doing something like ForeignKeyConstraint(['f1', 'f2'], [310, 'da.status_id']) with a constant? I can't think how I'd tell MySQL to do this, so it makes sense you can't. I could probably kludge a department field into that Parent record to make it work, but I was wondering if their is some buried SQLA feature that might be put to use? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
RE: [sqlalchemy] Self-referencing augmented class
Thanks!
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Sunday, April 07, 2013 5:46 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Self-referencing augmented class
On Apr 7, 2013, at 8:27 AM, Alexey Vihorev viho...@gmail.com wrote:
Hi!
I've got this setup:
cs = sqlite://:memory: sqlite:///:memory:
sa_engine = create_engine(cs)
Base = declarative_base()
class Person(Base):
__abstract__ = True
id = Column(Integer, primary_key=True)
name = Column(String(30))
class Employee(Person):
__tablename__ = 'employee'
manager_id = Column(Integer, ForeignKey('employee.id'))
manager = relationship('Employee', primaryjoin=(manager_id==Person.id),
remote_side=Person.id) #many-to-one
comment = Column(String(100))
Base.metadata.drop_all(sa_engine)
Base.metadata.create_all(sa_engine)
Session = sessionmaker(bind=sa_engine)
s = Session()
e1 = Employee(name='John Smith') #sqlalchemy.exc.CompileError: Cannot
compile Column object until it's 'name' is assigned.
What gives? Thanks!
that Column object assigned to Person.id is not actually associated with any
Table; a copy of it has been made and associated with the employee table.
This hasn't happened yet when you declare your Employee class, so that
primaryjoin and remote_side needs to be as a string:
relationship(Employee, primaryjoin=Employee.manager_id==Employee.id,
remote_side=Employee.id)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to mailto:sqlalchemy+unsubscr...@googlegroups.com
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to mailto:sqlalchemy@googlegroups.com
sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en
http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out
https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to mailto:sqlalchemy+unsubscr...@googlegroups.com
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to mailto:sqlalchemy@googlegroups.com
sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
