[sqlalchemy] Re: Seperate History Tables
It is done now. Thank you for all your help.
Suha
On Mon, Mar 30, 2009 at 17:06, Michael Bayer mike...@zzzcomputing.comwrote:
On Mar 30, 2009, at 5:10 AM, Suha Onay wrote:
The problem is i defined the history class with the following way
(examining the test_versioning file):
--
Base = declarative_base(metaclass=VersionedMeta)
class User(Base):
__tablename__ = 'users'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(12), info={'label': u'Kod Adı'})
fullname = sa.Column(sa.Unicode(40), info={'label': u'Adı ve
Soyadı'})
User_History = User.__history_mapper__.class_
def upgrade():
User.__table__.create(migrate_engine)
def downgrade():
User.__table__.drop(migrate_engine)
--
With the upper mode definition, when i updated a user data, it says
that no users_history table defined.
If in upgrade, add the line
User_History.__table__.create(migrate_engine), it says that:
type object 'UserHistory' has no attribute '__table__'
How can i define the models?
im going to assume that define the models means create the
tables. if so, just call Base.metadata.create_all().
--~--~-~--~~~---~--~~
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] Postgres: could not identify an ordering operator for type ... (for SQLA-created table)
Hello everyone,
Now that I have my PG db filled, I'm encountering this exception while
trying to use it:
ProgrammingError: (ProgrammingError) could not identify an ordering
operator for type virtualization
HINT: Use an explicit ordering operator or modify the query.
'SELECT hosts.id AS hosts_id, hosts.IP AS hosts_IP,
hosts.HostName AS hosts_HostName, hosts.Location AS
hosts_Location, hosts.Architecture_id AS hosts_Architecture_id,
hosts.OS_Kind_id AS hosts_OS_Kind_id, hosts.OS_version_id AS
hosts_OS_version_id, hosts.Additional_info AS
hosts_Additional_info, hosts.Column_12 AS hosts_Column_12,
hosts.Column_13 AS hosts_Column_13, hosts.Email_id AS
hosts_Email_id, hosts.Username AS hosts_Username, hosts.Password
AS hosts_Password, hosts.Alias AS hosts_Alias,
hosts.Virtualization_id AS hosts_Virtualization_id,
hosts.Shareable AS hosts_Shareable,
hosts.Shareable_between_projects AS
hosts_Shareable_between_projects, hosts.Notes AS hosts_Notes,
hosts.CPU AS hosts_CPU, hosts.RAM AS hosts_RAM,
hosts.Column_24 AS hosts_Column_24, hosts.Batch AS hosts_Batch,
hosts.ASSET AS hosts_ASSET, hosts.Owner AS hosts_Owner,
hosts.SSH_KEY_PRESENT AS hosts_SSH_KEY_PRESENT,
hosts.Machine_Type_Model AS hosts_Machine_Type_Model,
hosts.MAC_ADDRESS_ETH_0 AS hosts_MAC_ADDRESS_ETH_0,
hosts.Physical_Box AS hosts_Physical_Box, hosts.Up_n_running AS
hosts_Up_n_running, hosts.Available AS hosts_Available,
hosts.Earliest_reservation_id AS hosts_Earliest_reservation_id,
hosts.Project_id AS hosts_Project_id, architecture.id AS
architecture_id, architecture.Architecture AS
architecture_Architecture, os_kind.id AS os_kind_id, os_kind.OS_Kind
AS os_kind_OS_Kind, os_version.id AS os_version_id,
os_version.OS_version AS os_version_OS_version, virtualization.id AS
virtualization_id, virtualization.Virtualization AS
virtualization_Virtualization, virtualization.color AS
virtualization_color, project.id AS project_id, project.Project AS
project_Project \nFROM hosts, architecture, os_kind, os_version,
virtualization, project, email \nWHERE hosts.Architecture_id =
architecture.id AND hosts.OS_Kind_id = os_kind.id AND
hosts.OS_version_id = os_version.id AND hosts.Email_id = email.id
AND hosts.Virtualization_id = virtualization.id AND hosts.Project_id
= project.id AND hosts.Up_n_running = %(Up_n_running_1)s AND
hosts.Shareable = %(Shareable_1)s ORDER BY Virtualization DESC, IP
ASC' {'Shareable_1': True, 'Up_n_running_1': True}
Note the 'ORDER BY Virtualization' clause. The Postgres docs say:
33.13.5. System Dependencies on Operator Classes
PostgreSQL uses operator classes to infer the properties of operators
in more ways than just whether they can be used with indexes. Therefore,
you might want to create operator classes even if you have no intention
of indexing any columns of your data type.
In particular, there are SQL features such as ORDER BY and DISTINCT that
require comparison and sorting of values. To implement these features on
a user-defined data type, PostgreSQL looks for the default B-tree
operator class for the data type. The equals member of this operator
class defines the system's notion of equality of values for GROUP BY and
DISTINCT, and the sort ordering imposed by the operator class defines
the default ORDER BY ordering.
Comparison of arrays of user-defined types also relies on the semantics
defined by the default B-tree operator class.
If there is no default B-tree operator class for a data type, the system
will look for a default hash operator class. But since that kind of
operator class only provides equality, in practice it is only enough to
support array equality.
When there is no default operator class for a data type, you will get
errors like could not identify an ordering operator if you try to use
these SQL features with the data type.
( http://www.postgresql.org/docs/7.4/static/xindex.html )
But I am not using any user-defined data class. It's just a table with
VARCHAR for virtualization.Virtualization column, as created by SQLA:
reservations=# \d virtualization
Table public.virtualization
Column | Type| Modifiers
+---+-
id | integer | not null default
nextval('virtualization_id_seq'::regclass)
Virtualization | character varying |
color | character varying |
Indexes:
virtualization_pkey PRIMARY KEY, btree (id)
The SQLA query in question:
selectexpr = session.query(Host, Architecture, OS_Kind, OS_version,
Virtualization, Project)
...
if direction == 'descending':
selectexpr = selectexpr.filter_by(Up_n_running =
True).filter_by(Shareable = True).order_by(desc(field))
else:
selectexpr = selectexpr.filter_by(Up_n_running =
True).filter_by(Shareable = True).order_by(asc(field))
..where field happens to be 'Virtualization' by
[sqlalchemy] Re: Getting FlushError trying to merge related objects
On Tue, Mar 31, 2009 at 1:03 AM, Yassen Damyanov yassen@gmail.com wrote: On Mon, Mar 30, 2009 at 5:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: can you produce a small test case illustrating this ? Sure, I'll do my best to produce one for you. Mike, A minimalistic test case demonstrating the issue can be downloaded from here: http://www.itlabs.bg/6548438dda3e83f0/merge_test.py This has been run against MySQL 5.x only (that's what I had at hand). It might well be that I do the things wrong... please let me know. THANK YOU! Looking forward to hear your verdict, Yassen --~--~-~--~~~---~--~~ 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 query.get() work?
It's not so much that I'm querying but that I get a set of id's from the user and I've got some logic that will often change some of the values. I wanted to take advantage of SA's orm capabilities as opposed to issuing selects and updates. It's possible in the logic that I already have some of the entities retrieved, but it didn't make sense for me to pass around a map, knowing that SA already keeps one. And that still doesn't seem quite right--having to manage which objects I've previously selected. Perhaps I should just issue the updates. Christiaan Putter wrote: Hi, Small extract from the query's _get method: if not self._populate_existing and not refresh_state and not self._mapper_zero().always_refresh and lockmode is None: try: instance = self.session.identity_map[key] state = attributes.instance_state(instance) if state.expired: try: state() except orm_exc.ObjectDeletedError: self.session._remove_newly_deleted(state) return None return instance except KeyError: pass So you have access to the identity map through session.identity_map[key], where key is: key = self._only_mapper_zero(get() can only be used against a single mapped class.).identity_key_from_primary_key(ident) ident is the primary key for the record you're looking for. The above will basically return the instance to you if it's already in the identity map. Though state() will still execute sql to refresh attributes. So what you can do is build a loop using something like that going through all the primary keys you'd like to get directly from the identity mapper. I'm assuming it's using a pretty good hash so access should be some constant factor. Then you can use the normal query.get() to retrieve the ones that failed from the database. This is really something I would advise against though. What exactly are you querying for? Why don't you just keep a list of all the instances you've already loaded? 2009/3/25 Dan F danielfal...@gmail.com: I understand what get() is supposed to do, but it doesn't clear it up because it still seems like there should be a way of retrieving a *set* of records back from the database at once. I only see a couple choices currently. Either I can use filter() and retrieve every record in the set (even the ones that are mapped), or I can use get on each row individually. Since get() checks the identity map, it won't get records it doesn't need to get, but on the other hand, each record has to get retrieved in its own call. Does this make sense, and do I have it right? Thanks. On Mar 24, 2:08 am, Christiaan Putter ceput...@googlemail.com wrote: Hi, You won't be able to get() multiple objects at the same time. query(SomeClass).get(pk1, pk2, pk3) takes in a tuple of values representing the primary key of some record in your table. In this case the primary key consists of three separate columns (thus a composite key), though the record they identify will always be unique within your table. That's sort of the point of it being a primary key. Read the docs for an explanation of what parameters get() expects. Of course you could use filter() and get the same result. I'm not sure but I guess the actual SQL executed by SA should look exactly the same, set echo to True and have a look. The difference being of course that you can use filter to return more then one record. I'm not sure how get() works on databases that don't need primary keys. I'm guessing it won't. Maybe it's in the docs. Hope that cleared things up. Regards, Christian 2009/3/23 Dan F danielfal...@gmail.com: Hi, Is there a difference between using query.get(ident) and using query.filter(MyClass.id.in_())? Specifically with regard to how the mapper is used? If I'm right in my assumption, get() uses the map to avoid extra lookups, but I question whether the filter method is doing the same. If I'm correct, shouldn't there be a way to get() multiple objects at the same time? Thanks. --~--~-~--~~~---~--~~ 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] Extended Query subclass with add_named_column method
Hello, SQLAlchemy people, So I spoke to jek on IRC to see if there was a way to use add_column without causing the query to return a RowTuple and it doesn't look like there is, so I wrote this: class AdditiveQuery(Query): Extended sqlalchemy.orm.Query class with add_named_column method to add a column onto a query but store the values on the main RowProxy object instead of yielding RowTuples as add_column does: q = AdditiveQuery(FooModel, session=some_session) q = q.join((BarModel, FooModel.x == BarModel.x)) q = q.add_named_column(BarModel.baz, 'bar_baz') for row in q: print q.bar_baz def __init__(self, *args, **kwargs): self._named_columns = [] super(AdditiveQuery, self).__init__(*args, **kwargs) def add_named_column(self, column, alias=None): if alias is None: alias = column.key if alias in self._named_columns: raise ValueError(Alias %s already in use. % (alias,)) self._named_columns.append(alias) return self.add_column(column) def __iter__(self): def g(it): checked = False for rows in it: row = rows[0] rest = rows[1:] for alias, value in zip(self._named_columns, rest): if not checked and hasattr(row, alias): raise ValueError(Alias %s already exists on original row object. % (alias,)) setattr(row, alias, value) yield row checked = True it = super(AdditiveQuery, self).__iter__() return g(it) The only immediate issue I can think of with this is that if you do AdditiveQuery(FooModel, BarModel) then this is going to blow up as it'll try to call setattr on a RowTuple - I'm not sure whether to a) just let this happen, b) take preventative measures and raise a more useful exception, c) allow specifying exactly which entity to attach the named_column onto. Any suggestions welcome and, of course, if any of the SA boys want to stick this into SQLAlchemy, you're more than welcome. Thanks ! --~--~-~--~~~---~--~~ 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 mix declarative_base with orm.mapper?
a class that doesn't extend your declarative base would have to be mapped explicitly using the mapper() function, otherwise it can't be referenced within a relation(). On Mar 31, 2009, at 8:04 AM, Alex Maslov wrote: Hello, We have been trying to mix a class inherited from declarative_base() (Country) with plain Python classes mapped using orm.mapper. We're getting the error: UnmappedClassError: Class 'doupy.model.objects.Country' is not mapped code: Base = declarative_base() class Country(object): __tablename__ = 'countries' ... Base.metadata.bind = engine We also tried to create Base using Base = declarative_base(bind=engine) and Base = declarative_base(metadata=meta) with the same error. Full traceback below: File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/ __init__.py', line 73 in lambda return property(fget=lambda self: self.session.query(cls)) File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/session.py', line 898 in query return self._query_cls(entities, self, **kwargs) File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py', line 91 in __init__ self._set_entities(entities) File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py', line 100 in _set_entities self.__setup_aliasizers(self._entities) File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py', line 114 in __setup_aliasizers mapper, selectable, is_aliased_class = _entity_info(entity) File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py', line 485 in _entity_info mapper = class_mapper(entity, compile) File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py', line 560 in class_mapper mapper = mapper.compile() File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/mapper.py', line 662 in compile mapper._post_configure_properties() File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/mapper.py', line 691 in _post_configure_properties prop.init() File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/interfaces.py', line 408 in init self.do_init() File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/properties.py', line 707 in do_init self._get_target() File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/properties.py', line 719 in _get_target self.mapper = mapper.class_mapper(self.argument, compile=False) File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site- packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py', line 557 in class_mapper raise exc.UnmappedClassError(class_) UnmappedClassError: Class 'doupy.model.objects.Country' is not mapped Any help is very much appreciated! Regards, Alex --~--~-~--~~~---~--~~ 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: Postgres: could not identify an ordering operator for type ... (for SQLA-created table)
my guess is that its confusing the names virtualization between the
table and column name. im guessing the field you send to
order_by() is a string otherwise it would render it as
tablename.columnname and would be quoted for the upper cased
Virtualization.
On Mar 31, 2009, at 5:44 AM, Marcin Krol wrote:
Hello everyone,
Now that I have my PG db filled, I'm encountering this exception while
trying to use it:
ProgrammingError: (ProgrammingError) could not identify an ordering
operator for type virtualization
HINT: Use an explicit ordering operator or modify the query.
'SELECT hosts.id AS hosts_id, hosts.IP AS hosts_IP,
hosts.HostName AS hosts_HostName, hosts.Location AS
hosts_Location, hosts.Architecture_id AS hosts_Architecture_id,
hosts.OS_Kind_id AS hosts_OS_Kind_id, hosts.OS_version_id AS
hosts_OS_version_id, hosts.Additional_info AS
hosts_Additional_info, hosts.Column_12 AS hosts_Column_12,
hosts.Column_13 AS hosts_Column_13, hosts.Email_id AS
hosts_Email_id, hosts.Username AS hosts_Username,
hosts.Password
AS hosts_Password, hosts.Alias AS hosts_Alias,
hosts.Virtualization_id AS hosts_Virtualization_id,
hosts.Shareable AS hosts_Shareable,
hosts.Shareable_between_projects AS
hosts_Shareable_between_projects, hosts.Notes AS hosts_Notes,
hosts.CPU AS hosts_CPU, hosts.RAM AS hosts_RAM,
hosts.Column_24 AS hosts_Column_24, hosts.Batch AS
hosts_Batch,
hosts.ASSET AS hosts_ASSET, hosts.Owner AS hosts_Owner,
hosts.SSH_KEY_PRESENT AS hosts_SSH_KEY_PRESENT,
hosts.Machine_Type_Model AS hosts_Machine_Type_Model,
hosts.MAC_ADDRESS_ETH_0 AS hosts_MAC_ADDRESS_ETH_0,
hosts.Physical_Box AS hosts_Physical_Box, hosts.Up_n_running AS
hosts_Up_n_running, hosts.Available AS hosts_Available,
hosts.Earliest_reservation_id AS hosts_Earliest_reservation_id,
hosts.Project_id AS hosts_Project_id, architecture.id AS
architecture_id, architecture.Architecture AS
architecture_Architecture, os_kind.id AS os_kind_id,
os_kind.OS_Kind
AS os_kind_OS_Kind, os_version.id AS os_version_id,
os_version.OS_version AS os_version_OS_version,
virtualization.id AS
virtualization_id, virtualization.Virtualization AS
virtualization_Virtualization, virtualization.color AS
virtualization_color, project.id AS project_id, project.Project AS
project_Project \nFROM hosts, architecture, os_kind, os_version,
virtualization, project, email \nWHERE hosts.Architecture_id =
architecture.id AND hosts.OS_Kind_id = os_kind.id AND
hosts.OS_version_id = os_version.id AND hosts.Email_id = email.id
AND hosts.Virtualization_id = virtualization.id AND
hosts.Project_id
= project.id AND hosts.Up_n_running = %(Up_n_running_1)s AND
hosts.Shareable = %(Shareable_1)s ORDER BY Virtualization DESC, IP
ASC' {'Shareable_1': True, 'Up_n_running_1': True}
Note the 'ORDER BY Virtualization' clause. The Postgres docs say:
33.13.5. System Dependencies on Operator Classes
PostgreSQL uses operator classes to infer the properties of operators
in more ways than just whether they can be used with indexes.
Therefore,
you might want to create operator classes even if you have no
intention
of indexing any columns of your data type.
In particular, there are SQL features such as ORDER BY and DISTINCT
that
require comparison and sorting of values. To implement these
features on
a user-defined data type, PostgreSQL looks for the default B-tree
operator class for the data type. The equals member of this operator
class defines the system's notion of equality of values for GROUP BY
and
DISTINCT, and the sort ordering imposed by the operator class defines
the default ORDER BY ordering.
Comparison of arrays of user-defined types also relies on the
semantics
defined by the default B-tree operator class.
If there is no default B-tree operator class for a data type, the
system
will look for a default hash operator class. But since that kind of
operator class only provides equality, in practice it is only enough
to
support array equality.
When there is no default operator class for a data type, you will get
errors like could not identify an ordering operator if you try to
use
these SQL features with the data type.
( http://www.postgresql.org/docs/7.4/static/xindex.html )
But I am not using any user-defined data class. It's just a table with
VARCHAR for virtualization.Virtualization column, as created by SQLA:
reservations=# \d virtualization
Table public.virtualization
Column | Type|
Modifiers
+---
+-
id | integer | not null default
nextval('virtualization_id_seq'::regclass)
Virtualization | character varying |
color | character varying |
Indexes:
virtualization_pkey PRIMARY KEY, btree (id)
The SQLA query in question:
selectexpr =
[sqlalchemy] Re: Postgres: could not identify an ordering operator for type ... (for SQLA-created table)
my guess is that its confusing the names virtualization between the
table and column name. im guessing the field you send to
order_by() is a string otherwise it would render it as
tablename.columnname and would be quoted for the upper cased
Virtualization.
On Mar 31, 2009, at 5:44 AM, Marcin Krol wrote:
Hello everyone,
Now that I have my PG db filled, I'm encountering this exception while
trying to use it:
ProgrammingError: (ProgrammingError) could not identify an ordering
operator for type virtualization
HINT: Use an explicit ordering operator or modify the query.
'SELECT hosts.id AS hosts_id, hosts.IP AS hosts_IP,
hosts.HostName AS hosts_HostName, hosts.Location AS
hosts_Location, hosts.Architecture_id AS hosts_Architecture_id,
hosts.OS_Kind_id AS hosts_OS_Kind_id, hosts.OS_version_id AS
hosts_OS_version_id, hosts.Additional_info AS
hosts_Additional_info, hosts.Column_12 AS hosts_Column_12,
hosts.Column_13 AS hosts_Column_13, hosts.Email_id AS
hosts_Email_id, hosts.Username AS hosts_Username,
hosts.Password
AS hosts_Password, hosts.Alias AS hosts_Alias,
hosts.Virtualization_id AS hosts_Virtualization_id,
hosts.Shareable AS hosts_Shareable,
hosts.Shareable_between_projects AS
hosts_Shareable_between_projects, hosts.Notes AS hosts_Notes,
hosts.CPU AS hosts_CPU, hosts.RAM AS hosts_RAM,
hosts.Column_24 AS hosts_Column_24, hosts.Batch AS
hosts_Batch,
hosts.ASSET AS hosts_ASSET, hosts.Owner AS hosts_Owner,
hosts.SSH_KEY_PRESENT AS hosts_SSH_KEY_PRESENT,
hosts.Machine_Type_Model AS hosts_Machine_Type_Model,
hosts.MAC_ADDRESS_ETH_0 AS hosts_MAC_ADDRESS_ETH_0,
hosts.Physical_Box AS hosts_Physical_Box, hosts.Up_n_running AS
hosts_Up_n_running, hosts.Available AS hosts_Available,
hosts.Earliest_reservation_id AS hosts_Earliest_reservation_id,
hosts.Project_id AS hosts_Project_id, architecture.id AS
architecture_id, architecture.Architecture AS
architecture_Architecture, os_kind.id AS os_kind_id,
os_kind.OS_Kind
AS os_kind_OS_Kind, os_version.id AS os_version_id,
os_version.OS_version AS os_version_OS_version,
virtualization.id AS
virtualization_id, virtualization.Virtualization AS
virtualization_Virtualization, virtualization.color AS
virtualization_color, project.id AS project_id, project.Project AS
project_Project \nFROM hosts, architecture, os_kind, os_version,
virtualization, project, email \nWHERE hosts.Architecture_id =
architecture.id AND hosts.OS_Kind_id = os_kind.id AND
hosts.OS_version_id = os_version.id AND hosts.Email_id = email.id
AND hosts.Virtualization_id = virtualization.id AND
hosts.Project_id
= project.id AND hosts.Up_n_running = %(Up_n_running_1)s AND
hosts.Shareable = %(Shareable_1)s ORDER BY Virtualization DESC, IP
ASC' {'Shareable_1': True, 'Up_n_running_1': True}
Note the 'ORDER BY Virtualization' clause. The Postgres docs say:
33.13.5. System Dependencies on Operator Classes
PostgreSQL uses operator classes to infer the properties of operators
in more ways than just whether they can be used with indexes.
Therefore,
you might want to create operator classes even if you have no
intention
of indexing any columns of your data type.
In particular, there are SQL features such as ORDER BY and DISTINCT
that
require comparison and sorting of values. To implement these
features on
a user-defined data type, PostgreSQL looks for the default B-tree
operator class for the data type. The equals member of this operator
class defines the system's notion of equality of values for GROUP BY
and
DISTINCT, and the sort ordering imposed by the operator class defines
the default ORDER BY ordering.
Comparison of arrays of user-defined types also relies on the
semantics
defined by the default B-tree operator class.
If there is no default B-tree operator class for a data type, the
system
will look for a default hash operator class. But since that kind of
operator class only provides equality, in practice it is only enough
to
support array equality.
When there is no default operator class for a data type, you will get
errors like could not identify an ordering operator if you try to
use
these SQL features with the data type.
( http://www.postgresql.org/docs/7.4/static/xindex.html )
But I am not using any user-defined data class. It's just a table with
VARCHAR for virtualization.Virtualization column, as created by SQLA:
reservations=# \d virtualization
Table public.virtualization
Column | Type|
Modifiers
+---
+-
id | integer | not null default
nextval('virtualization_id_seq'::regclass)
Virtualization | character varying |
color | character varying |
Indexes:
virtualization_pkey PRIMARY KEY, btree (id)
The SQLA query in question:
selectexpr =
[sqlalchemy] Re: Extended Query subclass with add_named_column method
wouldn't this be accomplished more simply using contains_eager() ? On Mar 31, 2009, at 9:12 AM, Bob Farrell wrote: Hello, SQLAlchemy people, So I spoke to jek on IRC to see if there was a way to use add_column without causing the query to return a RowTuple and it doesn't look like there is, so I wrote this: class AdditiveQuery(Query): Extended sqlalchemy.orm.Query class with add_named_column method to add a column onto a query but store the values on the main RowProxy object instead of yielding RowTuples as add_column does: q = AdditiveQuery(FooModel, session=some_session) q = q.join((BarModel, FooModel.x == BarModel.x)) q = q.add_named_column(BarModel.baz, 'bar_baz') for row in q: print q.bar_baz def __init__(self, *args, **kwargs): self._named_columns = [] super(AdditiveQuery, self).__init__(*args, **kwargs) def add_named_column(self, column, alias=None): if alias is None: alias = column.key if alias in self._named_columns: raise ValueError(Alias %s already in use. % (alias,)) self._named_columns.append(alias) return self.add_column(column) def __iter__(self): def g(it): checked = False for rows in it: row = rows[0] rest = rows[1:] for alias, value in zip(self._named_columns, rest): if not checked and hasattr(row, alias): raise ValueError(Alias %s already exists on original row object. % (alias,)) setattr(row, alias, value) yield row checked = True it = super(AdditiveQuery, self).__iter__() return g(it) The only immediate issue I can think of with this is that if you do AdditiveQuery(FooModel, BarModel) then this is going to blow up as it'll try to call setattr on a RowTuple - I'm not sure whether to a) just let this happen, b) take preventative measures and raise a more useful exception, c) allow specifying exactly which entity to attach the named_column onto. Any suggestions welcome and, of course, if any of the SA boys want to stick this into SQLAlchemy, you're more than welcome. Thanks ! --~--~-~--~~~---~--~~ 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: Extended Query subclass with add_named_column method
Yes, if this has been defined on the mapper. Generally I do a lot of just using SQLAlchemy to as a way of writing SQL in Python code that can be passed around for dynamically building up queries so my mappers tend to be quite bare - or are you suggesting this can be done without configuring the relation on the mapper ? If there's a way to do this in SQLAlchemy that doesn't need extra mapper config then I'm all ears. :-) On Mar 31, 3:22 pm, Michael Bayer mike...@zzzcomputing.com wrote: wouldn't this be accomplished more simply using contains_eager() ? On Mar 31, 2009, at 9:12 AM, Bob Farrell wrote: Hello, SQLAlchemy people, So I spoke to jek on IRC to see if there was a way to use add_column without causing the query to return a RowTuple and it doesn't look like there is, so I wrote this: class AdditiveQuery(Query): Extended sqlalchemy.orm.Query class with add_named_column method to add a column onto a query but store the values on the main RowProxy object instead of yielding RowTuples as add_column does: q = AdditiveQuery(FooModel, session=some_session) q = q.join((BarModel, FooModel.x == BarModel.x)) q = q.add_named_column(BarModel.baz, 'bar_baz') for row in q: print q.bar_baz def __init__(self, *args, **kwargs): self._named_columns = [] super(AdditiveQuery, self).__init__(*args, **kwargs) def add_named_column(self, column, alias=None): if alias is None: alias = column.key if alias in self._named_columns: raise ValueError(Alias %s already in use. % (alias,)) self._named_columns.append(alias) return self.add_column(column) def __iter__(self): def g(it): checked = False for rows in it: row = rows[0] rest = rows[1:] for alias, value in zip(self._named_columns, rest): if not checked and hasattr(row, alias): raise ValueError(Alias %s already exists on original row object. % (alias,)) setattr(row, alias, value) yield row checked = True it = super(AdditiveQuery, self).__iter__() return g(it) The only immediate issue I can think of with this is that if you do AdditiveQuery(FooModel, BarModel) then this is going to blow up as it'll try to call setattr on a RowTuple - I'm not sure whether to a) just let this happen, b) take preventative measures and raise a more useful exception, c) allow specifying exactly which entity to attach the named_column onto. Any suggestions welcome and, of course, if any of the SA boys want to stick this into SQLAlchemy, you're more than welcome. Thanks ! --~--~-~--~~~---~--~~ 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: Extended Query subclass with add_named_column method
no, you'd need the mapping to be set up.So yes, if you need to compose your result objects together in some way that is specific to your use case and has no relationship to your mappings or any standard SQLAlchemy feature, you need to either post-process the result of Query or create a subclass that does what you want. On Mar 31, 2009, at 9:41 AM, Bob Farrell wrote: Yes, if this has been defined on the mapper. Generally I do a lot of just using SQLAlchemy to as a way of writing SQL in Python code that can be passed around for dynamically building up queries so my mappers tend to be quite bare - or are you suggesting this can be done without configuring the relation on the mapper ? If there's a way to do this in SQLAlchemy that doesn't need extra mapper config then I'm all ears. :-) On Mar 31, 3:22 pm, Michael Bayer mike...@zzzcomputing.com wrote: wouldn't this be accomplished more simply using contains_eager() ? On Mar 31, 2009, at 9:12 AM, Bob Farrell wrote: Hello, SQLAlchemy people, So I spoke to jek on IRC to see if there was a way to use add_column without causing the query to return a RowTuple and it doesn't look like there is, so I wrote this: class AdditiveQuery(Query): Extended sqlalchemy.orm.Query class with add_named_column method to add a column onto a query but store the values on the main RowProxy object instead of yielding RowTuples as add_column does: q = AdditiveQuery(FooModel, session=some_session) q = q.join((BarModel, FooModel.x == BarModel.x)) q = q.add_named_column(BarModel.baz, 'bar_baz') for row in q: print q.bar_baz def __init__(self, *args, **kwargs): self._named_columns = [] super(AdditiveQuery, self).__init__(*args, **kwargs) def add_named_column(self, column, alias=None): if alias is None: alias = column.key if alias in self._named_columns: raise ValueError(Alias %s already in use. % (alias,)) self._named_columns.append(alias) return self.add_column(column) def __iter__(self): def g(it): checked = False for rows in it: row = rows[0] rest = rows[1:] for alias, value in zip(self._named_columns, rest): if not checked and hasattr(row, alias): raise ValueError(Alias %s already exists on original row object. % (alias,)) setattr(row, alias, value) yield row checked = True it = super(AdditiveQuery, self).__iter__() return g(it) The only immediate issue I can think of with this is that if you do AdditiveQuery(FooModel, BarModel) then this is going to blow up as it'll try to call setattr on a RowTuple - I'm not sure whether to a) just let this happen, b) take preventative measures and raise a more useful exception, c) allow specifying exactly which entity to attach the named_column onto. Any suggestions welcome and, of course, if any of the SA boys want to stick this into SQLAlchemy, you're more than welcome. Thanks ! --~--~-~--~~~---~--~~ 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 mix declarative_base with orm.mapper?
Michael,
Thanks for the reply. Sorry, I am incredibly stupid for having forgotten
to replace 'object' with Base.
Anyway, after I have inherited Country from Base it looks like SQLAlchemy
cannot detect the relation:
URL: http://localhost:5010/pages/subscribe/
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/WebError-0.10.1-py2.5.egg/weberror/evalexception.py',
line 431 in respond
app_iter = self.application(environ, detect_start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Paste-1.7.2-py2.5.egg/paste/httpexceptions.py',
line 636 in __call__
return self.application(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/firepython/middleware.py',
line 288 in __call__
app_iter = self._app(environ, faked_start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/config/middleware.py',
line 72 in __call__
return self.app(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/config/middleware.py',
line 113 in __call__
resp = self.app(environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Beaker-1.2.2-py2.5.egg/beaker/middleware.py',
line 81 in __call__
return self.app(environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Beaker-1.2.2-py2.5.egg/beaker/middleware.py',
line 160 in __call__
return self.wrap_app(environ, session_start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Routes-1.10.3-py2.5.egg/routes/middleware.py',
line 130 in __call__
response = self.app(environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Paste-1.7.2-py2.5.egg/paste/registry.py',
line 350 in __call__
app_iter = self.application(environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Pylons-0.9.7-py2.5.egg/pylons/wsgiapp.py',
line 125 in __call__
response = self.dispatch(controller, environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Pylons-0.9.7-py2.5.egg/pylons/wsgiapp.py',
line 324 in dispatch
return controller(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/base.py', line 258
in __call__
self.request_setup()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/base.py', line 298
in request_setup
c.wpuser = setup_request_user(request, get_database)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/auth.py', line 48
in setup_request_user
g.read_wordpress_options(db)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/app_globals.py',
line 98 in read_wordpress_options
self.siteurl = db.get_wp_option('siteurl')
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py', line 150
in get_wp_option
return get_option()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py', line 148
in get_option
option = self.query_options.filter_by(option_name=name).one()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py', line 74
in lambda
return property(fget=lambda self: self.session.query(cls))
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/session.py',
line 898 in query
return self._query_cls(entities, self, **kwargs)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py',
line 91 in __init__
self._set_entities(entities)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py',
line 100 in _set_entities
self.__setup_aliasizers(self._entities)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py',
line 114 in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py',
line 485 in _entity_info
mapper = class_mapper(entity, compile)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py',
line 560 in class_mapper
mapper = mapper.compile()
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/mapper.py',
line 662 in compile
mapper._post_configure_properties()
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/mapper.py',
line 691 in _post_configure_properties
prop.init()
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/interfaces.py',
line 408 in init
self.do_init()
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/properties.py',
line 709
[sqlalchemy] Re: How to mix declarative_base with orm.mapper?
user_profiles_tbl and your declarative_meta need to share same
MetaData instance.
On Mar 31, 2009, at 9:56 AM, Alexey Maslov wrote:
Michael,
Thanks for the reply. Sorry, I am incredibly stupid for having
forgotten to replace 'object' with Base.
Anyway, after I have inherited Country from Base it looks like
SQLAlchemy cannot detect the relation:
URL: http://localhost:5010/pages/subscribe/
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/WebError-0.10.1-py2.5.egg/weberror/evalexception.py', line
431 in respond
app_iter = self.application(environ, detect_start_response)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/Paste-1.7.2-py2.5.egg/paste/httpexceptions.py', line 636 in
__call__
return self.application(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/firepython/
middleware.py', line 288 in __call__
app_iter = self._app(environ, faked_start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/config/
middleware.py', line 72 in __call__
return self.app(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/config/
middleware.py', line 113 in __call__
resp = self.app(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/Beaker-1.2.2-py2.5.egg/beaker/middleware.py', line 81 in
__call__
return self.app(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/Beaker-1.2.2-py2.5.egg/beaker/middleware.py', line 160 in
__call__
return self.wrap_app(environ, session_start_response)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/Routes-1.10.3-py2.5.egg/routes/middleware.py', line 130 in
__call__
response = self.app(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/Paste-1.7.2-py2.5.egg/paste/registry.py', line 350 in
__call__
app_iter = self.application(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/Pylons-0.9.7-py2.5.egg/pylons/wsgiapp.py', line 125 in
__call__
response = self.dispatch(controller, environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/Pylons-0.9.7-py2.5.egg/pylons/wsgiapp.py', line 324 in
dispatch
return controller(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/base.py',
line 258 in __call__
self.request_setup()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/base.py',
line 298 in request_setup
c.wpuser = setup_request_user(request, get_database)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/auth.py',
line 48 in setup_request_user
g.read_wordpress_options(db)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/
app_globals.py', line 98 in read_wordpress_options
self.siteurl = db.get_wp_option('siteurl')
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py',
line 150 in get_wp_option
return get_option()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py',
line 148 in get_option
option = self.query_options.filter_by(option_name=name).one()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py',
line 74 in lambda
return property(fget=lambda self: self.session.query(cls))
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/session.py', line
898 in query
return self._query_cls(entities, self, **kwargs)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py', line
91 in __init__
self._set_entities(entities)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py', line
100 in _set_entities
self.__setup_aliasizers(self._entities)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py', line
114 in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py', line
485 in _entity_info
mapper = class_mapper(entity, compile)
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py', line
560 in class_mapper
mapper = mapper.compile()
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/mapper.py', line
662 in compile
mapper._post_configure_properties()
File '/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-
packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/mapper.py', line
691 in
[sqlalchemy] Re: Getting FlushError trying to merge related objects
the stack trace would have said it all on this one, its just autoflush. fixed in r5875. On Mar 31, 2009, at 7:01 AM, Yassen Damyanov wrote: On Tue, Mar 31, 2009 at 1:03 AM, Yassen Damyanov yassen@gmail.com wrote: On Mon, Mar 30, 2009 at 5:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: can you produce a small test case illustrating this ? Sure, I'll do my best to produce one for you. Mike, A minimalistic test case demonstrating the issue can be downloaded from here: http://www.itlabs.bg/6548438dda3e83f0/merge_test.py This has been run against MySQL 5.x only (that's what I had at hand). It might well be that I do the things wrong... please let me know. THANK YOU! Looking forward to hear your verdict, Yassen --~--~-~--~~~---~--~~ 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 mix declarative_base with orm.mapper?
Thanks, it works when I added the MetaData instance to the
declarative_meta() call.
Have a great day!
Regards,
Alexey Maslov
On Tue, Mar 31, 2009 at 6:02 PM, Michael Bayer mike...@zzzcomputing.comwrote:
user_profiles_tbl and your declarative_meta need to share same MetaData
instance.
On Mar 31, 2009, at 9:56 AM, Alexey Maslov wrote:
Michael,
Thanks for the reply. Sorry, I am incredibly stupid for having forgotten
to replace 'object' with Base.
Anyway, after I have inherited Country from Base it looks like SQLAlchemy
cannot detect the relation:
URL: http://localhost:5010/pages/subscribe/
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/WebError-0.10.1-py2.5.egg/weberror/evalexception.py',
line 431 in respond
app_iter = self.application(environ, detect_start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Paste-1.7.2-py2.5.egg/paste/httpexceptions.py',
line 636 in __call__
return self.application(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/firepython/middleware.py',
line 288 in __call__
app_iter = self._app(environ, faked_start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/config/middleware.py',
line 72 in __call__
return self.app(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/config/middleware.py',
line 113 in __call__
resp = self.app(environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Beaker-1.2.2-py2.5.egg/beaker/middleware.py',
line 81 in __call__
return self.app(environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Beaker-1.2.2-py2.5.egg/beaker/middleware.py',
line 160 in __call__
return self.wrap_app(environ, session_start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Routes-1.10.3-py2.5.egg/routes/middleware.py',
line 130 in __call__
response = self.app(environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Paste-1.7.2-py2.5.egg/paste/registry.py',
line 350 in __call__
app_iter = self.application(environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Pylons-0.9.7-py2.5.egg/pylons/wsgiapp.py',
line 125 in __call__
response = self.dispatch(controller, environ, start_response)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/Pylons-0.9.7-py2.5.egg/pylons/wsgiapp.py',
line 324 in dispatch
return controller(environ, start_response)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/base.py', line 258
in __call__
self.request_setup()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/base.py', line 298
in request_setup
c.wpuser = setup_request_user(request, get_database)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/auth.py', line 48
in setup_request_user
g.read_wordpress_options(db)
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/lib/app_globals.py',
line 98 in read_wordpress_options
self.siteurl = db.get_wp_option('siteurl')
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py', line 150
in get_wp_option
return get_option()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py', line 148
in get_option
option = self.query_options.filter_by(option_name=name).one()
File '/Users/amaslov/Projects/dou-pylons/doupy/doupy/model/db.py', line 74
in lambda
return property(fget=lambda self: self.session.query(cls))
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/session.py',
line 898 in query
return self._query_cls(entities, self, **kwargs)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py',
line 91 in __init__
self._set_entities(entities)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py',
line 100 in _set_entities
self.__setup_aliasizers(self._entities)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/query.py',
line 114 in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py',
line 485 in _entity_info
mapper = class_mapper(entity, compile)
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/util.py',
line 560 in class_mapper
mapper = mapper.compile()
File
'/Users/amaslov/Projects/dou-pylons/py/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/mapper.py',
line 662 in compile
mapper._post_configure_properties()
File
[sqlalchemy] SqlSoup joins broken in 0.5.3
Heres some input that used to work, and the error that now happens from sqlalchemy.ext.sqlsoup import SqlSoup, MetaData db_uri = 'postgres://user:passw...@127.0.0.1/games' db = SqlSoup(db_uri) db.devmap_device.join(db.devmap_manufacturer, db.devmap_device.manufacturer_id == db.devmap_manufacturer.id).first() Traceback (most recent call last): File console, line 1, in module File string, line 1, in lambda File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\util.py, line 212, in go return fn(*args, **kw) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 870, in join return self.__join(props, outerjoin=False, create_aliases=aliased, from_joinpoint=from_joinpoint) File string, line 1, in lambda File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 52, in generate fn(self, *args[1:], **kw) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 1069, in __join clause = orm_join(clause, right_entity, onclause, isouter=outerjoin, join_to_left=join_to_left) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\util.py, line 439, in join return _ORMJoin(left, right, onclause, isouter, join_to_left) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\util.py, line 416, in __init__ expression.Join.__init__(self, left, right, onclause, isouter) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2482, in __init__ self.onclause = self._match_primaries(self.left, self.right) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2528, in _match_primaries return sql_util.join_condition(primary, secondary) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\util.py, line 114, in join_condition col = fk.get_referent(b) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\schema.py, line 860, in get_referent return table.corresponding_column(self.column) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2683, in __getattr__ return getattr(self.element, attr) AttributeError: '_BinaryExpression' object has no attribute 'corresponding_column' --~--~-~--~~~---~--~~ 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] Using SQLAlchemy to build sql files (without execution)
Hello all, I'm working on a project for which I need to create sql files (the sort that you get from a database dump) without actually connecting to any database. The resulting code must be compatible with MySQL. My first inclination was to write a bunch of functions that contain sql code templates and substitute dynamic values where needed. I then came across SQLAlchemy and wanted to see if could use it to make this task a little easier. So far I've been reading the documentation on meta data and running some tests. My guess is that I need to define the statements and compile them with MySQLDialect. This, however, doesn't seem to be working. I tried calling compile on a Table object with dialect set to MySQLDialect(). When the result is printed there is nothing there (probably because I have no idea what I'm doing :)). Can anyone provide some tips on whether what I'm trying to do is possible, and if so, what is the best way to go about it? Basically, the output files will be a combination of create database, create table, insert, and update statements. I should also mention that I'm using Python 2.6 on Windows. The latest stable MySQL-Python extension doesn't support 2.6, and there doesn't seem to be a test version available for Windows. Even if the database API was available, there is no database for me to connect to. The goal is to generate these files on the local machine without depending on any external resources. Eventually, it may be possible to execute this code directly on the database server, which is why I think using SQLAlchemy from the beginning would make that transition much easier. Thanks for any help. - Max --~--~-~--~~~---~--~~ 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: SqlSoup joins broken in 0.5.3
You can't just throw filter expressions into the join call in modern sqla. Try db.devmap_device.join(db.devmap_manufacturer).filter(db.devmap_device.manufacturer_id == db.devmap_manufacturer.id).first() -Jonathan On Tue, Mar 31, 2009 at 8:23 AM, Stu.Axon stu.a...@gmail.com wrote: Heres some input that used to work, and the error that now happens from sqlalchemy.ext.sqlsoup import SqlSoup, MetaData db_uri = 'postgres://user:passw...@127.0.0.1/games' db = SqlSoup(db_uri) db.devmap_device.join(db.devmap_manufacturer, db.devmap_device.manufacturer_id == db.devmap_manufacturer.id).first() Traceback (most recent call last): File console, line 1, in module File string, line 1, in lambda File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\util.py, line 212, in go return fn(*args, **kw) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 870, in join return self.__join(props, outerjoin=False, create_aliases=aliased, from_joinpoint=from_joinpoint) File string, line 1, in lambda File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 52, in generate fn(self, *args[1:], **kw) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 1069, in __join clause = orm_join(clause, right_entity, onclause, isouter=outerjoin, join_to_left=join_to_left) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\util.py, line 439, in join return _ORMJoin(left, right, onclause, isouter, join_to_left) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\util.py, line 416, in __init__ expression.Join.__init__(self, left, right, onclause, isouter) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2482, in __init__ self.onclause = self._match_primaries(self.left, self.right) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2528, in _match_primaries return sql_util.join_condition(primary, secondary) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\util.py, line 114, in join_condition col = fk.get_referent(b) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\schema.py, line 860, in get_referent return table.corresponding_column(self.column) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2683, in __getattr__ return getattr(self.element, attr) AttributeError: '_BinaryExpression' object has no attribute 'corresponding_column' --~--~-~--~~~---~--~~ 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: Postgres: could not identify an ordering operator for type ... (for SQLA-created table)
Michael Bayer wrote:
my guess is that its confusing the names virtualization between the
table and column name. im guessing the field you send to
order_by() is a string
Correct. I really have not much choice in the matter without somewhat
tedious programming, because column for order by comes from web app
input, and so it's obviously most straightforward to use a string...
otherwise it would render it as
tablename.columnname and would be quoted for the upper cased
Virtualization.
The thing is, for PG it would be useful to double-quote strings as well:
It turns out to be Postgres-specific problem, I asked on PG-general
users group and the reply is that it is standard quirk of Postgres:
Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
This is works-as-designed problem:
reservations=# SELECT * FROM virtualization ORDER BY Virtualization;
ERROR: could not identify an ordering operator for type virtualization
HINT: Use an explicit ordering operator or modify the query.
reservations=# SELECT * FROM virtualization ORDER BY
virtualization.Virtualization;
ERROR: column virtualization.virtualization does not exist
But:
reservations=# SELECT * FROM virtualization ORDER BY Virtualization;
id | Virtualization | color
+-+-
1 | BOX | #FAFAFA
17 | BOX | #FAFAFA
9 | BOX ESX HOST| #FAFAFA
7 | BOX MSVS HOST | #FAFAFA
18 | BOX MSVS HOST | #FAFAFA
So there's a humble request/proposal from me for you: would it be
possible for SQLA to always quote column names in ORDER BY clauses for
PG backend, even when it's a string?
In the meantime I will probably just rename the columns to lowercase...
Regards,
mk
On Mar 31, 2009, at 5:44 AM, Marcin Krol wrote:
Hello everyone,
Now that I have my PG db filled, I'm encountering this exception while
trying to use it:
ProgrammingError: (ProgrammingError) could not identify an ordering
operator for type virtualization
HINT: Use an explicit ordering operator or modify the query.
'SELECT hosts.id AS hosts_id, hosts.IP AS hosts_IP,
hosts.HostName AS hosts_HostName, hosts.Location AS
hosts_Location, hosts.Architecture_id AS hosts_Architecture_id,
hosts.OS_Kind_id AS hosts_OS_Kind_id, hosts.OS_version_id AS
hosts_OS_version_id, hosts.Additional_info AS
hosts_Additional_info, hosts.Column_12 AS hosts_Column_12,
hosts.Column_13 AS hosts_Column_13, hosts.Email_id AS
hosts_Email_id, hosts.Username AS hosts_Username,
hosts.Password
AS hosts_Password, hosts.Alias AS hosts_Alias,
hosts.Virtualization_id AS hosts_Virtualization_id,
hosts.Shareable AS hosts_Shareable,
hosts.Shareable_between_projects AS
hosts_Shareable_between_projects, hosts.Notes AS hosts_Notes,
hosts.CPU AS hosts_CPU, hosts.RAM AS hosts_RAM,
hosts.Column_24 AS hosts_Column_24, hosts.Batch AS
hosts_Batch,
hosts.ASSET AS hosts_ASSET, hosts.Owner AS hosts_Owner,
hosts.SSH_KEY_PRESENT AS hosts_SSH_KEY_PRESENT,
hosts.Machine_Type_Model AS hosts_Machine_Type_Model,
hosts.MAC_ADDRESS_ETH_0 AS hosts_MAC_ADDRESS_ETH_0,
hosts.Physical_Box AS hosts_Physical_Box, hosts.Up_n_running AS
hosts_Up_n_running, hosts.Available AS hosts_Available,
hosts.Earliest_reservation_id AS hosts_Earliest_reservation_id,
hosts.Project_id AS hosts_Project_id, architecture.id AS
architecture_id, architecture.Architecture AS
architecture_Architecture, os_kind.id AS os_kind_id,
os_kind.OS_Kind
AS os_kind_OS_Kind, os_version.id AS os_version_id,
os_version.OS_version AS os_version_OS_version,
virtualization.id AS
virtualization_id, virtualization.Virtualization AS
virtualization_Virtualization, virtualization.color AS
virtualization_color, project.id AS project_id, project.Project AS
project_Project \nFROM hosts, architecture, os_kind, os_version,
virtualization, project, email \nWHERE hosts.Architecture_id =
architecture.id AND hosts.OS_Kind_id = os_kind.id AND
hosts.OS_version_id = os_version.id AND hosts.Email_id = email.id
AND hosts.Virtualization_id = virtualization.id AND
hosts.Project_id
= project.id AND hosts.Up_n_running = %(Up_n_running_1)s AND
hosts.Shareable = %(Shareable_1)s ORDER BY Virtualization DESC, IP
ASC' {'Shareable_1': True, 'Up_n_running_1': True}
Note the 'ORDER BY Virtualization' clause. The Postgres docs say:
33.13.5. System Dependencies on Operator Classes
PostgreSQL uses operator classes to infer the properties of operators
in more ways than just whether they can be used with indexes.
Therefore,
you might want to create operator classes even if you have no
intention
of indexing any columns of your data type.
In particular, there are SQL features such as ORDER BY and DISTINCT
that
require comparison and sorting of values. To implement these
features on
a user-defined data
[sqlalchemy] Re: SqlSoup joins broken in 0.5.3
oh, you could send that as a tuple to join, i.e. db.devmap_device.join((db.devmap_manufacturer, db.devmap_device.manufacturer_id == db.devmap_manufacturer.id)).first() this because join() accepts a list of join conditions, a composite condition is sent as a tuple. On Mar 31, 2009, at 11:12 AM, Jonathan Ellis wrote: You can't just throw filter expressions into the join call in modern sqla. Try db .devmap_device .join(db.devmap_manufacturer).filter(db.devmap_device.manufacturer_id == db.devmap_manufacturer.id).first() -Jonathan On Tue, Mar 31, 2009 at 8:23 AM, Stu.Axon stu.a...@gmail.com wrote: Heres some input that used to work, and the error that now happens from sqlalchemy.ext.sqlsoup import SqlSoup, MetaData db_uri = 'postgres://user:passw...@127.0.0.1/games' db = SqlSoup(db_uri) db.devmap_device.join(db.devmap_manufacturer, db.devmap_device.manufacturer_id == db.devmap_manufacturer.id).first() Traceback (most recent call last): File console, line 1, in module File string, line 1, in lambda File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\util.py, line 212, in go return fn(*args, **kw) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 870, in join return self.__join(props, outerjoin=False, create_aliases=aliased, from_joinpoint=from_joinpoint) File string, line 1, in lambda File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 52, in generate fn(self, *args[1:], **kw) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 1069, in __join clause = orm_join(clause, right_entity, onclause, isouter=outerjoin, join_to_left=join_to_left) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\util.py, line 439, in join return _ORMJoin(left, right, onclause, isouter, join_to_left) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\util.py, line 416, in __init__ expression.Join.__init__(self, left, right, onclause, isouter) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2482, in __init__ self.onclause = self._match_primaries(self.left, self.right) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2528, in _match_primaries return sql_util.join_condition(primary, secondary) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\util.py, line 114, in join_condition col = fk.get_referent(b) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\schema.py, line 860, in get_referent return table.corresponding_column(self.column) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2683, in __getattr__ return getattr(self.element, attr) AttributeError: '_BinaryExpression' object has no attribute 'corresponding_column' --~--~-~--~~~---~--~~ 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: Postgres: could not identify an ordering operator for type ... (for SQLA-created table)
On Mar 31, 2009, at 11:13 AM, Marcin Krol wrote: So there's a humble request/proposal from me for you: would it be possible for SQLA to always quote column names in ORDER BY clauses for PG backend, even when it's a string? In the meantime I will probably just rename the columns to lowercase... we quote column names that are sent as mixed case, since we assume the same case sensitivity rules as PG. but if you're sending a string to ORDER BY, we have no idea what that is. It could be call_some_function(virtual.Virtualization, 12) or something like that, and we aren't parsing that. You should be sending SQL expressions and not strings to order_by() if you'd like SQLA to know something about it. --~--~-~--~~~---~--~~ 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 FlushError trying to merge related objects
On Tue, Mar 31, 2009 at 6:03 PM, Michael Bayer mike...@zzzcomputing.com wrote: the stack trace would have said it all on this one, its just autoflush. fixed in r5875. Works like a charm with r5875; thank you, Mike! Y. --~--~-~--~~~---~--~~ 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: Using SQLAlchemy to build sql files (without execution)
Max, I am an SA newbie so you should not take my words too seriously. So here's what I think: First, the only way to know and be sure that your code runs fine is to run it against a real database server. MySQL is not that hard to set up. (If you need help for that, let me know, I can help.) Second, if you mean that you want to generate the DDL (the tables creation statements), then how about having SA run against a real MySQL server and logging the SQL activity? (I guess this may sound ugly but it will work with sure.) If you want to generate anything more than simply DDL, it looks to me that a different approach will do a better job. (Advice from real experts is more than appreciated.) Yassen On Tue, Mar 31, 2009 at 6:30 PM, Maxim Khitrov mkhit...@gmail.com wrote: Hello all, I'm working on a project for which I need to create sql files (the sort that you get from a database dump) without actually connecting to any database. The resulting code must be compatible with MySQL. My first inclination was to write a bunch of functions that contain sql code templates and substitute dynamic values where needed. I then came across SQLAlchemy and wanted to see if could use it to make this task a little easier. So far I've been reading the documentation on meta data and running some tests. My guess is that I need to define the statements and compile them with MySQLDialect. This, however, doesn't seem to be working. I tried calling compile on a Table object with dialect set to MySQLDialect(). When the result is printed there is nothing there (probably because I have no idea what I'm doing :)). Can anyone provide some tips on whether what I'm trying to do is possible, and if so, what is the best way to go about it? Basically, the output files will be a combination of create database, create table, insert, and update statements. I should also mention that I'm using Python 2.6 on Windows. The latest stable MySQL-Python extension doesn't support 2.6, and there doesn't seem to be a test version available for Windows. Even if the database API was available, there is no database for me to connect to. The goal is to generate these files on the local machine without depending on any external resources. Eventually, it may be possible to execute this code directly on the database server, which is why I think using SQLAlchemy from the beginning would make that transition much easier. Thanks for any help. - Max --~--~-~--~~~---~--~~ 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: Using SQLAlchemy to build sql files (without execution)
you can use echo=True to metadata and/or logging to some extent. there was also some recipe about how to print some query's actual sql without issuing it, see recipes section at the site. but i'm not sure u can really do all you want without having a responding DB-api (which will at some point require a server). try, YMMV. svil www.svilendobrev.com On Tuesday 31 March 2009 22:24:16 Yassen Damyanov wrote: Max, I am an SA newbie so you should not take my words too seriously. So here's what I think: First, the only way to know and be sure that your code runs fine is to run it against a real database server. MySQL is not that hard to set up. (If you need help for that, let me know, I can help.) Second, if you mean that you want to generate the DDL (the tables creation statements), then how about having SA run against a real MySQL server and logging the SQL activity? (I guess this may sound ugly but it will work with sure.) If you want to generate anything more than simply DDL, it looks to me that a different approach will do a better job. (Advice from real experts is more than appreciated.) Yassen On Tue, Mar 31, 2009 at 6:30 PM, Maxim Khitrov mkhit...@gmail.com wrote: Hello all, I'm working on a project for which I need to create sql files (the sort that you get from a database dump) without actually connecting to any database. The resulting code must be compatible with MySQL. My first inclination was to write a bunch of functions that contain sql code templates and substitute dynamic values where needed. I then came across SQLAlchemy and wanted to see if could use it to make this task a little easier. So far I've been reading the documentation on meta data and running some tests. My guess is that I need to define the statements and compile them with MySQLDialect. This, however, doesn't seem to be working. I tried calling compile on a Table object with dialect set to MySQLDialect(). When the result is printed there is nothing there (probably because I have no idea what I'm doing :)). Can anyone provide some tips on whether what I'm trying to do is possible, and if so, what is the best way to go about it? Basically, the output files will be a combination of create database, create table, insert, and update statements. I should also mention that I'm using Python 2.6 on Windows. The latest stable MySQL-Python extension doesn't support 2.6, and there doesn't seem to be a test version available for Windows. Even if the database API was available, there is no database for me to connect to. The goal is to generate these files on the local machine without depending on any external resources. Eventually, it may be possible to execute this code directly on the database server, which is why I think using SQLAlchemy from the beginning would make that transition much easier. Thanks for any help. - Max --~--~-~--~~~---~--~~ 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: Using SQLAlchemy to build sql files (without execution)
On Mar 31, 2009, Maxim Khitrov mkhit...@gmail.com wrote: I should also mention that I'm using Python 2.6 on Windows. The latest stable MySQL-Python extension doesn't support 2.6, and there doesn't seem to be a test version available for Windows. The 1.2.2 Windows version of mysql-python should work flawlessly under Python 2.6. (I haven't tested that however, my Python is 2.5.) Y. --~--~-~--~~~---~--~~ 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.5.3 released
I noticed a significant performance increase between 0.5.2 and 0.5.3. While tracking down an unrelated issue I had written a dummy test that ran some SA code inside of a for i in range(1000): and going from 0.5.2 and 0.5.3 execution went from 14minutes to 12minutes. Keep in mind this was a very unscientific measurement, but still awesome. Michael Bayer wrote: I've been a little busy lately so we're a month off on this one, but there's a trove of bugfixes in this one, largely related to new features that are specific to the 0.5 series, as well as some behavioral enhancements to Query and declarative - read the changelog for details. I'm hammering on this release quite fiercely for a real project right now (which has driven a good chunk of the fixes) so it has my seal of approval. Download SQLAlchemy 0.5.3 at: http://www.sqlalchemy.org/download.html . 0.5.3 = - orm - The objects argument to session.flush() is deprecated. State which represents the linkage between a parent and child object does not support flushed status on one side of the link and not the other, so supporting this operation leads to misleading results. [ticket:1315] - Query now implements __clause_element__() which produces its selectable, which means a Query instance can be accepted in many SQL expressions, including col.in_(query), union(query1, query2), select([foo]).select_from(query), etc. - Query.join() can now construct multiple FROM clauses, if needed. Such as, query(A, B).join(A.x).join(B.y) might say SELECT A.*, B.* FROM A JOIN X, B JOIN Y. Eager loading can also tack its joins onto those multiple FROM clauses. [ticket:1337] - Fixed bug in dynamic_loader() where append/remove events after construction time were not being propagated to the UOW to pick up on flush(). [ticket:1347] - Fixed bug where column_prefix wasn't being checked before not mapping an attribute that already had class-level name present. - a session.expire() on a particular collection attribute will clear any pending backref additions as well, so that the next access correctly returns only what was present in the database. Presents some degree of a workaround for [ticket:1315], although we are considering removing the flush([objects]) feature altogether. - Session.scalar() now converts raw SQL strings to text() the same way Session.execute() does and accepts same alternative **kw args. - improvements to the determine direction logic of relation() such that the direction of tricky situations like mapper(A.join(B)) - relation- mapper(B) can be determined. - When flushing partial sets of objects using session.flush([somelist]), pending objects which remain pending after the operation won't inadvertently be added as persistent. [ticket:1306] - Added post_configure_attribute method to InstrumentationManager, so that the listen_for_events.py example works again. [ticket:1314] - a forward and complementing backwards reference which are both of the same direction, i.e. ONETOMANY or MANYTOONE, is now detected, and an error message is raised. Saves crazy CircularDependencyErrors later on. - Fixed bugs in Query regarding simultaneous selection of multiple joined-table inheritance entities with common base classes: - previously the adaption applied to B on A JOIN B would be erroneously partially applied to A. - comparisons on relations (i.e. A.related==someb) were not getting adapted when they should. - Other filterings, like query(A).join(A.bs).filter(B.foo=='bar'), were erroneously adapting B.foo as though it were an A. - Fixed adaptation of EXISTS clauses via any(), has(), etc. in conjunction with an aliased object on the left and of_type() on the right. [ticket:1325] - Added an attribute helper method ``set_committed_value`` in sqlalchemy.orm.attributes. Given an object, attribute name, and value, will set the value on the object as part of its committed state, i.e. state that is understood to have been loaded from the database. Helps with the creation of homegrown collection loaders and such. - Query won't fail with weakref error when a non-mapper/class instrumented descriptor is passed, raises Invalid column expession. - Query.group_by() properly takes into account aliasing applied to the FROM clause, such as with select_from(), using with_polymorphic(), or using from_self(). - sql - An alias() of a select() will convert to a scalar subquery
