[sqlalchemy] General questions of a newbee
Hi guys, I've done some programming, but I'm new to RDBMS and ORMs. I've read some documentation, but before diving in deeper and doing some tutorials, I'm trying to understand what can be done with SQLAlchemy and get a coarse understanding of how it works. Imagine some tables which are all related (like 'created by') to a user by a foreign key. If I query all tables by a certain user, I assume SQLAlchemy loads and creates all objects which have references in the column 'created by' to the certain user. Like a 'manual' eager loading. If I use the objects properties to follow the relations, does SQLA need to perform any more DB accesses? Or are the referenced objects directly referenced (maybe with properties that stores the direct reference after resolving after the first call)? How about backrefs? Would every call to those require a new SQL query under the hood? Or are those 'stored' in the ORM after the first call? I guess this would impact how to model parent-children relations. On the one hand it seems like an easy life to me if the parents don't need references to the children in the database, as children could be added without modifing the parents. One the other hand, how's the performance impact if you need to get the children by backref calls? Cheers, Jan -- 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: mapper error, and db object cannot be incremented
Thank you so much Michael. By the way SQLAlchemy and Mako template are
so cool. Thank you so much for all your efforts.
Liju.
On Jun 14, 9:38 am, Michael Bayer wrote:
> You're best starting with the declarative usage patterns described in the ORM
> tutorial athttp://www.sqlalchemy.org/docs/orm/tutorial.html, starting
> withhttp://www.sqlalchemy.org/docs/orm/tutorial.html#creating-table-class
> I would declare the class + table + mapping at once, to eliminate any
> confusion regarding mapping, which is not a per-usage operation; it is a
> permanent operation applied to a model class only once. The mapper() +
> Table pattern is not as easy to use and it's being de-emphasized in the
> documentation.
>
> The second error implies your class has a method called value() on it which
> is conflicting with the mapped attribute of .value.
>
> On Jun 14, 2011, at 12:22 PM, Liju wrote:
>
>
>
>
>
>
>
> > I'm new to SQLAlchemy. I wrote a method that retrieves a record,
> > update the object after incrementing it by 1, and return that record
> > object to the caller (pyramid view). Following is the test function. I
> > get following errors :
>
> > 1) when I call this method multiple times, I get an error that say
> > "ArgumentError: Class '' already has a
> > primary mapper defined. Use non_primary=True to create a non primary
> > Mapper. clear_mappers() will remove *all* current mappers from all
> > classes."
>
> > As a resolution i called 'clear_mappers()' before invoking mapper.
>
> > 2) I cant seem to increment the attribute of an object in orm session.
> > My understanding is that once a record is retrieved in an ORM session,
> > Session object keeps track of any changes to the record object and
> > updates the record when session.flush() is invoked.
> > But I get error "TypeError: unsupported operand type(s) for +:
> > 'instancemethod' and 'int'"
>
> > Can someone please explain to me what I'm doing wrong ?
>
> > class Models(object):pass
>
> > def countAndIncrement():
> > metadata = MetaData('sqlite:///CAS.db')
>
> > model_table = Table('models',
> > metadata,
> > Column('id',Integer,primary_key=True),
> > Column('name',String(40)),
> > Column('value',Integer)
> > )
>
> > clear_mappers()
>
> > mapper(Models,model_table) # already a
> > primary mapper defined error (when I call this function multiple times
>
> > Session = sessionmaker()
> > session = Session()
>
> > model = session.query(Models).filter(Models.id==1)
>
> > model.value = model.value + 1 #
> > increment error
>
> > session.flush()
> > session.close()
>
> > return model
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mapper error, and db object cannot be incremented
You're best starting with the declarative usage patterns described in the ORM
tutorial at http://www.sqlalchemy.org/docs/orm/tutorial.html, starting with
http://www.sqlalchemy.org/docs/orm/tutorial.html#creating-table-class-and-mapper-all-at-once-declaratively.
I would declare the class + table + mapping at once, to eliminate any
confusion regarding mapping, which is not a per-usage operation; it is a
permanent operation applied to a model class only once. The mapper() +
Table pattern is not as easy to use and it's being de-emphasized in the
documentation.
The second error implies your class has a method called value() on it which is
conflicting with the mapped attribute of .value.
On Jun 14, 2011, at 12:22 PM, Liju wrote:
> I'm new to SQLAlchemy. I wrote a method that retrieves a record,
> update the object after incrementing it by 1, and return that record
> object to the caller (pyramid view). Following is the test function. I
> get following errors :
>
> 1) when I call this method multiple times, I get an error that say
> "ArgumentError: Class '' already has a
> primary mapper defined. Use non_primary=True to create a non primary
> Mapper. clear_mappers() will remove *all* current mappers from all
> classes."
>
> As a resolution i called 'clear_mappers()' before invoking mapper.
>
> 2) I cant seem to increment the attribute of an object in orm session.
> My understanding is that once a record is retrieved in an ORM session,
> Session object keeps track of any changes to the record object and
> updates the record when session.flush() is invoked.
> But I get error "TypeError: unsupported operand type(s) for +:
> 'instancemethod' and 'int'"
>
> Can someone please explain to me what I'm doing wrong ?
>
> class Models(object):pass
>
> def countAndIncrement():
>metadata = MetaData('sqlite:///CAS.db')
>
>model_table = Table('models',
>metadata,
>Column('id',Integer,primary_key=True),
>Column('name',String(40)),
>Column('value',Integer)
>)
>
>clear_mappers()
>
>mapper(Models,model_table) # already a
> primary mapper defined error (when I call this function multiple times
>
>Session = sessionmaker()
>session = Session()
>
>model = session.query(Models).filter(Models.id==1)
>
>model.value = model.value + 1 #
> increment error
>
>session.flush()
>session.close()
>
>return model
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] mapper error, and db object cannot be incremented
I'm new to SQLAlchemy. I wrote a method that retrieves a record,
update the object after incrementing it by 1, and return that record
object to the caller (pyramid view). Following is the test function. I
get following errors :
1) when I call this method multiple times, I get an error that say
"ArgumentError: Class '' already has a
primary mapper defined. Use non_primary=True to create a non primary
Mapper. clear_mappers() will remove *all* current mappers from all
classes."
As a resolution i called 'clear_mappers()' before invoking mapper.
2) I cant seem to increment the attribute of an object in orm session.
My understanding is that once a record is retrieved in an ORM session,
Session object keeps track of any changes to the record object and
updates the record when session.flush() is invoked.
But I get error "TypeError: unsupported operand type(s) for +:
'instancemethod' and 'int'"
Can someone please explain to me what I'm doing wrong ?
class Models(object):pass
def countAndIncrement():
metadata = MetaData('sqlite:///CAS.db')
model_table = Table('models',
metadata,
Column('id',Integer,primary_key=True),
Column('name',String(40)),
Column('value',Integer)
)
clear_mappers()
mapper(Models,model_table) # already a
primary mapper defined error (when I call this function multiple times
Session = sessionmaker()
session = Session()
model = session.query(Models).filter(Models.id==1)
model.value = model.value + 1 #
increment error
session.flush()
session.close()
return model
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Classes with the same name in different namespaces.
this is fixed in r462bd17d7ea2 however you'll want to use __mapper_args__ and
not "mapper_args" in your classes, also the issue only exists if you didn't
specify "inherits" yourself, so since you're already explicitly referencing the
superclass you can work around easily by saying:
__mapper_args__ = {
'inherits':foo.Foo
}
On Jun 14, 2011, at 9:04 AM, Filip Zyzniewski - Tefnet wrote:
> import sqlalchemy
> import sqlalchemy.ext.declarative
>
> Base = sqlalchemy.ext.declarative.declarative_base()
>
> engine = sqlalchemy.create_engine('sqlite:///:memory:', echo = False)
>
> session = sqlalchemy.orm.scoped_session(
>sqlalchemy.orm.sessionmaker(
>bind = engine,
>autocommit = False
>)
> )
>
> Base.metadata.bind = engine
>
> class SomeObj(Base):
>__tablename__ = "someobj"
>Id = sqlalchemy.Column( sqlalchemy.types.Integer, primary_key=True,
> autoincrement=True)
>objType = sqlalchemy.Column(sqlalchemy.types.String(128), index =
> True, nullable = False)
>__mapper_args__ = {
>'polymorphic_identity': __tablename__,
>'polymorphic_on': objType
>}
>
> import bar
>
>
> ===> foo.py <==
> import sqlalchemy
> import names
>
> class Foo(names.SomeObj):
>__tablename__ = 'foo_foo'
>
>Id = sqlalchemy.Column(
>sqlalchemy.types.Integer(),
>sqlalchemy.schema.ForeignKey(names.SomeObj.Id),
>primary_key = True
>)
>
>mapper_args = {
>'inherit_condition': Id == names.SomeObj.Id,
>'polymorphic_identity': __tablename__,
>}
>
>
> ===> bar.py <==
> import sqlalchemy
> import foo
>
> class Foo(foo.Foo):
>__tablename__ = 'bar_foo'
>
>Id = sqlalchemy.Column(
>sqlalchemy.types.Integer(),
>sqlalchemy.schema.ForeignKey(foo.Foo.Id),
>primary_key = True
>)
>
>mapper_args = {
>'inherit_condition': Id == foo.Foo.Id,
>'polymorphic_identity': __tablename__,
>}
>
> =
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: porting GeoAlchemy to 0.7
On Jun 14, 2011, at 3:17 AM, Eric Lemoine wrote: > Here's our TypeEngine: > > > So dialect-specific parameters are stored in self.kwargs. > > I can try to add a test to SQLAlchemy if you indicate me where this > test should go. thanks, I need to add the elements of it to the user-defined types tests in test.sql.test_types. I also want to figure out what that _compiler_dispatch call is about, have you tried subclassing UserDefinedType instead ? that's the official "home base" for types like these. -- 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] Classes with the same name in different namespaces.
Hi,
I am having a problem with declarative and inheritance from a class
which has the same name, but different namespace (module):
===> names.py <==
# Fails with Python-2.7.1 and SQLAlchemy-0.7.1
#
import sqlalchemy
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()
engine = sqlalchemy.create_engine('sqlite:///:memory:', echo = False)
session = sqlalchemy.orm.scoped_session(
sqlalchemy.orm.sessionmaker(
bind = engine,
autocommit = False
)
)
Base.metadata.bind = engine
class SomeObj(Base):
__tablename__ = "someobj"
Id = sqlalchemy.Column( sqlalchemy.types.Integer, primary_key=True,
autoincrement=True)
objType = sqlalchemy.Column(sqlalchemy.types.String(128), index =
True, nullable = False)
__mapper_args__ = {
'polymorphic_identity': __tablename__,
'polymorphic_on': objType
}
import bar
===> foo.py <==
import sqlalchemy
import names
class Foo(names.SomeObj):
__tablename__ = 'foo_foo'
Id = sqlalchemy.Column(
sqlalchemy.types.Integer(),
sqlalchemy.schema.ForeignKey(names.SomeObj.Id),
primary_key = True
)
mapper_args = {
'inherit_condition': Id == names.SomeObj.Id,
'polymorphic_identity': __tablename__,
}
===> bar.py <==
import sqlalchemy
import foo
class Foo(foo.Foo):
__tablename__ = 'bar_foo'
Id = sqlalchemy.Column(
sqlalchemy.types.Integer(),
sqlalchemy.schema.ForeignKey(foo.Foo.Id),
primary_key = True
)
mapper_args = {
'inherit_condition': Id == foo.Foo.Id,
'polymorphic_identity': __tablename__,
}
=
After running names.py I get:
=
/home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/ext/declarative.py:1129:
SAWarning: The classname 'Foo' is already in the registry of this declarative
base, mapped to
_as_declarative(cls, classname, cls.__dict__)
Traceback (most recent call last):
File "names.py", line 29, in
import bar
File "/home/filip/tefnet/teferp/bugs/names/bar.py", line 4, in
class Foo(foo.Foo):
File
"/home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/ext/declarative.py",
line 1129, in __init__
_as_declarative(cls, classname, cls.__dict__)
File
"/home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/ext/declarative.py",
line 1059, in _as_declarative
compile=False)
File
"/home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/orm/util.py",
line 546, in class_mapper
raise exc.UnmappedClassError(class_)
sqlalchemy.orm.exc.UnmappedClassError: Class 'bar.Foo' is not mapped
=
despite separate namespaces.
Is there some way I could make the mapper aware of namespaces?
PS. Zzzeek - thanks for sorting out the count() thing before :).
regards,
Filip Zyzniewski
Tefnet
--
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.
names.tbz2
Description: application/bzip-compressed-tar
Re: [sqlalchemy] Re: What is the Sqlalchemy syntax for having ORDER BY and LIMIT on DELETE
On Mon, Jun 13, 2011 at 15:42, Moshe C. wrote: > What is the syntax for the "where id in (select ... ) " ? Does the sqlalchemy.sql.where() function and the in_() operator fit your needs? Can I also ask: why do you want to ORDER BY on a DELETE? > > On Jun 13, 5:17 pm, Michael Bayer wrote: >> That's a MySQL specific syntax you might be better off not using, perhaps >> you could say "delete from table where id in (select id from table order by >> timestamp limit 10)". >> >> To get the exact statement, it's probably easiest just to emit the string >> SQL. If you wanted the sqlalchemy.sql.delete() construct to do it you'd >> need to subclass Delete, add order_by() and limit() to it, and augment its >> compilation as described inhttp://www.sqlalchemy.org/docs/core/compiler.html >> >> On Jun 13, 2011, at 10:10 AM, Moshe C. wrote: >> >> >> >> >> >> >> >> > Hi, >> > I am using Sqlalchemy 0.6.5 . >> >> > How do I generate the following statement usin Sqlalchemy expressions >> > (not ORM). >> >> > DELETE FROM table ORDER BY timestamp LIMIT 10; >> >> > TIA >> >> > -- >> > You received this message because you are subscribed to the Google Groups >> > "sqlalchemy" group. >> > To post to this group, send email to sqlalchemy@googlegroups.com. >> > To unsubscribe from this group, send email to >> > sqlalchemy+unsubscr...@googlegroups.com. >> > For more options, visit this group >> > athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- Rami Chowdhury "Never assume malice when stupidity will suffice." -- Hanlon's Razor +44-7581-430-517 / +1-408-597-7068 / +88-0189-245544 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] question re using the session object
> -Original Message- > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] > On Behalf Of robert rottermann > Sent: 14 June 2011 10:53 > To: sqlalchemy@googlegroups.com > Subject: [sqlalchemy] question re using the session object > > hi there, > > for a zope website I am using sqlalchemy. > Now I am unsure how to use the session object. > > What I do now is: > from sqlalchemy.orm import scoped_session > ... > Session = scoped_session(session_factory, scopefunc) > session = Session() > > this session object I import into all classes where ever I need it. > > Now my question: > > is it ok to use this single instance troughout the life of the Zope > severer, or > should I call Session() whenever I need a session? > > thanks > robert > You definitely shouldn't use your 'session' instance throughout the application - it won't be thread-safe. Scoped sessions are described at http://www.sqlalchemy.org/docs/orm/session.html#unitofwork-contextual, but basically, you have two choices. You can: a) Call Session() whenever you need a session. SA will ensure that if you call it twice within the same "scope" (which is typically the current thread), the same instance will be returned. b) Use your "Session" directly - it implements the same interface as the real session, and forwards all requests on to the underlying thread-local session. Hope that helps, Simon -- 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] question re using the session object
hi there, for a zope website I am using sqlalchemy. Now I am unsure how to use the session object. What I do now is: from sqlalchemy.orm import scoped_session ... Session = scoped_session(session_factory, scopefunc) session = Session() this session object I import into all classes where ever I need it. Now my question: is it ok to use this single instance troughout the life of the Zope severer, or should I call Session() whenever I need a session? thanks robert -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: porting GeoAlchemy to 0.7
On Thu, Jun 9, 2011 at 7:21 PM, Michael Bayer wrote: > > On Jun 9, 2011, at 12:37 PM, Eric Lemoine wrote: > >> On Thu, Jun 9, 2011 at 6:28 PM, Michael Bayer >> wrote: >>> >>> >>> That's the default adaption provided by TypeEngine.adapt(). Provide your >>> own adapt() that does what's needed. For examples see Interval, Enum. >> >> Ok, I'll take a look at adapt(). Note that our Geometry type isn't >> specific to Oracle though. > > When you get it going, if you can show us what you're doing, we can create a > prototypical version of your type, demonstrating the kind of "add new > arguments per dialect" functionality it has, and add it to our test suite, > to ensure those usage patterns don't break. SQLAlchemy usually uses > distinct type classes per backend to handle backend-specific arguments, so > your approach of allowing DB-specific keyword arguments to a single type, > which while entirely appropriate in your case, isn't a pattern we test for at > the moment. Hi Michael Here's our TypeEngine: class GeometryBase(TypeEngine): """Base Geometry column type for all spatial databases. Converts bind/result values to/from a generic Persistent value. This is used as a base class and overridden into dialect specific Persistent values. """ name = 'GEOMETRY' def __init__(self, dimension=2, srid=4326, spatial_index=True, **kwargs): self.dimension = dimension self.srid = srid self.spatial_index = spatial_index self.kwargs = kwargs super(GeometryBase, self).__init__() def bind_processor(self, dialect): def process(value): if value is not None: if isinstance(value, SpatialElement): if isinstance(value.desc, SpatialElement): return value.desc.desc return value.desc else: return value else: return value return process def result_processor(self, dialect, coltype=None): def process(value): if value is not None: return PersistentSpatialElement(value) else: return value return process def _compiler_dispatch(self, *args): """Required for the Cast() operator when used for the compilation of DBSpatialElement""" return self.name def adapt(self, cls, **kwargs): return cls(dimension=self.dimension, srid=self.srid, spatial_index=self.spatial_index, **self.kwargs) So dialect-specific parameters are stored in self.kwargs. I can try to add a test to SQLAlchemy if you indicate me where this test should go. Cheers -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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.
