[sqlalchemy] Many-to-Many Adjacency Relationships
Hello all,
I need a little help, guidance to get a Many-to-Many Adjacency
Relationships, a.k.a. a dependency graph working.
Here is the short code:
metadata = MetaData('sqlite:///')
tasks_table = Table('tasks', metadata,
Column('id', Integer, primary_key = True),
Column('name', String()),
)
task_depends_on_tasks_table = Table('task_depends_on_tasks', metadata,
Column('task_id', Integer, ForeignKey('tasks.id')),
Column('dependent_id', Integer, ForeignKey('tasks.id')),
)
metadata.create_all()
class Task(object):
def __init__(self, name):
self.name = name
The problem is off cause the mapping configuration, some blindly
copy'n'pasting ended up in this:
m = mapper(Task, tasks_table,
properties = {
'dependsOn' : relation(Task,
secondary = task_depends_on_tasks_table,
remote_side = [tasks_table.c.id],
primaryjoin =
tasks_table.c.id == \
task_depends_on_tasks_table.c.task_id,
secondaryjoin =
tasks_table.c.id == \
task_depends_on_tasks_table.c.dependent_id,
),
}
)
m.compile()
But expectantly this doesn't work. So how would I do this?
Thank you very much.
Regards
Bert Wesarg
--~--~-~--~~~---~--~~
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: Slow relation based assignment.
Hello again Michael,
Have read the documentation you referenced, but am still unsure how to
now delete a Tag without generating the following error: (Note - using
Postgres in production)
(IntegrityError) update or delete on tags violates foreign key
constraint employeesTags_tag_id_fkey on employeesTags
DETAIL: Key (id)=(3) is still referenced from table employeesTags.
'DELETE FROM tags WHERE tags.id = %(id)s' {'id': 3}
Without the lazy='dynamic' it works fine (correctly deletes entries
from employeesTags first).
The delete operation I am performing is:
session.begin()
entry = session.query(Tag).filter_by(id=3).first()
try:
session.delete(entry)
session.commit()
except Exception, error:
print error
session.rollback()
else:
print 'Deleted successfully'
Thanks again for all your help so far,
Martin
On Dec 5, 5:27 pm, Michael Bayer [EMAIL PROTECTED] wrote:
hi martin -
the issue is that each Tag object contains a collection of 1000
employees on it, and when you make an assignment in the forwards
direction (i.e. employee.tag.append(sometag)), the corresponding
reverse relation needs to be fully loaded and then updated according
to backref semantics. since you're using eager loading by default
between employees and tags, there is a load of 20,000 rows each time
an uninitialized tags.employees collection is touched.
To prevent the backref from being unnecessarily loaded, and since it
is a large collection, you should use a dynamic collection for the
reverse:
mapper(Employee, employees, properties={
'tags': relation(Tag,
secondary=employeesTags,backref=backref('employees', lazy='dynamic'),
lazy=False)
})
mapper(Tag, tags)
the employees collection on Tag is now a filterable Query object
which only queries when read from, and you'll see that the time goes
down to nothing. you can also append and delete from a dynamic
collection like a regular list.
large collection techniques are discussed
at:http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio...
we do have a ticket in trac to try improving upon backrefs to not load
unloaded collections in any case, this is ticket #871.
On Dec 5, 12:07 pm, Martin Pengelly-Phillips
[EMAIL PROTECTED] wrote:
Hello again,
I have recently noticed that a particular assignment seems to be
taking a relatively long time.
Not being a database expert I am confused as to whether the last
assignment 'person.tags = tags' should be so slow when referencing
existing tags that are used by other entities - it seems to try and
get a list of all employees that use the given tag and then spends the
time doing something with the resulting set, but why?
Test case below.
If the slow assignment is expected do you have any advice on how to
speed up such a statement?
Thank you in advance,
Martin
--
import os, datetime, time
from sqlalchemy import *
from sqlalchemy.orm import *
file = '/tmp/test.db'
if os.path.isfile(file): os.remove(file)
engine = create_engine('sqlite:///%s' % file, echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=False, bind=engine))
mapper = Session.mapper
# Classes
#--
class Employee(object):
def __init__(self, name=None):
self.name = name
def __repr__(self):
return '%s:%s' % (self.id, self.name)
class Tag(object):
def __init__(self, label):
self.label = label
# Setup tables
#--
employees = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False,
default='bob'),
Column('dob', DateTime, nullable=False,
default=datetime.datetime.now),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
employeesTags = Table('employeesTags', metadata,
Column('employee_id', Integer,
ForeignKey('employees.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#--
mapper(Employee, employees, properties={
'tags': relation(Tag, secondary=employeesTags,
backref='employees', lazy=False)})
mapper(Tag, tags)
# Test
#--
metadata.create_all(engine)
session = Session()
session.begin()
tags = []
for i in xrange(20):
tag = Tag(str(datetime.datetime.now()))
tags.append(tag)
for i in xrange(1000):
p = Employee('john%d' % i)
p.tags = tags
session.commit()
session.clear()
session.begin()
[sqlalchemy] Re: Question about an ORM query with an aggregate function
I forgot to mention, I am using SA 0.3.10.
Thanks,
Allen
On Dec 7, 2007 7:49 AM, Allen Bierbaum [EMAIL PROTECTED] wrote:
I am trying to create two queries with some of my SA ORM objects that
will use the sum of a field found through a relationship. To be a bit
more concrete, here is a simple setup similar to mine.
# table object
users_table = Table('users', meta,
Column('user_id', Integer, primary_key=True),
Column('user_name', String(16)),
Column('state', String(2))
)
order_table = Table('orders', metadata,
Column('user_id', Integer, ForeignKey(users.user_id)),
Column('total', Integer),
)
mapper(Order, order_table)
mapper(User, users_table, properties = {
'orders' : relation(Order)})
What I want to do is two different queries.
1. Return a list of all User objects meeting a given criteria along
with a field that is a sum of all their order totals.
For example:
users = session.query(User).filter(User.c.state == 'IA').X.all()
for u in users:
print u.rolled_up_order_total
2. Find all users in a given state (User.c.state == NY) with their
sum of all order totals being greater then some number X.
Very similar to above, but I don't need the total to come out as an
attribute on the object.
Is there some way to do this with SA and the ORM? I know that with
straight up SQL I could create a query for this information and store
the results of an aggregate sum function in a column alias, but how do
I do this with the ORM? Is there a way to create a query that
dynamically injects additional attributes into the loaded object on
demand? (if not, that sounds like a pretty nice feature to me :)
Thanks,
Allen
--~--~-~--~~~---~--~~
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] Question about an ORM query with an aggregate function
I am trying to create two queries with some of my SA ORM objects that
will use the sum of a field found through a relationship. To be a bit
more concrete, here is a simple setup similar to mine.
# table object
users_table = Table('users', meta,
Column('user_id', Integer, primary_key=True),
Column('user_name', String(16)),
Column('state', String(2))
)
order_table = Table('orders', metadata,
Column('user_id', Integer, ForeignKey(users.user_id)),
Column('total', Integer),
)
mapper(Order, order_table)
mapper(User, users_table, properties = {
'orders' : relation(Order)})
What I want to do is two different queries.
1. Return a list of all User objects meeting a given criteria along
with a field that is a sum of all their order totals.
For example:
users = session.query(User).filter(User.c.state == 'IA').X.all()
for u in users:
print u.rolled_up_order_total
2. Find all users in a given state (User.c.state == NY) with their
sum of all order totals being greater then some number X.
Very similar to above, but I don't need the total to come out as an
attribute on the object.
Is there some way to do this with SA and the ORM? I know that with
straight up SQL I could create a query for this information and store
the results of an aggregate sum function in a column alias, but how do
I do this with the ORM? Is there a way to create a query that
dynamically injects additional attributes into the loaded object on
demand? (if not, that sounds like a pretty nice feature to me :)
Thanks,
Allen
--~--~-~--~~~---~--~~
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] Query problem with chars acute or grave
I'am using SA 0.3.11 with MySql 5.0 through MySQLdb latest stable version and I don't get results when searching string with chars acute or grave like è, é, à. I haven't found flags on SA or MySQLdb for support exotic chars, I have miss something ? --~--~-~--~~~---~--~~ 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: IMPORTANT: Does SA caches objects in memory forever?
On Dec 7, 2007, at 1:17 AM, Arun Kumar PG wrote: I used weak ref dict and doing a session.clear() post every request. But when I do a len(gc.get_objects()) post every request I can see that the number of objects keeps on increasing. Do we have a background thread that frees up the unused objects in SA after a while? theres no threading code in sqlalchemy. the session lets go of things that are no longer referenced and especially if youre saying session.clear() it unconditionally clears everything. if youd like to ensure that your session is empty, just call len(list(session)). make sure youre not doing anything like new creating classes and mappers on the fly, mappers are stored in a registry (this registry should be self-cleaning also on the next release). Also, if your application uses multiple threads, and you are using a thread local storage object such as the scoped_session(), if the threading model is such that brand new threads are spawned for every request instead of reusing existing threads from a thread pool, that you call Session.remove() at the end of the thread's lifecycle. Otherwise that Session object will hang around in the thread local dict keyed to a thread id that doesn't exist anymore. --~--~-~--~~~---~--~~ 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: Key error when mapping against arbitrary select
fixed in r3867. your code below will still raise an exception, just a
more decriptive one.
On Dec 7, 2007, at 6:35 AM, Andrew Stromnov wrote:
persons_table = Table(u'persons', dbconfig.metadata,
Column(u'person_id', Integer(20), primary_key=True,
autoincrement=True, nullable=False, default=0),
Column(u'level', Integer(11), nullable=False, default=1),
autoload = False)
class Person(object):
pass
mapper(Person, persons_table)
stat_by_level = select([Person.level,
func.count(Person.level).label('count')],
order_by=[Person.level],
group_by=[Person.level]).alias('stats')
class StatByLevel(object):
pass
mapper(StatByLevel, stat_by_level)
desktop:~/Projects/test$ python model.py
Traceback (most recent call last):
File model.py, line 58, in module
mapper(StatByLevel, stat_by_level)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.2dev_r3866-
py2.5.egg/sqlalchemy/orm/__init__.py, line 518, in mapper
return Mapper(class_, local_table, *args, **params)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.2dev_r3866-
py2.5.egg/sqlalchemy/orm/mapper.py, line 153, in __init__
self._compile_pks()
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.2dev_r3866-
py2.5.egg/sqlalchemy/orm/mapper.py, line 421, in _compile_pks
if len(self._pks_by_table[self.mapped_table]) == 0:
KeyError: sqlalchemy.sql.expression.Alias object at 0x842c98c
--~--~-~--~~~---~--~~
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] Design: mapped objects everywhere?
More of a design question, complicated by the fact that I'm a database neophyte (although I've used SA a fair bit). I have a web application that with periodically connect to a database. I've written a wrapper for the db using SQLalchemy, but I'm unsure about the best way to use the mapped classes. In some cases the client will fetch records (mapped classes) from the database and immediately use and then dispose of them. No problems. In other cases, it will keep those fetched objects around indefinitely, perhaps late changing them and sending them back. In even other cases, it might create instances of those objects independently of the db, and use them, maybe checking them in. Put as a use case: A Sample may be created by the web application or fetched from the database. Later on, it may be disposed of, edited or checked back into the db. So, the design question is should the mapped (database) object classes be the same as the extra-db classes? On one hand, it's a lot simpler to have a single class Sample, rather than SampleRow (the mapped class) and SampleData (an unmapped, extra-db class). On the other hand, the requirements and coding of both classes are kinda different, and I find myself changing the properties of the mapped class for better mapping and making the use of the class in non-Db contexts more awkward. Also, the SA magic has made debugging of some non-Db issues quite difficult. Opinions or issues to consider. -- Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health [EMAIL PROTECTED] / [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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] dynamic relations
hi, what's wrong with the attached example?
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData('sqlite://')
#~ metadata.bind.echo = 'debug'
companies = Table('companies', metadata,
Column('company_id', Integer, primary_key=True),
Column('name', String(50)))
employees_table = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('company_id', Integer, ForeignKey('companies.company_id')),
Column('name', String(50)),
)
metadata.create_all()
class Person(object):
def __init__(self, **kwargs):
for key, value in kwargs.iteritems():
setattr(self, key, value)
def __repr__(self):
return Ordinary person %s % self.name
class Company(object):
def __init__(self, **kwargs):
for key, value in kwargs.iteritems():
setattr(self, key, value)
def __repr__(self):
return Company %s % self.name
company_mapper = mapper(Company, companies)
person_mapper = mapper(Person, employees_table, properties={
'company': relation(Company, backref=backref('employees', lazy='dynamic'))
})
session = create_session()
c = Company(name='company1')
c.employees.append(Person(name='joesmith'))
session.save(c)
session.flush()
session.clear()
c = session.query(Company).get(1)
print c.employees
print list(c.employees)
print c.employees.count()
metadata.drop_all()
[sqlalchemy] Re: Stats implementation question.
I think it shoud be done throught mapping against arbitrary select
(
http://www.sqlalchemy.org/docs/04/documentation.html#advdatamapping_mapper_selects
).
Thanks.
On 7 дек, 13:23, Andrew Stromnov [EMAIL PROTECTED] wrote:
How to implement this stat behaviour (RPG-like stats):
players_table = Table('player',
Column('id', Integer, primary_key=True),
Column('name', String),
Column('level', Integer),
)
# SELECT 'level', count('level') as 'count' FROM player ORDER BY
'level' GROUP BY 'level'
#
class Stats(object): pass
mapper(Stats, players_table, include_properties=['level'], properties
= {
'count': column_property( func.count('level).label('count') )
})
this fails with error...
And where 'ORDER BY' and 'GROUP BY' must be placed?
--~--~-~--~~~---~--~~
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] Key error when mapping against arbitrary select
persons_table = Table(u'persons', dbconfig.metadata,
Column(u'person_id', Integer(20), primary_key=True,
autoincrement=True, nullable=False, default=0),
Column(u'level', Integer(11), nullable=False, default=1),
autoload = False)
class Person(object):
pass
mapper(Person, persons_table)
stat_by_level = select([Person.level,
func.count(Person.level).label('count')],
order_by=[Person.level],
group_by=[Person.level]).alias('stats')
class StatByLevel(object):
pass
mapper(StatByLevel, stat_by_level)
desktop:~/Projects/test$ python model.py
Traceback (most recent call last):
File model.py, line 58, in module
mapper(StatByLevel, stat_by_level)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.2dev_r3866-
py2.5.egg/sqlalchemy/orm/__init__.py, line 518, in mapper
return Mapper(class_, local_table, *args, **params)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.2dev_r3866-
py2.5.egg/sqlalchemy/orm/mapper.py, line 153, in __init__
self._compile_pks()
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.2dev_r3866-
py2.5.egg/sqlalchemy/orm/mapper.py, line 421, in _compile_pks
if len(self._pks_by_table[self.mapped_table]) == 0:
KeyError: sqlalchemy.sql.expression.Alias object at 0x842c98c
--~--~-~--~~~---~--~~
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] Stats implementation question.
How to implement this stat behaviour (RPG-like stats):
players_table = Table('player',
Column('id', Integer, primary_key=True),
Column('name', String),
Column('level', Integer),
)
# SELECT 'level', count('level') as 'count' FROM player ORDER BY
'level' GROUP BY 'level'
#
class Stats(object): pass
mapper(Stats, players_table, include_properties=['level'], properties
= {
'count': column_property( func.count('level).label('count') )
})
this fails with error...
And where 'ORDER BY' and 'GROUP BY' must be placed?
--~--~-~--~~~---~--~~
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: IMPORTANT: Does SA caches objects in memory forever?
Felix Schwarz wrote:
I have a question which I think is similar enough to be asked
in the same
thread: I have a set of quite simple migration scripts which
us SQLAlchemy 0.4
and Elixir 0.4. I do extract data from the old legacy (MySQL)
database with
SQLAlchemy and put this data into new Elixir objects.
Currently, these scripts use up to 600 MB RAM. This is no
real problem as we
probably could devote a machine with 4 GB ram solely for the
automated
migration. But it would be nice to use lower-powered machines
for our migration
tasks.
What wonders me is that I do not (knowingly) keep references
neither to the old
data items nor the new elixir objects. Nevertheless memory
usage increases
during the migration. Is there any way to debug this easily
to see why Python
does need so much memory/which references prevent the objects
from being garbage
collected? Running the garbage collector manually did not
help much (saving only
about 5 MB).
fs
Here is a snippet that I've used before when trying to track down
objects that aren't getting cleaned up properly. I don't think it'll
find leaks of built-in types, but it should help with instances of
user-defined classes. Just call 'report_objects' every now and then.
import gc
_previous = {}
def report_objects(threshold=500):
objects = gc.get_objects()
print Number of objects in memory: %d % len(objects)
modules = {}
for obj in gc.get_objects():
if getattr(obj, '__module__', None) is not None:
module_parts = obj.__module__.split('.')
module = '.'.join(module_parts[:3])
modules.setdefault(module, 0)
modules[module] += 1
print Modules with %d objects: % threshold
dump_modules(modules, threshold)
if _previous:
changes = {}
for module, value in modules.items():
changes[module] = value - _previous.get(module, 0)
print Changes since last time:
dump_modules(changes, 10)
_previous.clear()
_previous.update(modules)
print
def dump_modules(modules, threshold):
maxlen = max(len(m) for m in modules)
l = [(value, module) for module, value in modules.items()
if value threshold]
if l:
l.sort(reverse=True)
for value, module in l:
print %*s %5d % (maxlen+1, module, value)
else:
printNone
-
The first time you call report_objects, you should get something like
this:
Number of objects in memory: 100794
Modules with 500 objects:
sqlalchemy.ext.assignmapper 1935
sqlalchemy.util 1362
sqlalchemy.types 1250
sqlalchemy.schema 1170
sqlalchemy.sql 1124
sqlalchemy.orm.unitofwork 1003
sqlalchemy.orm.strategies 956
sqlalchemy.orm.properties 750
sqlalchemy.orm.attributes 699
sqlalchemy.orm.mapper 681
testresults.define_schema 665
And then when you call it again some time later:
Number of objects in memory: 102349
Modules with 500 objects:
sqlalchemy.ext.assignmapper 1935
sqlalchemy.util 1418
sqlalchemy.types 1250
sqlalchemy.schema 1204
sqlalchemy.sql 1177
sqlalchemy.orm.unitofwork 1004
sqlalchemy.orm.strategies 993
sqlalchemy.orm.properties 750
sqlalchemy.orm.attributes 708
sqlalchemy.orm.mapper 681
testresults.define_schema 665
Changes since last time:
sqlalchemy.util56
sqlalchemy.sql53
sqlalchemy.databases.mysql49
MySQLdb.cursors45
sqlalchemy.orm.strategies37
sqlalchemy.schema34
MySQLdb.connections16
MySQLdb.converters11
Note that the module names are where the classes are defined, not where
they are used, but it may be enough to give you a clue.
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Slow relation based assignment.
On Dec 7, 2007, at 5:21 AM, Martin Pengelly-Phillips wrote:
Hello again Michael,
Have read the documentation you referenced, but am still unsure how to
now delete a Tag without generating the following error: (Note - using
Postgres in production)
(IntegrityError) update or delete on tags violates foreign key
constraint employeesTags_tag_id_fkey on employeesTags
DETAIL: Key (id)=(3) is still referenced from table employeesTags.
'DELETE FROM tags WHERE tags.id = %(id)s' {'id': 3}
Without the lazy='dynamic' it works fine (correctly deletes entries
from employeesTags first).
The delete operation I am performing is:
session.begin()
entry = session.query(Tag).filter_by(id=3).first()
try:
session.delete(entry)
session.commit()
except Exception, error:
print error
session.rollback()
else:
print 'Deleted successfully'
hey Martin -
I think this is actually a bug in sqlalchemy regarding the dynamic
relation; ive added ticket #895.
Ive recently enhanced regular relations to also not unnecessarily load
backrefs, that code is in trunk if youd like to try it, and i dont
think it has this particular issue.
Otherwise, for now, when you load the Tag, you have two (well, three)
choices: you can load the colleciton of Employees attached to the
Tag, and explicitly remove the tag from each employee; or, you can
implement ON DELETE CASCADE in your database on the foreign key in
question so that it automatically updates itself; finally, you can,
within the transaction, issue a DELETE FROM employees_tags where
tag_id=3 before you call session.commit(), although this might
conflict with existing Employee records.
implementing ON DELETE CASCADE is definitely the most legit way to
go here since it lets the database do most of the work.
--~--~-~--~~~---~--~~
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: Query problem with chars acute or grave
Expo wrote: I'am using SA 0.3.11 with MySql 5.0 through MySQLdb latest stable version and I don't get results when searching string with chars acute or grave like è, é, à. I haven't found flags on SA or MySQLdb for support exotic chars, I have miss something ? There is an option to MySQLdb to set the connection encoding but it's only needed when the server configuration isn't correct. For this database, are the characters valid in the server's configured connection encoding? Are they valid in the column's character set? A mismatch in either of those may leave you with incorrectly encoded data in the column, which would cause equality comparisons to fail. --~--~-~--~~~---~--~~ 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: Question about an ORM query with an aggregate function
Thanks for the feedback. I hadn't thought of doing it this way. I did comb through the documentation a 2nd and 3rd time today though and found the add_column() method for queries. It looks like that may be another way to get what I want. I am thinking about even creating a little builder function that takes the results from a query with add_column and adds them back to the primary object as custom attributes. Now that I have a couple options, I think I can get at least one of them to work. :) -Allen On Dec 7, 2007 3:11 PM, Paul Johnston [EMAIL PROTECTED] wrote: Hi, 1. Return a list of all User objects meeting a given criteria along with a field that is a sum of all their order totals. You want to create another class and mapper, UserWithSum (or a friendlier name) that maps to an arbitrary select. http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_selects 2. Find all users in a given state (User.c.state == NY) with their sum of all order totals being greater then some number X. Once you've achieved 1, this is pretty straightforward. Paul --~--~-~--~~~---~--~~ 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: Design: mapped objects everywhere?
Hi, A Sample may be created by the web application or fetched from the database. Later on, it may be disposed of, edited or checked back into the db. On the other hand, the requirements and coding of both classes are kinda different, and I find myself changing the properties of the mapped class for better mapping and making the use of the class in non-Db contexts more awkward. Sounds like you want your app to be mostly unaware of whether a class is saved in the db or not (i.e. persistent)? If so, I'd use a single class, design the properties so they work in non-persistent mode, and then they'll work in persistent mode as well. Paul --~--~-~--~~~---~--~~ 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] From arbitrary SELECT to Query
I have a compound SELECT statement (of class CompoundSelect) that is composed of a few normal SELECTs combined by UNION. from_obj of this SELECT contains all columns of a table that is mapped to a class. I would like to get objects created using defined mapper, but selected using my compound SELECT statement. I have found a solution: mappers have method 'instances' that can be used like that: instancesList = someMapper.instances(dbSession.execute(compoundSelect), dbSession) The problem is that I get normal Python list, which eats much resources when database is big. Much better would be Query object which supports lazy loading. Note that I cannot use Query.filter(compoundSelect._whereclause) because CompundSelect doesn't have _whereclause. Another solution is to use a new, non_primary mapper defined on compundSelect, but then I wouldn't have relations. I hope some simple solution exists :). Regards, Artur --~--~-~--~~~---~--~~ 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: dynamic relations
im glad people are finally using dynamic relationstry out rev 3869.
On Dec 7, 2007, at 10:25 AM, Vladimir Iliev wrote:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData('sqlite://')
#~ metadata.bind.echo = 'debug'
companies = Table('companies', metadata,
Column('company_id', Integer, primary_key=True),
Column('name', String(50)))
employees_table = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('company_id', Integer, ForeignKey('companies.company_id')),
Column('name', String(50)),
)
metadata.create_all()
class Person(object):
def __init__(self, **kwargs):
for key, value in kwargs.iteritems():
setattr(self, key, value)
def __repr__(self):
return Ordinary person %s % self.name
class Company(object):
def __init__(self, **kwargs):
for key, value in kwargs.iteritems():
setattr(self, key, value)
def __repr__(self):
return Company %s % self.name
company_mapper = mapper(Company, companies)
person_mapper = mapper(Person, employees_table, properties={
'company': relation(Company, backref=backref('employees',
lazy='dynamic'))
})
session = create_session()
c = Company(name='company1')
c.employees.append(Person(name='joesmith'))
session.save(c)
session.flush()
session.clear()
c = session.query(Company).get(1)
print c.employees
print list(c.employees)
print c.employees.count()
metadata.drop_all()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Design: mapped objects everywhere?
Paul Johnston wrote: Hi, A Sample may be created by the web application or fetched from the database. Later on, it may be disposed of, edited or checked back into the db. On the other hand, the requirements and coding of both classes are kinda different, and I find myself changing the properties of the mapped class for better mapping and making the use of the class in non-Db contexts more awkward. Sounds like you want your app to be mostly unaware of whether a class is saved in the db or not (i.e. persistent)? If so, I'd use a single class, design the properties so they work in non-persistent mode, and then they'll work in persistent mode as well. or like a single class that does the what and why, and an interchangeable layer/context that does load/saving (and the relations!). in such situations declarative programming helps a lot, so u dont bind your self to (the) db (or whatever persistency). Check dbcook.sf.net. My own latest experience is about turning a project that was thought for db/using dbcook into non-db simple-file-based persistency. The change was relatively small, like 5-10 lines per class - as long as there are Collections etc similar notions so Obj side of ORM looks same. --~--~-~--~~~---~--~~ 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: dynamic relations
i'm glad you're always so responsive ;-) Michael Bayer написа: im glad people are finally using dynamic relationstry out rev 3869. --~--~-~--~~~---~--~~ 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: From arbitrary SELECT to Query
On Dec 7, 2007, at 2:39 PM, Artur Siekielski wrote: I have a compound SELECT statement (of class CompoundSelect) that is composed of a few normal SELECTs combined by UNION. from_obj of this SELECT contains all columns of a table that is mapped to a class. I would like to get objects created using defined mapper, but selected using my compound SELECT statement. I have found a solution: mappers have method 'instances' that can be used like that: instancesList = someMapper.instances(dbSession.execute(compoundSelect), dbSession) use instances on Query. instances on mapper is long ago deprecated, added a warning in 3874. The problem is that I get normal Python list, which eats much resources when database is big. Much better would be Query object which supports lazy loading. Note that I cannot use Query.filter(compoundSelect._whereclause) because CompundSelect doesn't have _whereclause. apply the appropriate LIMIT/OFFSET criterion to the select in order to limit rows. The Query object doesnt have the option to fetch only part of a result sets since single instances may be comprised of many rows, the same object can appear many times in the same result set, and dependencies may exist between objects present in multiple rows; everything has to be held as unique against its identity across the full result set. --~--~-~--~~~---~--~~ 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: Slow relation based assignment.
hey Mike, Thanks for the update - I'll try it out tomorrow. Martin p.s. Have I mentioned you guys provide the best support I have encountered in a long time (including commercial products). On Dec 7, 4:14 pm, Michael Bayer [EMAIL PROTECTED] wrote: hey martin - this bug is fixed in trunk r3868, so if you use the svn trunk you can either keep using the dynamic or go back to the regular relation, you should be good in both cases. - mike --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
