[sqlalchemy] Mapping dictionaries with string keys and record values
Hi everybody.
After reading the documentation on dictionary based collections at
http://www.sqlalchemy.org/docs/mappers.html#dictionary-based-collections, I
am wondering if I am the only one who things that this code is intuitive:
item = Item()
item.notes['color'] = Note('color', 'blue')
print item.notes['color']
I'd rather write
item.notes['color'] = Note('blue')
That the key is stored with the value should be an implementation
detail I think.
I extended sqlalchemy.orm.collections.MappedCollection with a few lines
to implement this (attached).
Shouldn't something like this be included with SQLAlchemy? Or is this a
bad idea?
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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.
import operator
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.orm.collections import MappedCollection
class MyMappedCollection(MappedCollection):
def __init__(self, key_attr):
super(MyMappedCollection, self).__init__(operator.attrgetter(key_attr))
self._key_attr = key_attr
def __setitem__(self, key, value):
setattr(value, self._key_attr, key)
if key in self:
del self[key]
super(MappedCollection, self).__setitem__(key, value)
def map_on(attr):
return lambda: MyMappedCollection(attr)
metadata = sa.MetaData()
item_table = sa.Table(item, metadata,
sa.Column(id, sa.Integer, primary_key=True))
note_table = sa.Table(note, metadata,
sa.Column(id, sa.Integer, sa.ForeignKey(item_table.c.id), primary_key=True, index=True),
sa.Column(key, sa.String, primary_key=True),
sa.Column(value, sa.String),
sa.Column(desc, sa.String))
class Item(object): pass
class Note(object):
def __init__(self, **args): self.__dict__.update(args)
orm.mapper(Note, note_table)
orm.mapper(Item, item_table, properties=dict(
note=orm.relation(Note, cascade=all,delete-orphan, collection_class=map_on(key
engine = sa.create_engine(sqlite:///)
metadata.create_all(engine)
session = orm.sessionmaker(bind=engine)()
item = Item()
item.note[color] = Note(value=blue, desc=This should be blue because)
item.note[shape] = Note(value=rectangular)
session.add(item)
session.commit()
[sqlalchemy] Storing Nested Lists
Hi All, I'm new to sqlalchemy. I've been reading the documentation and group archives, but can't really find an answer to my question. I suspect it's a question of terminology, and that I don't really know the term for what I'm looking for. Can I map a nested list to one column, and have my nested list returned to me intact? A simple example is a list like: ['a','b','c', ['x','y','z',['m','n','o']]] If anyone can point me in the right direction, I'd much appreciate it. Thanks. -- 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 structure multiple polymorphic association tables
I have a real table A, parent of few non-real polymorphic tables B_i, each with a relationship that points to tables C_i, using a generic id column. There are no foreign keys from A, because the table the id column points to differs depending on a type column. For each of the B_i, I defined a relationship using primaryjoin and foreign_keys. This works well for retrieving data. I cannot set data without being explicit (setting the column id to the C_i's id value). I would like to do ObjB.link = ObjC, but viewing the debug log it does all the insertion well, but it never does the ObjB.link_id = ObjC.id. The only way I can get around it is doing it explicitly, but this requires first inserting ObjC in the database, flush to get the id, and then manually setting link_id. Is there a parameter or configuration that allow me to do this? -- 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] Cascading orphan deletes in self-referential table
Hi,
I'm using the following table (shortened, removed unnecessary columns)
to store a menu tree.
class MenuNode(Base):
__tablename__ = 'menu'
id = Column(Integer, primary_key=True, nullable=False,
unique=True)
parent_id = Column(Integer, ForeignKey('menu.id',
onupdate='CASCADE', ondelete='CASCADE'), nullable=True, index=True)
name = Column(String(64), nullable=False)
parent = relationship('MenuNode', remote_side='MenuNode.id',
cascade='all, delete', passive_deletes=True,
backref=backref('children', order_by=position))
Top-level menu nodes have parent_id=NULL.
When deleting a node via session.delete(node) sqlalchemy issues UPDATE
statements to set all children's parent_id fields to NULL instead of
deleting the children.
How can i tell SA that I want to delete any children?
--
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] Mapper table properties
On Apr 23, 2010, at 11:29 AM, Jason Baker jba...@zeomega.com wrote: Given an entity class (or entity instance), I'd like to get the table that is mapped to it. If I get the mapper using object_mapper/ class_mapper, then I get a mapper with the following properties defined (among others): local_table, mapped_table, and tables. Can someone help me understand what the difference between each of these properties is (or point me towards documentation on them)? Mapped_table is the selectable that is selected from. local_table is the selectable local to only this mapper and the mapped_table of submappers, but not the superclass mapper. -- 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] How do we retrieve the database dialect that a specific session is using?
On Apr 24, 2010, at 10:00 AM, Roy Hyunjin Han starsareblueandfara...@gmail.com wrote: Is there any way to retrieve the database dialect that a specific session is using? e.g. MySQL, PostGIS, etc. I am trying to modify Sanjiv Singh's GeoAlchemy extension so that a person can retrieve the appropriate database procedure for a given dialect, so that geospatial queries can be both direct (performed in a single database call) as well as dialect-neutral. For example, in PostGIS, getting the WKT of a geometry requires func.ST_AsText() session.query(func.ST_AsText(Road.geom)).first() However, in MySQL, the same procedure requires func.AsText() session.query(func.AsText(Road.geom)).first() I would like to be able to create a function called wkt() in both geoalchemy/mysql.py and geoalchemy/postgis.py that enables me to the following: session.query(session.dialect.wkt(Road.geom)).first() As I explained to sanjiv at pycon earlier this year, the best route to dialect specific resolution of SQL constructs is via the construction of custom SQL elements in combination with the sqlalchemy.ext.compiler extension - it's in the docs. Thanks, Roy Hyunjin Han Modi Research Group Earth Institute at Columbia University http://modi.mech.columbia.edu -- 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] Cascading orphan deletes in self-referential table
On 4/25/2010 6:39 AM, Adrian wrote:
class MenuNode(Base):
__tablename__ = 'menu'
id = Column(Integer, primary_key=True, nullable=False,
unique=True)
parent_id = Column(Integer, ForeignKey('menu.id',
onupdate='CASCADE', ondelete='CASCADE'), nullable=True, index=True)
name = Column(String(64), nullable=False)
parent = relationship('MenuNode', remote_side='MenuNode.id',
cascade='all, delete', passive_deletes=True,
backref=backref('children', order_by=position))
Top-level menu nodes have parent_id=NULL.
When deleting a node via session.delete(node) sqlalchemy issues UPDATE
statements to set all children's parent_id fields to NULL instead of
deleting the children.
How can i tell SA that I want to delete any children?
Does specifying cascade='all, delete-orphan' on the parent relationship
accomplish what you're after?
Lance
--
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] sql particle as bind parameter option:
On Apr 23, 2010, at 12:13 PM, Paul Balomiri paulbalom...@gmail.com
wrote:
On 23.04.2010, at 17:03, Michael Bayer wrote:
On Apr 23, 2010, at 9:59 AM, Paul Balomiri paulbalom...@gmail.com
wrote:
Hi,
Thanks, for your reply. I was aware that it is not just a string
substitution,
and that the bindparams also maps the type of the parameters e.t.c.
But is it possible to supply a type /set of types which are or
represent a lexical sub-structure ?
I might go into doing it, but first i want to check that i'm not
reinventing the wheel.
I noticed that the where clause can be supplied several times, so
select( [my_table_metadata]). where(). where(...) is valid,
thus it is possible
for this special case to insert a logical expression after the
initial select() definition.
I am looking for a similar, but more general case, not necessarily
using bindparam where func.now()
would be a valid substitution or insertion element into a
preexisting expression.
Why not just create a python function that generatively produces
the desired statement based on arguments? I don't see the
advantage to something more magical than that.
Well, this is what i already do:
lazy_sql_partial = functools.partial(lambda x: select([...] ,
from_obj(func.a(bindparam('a'),bindparam('b'),x ))) )
and then i name the binding like so:
lazy_sql_unbound = (lazy_sql_partial, (funcarg))
At the time when i generate the sql expression I do not know the
parameters, not even a default. Those are generated based on user
input.
in case parameters are missing for a whole expression the whole
expression is skipped.
A even simpler expression would be a=1 or a=2 or ... At the
moment i cannot generate such a constuct, which allows me to
define a select in one place, and then later add a or_(1,2,...)
clause at a certain point.As a special case i could use select
().where(1). where(2)... to get an and_(1,2,...)
An example :
Whenever the map window changes, i need to calculate the dataset
which falls out of the current window, and the ones which drops in.
Additionally i want to query the a modified sql expression whenever
the table changes (modified = with an additional in_ costraining to
pks in the table)
to do both i need to regenerate parts of the sql, but not all of it.
The way i do it now seems rather ugly because:
1) (partial_bound_func, (funcarg)) is error prone ( I know i cold
do more reflection to find out argument numbers and names , but it
would in the end duplicate the bindparam mechanism )
2) to execute i have to:
- first look for parameters which are arguments to the partial
generative functions myselect= lazy_sql_unbound( user input params
for partial )
- then use the remaining parameters in session.execute( )
It just feels like all of this rather belongs into the lib, because
it could be all solved by allowing bindparam to have a value of type
SQLExpression.
A bind parameter is not a sqlalchemy invention - it would be wrong
for us to redefine the term bind parameter to mean something
entirely different within the library. It is also not permitted by any
dbapi so it is impossible in any case.
You can acheive mostly the result you want using a custom SQL
expression construct, in conjunction with sqlalchemy.ext.compiler.
The construct would peek into a context-specific dictionary for its
contents at compile time and forward onto it's corresponding
construct. You'd stick the dictionary on the the outermost
containing construct, which you can then grab from the compiler
instance.
moreover, the expression substitutions could accept bindparams as
well.
As a last point , the compiler could check the validity, as it does
already.
regards
Paul Balomiri
paulbalom...@gmail.com
On 23.04.2010, at 15:26, Michael Bayer wrote:
On Apr 23, 2010, at 6:58 AM, Paul Balomiri wrote:
Hi,
Several days ago i has a similar question, which
was, perhaps a bit too theoretical. Here is a simple case:
select(text(select * from f(:time)) )
can I supply alternatively either
{time: datetime.datetime(1999,9,9,9,9,9)}
or
{time: now()}
I cannot get around that sqlalchemy interprets now() as a
string, while i try to use the postgres(or other backends) now()
function.
bind parameters exist only for the purpose of specifying literal
data to be injected as the values into a SQL statement. They
cannot be used to modify the lexical structure of a statement.
So a lexical unit like CURRENT TIMESTAMP or NOW() is part of
the statement's lexical source and cannot be injected as a bind
parameter.
To put it another way, bind parameters are not just string
substitutions the way something like foo %s % ('bar') is in
Python, even though they may look that way.
--
You received this message because you are subscribed to the
Google Groups sqlalchemy group.
To post to this group, send email to
Re: [sqlalchemy] Mapping dictionaries with string keys and record values
On Apr 25, 2010, at 7:31 PM, Michael Bayer wrote:
On Apr 25, 2010, at 1:38 PM, Torsten Landschoff
torsten.landsch...@dynamore.de wrote:
Hi everybody.
After reading the documentation on dictionary based collections at
http://www.sqlalchemy.org/docs/mappers.html#dictionary-based-collections,
I am wondering if I am the only one who things that this code is intuitive:
item = Item()
item.notes['color'] = Note('color', 'blue')
print item.notes['color']
I'd rather write
item.notes['color'] = Note('blue')
That the key is stored with the value should be an implementation detail I
think.
I extended sqlalchemy.orm.collections.MappedCollection with a few lines to
implement this (attached).
Shouldn't something like is be included with SQLAlchemy? Or is this a bad id
You should be able to roll this yourself as a custom dictlike collection
class .
sorry, it seems you've done that already. I'm surprised the existing
attribute_mapped_collection doesn't do this already ?
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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.
dictmapping.py
--
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] Mapping dictionaries with string keys and record values
On Apr 25, 2010, at 10:18 PM, Michael Bayer wrote:
On Apr 25, 2010, at 7:31 PM, Michael Bayer wrote:
On Apr 25, 2010, at 1:38 PM, Torsten Landschoff
torsten.landsch...@dynamore.de wrote:
Hi everybody.
After reading the documentation on dictionary based collections at
http://www.sqlalchemy.org/docs/mappers.html#dictionary-based-collections,
I am wondering if I am the only one who things that this code is intuitive:
item = Item()
item.notes['color'] = Note('color', 'blue')
print item.notes['color']
I'd rather write
item.notes['color'] = Note('blue')
That the key is stored with the value should be an implementation detail I
think.
I extended sqlalchemy.orm.collections.MappedCollection with a few lines to
implement this (attached).
Shouldn't something like is be included with SQLAlchemy? Or is this a bad id
You should be able to roll this yourself as a custom dictlike collection
class .
sorry, it seems you've done that already. I'm surprised the existing
attribute_mapped_collection doesn't do this already ?
well, it doesn't. It should, and maybe should raise an error if the two values
don't match. It doesn't make much sense to add another collection class to do
what the first one should.
I'm sure you noticed that you can remove the repetition by working the other
way, i.e.:
item.note.set(Note(key=color, value=blue, desc=This should be blue
because))
item.note.set(Note(key=shape, value=rectangular))
also, never do this:
class Note(object):
def __init__(self, **args):
self.__dict__.update(args)
you bypass all of SQLAlchemy's history tracking logic. do this instead:
class Note(object):
def __init__(self, **args):
for k, v in args.items():
setattr(self, k, v)
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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.
dictmapping.py
--
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.
--
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] sql particle as bind parameter option:
On Apr 25, 2010, at 7:46 PM, Michael Bayer wrote:
You can acheive mostly the result you want using a custom SQL expression
construct, in conjunction with sqlalchemy.ext.compiler. The construct would
peek into a context-specific dictionary for its contents at compile time and
forward onto it's corresponding construct. You'd stick the dictionary on
the the outermost containing construct, which you can then grab from the
compiler instance.
here's that:
from sqlalchemy import *
from sqlalchemy.sql.expression import ColumnElement
from sqlalchemy.ext.compiler import compiles
def with_sql_tokens(element, **tokens):
element._sql_tokens = tokens
return element
class SQLToken(ColumnElement):
def __init__(self, name):
self.name = name
@compiles(SQLToken)
def visit_sql_token(element, compiler, **kw):
value = compiler.statement._sql_tokens[element.name]
return compiler.process(value)
if __name__ == '__main__':
from sqlalchemy.sql import table, column
t1 = table('sometable', column('c1'), column('c2'))
s1 = select([t1]).where(SQLToken('whereclause'))
print with_sql_tokens(s1, whereclause=or_(t1.c.c1=='foo', t1.c.c2=='bar'))
--
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] custom dialect with no join syntax support possible?
On Apr 25, 2010, at 10:32 PM, Lance Edgar wrote: Michael, thanks for the tip(s). From what I can tell, SQLBase does also use the (+) syntax for outer joins. However, my requirements for this dialect are so specific that I won't bother adding that to my compiler unless/until I or someone else actually needs it. In the meantime, I'm making the package available at: http://sqlbase7-sa.edbob.org/ I'm assuming there's no reason whatsoever to include the dialect within the sqlalchemy codebase, but I mention the project site in case anyone else would find it useful. You're welcome to link to it as you have the Progress dialect (http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#Progress), which BTW I also appreciated as a starting point, but I suspect the audience will be terribly small or nonexistent so it's up to you. nice job ! I didn't even know we had Progress on the wiki there - feel free to log in and add notes for your dialect as well (I'm not sure if thats the best place for them, but since you went there that suggests it may be). Maybe a new section User-contributed dialects or something like that. Lance -- 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] Cascading orphan deletes in self-referential table
Does specifying cascade='all, delete-orphan' on the parent relationship accomplish what you're after? delete-orphan doesn't work in self-relational relationships (there are always some nodes without a parent). However, moving passive_deletes=True into the backref() fixed it. -- Adrian -- 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.
