[sqlalchemy] Re: Locking tables

[Nathan Harmston]

Hi,

Thanks for that..it does seem like committing on the session
causes the connection to be closed, but I can write a workaround.
I m about to implement the multiple inserts changes but I thought this
would be a quick bang or a small amount of effort.

http://www.nevermind.co.nz/2009/10/13/speeding-up-inserts-on-mysql/

Thanks for the advice,

Nathan

2009/10/31 Michael Bayer mike...@zzzcomputing.com:


 On Oct 30, 2009, at 9:45 AM, Nathan Harmston wrote:


 Hi,

 I have a script which will process a large amount of data and save
 some of those results to the database. This takes a large amount of
 time and so what I would do in something like java is to lock the
 tables before processing, do the work, and then lock them after
 processing..concurrency isnt needed, no other users apart from
 me. I lock the tables before in order to get a speed increase. I am
 trying to figure out how to do this using SQLAlchemy..

 that's interesting that locking in a non-concurrent situation would
 provide a speed increase (is this mysql ?).     Also you'd get hugely
 better performance batching your rows to be inserted into a single
 execute() call instead of executing N INSERTs from the python side,
 thats the biggest speed issue with your code.

 anyway you're mixing up an individual Connection with a query on your
 Session, which pulls its own Connection independently of that which
 you acquired from the engine.   So you'd probably want to use
 session.connection() to share that connection.   or to join a Session
 onto your connection, see the examples at 
 http://www.sqlalchemy.org/docs/05/session.html#joining-a-session-into-an-external-transaction
  .




 session = SDY.Session()
 conn = SDY.serendipity_engine.connect()
 analysis =SDY.Analysis()
 ans = {}
 session.add(analysis)

 locks = [ intervals, entities ]
 q = session.query(SDY.Sentence).order_by(SDY.Sentence.sentence_id)
    for s in iterresults(q, conn, locks):
         results = go(s)
         conn.execute(entities_table.insert(), results )

 class iterresults(object):
    def __init__(self, query, conn = None, locks = []):
        self.query = query
        self.n = 1000
        self.conn = conn
        self.locks = locks
    def __iter__(self):
        start = 0
        while True:
            index = 0
            for tblname in self.locks:
                self.conn.execute(lock tables  + tblname +  write)

            r = self.query[start:start + self.n]
            for i in r:
                index = index + 1
                yield i
            if index  self.n:
                break
            else:
                start += self.n
            if self.locks:
                self.conn.execute(unlock tables)

 I get the following exception:

 sqlalchemy.exc.OperationalError: (OperationalError) (1100, Table
 'entities' was not locked with LOCK TABLES) u'INSERT INTO entities
 (interval_id, type) VALUES (%s, %s, %s, %s, %s)' [29046L, 'FOO']

 What I am doing wrong? Is there something with the way conn.execute
 and insert operate? And can I find a way around this?

 Nathan

 


 


--~--~-~--~~~---~--~~
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] Locking tables

[Nathan Harmston]

Hi,

I have a script which will process a large amount of data and save
some of those results to the database. This takes a large amount of
time and so what I would do in something like java is to lock the
tables before processing, do the work, and then lock them after
processing..concurrency isnt needed, no other users apart from
me. I lock the tables before in order to get a speed increase. I am
trying to figure out how to do this using SQLAlchemy..

session = SDY.Session()
conn = SDY.serendipity_engine.connect()
analysis =SDY.Analysis()
ans = {}
session.add(analysis)

locks = [ intervals, entities ]
q = session.query(SDY.Sentence).order_by(SDY.Sentence.sentence_id)
for s in iterresults(q, conn, locks):
 results = go(s)
 conn.execute(entities_table.insert(), results )

class iterresults(object):
def __init__(self, query, conn = None, locks = []):
self.query = query
self.n = 1000
self.conn = conn
self.locks = locks
def __iter__(self):
start = 0
while True:
index = 0
for tblname in self.locks:
self.conn.execute(lock tables  + tblname +  write)

r = self.query[start:start + self.n]
for i in r:
index = index + 1
yield i
if index  self.n:
break
else:
start += self.n
if self.locks:
self.conn.execute(unlock tables)

I get the following exception:

sqlalchemy.exc.OperationalError: (OperationalError) (1100, Table
'entities' was not locked with LOCK TABLES) u'INSERT INTO entities
(interval_id, type) VALUES (%s, %s, %s, %s, %s)' [29046L, 'FOO']

What I am doing wrong? Is there something with the way conn.execute
and insert operate? And can I find a way around this?

Nathan

--~--~-~--~~~---~--~~
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: Vertical partitioning, mappers and foreign keys

[Nathan Harmston]

I m still having problems with this vertical partitioning malarky (I
think the previous problem was due to a redundant copy of one of the
tables existing in the wrong database) , and it seems to currently be
when I have a secondary table involved between the two vertical
partitions

engine1 = create_engine(database_string)
engine2 = create_engine(database_string2)

Base = declarative_base()
metadata = Base.metadata

celllines_table = Table(celllines, metadata,
Column(cellline_id, String(14), primary_key=True),
Column(name, Text)
   )

celllines_to_species_table = Table(cll2spe_table, metadata,
   Column(cellline_id, String(14),
ForeignKey(celllines.cellline_id)),
   Column(taxa_id, Integer,
ForeignKey(taxonomy_species.taxa_id)),
   )

species_table = Table('taxonomy_species', metadata,
  Column('taxa_id', Integer, primary_key=True),
  Column('rank', Text)
  )
celllines_to_species_table.create(bind = engine1)
species_table.create(bind=engine2)
celllines_table.create(bind = engine1)

mapper(Taxa, species_table)
mapper(CellLine, celllines_table, properties = {

species:relation(Taxa, secondary=celllines_to_species_table)
})

Session = sessionmaker(twophase=True)
Session.configure(binds={Taxa:engine2, CellLine:engine1})

So when I try to access the species, the following queries are issued 

2009-08-18 13:49:51,150 INFO sqlalchemy.engine.base.Engine.0x...77d0 XA BEGIN %s
2009-08-18 13:49:51,151 INFO sqlalchemy.engine.base.Engine.0x...77d0
['_sa_0590dc58dd5fe4e11919839594ae35f3']
2009-08-18 13:49:51,152 INFO sqlalchemy.engine.base.Engine.0x...77d0
SELECT celllines.cellline_id AS celllines_cellline_id, celllines.name
AS celllines_name
FROM celllines
WHERE celllines.cellline_id = %s
 LIMIT 0, 2000
2009-08-18 13:49:51,152 INFO sqlalchemy.engine.base.Engine.0x...77d0
['CellLine_5162']
CellLine_51622009-08-18 13:49:51,246 INFO
sqlalchemy.engine.base.Engine.0x...7c10 XA BEGIN %s
2009-08-18 13:49:51,246 INFO sqlalchemy.engine.base.Engine.0x...7c10
['_sa_79f9588fb7d56ddea80688cc02faa7d0']
2009-08-18 13:49:51,249 INFO sqlalchemy.engine.base.Engine.0x...7c10
SELECT taxonomy_species.taxa_id AS taxonomy_species_taxa_id,
taxonomy_species.rank AS taxonomy_species_rank
FROM taxonomy_species, cll2spe_table
WHERE %s = cll2spe_table.cellline_id AND taxonomy_species.taxa_id =
cll2spe_table.taxa_id
2009-08-18 13:49:51,249 INFO sqlalchemy.engine.base.Engine.0x...7c10
['CellLine_5162']
[]

With nothing in the species..I can't understand whats going on ...
my only thought is that cll2spe_table isn't being found correctly. Is
there something going wrong. I am using 0.5.3 of SQLAlchemy.

Any help greatly appreciated yet again,

Nathan
PS I continued the old thread as its still regarding the same
topic...I hope this is ok.

2009/8/14 Michael Bayer mike...@zzzcomputing.com:

 Nathan Harmston wrote:

 HI,

 I m currently trying to vertical partition my database and I have
 split it into two groups of tables (say { documents, table1, table2 }
 and { table3 }.
 So I have

 s_engine = create_engine(t_database_string)#, echo=True)
 #echo=debug)#, echo=True)
 t_engine = create_engine(t_database_string)

 Base = declarative_base()

 metadata = Base.metadata

 documents = Table(documents', metadata,
       Column(document_id, Integer, primary_key = True),
 }

 table1 = Table(table1, metadata,
                        Column(interval_id, Integer, primary_key=True),
                       Column(document_id, Integer,
 ForeignKey(documents.document_id)),
 )

 table3 = Table(table3, metadata,
  Column(foo_id, Integer, primary_key = True),
 )

 table2 = Table(table2, metadata,
  Column(id, Integer, primary_key = True),
  Column(interval_id, Integer, ForeignKey(table1.interval__id))
  Column(foo_id, Integer, ForeignKey(table3.foo_id))
 )

 secondary = table1.join(table2,
 onclause=table1.c.interval_id==table2.c.interval_id).alias(spe_join)

 table3.create(bind=t_engine)
  etc



 mapper(Foo, table3)
 mapper(Document, documents_table, properties={
                                           foos:relation(Foo,
 primaryjoin=documents_table.c.document_id ==
 secondary.c.intervals_document_id,

 secondaryjoin=secondary.c.table2_foo_id == table3.c.foo_id,
                                                                viewonly =
 True,

 secondary=secondary)

                                          })
 session = sessionmaker(binds={Document:s_engine, Foo:t_engine})

 however what happens is that it complains about the foos relation in
 that it says that
 sqlalchemy.exc.ArgumentError: Could not determine relation direction
 for secondaryjoin condition 'spe_join.table2_foo_id = foo.foo_id', on
 relation Document.foos. Specify the foreign_keys argument to indicate
 which

[sqlalchemy] Vertical partitioning, mappers and foreign keys

[Nathan Harmston]

HI,

I m currently trying to vertical partition my database and I have
split it into two groups of tables (say { documents, table1, table2 }
and { table3 }.
So I have

s_engine = create_engine(t_database_string)#, echo=True)
#echo=debug)#, echo=True)
t_engine = create_engine(t_database_string)

Base = declarative_base()

metadata = Base.metadata

documents = Table(documents', metadata,
  Column(document_id, Integer, primary_key = True),
}

table1 = Table(table1, metadata,
   Column(interval_id, Integer, primary_key=True),
  Column(document_id, Integer,
ForeignKey(documents.document_id)),
)

table3 = Table(table3, metadata,
 Column(foo_id, Integer, primary_key = True),
)

table2 = Table(table2, metadata,
 Column(id, Integer, primary_key = True),
 Column(interval_id, Integer, ForeignKey(table1.interval__id))
 Column(foo_id, Integer, ForeignKey(table3.foo_id))
)

secondary = table1.join(table2,
onclause=table1.c.interval_id==table2.c.interval_id).alias(spe_join)

table3.create(bind=t_engine)
 etc



mapper(Foo, table3)
mapper(Document, documents_table, properties={
  foos:relation(Foo,
primaryjoin=documents_table.c.document_id ==
secondary.c.intervals_document_id,

secondaryjoin=secondary.c.table2_foo_id == table3.c.foo_id,
   viewonly = True,

secondary=secondary)

 })
session = sessionmaker(binds={Document:s_engine, Foo:t_engine})

however what happens is that it complains about the foos relation in
that it says that
sqlalchemy.exc.ArgumentError: Could not determine relation direction
for secondaryjoin condition 'spe_join.table2_foo_id = foo.foo_id', on
relation Document.foos. Specify the foreign_keys argument to indicate
which columns on the relation are foreign.

When I had all the tables in the same database I had no problems
involving having to specify foreign keys. What does SQLalchemy do it
the background here? Is my problem that it doesnt know that table3 is
accessed via the t_engine. How can I make it explicitly know this
here? I have tried using different metadata bindings but to no good.
Any pointers/help would be greatly appreciated?

Nathan

--~--~-~--~~~---~--~~
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] Accessing attributes hidden by a join

[Nathan Harmston]

Hi everyone, I am trying to get access to attributes which are hidden by a join:

I have two tables

intervals_table = Table(intervals, metadata,
Column(interval_id, Integer, primary_key=True),
Column(sentence_id, Integer,
ForeignKey(sentences.sentence_id)),
Column(start, Integer, nullable=False),
Column(end, Integer, nullable=False),
Column(deleted, Integer(1), default=0))

entities_table = Table(entities, metadata,
Column(entity_id, Integer, primary_key=True),
Column(interval_id, Integer,
ForeignKey(intervals.interval_id)),
Column(type, String(20), nullable=False),
Column(deleted, Integer(1), default=0),
)

and I have two classes:

class Interval(object):
pass

class Entity(object):
   pass

which are mapped to these tables:
mapper(Interval, intervals_table)
mapper(Entity, entities_table.join(intervals_table,
intervals_table.c.interval_id == entities_table.c.interval_id))

this leaves Entity with one deleted attribute (the one in the
entities_tablesensible)...but I would like to make it so I could
access the interval.deleted in the interval_table. Is there an easy
way to allow this?

If I then wanted to extend this so the Interval class has a derived
attribute deleted ( the or of interval.deleted and entity.deleted ) is
this possible to do aswell?

Many thanks in advance

Nathan

--~--~-~--~~~---~--~~
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] Unique parent class and various subclasses

[Nathan Harmston]

Hi,
Currently I have a mapper like this:

class Entity:
def __init__(self, type, create, sentence_id, start, end):
 self.type = type
 self.create = create
 self.sentence_id = sentence_id
 self.start = start
 self.end = end

class Interval:
def __init__(self, sentence_id, start, end):
 self.type = type
 self.create = create
 self.start = start
 self.end = end

mapper(Interval, intervals_table)
mapper(Entity, entities_table.join(intervals_table,
intervals_table.c.interval_id == entities_table.c.interval_id))

However I have the problem that an interval is unique ( there is a
UniqueConstraint(sentence_id, start, end) ). So when I create an
Entity object like so
   e = Entity(FOO, MADE BY BAR, 100, 2, 3 )
   session.add(e)
   session.commit()

then it works fine, now if I try to do this:
   e = Entity(SPAM, MADE BY GUMP, 100, 2, 3)
   session.add(e)
   session.commit()

then this will throw an IntegrityException. So I have tried using a
transaction-esque way of doing this, first trying to select the
interval_id from the intervals table and then setting e's interval_id.
However this still doesnt work. Has anybody done anything like this
before? Is there a simple way of doing this, maybe at the level of the
mapper?

Many thanks in advance,
Nathan

--~--~-~--~~~---~--~~
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] Accessing classes a table away in a mapper - why does this work?

[Nathan Harmston]

Hi,

I have been struggling with trying to create relations which reference
objects a couple of tables away.

e.g

Sentence has many entities
Entity has many NormalisedVersion
NormalisedVersion has one Gene

kind of thing

and was trying to link from Sentence to genes directly.

secondary = entities_table.join(entities_genes_normalised_table,
onclause=and_(entities_table.c.entity_id==entities_genes_normalised_table.c.entity_id,
entities_table.c.deleted == 0 )).alias(fubar)

mapper(Sentence, sentences_table, properties={ genes:relation(Gene,
primaryjoin=sentences_table.c.sentence_id ==
secondary.c.entities_sentence_id,

secondaryjoin=secondary.c.entities_genes_normalised_entrez_gene_id ==
genes_table.c.entrez_gene_id,
   viewonly = True,

secondary=secondary)

I spent ages on this hacking/reading/getting frustrated and finally
tried putting the alias call on the end of the secondary join, which
makes it work perfectly.
Without it calling sentence.genes gives me all of the genes found in
all of the sentences. So my question is really ... why does this work?
What effect does the alias have?

Many thanks in advance,

Nathan

--~--~-~--~~~---~--~~
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: Accessing classes a table away in a mapper

[Nathan Harmston]

The problem was solved by using a join query and then specifying both
foreign keys and the secondary argument to the relation.

Many thanks,

Nathan


secondary = sentences_table.join(tag_table.join(processed_tags_table,
processed_tags_table.c.tag_id == tag_table.c.tag_id),
sentences_table.c.sentence_id ==
tag_table.c.sentence_id)

mapper(Zone, zones_table, properties={
  species:relation(Name,

primaryjoin=zones_table.c.zone_id == secondary.c.sentences_zone_id,

foreign_keys = [ secondary.c.sentences_zone_id, names_table.c.name_id
],

secondary=processed_tags_normalised_table,
viewonly=True)


2009/5/19 Michael Bayer mike...@zzzcomputing.com:


 On May 18, 2009, at 8:06 PM, Michael Bayer wrote:


 if you want to make a chained join like that, use just primaryjoin(),
 dont use secondary, and set viewonly=True.

 I might be off on the dont use secondary advice.   if you do use
 secondary, secondary is the middle table, so in this case it
 seems like it would be tags_table and the target would be
 processed_tag_table.

 As an alternative to the above approach, you can also try using
 traditional relations between each class and use the association proxy
 to simplify access from one end to the other.

 this advice stlll holds though (as does use viewonly=True).


 


--~--~-~--~~~---~--~~
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: Accessing classes a table away in a mapper

[Nathan Harmston]

I think I jumped the gun a bit this morning with my excitement.

The results are wrong in that they return all of the identifiers in my
names table.

species_sentences_secondary = sentences_table.join(entities_table,
and_(sentences_table.c.sentence_id == entities_table.c.sentence_id,
entities_table.c.deleted == 0 ))

print dir(species_sentences_secondary), species_sentences_secondary.c
mapper(Zone, zones_table, properties={ species:relation(Taxa,
primaryjoin=zones_table.c.zone_id ==
species_sentences_secondary.c.sentences_zone_id,

secondary=entities_species_normalised_table,
viewonly=True
)

is my mapper .. ( I m sorry the naming has changed but I've been
updating my code from toy code to real app code)

a Zone has many sentences a Sentence has many entities (some of these
entities are normalised and has one row in
entities_species_normalised, which links to one Taxa)

I want to get a list of species mentioned in that Zone...but I
just can't get it rightI have tried everything I can think
of, but I just can't see the final step to finish it off.

Many thanks in advance

Nathan









2009/5/20 Nathan Harmston iwanttobeabad...@googlemail.com:
 The problem was solved by using a join query and then specifying both
 foreign keys and the secondary argument to the relation.

 Many thanks,

 Nathan


 secondary = sentences_table.join(tag_table.join(processed_tags_table,
 processed_tags_table.c.tag_id == tag_table.c.tag_id),
                                sentences_table.c.sentence_id ==
 tag_table.c.sentence_id)

 mapper(Zone, zones_table, properties={
                                      species:relation(Name,

 primaryjoin=zones_table.c.zone_id == secondary.c.sentences_zone_id,

 foreign_keys = [ secondary.c.sentences_zone_id, names_table.c.name_id
 ],

 secondary=processed_tags_normalised_table,
                                                            viewonly=True)


 2009/5/19 Michael Bayer mike...@zzzcomputing.com:


 On May 18, 2009, at 8:06 PM, Michael Bayer wrote:


 if you want to make a chained join like that, use just primaryjoin(),
 dont use secondary, and set viewonly=True.

 I might be off on the dont use secondary advice.   if you do use
 secondary, secondary is the middle table, so in this case it
 seems like it would be tags_table and the target would be
 processed_tag_table.

 As an alternative to the above approach, you can also try using
 traditional relations between each class and use the association proxy
 to simplify access from one end to the other.

 this advice stlll holds though (as does use viewonly=True).


 



--~--~-~--~~~---~--~~
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] Accessing classes a table away in a mapper

[Nathan Harmston]

Hi,

I am trying to map a relation in class which allows it to access
objects from a couple of tables away ie.

class Zone(object):
pass

class Sentence(object):
   pass

class RawTag(object):
pass

class ProcessedTag(RawTag):
   pass

mapper(RawTag, tag_table)
mapper(Sentence, sentence_table, ptags:relation(RawTag,
primaryjoin=and_(sentences_table.c.sentence_id==tag_table.c.sentence_id,
tag_table_table.c.deleted==0 ), secondary=processed_tag_table) )
mapper(ProcessedTag, processed_tag_table)
mapper(Zone, zone_table)

There is a 1:M relation between Document and Sentence and between
Sentence and RawTag. I want to be able to access all of the
ProcessedTags for a Zone from the Zone class, I can do this for the
Sentences table using the tags relation that I defined. But I am
having trouble in allowing the same behaviour for Zone.

So I have tried to use primaryjoin in order to to do this but I am not
having any luck.
Among the many exceptions I can generate:
 ptags:relation(ProcessedTag,
primaryjoin=and_(and_(sentences_table.c.zone_id ==
zones_table.c.zone_id,
sentences_table.c.sentence_id==tags_table.c.sentence_id),
tags_table.c.deleted==0 ), secondary=processed_tag_table)

UnmappedColumnError: No column sentences.sentence_id is configured on
mapper Mapper|Zone|zones

Any pointers greatly appreciated!

Many thanks
Nathan

--~--~-~--~~~---~--~~
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] Joined table inheritance mapping issues

[Nathan Harmston]

Hi,

I am playing with the mapping inheritance structures to my database.

I have a the following hierarchy:

class Tag(object):
pass

class NormalisedNameTag(Tag):
pass

class NormalisedPlaceTag(Tag):
pass

The idea is that a pre-processing step will produce a series of tags
and then all of the Tags will be postprocessed to normalise them ( map
them to a specific identifier in a controlled vocabulary).

mapper(Tag, tags_table, polymorphic_on=tags_table.c.type)
mapper(NormalisedNameTag, tag_name_normalised_table,
polymorphic_identity='NAME', inherits=Tag )
mapper(NormalisedPlaceTag, tag_name_normalised_table, inherits=Tag,
polymorphic_identity=PLACE)

however, if I do the following

 for entity in session.query(SDY.Tag).filter(SDY.Tag.type ==
NAME).filter(SDY.Tag.deleted == 0):
print entity, type(Entity)
# look up entity in a dictionary and assign an identifier to it
and store in NormalisedNameTag
entity.name_id = dictionary[ entity.word ]
session.commit()

then SQLAlchemy decides that it is actually looking for
NormalisedNameTag instead of just plain old Tag.

raise exc.ObjectDeletedError(Instance '%s' has been deleted. %
state_str(state))
sqlalchemy.orm.exc.ObjectDeletedError: Instance 'NormalisedNameTag at
0x2004ef0' has been deleted.

So I am confusing SQLAlchemy into thinking that the object has been
deleted when in fact I have never created it? So is there a way round
for this? Can you defer the loading of the inheritance mapping until
later?

Many thanks in advance, yet again,

Nathan

--~--~-~--~~~---~--~~
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 orderedlist with a secondary table

[Nathan Harmston]

Hi,

I am currently trying to make a relation between Document and Author, where
there is a many to many association which is dealt with by a secondary table
and I am trying to store the position of an author in the author list.
mapper(Paper, document_table, properties={'journal':relation(Journal,
backref='paper'),
  'authors':relation(Author,
secondary=papers_to_authors_table,

collection_class=ordering_list('position'),

order_by=[papers_to_authors_table.c.position]),
  'keywords':relation(Keyword,
secondary=documents_to_keywords_table)
  })

papers_to_authors_table = Table('p2a_association', metadata,
Column('document', Integer,
ForeignKey('documents.document_id')),
Column('author', Integer,
ForeignKey('authors.author_id')),
Column('position', Integer)
)

However when I try to do this:
 File Worker.py, line 281, in module
p = Paper(title, date, authors, journal, j_volume, j_issue, keywords,
abstract, None, body, pmid, institution)
  File
/cluster/soft/Linux_2.6_64/lib/python2.5/site-packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/orm/attributes.py,
line 1218, in init
oldinit(instance, *args, **kwargs)
  File /cluster/home/nathan/work/workspace/txtmining/Registry.py, line
258, in __init__
self.authors.append(a)
  File
/cluster/soft/Linux_2.6_64/lib/python2.5/site-packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/orm/collections.py,
line 922, in append
fn(self, item)
  File
/cluster/soft/Linux_2.6_64/lib/python2.5/site-packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/ext/orderinglist.py,
line 214, in append
self._order_entity(len(self) - 1, entity, self.reorder_on_append)
  File
/cluster/soft/Linux_2.6_64/lib/python2.5/site-packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/ext/orderinglist.py,
line 202, in _order_entity
have = self._get_order_value(entity)
  File
/cluster/soft/Linux_2.6_64/lib/python2.5/site-packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/ext/orderinglist.py,
line 189, in _get_order_value
return getattr(entity, self.ordering_attr)
AttributeError: 'Author' object has no attribute 'position'

So is there an easy way of performing this functionality using the ordered
list or do I need to do something else?

Many thanks in advance

nathan

--~--~-~--~~~---~--~~
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: SQL Expressions as Mapped Attributes

[Nathan Harmston]

Thanks for that, I think I should have made my question a little clearer,
(it works for what I was asking but not what I want it to do).

spec_names = join(species_table, species_names_table,
and_(species_table.c.rank==species,
species_table.c.taxa_id==species_names_table.c.taxa))
mapper(Species, spec_names, properties={'names':column_property(
species_names_table.c.name)})

So far I have done this gives me kind of the correct result, except I get
multiple instances of a Species object back because it has multiple names.
Instead what I what to do is just create a single instance of the Species
object with a list of names in an attribute called names. How do I
accomplish this?

Also what do you mean by the correlated criterion of the column_property I
have looked for this and have been unable to find it.

Many thanks in advance,

Nathan

2009/1/26 Michael Bayer mike...@zzzcomputing.com


 On Jan 26, 2009, at 12:50 PM, Nathan Harmston wrote:

 Hi,

 I am currently trying to use an SQL expression as a mapped attribute. I
 have a table called species_table and a species_names_tables, there is a one
 to many relationship between them on species_table.c.taxa_id and
 species_names_table.c.taxa. So one species can have multiple names. I am
 currently trying to make it so that a Species object has an attribute called
 names which is a list of the names held in the species_table (there are
 other attributes in this table, but I don't want any of them). So this is
 what I ve tried to do:

 mapper(Species, select([species_table],
 species_table.c.rank=='species').alias('speciesfooalias'),
 properties={'names':column_property(select([species_names_table.c.name],
 species_table.c.taxa_id==species_names_table.c.taxa).label(names))})

 #metadata.create_all(engine)
 session = Session()
 s  =session.query(Species).filter(Species.taxa_id==7).one()
 this is the problem, because now it says that
 raise exceptions.InvalidRequestError('No rows returned for one()')
 sqlalchemy.exceptions.InvalidRequestError: No rows returned for one()

 however if I just do it using a simple mapper with no column_property:
 mapper(Species, select([species_table],
 species_table.c.rank=='species').alias('speciesfooalias'))

 I get the correct output. I am using SQLAlchemy 0.4.8

 I am pretty confused by this. Any help is very much appreciated.


 your column_property() must be expressed in terms of the mapped selectable.
  Therefore assign your select([species_table]) to a variable, map to that,
 and also reference it in the correlated criterion of your column_property().

 in this case, since you are mapping to a select anyway, its probably easier
 and more efficient to map to a simple join of species_table and
 species_names_table, including species_names_table.c.name in the list of
 columns so that it is mapped directly, and forego the usage of the separate
 column_propery().



 


--~--~-~--~~~---~--~~
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] SQL Expressions as Mapped Attributes

[Nathan Harmston]

Hi,

I am currently trying to use an SQL expression as a mapped attribute. I have
a table called species_table and a species_names_tables, there is a one to
many relationship between them on species_table.c.taxa_id and
species_names_table.c.taxa. So one species can have multiple names. I am
currently trying to make it so that a Species object has an attribute called
names which is a list of the names held in the species_table (there are
other attributes in this table, but I don't want any of them). So this is
what I ve tried to do:

mapper(Species, select([species_table],
species_table.c.rank=='species').alias('speciesfooalias'),
properties={'names':column_property(select([species_names_table.c.name],
species_table.c.taxa_id==species_names_table.c.taxa).label(names))})

#metadata.create_all(engine)
session = Session()
s  =session.query(Species).filter(Species.taxa_id==7).one()
this is the problem, because now it says that
raise exceptions.InvalidRequestError('No rows returned for one()')
sqlalchemy.exceptions.InvalidRequestError: No rows returned for one()

however if I just do it using a simple mapper with no column_property:
mapper(Species, select([species_table],
species_table.c.rank=='species').alias('speciesfooalias'))

I get the correct output. I am using SQLAlchemy 0.4.8

I am pretty confused by this. Any help is very much appreciated.

Many thanks in advance,

Nathan

--~--~-~--~~~---~--~~
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] Looking for a pattern/simple way to simplify parsing and storing files in a database

[Nathan Harmston]

Hi everyone,

I m trying to get to grips with SQLAlchemy by parsing a file, extracting
certain fields and then storing them in a database. I am currently using the
object relational and trying to get a sense of how it all works. I have two
objects Paper and Author, which is a many to many relationship. So currently
I am parsing the file and then creating a Paper object

names = [ list of author names ]
authors = [ Author(a) for a in names ]
p = Paper(title, authors)
session.add(p)
session.commit(p)

However, when I get to a new paper with an already existing author I get the
following error,
sqlalchemy.exceptions.IntegrityError: (IntegrityError) (1062, Duplicate
entry 'Sole R' for key 'name') u'INSERT INTO authors (name) VALUES (%s)'
['Sole R']

which makes sense, however I d prefer not to query the database over and
over to check if Sole R has already been added to the database. So I try a
different approach without building Author objects and using the name string
instead as an arg to Paper:
and get the error:
AttributeError: 'str' object has no attribute '_state'

So I'm out of ideas, I want to make it fast as possible and without firing
off lots of queries to the database to look for identity. Is there a way
supported by the ORM that I ve missed completely, so is it more normal to
create a cache whereby you add things to a dict and look for identity in the
dict otherwise query the database? The problem is that some of the files I
am going to be parsing are quite large (Gb) and I dont want to saturate my
database server with requests and likewise I only have limited memory on my
machines.
Have I missed something simple in the documentation?

My code is below:

Many thanks in advance,

Nathan



document_table = Table('documents', metadata,
   Column('id', Integer, primary_key=True),
   Column('title', String, nullable=False),
  )

authors_table = Table('authors', metadata,
  Column('id', Integer, primary_key=True),
  Column('name', String(40), unique=True),

  )

papers_to_authors_table = Table('p2a_assocation', metadata,
Column('document', Integer, ForeignKey('
documents.id')),
Column('author', Integer, ForeignKey('
authors.id'))
)

class Author(object):
def __init__(self, name):
self.name = name

class Paper(object):
def __init__(self, title, authors):
self.title = title
self.authors = author


mapper(Paper, document_table, properties={'authors':relation(Author,
secondary=papers_to_authors_table, backref='publications')})
mapper(Author, authors_table)

--~--~-~--~~~---~--~~
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] exceptions.ArgumentError when trying to map class to a table

[Nathan Harmston]

Hi everyone ,

I have the following setup (snipped):

in_table = Table('in', metadata,
  Column('id', Integer, primary_key=True),
  Column('pA', String(6), ForeignKey('p.id'),
nullable=False),
  Column('pB', String(6), ForeignKey('p.id'),
nullable=False),
  Column('evidence', Integer, ForeignKey('documents.id'),
nullable=False)
  )
mapper(Paper, document_table, properties={'authors':relation(Author,
secondary=papers_to_authors_table, backref='publications')})
mapper(Author, authors_table)
mapper(Ent, p_table)
mapper(Cooccurrence, in_table, properties={ 'evidence':relation(Paper) })

I can't seem to get the last mapper to work and I get the following
exception:

  raise exceptions.ArgumentError(WARNING: column '%s' not being added due
to property '%s'.  Specify 'allow_column_override=True' to mapper() to
ignore this condition. % (column.key, repr(prop)))
sqlalchemy.exceptions.ArgumentError: WARNING: column 'evidence' not being
added due to property 'sqlalchemy.orm.properties.PropertyLoader object at
0x2aa171b6d0'.  Specify 'allow_column_override=True' to mapper() to ignore
this condition.

So I try to use the allow_column_override but it doesnt work and I just get
a new error:

raise exceptions.UnmappedColumnError(Can't execute sync rule for
destination column '%s'; mapper '%s' does not map this column.  Try using an
explicit `foreign_keys` collection which does not include this column (or
use a viewonly=True relation). % (dest_column, source_mapper))
sqlalchemy.exceptions.UnmappedColumnError: Can't execute sync rule for
destination column 'ppi.evidence'; mapper 'Mapper|Paper|documents' does not
map this column.  Try using an explicit `foreign_keys` collection which does
not include this column (or use a viewonly=True relation).

Any help greatly appreciated,
Many thanks in advance,

Nathan

--~--~-~--~~~---~--~~
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] Problem Mapping table/relationship to a dictionary.

[Nathan Harmston]

Hi,

 I m trying to write a way of storing a 2 level dictionary in a database.

class Foo(object):
   def __init__(self):
   self.a = {}
   def __iadd__(self, i):
   if i not in self.a:
   self.a[i] = {}
   return self
   def keys(self):
   for i in self.a.keys():
   yield i
   def __getitem__(self, k):
   return self.a[k]
  def save(self):
  # saves the object in the database

f = Foo()
f += Node1
f[Node1][Node2] = edge

THe idea is that its a self-referential join (node1 - node2 ) with
association object (edge), ie. node1 has many edges. one edge has has
node1 and 1 node 2.

IS there a pattern in sqlalchemy for doing this sort of thing, I
noticed in the new version of SQLalchemy you can map to sets, but I m
a little clueless where to start.

Any help muchly appreciated.

Nathan

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Problems trying to run a query - typeerror

[nathan harmston]

HI,

I m currently trying to build an api for a database and I really like the
way that Djangos manager ( Class_name.objects ). This seems very intuitive
for me. After reading through the some of the django source and getting
slightly confused I ve implemented a basic version for one of my tables. The
problem is I get an exception, when I m not expecting one. If this isnt a
specific sqlalchemy question then please tell me so I can re-post to python
etc.

registry.py -- contains table definitions etc.

Manager.py

from Registry import *

class Manager(object):
def __init__(self, model, table=None):
self.model = model
self.table = table
def get(self, slice):
pass
def all(self):
print 1
mapper(self.model, interpro_table)
print 2
session = create_session()
print 3
query = session.query(self.model)
print 4
return query.select()

Models.py

from Manager import *

class ModelBase(type):
def __new__(cls, name, bases, dict):
print cls
setattr(cls, 'objects', Manager(cls))
return type.__new__(cls, name, bases, dict)

class Model(object):
__metaclass__=ModelBase

class InterPro(Model):
_tableName = interpro_table
def __init__(self, interpro_ac):
self.interpro_ac = interpro_ac
def __str__(self):
return InterPro: %s %(self.interpro_ac)
def __repr__(self):
return InterPro: %s %(self.interpro_ac)

if __name__=='__main__':
a = Manager(InterPro)
print a
print a.all() -- this prints out all of the objects in the database
i = InterPro('IPR014697')
print InterPro.objects
print InterPro.objects.all()
 -- this fails and produces the exception.

Traceback (most recent call last):
  File Model.py, line 28, in ?
print InterPro.objects.all()
  File /home/skeg/workspace/test/src/Manager.py, line 17, in all
return query.select()
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 247,
in select
return self.select_whereclause(whereclause=arg, **kwargs)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 252,
in select_whereclause
return self._select_statement(statement, params=params)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 378,
in _select_statement
return self.execute(statement, params=params, **kwargs)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 310,
in execute
return self.instances(result, **kwargs)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 329,
in instances
self.mapper._instance(context, row, result)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/mapper.py, line
1213, in _instance
instance = self._create_instance(context.session)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/mapper.py, line
1234, in _create_instance
obj = self.class_.__new__(self.class_)
TypeError: __new__() takes exactly 4 arguments (1 given)

Does anyone know what the problem is? Is it the way I ve programmed this
using metaclasses or is it sqlalchemy and the way in instantiates objects or
even both?

Many Thanks in advance,

Nathan

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Mapping existing structure of a database

[nathan harmston]

Hi,

I m currently playing with using sqlalchemy to map a pre-existing
database to objects. I ve had a quick look through the docs and not
being able to find anything about this.

Does SQLAlchemy support introspection (if thats the right word,
probably not) into existing databases, if so how? If not is there any
advice you have to actually use SQLAlchemy in such a way.

I m trying to create an API to my database and dont really want to
have to declare all the tables again.

Many Thanks in advance,

Nathan


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Mapping existing structure of a database

[nathan harmston]

What kind of overhead is associated with using the autoload flag?

What kind of overhead would be associated with this over a network? (with a
remote database). Is there a way to dump the structure of a database to a
file and import this as a kind of module?

Thanks

Nathan

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---