[sqlalchemy] What is polymorphic_on needed for in a joined table inheritance schema?
I used joined table inheritance in Hibernate and it worked fine without any extra discriminator columns. Why is it necessary in SQLAlchemy? I can understand the need for such a column in single table inheritance, but not joined table. -- 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] implementing implicit scalar collections
Hi, I've just implemented support for scalar collections for Spyne. (In Spyne terms that's sql serialization of an array of primitives). Seems to be working fine so far. The question is: Is the association proxy the only (read/write) way of doing this? It requires the child table to be mapped, which requires the child table to have a primary key, which is sometimes completely useless. I also have to create another implicit attribute so that the association proxy can fetch the value off of it. Here's the relevant bit: https://github.com/plq/spyne/blob/master/spyne/util/sqlalchemy.py#L563 Here's its test: https://github.com/plq/spyne/blob/master/spyne/test/test_sqlalchemy.py#L917 Setting both columns as primary keys breaks the test: https://gist.github.com/plq/5630698#file-spyne-patch column_property is read-only, so I can't use it. Any suggestions? Another question, instead of deleting, it seems to be updating foreign keys to null and re-inserting data. Why not just delete the old values? Best regards, Burak -- 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] non foreign-key relationship between schemas
Am 24.05.2013, 17:53 Uhr, schrieb YKdvd : but I can't seem to find anything that works. I can provide some sort of instance method or property with the necessary id value for foreign(), but I'm not sure if this is acceptable, or even if the remote reference is correct (I've tried the string "Studio.productions.id" as well as the Production.id variable. Note that you can stuff a join() method with all the conditions you need and this is sometimes unavoidable. I could probably add a "production_id" column to the episodes table - it would get filled with the same value for all records in a particular Production_?.episodes table. That would let me do a normal foreign_key relationship and shouldn't break the legacy PHP access. But I was curious if there is a way to torture SQLAlchemy into creating this sort of non-column relationship? You really do not want to try to trick SQLAlchemy (or yourself) into even thinking this. Non-existent foreign keys will almost certainly mean a table scan with terrible implications for performance. I recently discovered that MySQL will do this even for indexed columns. :-/ Make the relationship explicit, note dump, truncate, alter, import is often the only way to do this and enjoy the, er, show. Charlie -- Charlie Clark Managing Director Clark Consulting & Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- 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] Can SQLAlchemy execute multiple select statements in a single round trip?
Am 23.05.2013, 21:50 Uhr, schrieb Sean Lynch : Not within one of my SQLAlchemy apps, but I have an NHibernate application where the database and application servers are in different data centers (out of my control) and thus using .future() calls saves a good bit I/O time. After seeing the ActiveRecord::Futures project show up on https://github.com/languages/Ruby, I was curious if SQLAlchemy had a similar feature / capability. I'm not sure how related this is to your problem but ActiveRecord needs something like this because it has a very poor model with lots of I/O to the database, SQLAlchemy gives you the flexibility to decide how you want your queries processed. With a persistent connection I wouldn't have thought it made much difference where the servers are. Charlie -- Charlie Clark Managing Director Clark Consulting & Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- 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] non foreign-key relationship between schemas
> > Yup, all episodes in a Production schema would belong to one specific > production row. I guess I was thinking about the relationship more in > terms of the automatic loading of the collection, and being able to > add/delete from it and have it reflected on flush. It looks like @property > handles the first - I'll have to check the docs to see if a property can > provide an aware collection? Good weekend research - the joys of reverse > designing from an existing setup! -- 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] non foreign-key relationship between schemas
On May 24, 2013, at 12:34 PM, YKdvd wrote: > Yeah, I was afraid of that, but I thought there might be something going on > with the relationship layer that might do the knitting. It would be a > constant value (per schema/engine) I could have provided to the engine, > metadata, mapper or whatever, but it isn't in the Episode row explicitly. Oh > well, There's ways to get app-level constant values into relationship. But if there's nothing in the Episode row at all, you aren't actually getting any good use from relationship here - you don't need anything about Episode to load it, you don't need anything to happen regarding Episode to persist it. A @property that just does a query(Production).get() would work just as well. > > I'll take a look at the docs for whether a @property can handle writable > collecitons, but I may just break down and put in that column for a > constant-value foreign-key relationship - I wanted to try and backref it to > the "Studio.productions" table so that productions loaded from a Production > schema have a collection of episodes, if possible. butthere's no linkage, so Production.episodes would just be, "select * from episodes", wouldn't it ? What row in "episodes" does *not* line up with that production row, and why ? There's nothing in the episode row that tells us this, so it's all rows. -- 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] Re: non foreign-key relationship between schemas
Yeah, I was afraid of that, but I thought there might be something going on with the relationship layer that might do the knitting. It would be a constant value (per schema/engine) I could have provided to the engine, metadata, mapper or whatever, but it isn't in the Episode row explicitly. Oh well, I'll take a look at the docs for whether a @property can handle writable collecitons, but I may just break down and put in that column for a constant-value foreign-key relationship - I wanted to try and backref it to the "Studio.productions" table so that productions loaded from a Production schema have a collection of episodes, if possible. 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] non foreign-key relationship between schemas
On May 24, 2013, at 11:53 AM, YKdvd wrote:
>
> class Production(Base):
> __table_args__ = {'schema':'Studio'}
>id = Column('id', Integer, primary_key=True, nullable=False)
>
> class Episode(Base):
>...
> # some sort of relationship() back to Production, even though there is no
> column to use as a foreign key
>
> "Creating Custom Foreign Conditions" documents the remote() and foreign()
> functions, and I was wondering if these could be used somehow. I've played
> around with something like
>
> production = relationship("Production",
> primaryjoin=remote(Production.id)==foreign(???))
>
> but I can't seem to find anything that works. I can provide some sort of
> instance method or property with the necessary id value for foreign(), but
> I'm not sure if this is acceptable, or even if the remote reference is
> correct (I've tried the string "Studio.productions.id" as well as the
> Production.id variable.
if I give you a row from the Episode table, and nothing else, tell me what row
it refers to in Production, and how you know that.
If the answer is, "nothing, only the code knows", then you can't use
relationship(), just use a @property. Though it's still possible that the
rules that are in the code could be translated into SQL, but only if they
ultimately derive from the data that's in that Episode row.
--
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] non foreign-key relationship between schemas
I'm working with a MySQL setup that has, say, a Studio database (or
schema), and multiple Production schemas (Production_1, Production_2, etc).
The Studio database has a "seasons" table, and the Production databases
have "episodes" tables. Currently, the episodes table doesn't have a
foreign key linkage back to Production.productions (there isn't even a
column containing the "id" from Production.productions), the original PHP
usage didn't work this way. For SQLAlchemy, I'd like to create a
relationship so that if I have an engine for, say, Production_1, a
retrieved "episodes" object does the many->one back to its "productions"
parent. Things are something like this:
class Production(Base):
__table_args__ = {'schema':'Studio'}
id = Column('id', Integer, primary_key=True, nullable=False)
class Episode(Base):
...
# some sort of relationship() back to Production, even though there is no
column to use as a foreign key
"Creating Custom Foreign Conditions" documents the remote() and foreign()
functions, and I was wondering if these could be used somehow. I've played
around with something like
production = relationship("Production",
primaryjoin=remote(Production.id)==foreign(???))
but I can't seem to find anything that works. I can provide some sort of
instance method or property with the necessary id value for foreign(), but
I'm not sure if this is acceptable, or even if the remote reference is
correct (I've tried the string "Studio.productions.id" as well as the
Production.id variable.
I could probably add a "production_id" column to the episodes table - it
would get filled with the same value for all records in a particular
Production_?.episodes table. That would let me do a normal foreign_key
relationship and shouldn't break the legacy PHP access. But I was curious
if there is a way to torture SQLAlchemy into creating this sort of
non-column relationship?
--
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] feel dirty, is there a better way?
I would apply a single event listener to Temporal, and do all the work of
adding the constraints and all that in the one event handler. I'd skip
__table_args__.
If you're on 0.8, you can apply listeners to mixins and unmapped classes using
"propagate=True", and the events should trigger for all subclasses.
On May 23, 2013, at 7:49 AM, Chris Withers wrote:
> Hi All,
>
> I have a mixin defined like this:
>
> def add_exclude_constraint(mapper, class_):
> table = class_.__table__
> elements = [('period', '&&')]
> for col in table.primary_key.columns:
> if col.name!='period':
> elements.append((col, '='))
> table.append_constraint(ExcludeConstraint(*elements))
>
> class Temporal(object):
>
> @declared_attr
> def __table_args__(cls):
> listen(cls, 'instrument_class', add_exclude_constraint)
> return (
> CheckConstraint("period != 'empty'::tsrange"),
> )
>
> period = Column(DateTimeRange(), nullable=False, primary_key=True)
>
> That listen call is the source of the dirty feeling...
>
> What's the "right" way to do this?
>
> Chris
>
> --
> Simplistix - Content Management, Batch Processing & Python Consulting
> - http://www.simplistix.co.uk
>
> --
> 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] Creating an index if it doesn't exist
On May 23, 2013, at 8:06 PM, Mike Bissell wrote: > How might I convince SQLAlchemy 0.7.9 to create a newly added index on a > table in the event that the index doesn't already exist? This new index is > created as a member of __table_args__; it is not instantiated with > index=True. I have many such index objects to create. an Index has a create() method for single creates. > > I did check stackoverflow, and their unsatisfactory advice was simply to cut > and paste the generated DDL: > > > http://stackoverflow.com/questions/14419299/adding-indexes-to-sqlalchemy-models-after-table-creation that answer is a disaster and I've added my own answer to that question, and I would imagine it threw you off the path here. > If this feature doesn't exist, I would settle for a function that would take > a table and conditionally create any missing pieces (specifically indexes). > > As a last resort, is the correct way to do this simply to call Index.create > for each index I make? OK confused, you apparently know about index.create(), so what exactly is the feature you're looking for as far as conditionally creating missing pieces ? Other kinds of "missing pieces" like constraints use the AddConstraint() construct,plenty of detail on that here: http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#controlling-ddl-sequences , note however we don't support "checking" for individual table constraints right now (indexes are a separate concern). -- 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] distinct on
Thanks for reply, Mariano.
j
On 05/23/2013 12:37 PM, Mariano Mara wrote:
On 05/23/2013 04:42 AM, jo wrote:
|Hi all,
I wondered if it is possible to execute a partial distinct in
sqlalchemy.
The following query works in oracle and postgresql:
select distinct col1, first_value(col2) over (partition by col1 order by
col2 asc)
from tmp;
How can I do such query in sqlalchemy?
Thanks for any help.
j
Yes, it is entirely possible. Something like this should do the trick
(not tested):
>>> from sqlalchemy import select, func
>>> from sqlalchemy.sql.expression import over
>>> q = select([tmp.c.id.distinct(), over(func.first_value(tmp.c.cid),
partition_by=tmp.c.id, order_by=tmp.c.name.asc())])
>>> print(q)
SELECT DISTINCT "user".id, first_value("user".cid) OVER (PARTITION BY
"user".id ORDER BY "user".name ASC) AS anon_1
FROM "user"
This chapter of the documentation will help with these features and
much more: http://docs.sqlalchemy.org/en/latest/core/expression_api.html
--
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.
