[sqlalchemy] Training or consultant?

[George V. Reilly]

Is there such a thing as SQLAlchemy training or a SA consultant? I'm
starting to think that my team might benefit from some time with
someone who really knows their stuff.

/George Reilly, Seattle

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] INSERT…RETURNING being issued wrongly

[Oliver Beattie]

I'm not entirely sure why this is happening… it seems to work for me
in nearly all other circumstances so I'm a bit stumped. Basically, I
have a declarative table which has a character field as its primary
key (it's not an ID which can be returned by the server), yet
SQLAlchemy is issuing an INSERT…RETURNING statement for it.

The scenario that seems to make it happening is like this. I'm trying
to create a copy of an existing object, with a new ID. All the
attributes on the new object should be pulled from the old object
(which I'm doing through use of the iterate_properties iterator along
with setattr, getarre calls), apart from this primary key field (name)
which is a character field and will be something different. A relation
on the old object will be updated to point at the created object.
When commit() happens, I get an IntegrityError because it's thinking
the db will return it the primary key (even though I have explicitly
set it on the non-persistant instance).

Is this a know bug or something I'm doing wrong?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: INSERT…RETURNING being issued wro ngly

[Oliver Beattie]

I'm using Postgres

On Aug 18, 1:27 pm, Oliver Beattie oli...@obeattie.com wrote:
 I'm not entirely sure why this is happening… it seems to work for me
 in nearly all other circumstances so I'm a bit stumped. Basically, I
 have a declarative table which has a character field as its primary
 key (it's not an ID which can be returned by the server), yet
 SQLAlchemy is issuing an INSERT…RETURNING statement for it.

 The scenario that seems to make it happening is like this. I'm trying
 to create a copy of an existing object, with a new ID. All the
 attributes on the new object should be pulled from the old object
 (which I'm doing through use of the iterate_properties iterator along
 with setattr, getarre calls), apart from this primary key field (name)
 which is a character field and will be something different. A relation
 on the old object will be updated to point at the created object.
 When commit() happens, I get an IntegrityError because it's thinking
 the db will return it the primary key (even though I have explicitly
 set it on the non-persistant instance).

 Is this a know bug or something I'm doing wrong?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Set-returning functions

[bekozi]

Thanks! Will try to get this working.

I am using GeoAlchemy quite extensively but these more obscure
functions and types are not supported...

On Aug 16, 9:14 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 On Aug 16, 2010, at 11:21 AM, bekozi wrote:

  Is it possible to work with set-returning functions in SQLAlchemy
  without using raw SQL? For example, the PostgreSQL/PostGIS function
  ST_Dump (http://bit.ly/culek7) returns a “geometry_dump set. Using
  ST_Dump in raw SQL goes something like:

     SELECT
       ST_Dump(ST_Intersection(data_table1.geom,data_table2.geom)).geom AS
  geom...

  In SQLAlchemy an attempt to construct this unsurprisingly yields an
  attribute error:

  session.query(func.ST_Dump(functions.intersection(Data1.geom,Data2.geom).ge 
  om.label('geom'))
     AttributeError: 'Function' object has no attribute 'geom'

 any SQL you want can be made available with Python expressions using 
 @compiles:

 from sqlalchemy import *
 from sqlalchemy.sql import ColumnElement, column

 from sqlalchemy.ext.compiler import compiles

 class geom(ColumnElement):
     def __init__(self, base):
         self.base = base

 @compiles(geom)
 def compile(expr, compiler, **kw):
     return compiler.process(expr.base) + .geom

 data1, data2 = column('data1'), column('data2')
 print select([func.ST_Dump(
             geom(func.intersection(geom(data1),geom(data2)))
             ).label('geom')])

  Curious if anyone knows a solution! A search for using set-returning
  functions in SQLAlchemy yielded no obvious solution...

 any reason you aren't using GeoAlchemy ?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Locked file when trying to copy

[Jeroen Dierckx]

Thanks a lot Lance, that did the trick.

best regards,
jeroen

On Mon, Aug 16, 2010 at 10:18 PM, Lance Edgar lance.ed...@gmail.com wrote:
 See
 http://groups.google.com/group/sqlalchemy/browse_thread/thread/aa9c753384532e6c/8d070ff7208494b1

 The solution though I believe is just:

 from sqlalchemy import create_engine
 from sqlalchemy.pool import NullPool

 to_engine = create_engine('sqlite:///%s' % temp_file_name,
 poolclass=NullPool)

 Lance


 On Mon, 2010-08-16 at 21:08 +0200, Jeroen Dierckx wrote:

 Hello all,

 I am trying to export data from a MySQL database to a sqlite database
 using SqlAlchemy.
 I am using 2 engines for each database ( from and to ).
 This is the part that creates the sqlite engine:

 to_engine   = create_engine(u'sqlite:///%s'%temp_file_name)
 to_meta_data = MetaData()
 to_meta_data.bind = to_engine

 Then i move the tables from one engine to the other.
 I close the connections;
 to_connection.close()


 Finally i want to copy the sqlite file to its permanent location using
 shutil.move. But here it goes wrong; the file seems to be locked
 still:
 shutil.move(temp_file_name, self._filename)

 I get this error (might not be verbatim):
 WindowsError: [Error 32] The process does not have access to the file
 because the file is being used by another process.

 Can anyone give direction as to why the file might still be locked and
 how to avoid it?

 Thanks in advance,
 Best regards,

 Jeroen


 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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] dictionary-like objects for ORM

[yota]

Hello,

sqlalchemy seems to be the proper tool for my needs but I can't figure
out how to design my project or set the ORM properly.

Let's say, I build a music database, storing tracks and their
associated metadata in an sql-like database defined as such :

TRACK_TABLE ( ident *, url , duration )
METADATA_TABLE ( track_ident *, field_name *, field_content )

(track_ident, field_name) being the primary key for METADATA_TABLE...


intuitively, a meaningful object for a metadata set would be a
dictionary-like object. The name and number of fields being unknown in
advance but stored in the METADATA_TABLE as one row per field.

Here is the question : how may I use the ORM to map
a table :
123 / title / waka waka
123 / artist / shakira
123 / featuring / my sister

to an object like :
metadata.title = waka waka
metadata.artist = shakira
metadata.featuring = my sister

or a dictionary like :
metadata = { title : waka waka, artist : shakira,
featuring : my sister }

or whatsoever ...

the solution might also be in rewrite of the sql schema :)


thanks for your advices

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Self-referential, one-to-one, reflexive mappings

[Ross Vandegrift]

On Tue, Aug 17, 2010 at 07:32:41PM -0700, Enrico wrote:
 Micahael gave me this advice:
 http://groups.google.com/group/sqlalchemy/browse_thread/thread/df6e451855d13a60/386232232434ff92?lnk=gstq=enrico#386232232434ff92
 in which there are two backrefs and I think it's declarative whereas
 yours is classical.

Hmmm, that's more of a general one-to-many relation with an
association object - it doesn't enfore one-to-one-ness or reflexivity.
There is something I don't understand in there though - the node and
adj_node properties mutually overwrite one-another with backrefs.  How
does that work?


Here's what I want to be able to do:

n1 = Node(Node1)
n2 = Node(Node2)
n1.peer = n2
print n2.peer
 Node(Node1)
print n1.peer
 Node(Node2)

q = session.query(Node)
n1 = q.filter_by(peer=n2).one()

Usual backref patterns don't work because the backref would need to be
named the same thing as the forward reference.  Hence why I went with
an association object.

But since the association object has a relation to both nodes, there
not an easy way to know that n1's peer is the OTHER entry in
n1.node.  Hence why I added the property an synonym.

Ross



 
 
 On Aug 18, 4:59 am, Ross Vandegrift r...@kallisti.us wrote:
  Hi everyone,
 
  Does anyone have a good setup for one-to-one relationships that are
  always symmetric and provide a common property to access the paired
  object?  Ie, it's always the case that:
          1) (x,y) is in the relation iff (y,x) is in the relation.
          2) x.peer = y
          3) y.peer = x
 
  Here's the best thing I've come up with so far.  It's not perfect -
  for instance, there's no way to query by partner.
 
  class Person(object):
      def _get_partner(self):
          if self.marriage:
              return self.marriage.get_partner_of(self)
          else:
              return None
      partner = property(_get_partner)
 
  class Marriage(object):
      def __init__(self, a, b):
          self.partners = [a, b]
      def get_partner_of(self, a):
          x = list(self.partners)
          x.remove(a)
          return x[0]
 
  person_t = Table('person', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('marriageid', Integer, ForeignKey('marriage.id')))
  marriage_t = Table('marriage', metadata,
                     Column('id', Integer, primary_key=True))
 
  person_m = orm.mapper(Person, person_t)
  marriage_m = orm.mapper(Marriage, marriage_t,
                          properties={'partners': orm.relation(Person, 
  backref=marriage)})
 
  Ross
 
  --
  Ross Vandegrift
  r...@kallisti.us
 
  If the fight gets hot, the songs get hotter.  If the going gets tough,
  the songs get tougher.
          --Woody Guthrie
 
   signature.asc
   1KViewDownload
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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.

-- 
Ross Vandegrift
r...@kallisti.us

If the fight gets hot, the songs get hotter.  If the going gets tough,
the songs get tougher.
--Woody Guthrie


signature.asc
Description: Digital signature

Re: [sqlalchemy] SqlAlchemy logging FAQ

[Michael Bayer]

On Aug 17, 2010, at 11:45 AM, Kent wrote:

 The logging FAQ states Therefore, when using Python logging, ensure
 all echo flags are set to False at all times, to avoid getting
 duplicate log lines.
 http://www.sqlalchemy.org/docs/dbengine.html#configuring-logging
 
 Is this no longer correct information?

it is correct


 
 I am using turbogears (which creates the engine with
 engine_from_config()) and with the config file like this:
 
 sqlalchemy.echo = false
 sqlalchemy.echo_pool = false
 sqlalchemy.pool_recycle = 3600
 
 I cannot get engine logging no matter what I put in the logging config
 file.  echo is turned off and sqlalchemy won't even hit the
 logger.debug() method.

assuming you're on the pylons version of turbogears, you leave the echo flags 
alone.  You configure logging in the [logging] section of your .ini file, using 
loggers and log levels.   I use Pylons and this all works very well.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Eager/joined loading of JTI models

[Michael Bayer]

On Aug 17, 2010, at 5:05 PM, flzz wrote:

 Thanks this did the trick, I agree, an option in relationship to
 define how this behaves would be nice.

its a long term TODO.   


 
 Cheers
 Etrik
 
 On Aug 17, 3:27 pm, Conor conor.edward.da...@gmail.com wrote:
 On 08/17/2010 11:21 AM, flzz wrote:
 
 
 
 
 
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy import create_engine, Column, Integer, String,
 ForeignKey
 from sqlalchemy.orm import sessionmaker, relationship
 from sqlalchemy.orm.collections import attribute_mapped_collection
 
 engine=create_engine('sqlite://', echo=True)
 Base = declarative_base(engine)
 
 def monkey_repr():
 def __repr__(self):
 Show attribute values in the repr().
 simple_props = ', '.join('%s=%r' % (attr, value)
  for attr, value in
 sorted(vars(self).items())
  if isinstance(value, (basestring,
 int)))
 return '%s (%s)' % (type(self).__name__, simple_props)
 return __repr__
 
 Base.__repr__ = monkey_repr()
 
 class Company(Base):
 __tablename__ = 'companies'
 id = Column(Integer, primary_key=True)
 people = relationship('Person', lazy='joined',
 
 collection_class=attribute_mapped_collection('name'))
 
 class Person(Base):
 __tablename__ = 'people'
 id = Column(Integer, primary_key=True)
 _company_id = Column(Integer,  ForeignKey('companies.id'))
 name = Column(String)
 discriminator = Column('type', String(50))
 __mapper_args__ = {'polymorphic_on': discriminator}
 
 class Engineer(Person):
 __tablename__ = 'engineers'
 __mapper_args__ = {'polymorphic_identity': 'engineer'}
 name = Column(String)
 department = Column(String)
 id = Column(Integer, ForeignKey('people.id'), primary_key=True)
 primary_language = Column(String(50))
 
 class Accountant(Person):
 __tablename__ = 'accountants'
 __mapper_args__ = {'polymorphic_identity': 'accountant'}
 name = Column(String)
 bank = Column(String)
 id = Column(Integer, ForeignKey('people.id'), primary_key=True)
 primary_language = Column(String(50))
 
 Base.metadata.create_all()
 
 sess = sessionmaker()()
 
 company = Company()
 
 for p in [Engineer(name='far'),
   Person(name='nar'),
   Engineer(name='zar', department='rover'),
   Accountant(name='jak', bank='hsb')]:
 company.people.set(p)
 
 sess.add(company)
 sess.commit()
 sess.expunge_all()
 
 print \n#\n# Query for the first company \n#\n
 c = sess.query(Company).first()
 print c.people
 print \n#\n# This will issue another query even though lazy='joined'
 \n#\n
 print c.people['zar'].department
 
 The easiest way is to add a with_polymorphic argument to
 Person.__mapper_args__:
 
 class Person(object):
 [...]
 __mapper_args__ = {'polymorphic_on': discriminator,
'with_polymorphic': '*'}
 
 The downside is that this will always enable joinedloads on the subclass
 tables when loaded via relationships. Seems like it would be nice to add
 a with_polymorphic parameter to relationship().
 
 -Conor
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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] SqlAlchemy logging FAQ

[Kent Bower]

 Ah. Then the problem is in turbogears (which creates a default .ini 
file with):



#echo shouldn't be used together with the logging module.
sqlalchemy.echo = false
sqlalchemy.echo_pool = false
sqlalchemy.pool_recycle = 3600

...logging sections...



Instead, its default .ini file should leave the flag alone.

I've directed TG group to this thread, thanks for your help.


On 8/18/2010 10:58 AM, Michael Bayer wrote:

On Aug 17, 2010, at 11:45 AM, Kent wrote:


The logging FAQ states Therefore, when using Python logging, ensure
all echo flags are set to False at all times, to avoid getting
duplicate log lines.
http://www.sqlalchemy.org/docs/dbengine.html#configuring-logging

Is this no longer correct information?

it is correct



I am using turbogears (which creates the engine with
engine_from_config()) and with the config file like this:

sqlalchemy.echo = false
sqlalchemy.echo_pool = false
sqlalchemy.pool_recycle = 3600

I cannot get engine logging no matter what I put in the logging config
file.  echo is turned off and sqlalchemy won't even hit the
logger.debug() method.

assuming you're on the pylons version of turbogears, you leave the echo flags 
alone.  You configure logging in the [logging] section of your .ini file, using 
loggers and log levels.   I use Pylons and this all works very well.




--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] dictionary-like objects for ORM

[Conor]

On 08/17/2010 11:32 AM, yota wrote:
 Hello,

 sqlalchemy seems to be the proper tool for my needs but I can't figure
 out how to design my project or set the ORM properly.

 Let's say, I build a music database, storing tracks and their
 associated metadata in an sql-like database defined as such :

 TRACK_TABLE ( ident *, url , duration )
 METADATA_TABLE ( track_ident *, field_name *, field_content )

 (track_ident, field_name) being the primary key for METADATA_TABLE...


 intuitively, a meaningful object for a metadata set would be a
 dictionary-like object. The name and number of fields being unknown in
 advance but stored in the METADATA_TABLE as one row per field.

 Here is the question : how may I use the ORM to map
 a table :
 123 / title / waka waka
 123 / artist / shakira
 123 / featuring / my sister

 to an object like :
 metadata.title = waka waka
 metadata.artist = shakira
 metadata.featuring = my sister

 or a dictionary like :
 metadata = { title : waka waka, artist : shakira,
 featuring : my sister }

 or whatsoever ...

 the solution might also be in rewrite of the sql schema :)


 thanks for your advices
   

SQLAlchemy supports for this via the attribute_mapped_collection and
association_proxy classes:

from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection

def create_metadata(field_name, field_content):
return TrackMetaData(field_name=field_name, field_content=field_content)

class Track(Base):
__tablename__ = track

id = Column(Integer, primary_key=True)
[...]

field2metadata = relationship(TrackMetaData,
  backref=track,
  
collection_class=attribute_mapped_collection(field_name))
field2content = association_proxy(field2metadata,
  field_content,
  creator=create_metadata)

# I'm only naming this class TrackMetaData to prevent confusion with 
sqlalchemy.MetaData.
class TrackMetaData(Base):
__tablename__ = metadata

track_id = Column(Integer, ForeignKey(track.id), primary_key=True)
field_name = Column(Unicode(...), primary_key=True)
field_content = Column(Unicode(...), nullable=False)

In this way you can access field2content like a dictionary:

artist = track.field2content[uartist]
track.field2content[utitle] = uwaka waka

Note that association_proxy does not supply a comparator yet, so if you
want to join/query on metadata then you need to use the field2metadata
relationship:

# Find all tracks by artist Shakira.
q = Session.query(Track)
q = q.filter(Track.field2metadata.any(and_(TrackMetaData.field_name == 
uartist,
   TrackMetaData.field_content = 
uShakira)))
tracks = q.all()

# Load all tracks, eagerloading their metadata.
q = Session.query(Track)
q = q.options(joinedload(Track.field2metadata))
tracks = q.all()

-Conor

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: dictionary-like objects for ORM

[yota]

Thank you very much for the source,

I also learned the term of vertical paradigm and stumble upon the
dictlike.py example in the sqlalchemy source ...

even if yours remains simpler

On Aug 18, 5:05 pm, Conor conor.edward.da...@gmail.com wrote:
 On 08/17/2010 11:32 AM, yota wrote:



  Hello,

  sqlalchemy seems to be the proper tool for my needs but I can't figure
  out how to design my project or set the ORM properly.

  Let's say, I build a music database, storing tracks and their
  associated metadata in an sql-like database defined as such :

  TRACK_TABLE ( ident *, url , duration )
  METADATA_TABLE ( track_ident *, field_name *, field_content )

  (track_ident, field_name) being the primary key for METADATA_TABLE...

  intuitively, a meaningful object for a metadata set would be a
  dictionary-like object. The name and number of fields being unknown in
  advance but stored in the METADATA_TABLE as one row per field.

  Here is the question : how may I use the ORM to map
  a table :
  123 / title / waka waka
  123 / artist / shakira
  123 / featuring / my sister

  to an object like :
  metadata.title = waka waka
  metadata.artist = shakira
  metadata.featuring = my sister

  or a dictionary like :
  metadata = { title : waka waka, artist : shakira,
  featuring : my sister }

  or whatsoever ...

  the solution might also be in rewrite of the sql schema :)

  thanks for your advices

 SQLAlchemy supports for this via the attribute_mapped_collection and
 association_proxy classes:

 from sqlalchemy.ext.associationproxy import association_proxy
 from sqlalchemy.orm.collections import attribute_mapped_collection

 def create_metadata(field_name, field_content):
     return TrackMetaData(field_name=field_name, field_content=field_content)

 class Track(Base):
     __tablename__ = track

     id = Column(Integer, primary_key=True)
     [...]

     field2metadata = relationship(TrackMetaData,
                                   backref=track,
                                   
 collection_class=attribute_mapped_collection(field_name))
     field2content = association_proxy(field2metadata,
                                       field_content,
                                       creator=create_metadata)

 # I'm only naming this class TrackMetaData to prevent confusion with 
 sqlalchemy.MetaData.
 class TrackMetaData(Base):
     __tablename__ = metadata

     track_id = Column(Integer, ForeignKey(track.id), primary_key=True)
     field_name = Column(Unicode(...), primary_key=True)
     field_content = Column(Unicode(...), nullable=False)

 In this way you can access field2content like a dictionary:

 artist = track.field2content[uartist]
 track.field2content[utitle] = uwaka waka

 Note that association_proxy does not supply a comparator yet, so if you
 want to join/query on metadata then you need to use the field2metadata
 relationship:

 # Find all tracks by artist Shakira.
 q = Session.query(Track)
 q = q.filter(Track.field2metadata.any(and_(TrackMetaData.field_name == 
 uartist,
                                            TrackMetaData.field_content = 
 uShakira)))
 tracks = q.all()

 # Load all tracks, eagerloading their metadata.
 q = Session.query(Track)
 q = q.options(joinedload(Track.field2metadata))
 tracks = q.all()

 -Conor

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] How to get read-only objects from database?

[Alvaro Reinoso]

Hello,

I'd like to query the database and get read-only objects with session
object. I need to save the objects in my server and use them through
the user session. If I use a object outside of the function that calls
the database, I get this error:

DetachedInstanceError: Parent instance is not bound to a Session;
lazy load operation of attribute 'items' cannot proceed

I don't need to make any change in those objects, so I don't need to
load them again.

Is there any way that I can get that?

Thanks in advance!

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] sqlalchemy-migrate examples

[Chris Withers]

Hi All,

Does anyone have any good examples of migration scripts?
The documentation is surprisingly sparse..

Of course, an alernative which doesn't do any abusive monkey patching or 
have any annoying * imports would be handy. Michael, how's Alembic coming?


Chris

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] How to get read-only objects from database?

[Conor]

On 08/18/2010 10:27 AM, Alvaro Reinoso wrote:
 Hello,

 I'd like to query the database and get read-only objects with session
 object. I need to save the objects in my server and use them through
 the user session. If I use a object outside of the function that calls
 the database, I get this error:

 DetachedInstanceError: Parent instance is not bound to a Session;
 lazy load operation of attribute 'items' cannot proceed

 I don't need to make any change in those objects, so I don't need to
 load them again.

 Is there any way that I can get that?

 Thanks in advance!
   

You have two options:

   1. Keep the DB session open longer so the objects can lazy-load
  attributes from the database.
   2. Ensure that all the attributes you will use are fully loaded in
  your function that calls the database. Generally this means
  adding joinedload() or subqueryload() options to your DB queries.
  This will prevent lazy-loads from occurring and makes it safe to
  use the objects without a DB session.

-Conor

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: INSERT…RETURNING being issued wro ngly

[Oliver Beattie]

On Aug 18, 3:39 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 Sent from my iPhone

 On Aug 18, 2010, at 8:27 AM, Oliver Beattie oli...@obeattie.com wrote:





  I'm not entirely sure why this is happening… it seems to work for me
  in nearly all other circumstances so I'm a bit stumped. Basically, I
  have a declarative table which has a character field as its primary
  key (it's not an ID which can be returned by the server), yet
  SQLAlchemy is issuing an INSERT…RETURNING statement for it.

  The scenario that seems to make it happening is like this. I'm trying
  to create a copy of an existing object, with a new ID. All the
  attributes on the new object should be pulled from the old object
  (which I'm doing through use of the iterate_properties iterator along
  with setattr, getarre calls), apart from this primary key field (name)
  which is a character field and will be something different. A relation
  on the old object will be updated to point at the created object.
  When commit() happens, I get an IntegrityError because it's thinking
  the db will return it the primary key (even though I have explicitly
  set it on the non-persistant instance).

  Is this a know bug or something I'm doing wrong?

 If the primary key is a character field, you have to ensure the attribute is 
 populated before the insert occurs.  Otherwise sqlalchemy assumes the value 
 is created by a generator of some kind such as a column default and issues 
 RETURNING, which is perfectly valid for any type of column, to get the value 
 back.

Thanks for your reply, Michael — the value is being populated (as in,
I am setting the value on the instance before I do session.add()) — is
this what you mean?






  --
  You received this message because you are subscribed to the Google Groups 
  sqlalchemy group.
  To post to this group, send email to sqlalch...@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 sqlalch...@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] Which columns changing during orm commit?

[Michael Hipp]

SQLAlchemy seems pretty smart about updating only the changed columns in an orm 
object...


If I have an orm object. Something changes one of the columns. Just before I 
commit() the session, is there a way to tell which columns will be updated vs 
those that are unchanged?


Any way to ascertain the before/after values on those changed columns?

Thanks,
Michael

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Performance: orm vs sql

[Michael Hipp]

The little diddly below is comparing performance of orm access vs sql 
expression language.


When I run it with number=1 I get a 5.8x advantage for sql. When I run it 10 
times I get a 2.7x advantage. The actual numbers are, respectively:


1.47375132
0.25630808

5.45569524
1.96911144

Is this a typical/expected difference in performance between the two query 
methods?

Michael



def timing1():
orm method
recs = sess.query(dm.Dealer).order_by('name').all()

def timing2():
sql method
dealers = dm.Dealer.__table__
recs = engine.execute(select([dealers], order_by='name')).fetchall()

def timing():
t = timeit.Timer(timing1)
print t.timeit(number=1)
t = timeit.Timer(timing2)
print t.timeit(number=1)

if __name__ == __main__:
db.start(DATABASE)
from common.database import engine
sess = db.Session()
timing()

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: INSERT…RETURNING being issued wrongly

[Michael Bayer]

On Aug 18, 2010, at 12:29 PM, Oliver Beattie wrote:

 On Aug 18, 3:39 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 Sent from my iPhone
 
 On Aug 18, 2010, at 8:27 AM, Oliver Beattie oli...@obeattie.com wrote:
 
 
 
 
 
 I'm not entirely sure why this is happening… it seems to work for me
 in nearly all other circumstances so I'm a bit stumped. Basically, I
 have a declarative table which has a character field as its primary
 key (it's not an ID which can be returned by the server), yet
 SQLAlchemy is issuing an INSERT…RETURNING statement for it.
 
 The scenario that seems to make it happening is like this. I'm trying
 to create a copy of an existing object, with a new ID. All the
 attributes on the new object should be pulled from the old object
 (which I'm doing through use of the iterate_properties iterator along
 with setattr, getarre calls), apart from this primary key field (name)
 which is a character field and will be something different. A relation
 on the old object will be updated to point at the created object.
 When commit() happens, I get an IntegrityError because it's thinking
 the db will return it the primary key (even though I have explicitly
 set it on the non-persistant instance).
 
 Is this a know bug or something I'm doing wrong?
 
 If the primary key is a character field, you have to ensure the attribute is 
 populated before the insert occurs.  Otherwise sqlalchemy assumes the value 
 is created by a generator of some kind such as a column default and issues 
 RETURNING, which is perfectly valid for any type of column, to get the value 
 back.
 
 Thanks for your reply, Michael — the value is being populated (as in,
 I am setting the value on the instance before I do session.add()) — is
 this what you mean?

yup.  if the correct attribute is populated it will be present in the VALUES 
clause of the INSERT.  check your SQL logs to ensure this is the case.


 
 
 
 
 
 
 --
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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 sqlalch...@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] Performance: orm vs sql

[Michael Bayer]

On Aug 18, 2010, at 4:16 PM, Michael Hipp wrote:

 The little diddly below is comparing performance of orm access vs sql 
 expression language.
 
 When I run it with number=1 I get a 5.8x advantage for sql. When I run it 10 
 times I get a 2.7x advantage. The actual numbers are, respectively:
 
 1.47375132
 0.25630808
 
 5.45569524
 1.96911144
 
 Is this a typical/expected difference in performance between the two query 
 methods?

Whats important to note is that the ORM is providing state-managed proxy 
objects, keyed on an identity map, which will faithfully represent the correct 
state in the transaction at all times.   A resultset is just a quick tuple.

I've done some competetive testing recently around the unit-of-work capable 
Python ORM field, I'll leave it as an exercise who it is we compete with, and 
we are still close to the same speed for small result sets and still faster for 
large result sets (and still with all the other performance enhancing features 
like eager loads that aren't possible with the competition).   So we're doing 
very well.




 
 Michael
 
 
 
 def timing1():
orm method
recs = sess.query(dm.Dealer).order_by('name').all()
 
 def timing2():
sql method
dealers = dm.Dealer.__table__
recs = engine.execute(select([dealers], order_by='name')).fetchall()
 
 def timing():
t = timeit.Timer(timing1)
print t.timeit(number=1)
t = timeit.Timer(timing2)
print t.timeit(number=1)
 
 if __name__ == __main__:
db.start(DATABASE)
from common.database import engine
sess = db.Session()
timing()
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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.