Re: [sqlalchemy] Handling detached instances
On 30 Sep 2014, at 23:18, tonthon tontho...@gmail.com wrote: I didn't knew merge was supposes to be used in such a case, it works like a charm. Following that tip, I've added this decorator : def cache_wrapper(func): ensure a model returned from a cached function is attached to the current session def cached_func_wrapper(*args, **kwargs): obj = func(*args, **kwargs) if object_session(obj) is None and has_identity(obj): obj = DBSESSION().merge(obj) return obj return cached_func_wrapper Keep in mind that merge can still hit your SQL database to refresh and attributes. If you know your cache is not stale you will want to use the load=False parameter for merge() to prevent that from happening. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] too many SQL variables in in_
On 13 Sep 2014, at 11:25, pyArchInit ArcheoImagineers pyarchi...@gmail.com wrote: Il giorno giovedì 11 settembre 2014 18:39:24 UTC+2, Jonathan Vanasco ha scritto: i once thought about extending SqlAlchemy to handle this issue behind the scenes, but each database treats `IN()` differently. for example: oracle maxes out at a number of elements, while mysql maxes out based on the size of the overall statement (which is configured on the server). it's too much work to limit this in sqlalchemy, as these limits change across servers. [ i forget what postgres maxed out on, i think it was a hard number too.] the workaround I used was to just build a query-base, and then run multiple selects with a single `IN` within a for-loop which appends to a list. i found that performance to be much better than chaining multiple `IN()` with `OR` My problem is to find a dataset of more than 999 records and sort all through ORDER BY statement. How can I use multiple selects and order all records? You can try a different approach, such as creating a (temporary table) which you fill with all your ids, and then do a SQL statement with something like WHERE id IN (SELECT id FROM temp_table); That bypasses any limits in the IN operator. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can I make a rollback in a sequence?
On 20 Aug 2014, at 20:02, Rafael Henrique da Silva Correia
rafaelhenr...@gmail.com wrote:
Hi !
I have a block of code similar to this I made to test:
def TEST():
teste = Test(descricao=str('wololo'))
try:
db.session.add(wololo)
db.session.commit()
db.session.close()
except IntegrityError, e:
db.session.rollback()
db.session.close()
print e.message
[..]
I dont receive any error BUUUTT my sequence increases even in a case except
That’s just how PostgreSQL works. From the PostgreSQL documentation:
Important: To avoid blocking concurrent transactions that obtain numbers from
the same sequence, a nextval operation is never rolled back; that is, once a
value has been fetched it is considered used, even if the transaction that did
the nextval later aborts. This means that aborted transactions might leave
unused holes in the sequence of assigned values.
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Session.execute() calls aren't committing...
On 17 Apr 2014, at 04:43, Chip Kellam blackroomd...@gmail.com wrote: I have an application in which I primarily rely on MySQL/InnoDB and using the SQLAlchemy ORM and leveraging the transaction python module. Everything is good. My problem is that, try as I might, using code similar to the following, I can't get the transaction to commit: with transaction.manager: session.execute(INSERT INTO table (c1, c2) VALUES (v1, v2)) All I get is a rollback. Is there a pre-defined way to do this with issuing raw SQL BEGIN/END statements? From the zope.sqlalchemy documentation at https://pypi.python.org/pypi/zope.sqlalchemy : By default, zope.sqlalchemy puts sessions in an 'active' state when they are first used. ORM write operations automatically move the session into a 'changed' state. This avoids unnecessary database commits. Sometimes it is necessary to interact with the database directly through SQL. It is not possible to guess whether such an operation is a read or a write. Therefore we must manually mark the session as changed when manual SQL statements write to the DB. Which you do like this: from zope.sqlalchemy import mark_changed mark_changed(session) Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] celery and race conditions
On 15 Apr 2014, at 01:12, Jonathan Vanasco jonat...@findmeon.com wrote: i've got that now as a stopgap; i was hoping someone has better ideas. i don't like the idea of a post-commit hook, because i fear requesting the celery task request will create an error. I really don't want to build `transaction` support for celery, but i might need to. That isn't an uncommon scenario; I touched upon that in http://www.wiggy.net/articles/task-queues as well. For rq I am using a variant of https://gist.github.com/wichert/10714681 . One extra problem you need to take into account is that you are likely to run into problems when one of the arguments is a SQLAlchemy ORM instance: when your function is later run in another process that instance won't be associated with the current session, so you need to merge it. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] defining a relationship with IS NOT
On 15 Apr 2014, at 13:25, Richard Gerd Kuesters rich...@humantech.com.br wrote: interesting, i didn't knew that :D i was using shomething like (for softwares such as st2, which has pep8 checking): ## variables NULL = None # f**k pep-8 TRUE = True # f**k pep-8 FALSE = False # f**k pep-8 You can also use sqlalchemy.sql.null(), sqlalchemy.sql.true() and sqlalchemy.sql.false() Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] query returns sqlalchemy.util._collections.KeyedTuple. not a maped class instance
On 15 Apr 2014, at 13:06, robert rob...@redcor.ch wrote:
Hi there,
I m using SQLAlchemy V. 8.6 together with geomalcheny 2.4a second I get a call
I have a a mapped class:
# tblKey2goGdataLocation
#
#
class tblKey2goGdataLocation(Base):
__tablename__ = tblKey2goGdataLocation
id = Column(Integer, nullable=False, primary_key=True)
location = Column(Geometry('Point'))
name = Column( Text )
description = Column( Text )
location_type = Column( Integer )
companies = relation(
tblCompany,
secondary= tblCompanyLocation.__table__,
backref=locations,
)
I try to retrieve an maped instance using this query:
q = session.query(tblKey2goGdataLocation.__table__)
c = tblKey2goGdataLocation.__table__.c
q = q.filter(c['id'] == 123)
result = q.first()
now result is of type:
type(result)
class 'sqlalchemy.util._collections.KeyedTuple'
why?
Because you are asking SQLALchemy to return a table object from its query,
which is a somewhat odd thing to do. I'm surprised it actually returns anything
at all :). Since id is your primary key you can just do this:
result = session.query(tblKey2goGdataLocation).get(123)
This also won't do an extra SQL query if that object already happens to have
been loaded during the current session. if you want to filter on other columns
use the filter() method instead:
result = session.query(tblKey2goGdataLocation).filter(tblkey2goydatalocation.id
== 123).first()
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
On 08 Jan 2014, at 01:26, limodou limo...@gmail.com wrote: But I don't know why make this decision. Because where NULL will get nothing. And in 0.8.X version, I need to combine multiple condition according user input to one condition, so my code just like: cond = None for c in conditions: cond = c cond Why don’t you change the initial value to true() instead of None? If I read the documentation correctly that should work correctly in both SQLAlchemy versions. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] PostgreSQL: interval columns, values measured in months or years
On 25 Dec 2013, at 00:27, David Bolen db3l@gmail.com wrote: Sibylle Koczian nulla.epist...@web.de writes: Exactly, that's it. The stackoverflow discussion was very instructive, thank you! I'll try out psycopg2-dateutils. I'm a big fan of dateutil. If you do use it, you may also choose to bypass the use of timedelta entirely, since as you've seen it can fail to accurately represent the database value (timedelta only has days, minutes and seconds as components). For example, I use the code below to map PostgreSQL interval columns directly to dateutil's relativedelta subclass. How does your code differ from https://pypi.python.org/pypi/psycopg2-dateutils ? Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] PostgreSQL: interval columns, values measured in months or years
On 24 Dec 2013, at 08:11, Laurence Rowe laurencer...@gmail.com wrote: Postgres' INTERVAL supports deltas of quantities other than days, +1 month may mean 28/29/30/31 days depending on the month. From http://stackoverflow.com/questions/546321/how-do-i-calculate-the-date-six-months-from-the-current-date-using-the-datetime, dateutil has support for a richer relativedelta type and this can configure it on the psycopg2 level: https://pypi.python.org/pypi/psycopg2-dateutils In theory that works. Unfortunately the psycopg2-dateutils is two years old and still not installable due to a bug in its setup.py. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Using session.execute to bulk-insert into MySQL
On Oct 2, 2013, at 3:53 AM, Jonathan Vanasco jonat...@findmeon.com wrote: do you recall if `flush()` initiated 'mark_changed' at some point in history? i thought it did. I'm pretty sure it never did that. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Using session.execute to bulk-insert into MySQL
This message did not make it to the list last time, lets see if it work now..
On Sep 29, 2013, at 2:41 PM, Roman Iten roman.i...@sg-unteraegeri.ch wrote:
Hi
I started a pyramid project using the alchemy scaffold (includes
ZopeTransactionExtension to scope sessions to requests, see [1]).
Trying to insert many 'shots' into a MySQL database with a single SQL
statement, I implemented the following view:
DBSession = scoped_session(
sessionmaker(extension=ZopeTransactionExtension()))
@view_config(route_name='upload')
def upload_view(request):
shotlist = []
shotlist.append(Shot(score=92))
shotlist.append(Shot(score=82))
shotlist.append(Shot(score=99))
shotlist.append(Shot(score=78))
# The transaction is only committed if this line is enabled:
# DBSession.add(Shot(score=42))
DBSession.execute(Shot.__table__.insert(),
[shot.__dict__ for shot in shotlist])
return Response('OK')
But the transaction is not committed. I discovered that the transaction is
committed only if I call add() on DBSession (all five shots are inserted
then)…
That is expected behaviour: zope.sqlalchemy can only detect changes when you
use the ORM. If you execute statements directly you need to tell
zope.sqlalchemy explicitly that a change was made in the session. This is done
via mark_changed:
from zope.sqlalchemy import mark_changed
mark_changed(DBSession())
For more information see the zope.sqlalchemy documentation at
https://pypi.python.org/pypi/zope.sqlalchemy
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] possible bug in InstrumentedAttribute.__delete__?
On Jul 13, 2013, at 22:59, Michael Bayer mike...@zzzcomputing.com wrote: The original intent of default for Column is that it acts exactly like server_default, except it runs on the Python side for the case that your schema wasn't created with this default, or the functionality of your default is not supported by the database. But otherwise, just like a server_default, the value is None on the Python side until the INSERT occurs. For what it's worth I forget that all the time and end up flushing objects out just to get the default values right. Really, a better solution here would be an ORM-level default, that's what you've implemented anyway. In order to keep things pythonic I wonder if this should just be left as default arguments for the constructor. If everyone would do this: class Foo(Base): start_end = sa.Column(sa.Timestamp()) def __init__(self, start_end=datetime(2001, 2, 3), **kw): Base.__init__(self, start_end. **kw) This is standard python and easy to read. For association tables you already need to do this, either this way or by supplying a factory function, so the step to doing it for other objects is not that big. The downside is that the default value is now at a different place than the column definition, but it does make it very clear that this operates at the Python object level instead of the core SQL level. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] between .one() and .first()
On Jun 17, 2013, at 08:58 , Chris Withers ch...@simplistix.co.uk wrote:
Hi All,
I seems to commonly need to do a query which should return zero or one mapped
objects.
.one() isn't what I want as no returned object is ok.
.first() isn't what I want as if my query would return more than one object,
I have the query wrong and so want an exception.
Is there something already available that meets this need?
This will requrie you to run a query which limits the result to max 2 rows so
you can check if more than one result would be returned. I would just create a
simple wrapper function:
def one_optional(query):
rows = query.limit(2).all()
count = len(rows)
if count == 0:
return None
elif count == 1:
return rows[0]
else:
raise RuntimeError('More than one result found.')
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sqlalchemy does not enforce foreign key constraint
On May 23, 2013, at 07:16 , Shyam Purkayastha shyam.g...@gmail.com wrote: I am trying to play with the sql alchemy ORM based db definition with an inmemory sqlite db. SQLite does not enforce foreign key constraints unless you manually tell it to. You can do that with a PRAGMA foreign_keys=ON statement. See section two of http://www.sqlite.org/foreignkeys.html for the exact details. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relate to Object by Two Refs
On May 16, 2013, at 03:09 , Ji Zhang zhangj...@gmail.com wrote:
Hi,
Say I have a Request model and User model:
class Request(Base):
id = Column(Integer, primary_key=True)
user_id = Column(Integer)
admin_id = Column(Integer)
class User(Base):
id = Column(Integer, primary_key=True)
username = Column(String)
The Request is created by a user (User) and get verified by an admin (also a
User). How to get both request.user and request.admin?
You will need to specify the foreign keys used for the relation ship:
class Request(Base):
user_id = Column(Integer, ForeignKey('user.id'))
user = relationship(User, foreign_keys=[user_id])
admin_id = Column(Integer, ForeignKey('user.id'))
admin = relationship(User, foreign_keys=[admin_id])
You can find more details in the SQLAlchemy documentation:
http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#handling-multiple-join-paths
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Using a UUID as primary key
I was looking at using a UUID as primary key for a table. Using the backend-agnostic GUID type from http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type I get strange behaviour though. I whipped up a simple test case: class Data(BaseObject): __tablename__ = 'data' uuid = Column(GUID(), primary_key=True) metadata.create_all() session = sessionmaker(autocommit=False)() uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c' obj = Data(uuid=uuid) session.add(obj) assert session.query(Data).get(uuid) is obj assert session.query(Data).filter(Data.uuid == uuid).first() is obj Both asserts fail when I do this. The problem appears to be conversion from to a uuid.UUID instance in GUID.process_result_value: this always creates a new UUID instance, which probably causes a miss when SQLAlchemy tries to find an instance in its identify map. Is this a bug in the GUID example in the documentation, or is this something that can be improved in the identity map? Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Using a UUID as primary key
For reference I have attached a complete test case including a copy of the GUID
code from the documentation.
On May 6, 2013, at 23:22, Wichert Akkerman wich...@wiggy.net wrote:
I was looking at using a UUID as primary key for a table. Using the
backend-agnostic GUID type from
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type
I get strange behaviour though. I whipped up a simple test case:
class Data(BaseObject):
__tablename__ = 'data'
uuid = Column(GUID(), primary_key=True)
metadata.create_all()
session = sessionmaker(autocommit=False)()
uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c'
obj = Data(uuid=uuid)
session.add(obj)
assert session.query(Data).get(uuid) is obj
assert session.query(Data).filter(Data.uuid == uuid).first() is obj
Both asserts fail when I do this. The problem appears to be conversion from
to a uuid.UUID instance in GUID.process_result_value: this always creates a
new UUID instance, which probably causes a miss when SQLAlchemy tries to find
an instance in its identify map.
Is this a bug in the GUID example in the documentation, or is this something
that can be improved in the identity map?
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
On May 6, 2013, at 23:22, Wichert Akkerman wich...@wiggy.net wrote:I was looking at using a UUID as primary key for a table. Using the backend-agnostic GUID type fromhttp://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-typeI get strange behaviour though. I whipped up a simple test case:class Data(BaseObject): __tablename__ = 'data' uuid = Column(GUID(), primary_key=True)metadata.create_all()session = sessionmaker(autocommit=False)()uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c'obj = Data(uuid=uuid)session.add(obj)assert session.query(Data).get(uuid) is objassert session.query(Data).filter(Data.uuid == uuid).first() is objBoth asserts fail when I do this. The problem appears to be conversion from to a uuid.UUID instance in GUID.process_result_value: this always creates a new UUID instance, which probably causes a miss when SQLAlchemy tries to find an instance in its identify map.Is this a bug in the GUID example in the documentation, or is this something that can be improved in the identity map?Wichert.from uuid import UUID
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects import postgresql
metadata = MetaData(bind=create_engine('sqlite://'))
BaseObject = declarative_base(metadata=metadata)
class GUID(TypeDecorator):
Platform-independent GUID type.
Uses Postgresql's UUID type, otherwise uses CHAR(36), storing as
stringified hex values.
This implementation is based on the SQLAlchemy
`backend-agnostic GUID Type
http://www.sqlalchemy.org/docs/core/types.html#backend-agnostic-guid-type`_
example.
impl = CHAR
def load_dialect_impl(self, dialect):
if dialect.name == 'postgresql':
return dialect.type_descriptor(postgresql.UUID())
else:
return dialect.type_descriptor(CHAR(36))
def process_bind_param(self, value, dialect):
if value is None:
return value
elif dialect.name == 'postgresql':
return str(value)
else:
if not isinstance(value, UUID):
return str(UUID(value))
else:
# hexstring
return str(value)
def process_result_value(self, value, dialect):
if value is None:
return value
else:
return UUID(value)
class Data(BaseObject):
__tablename__ = 'data'
uuid = Column(GUID(), primary_key=True)
metadata.create_all()
session = sessionmaker(autocommit=False)()
uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c'
obj = Data(uuid=uuid)
session.add(obj)
assert session.query(Data).get(uuid) is obj
assert session.query(Data).filter(Data.uuid == uuid).first() is obj
Re: [sqlalchemy] SA 0.8.0 UnicodeDecodeError with Postgres 9.1.9
On Apr 19, 2013, at 08:54 , Andreas Jung j...@lpcnv.com wrote: Problem seems to be related how I moved the database (pg_dump + pg_restore). After moving the database files directory I can no longer reproduce this error. I would guess you loaded your database dump into a database with a different encoding, which is an easy to make and common mistake. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] updating with a Session ?
On Feb 6, 2013, at 03:05 , Jonathan Vanasco jonat...@findmeon.com wrote: I use SqlAlchemy in a Pyramid app. All my models, connections, etc are within and set up by Pyramid. I'm trying to do a maintenance script, and am a bit confused. In my script, thanks to a bootstraped commandline Pyramid feature, i have a Session which can query objects. great. results = dbSession.query( model.core.Useraccount )\ .filter(\ model.core.Useraccount.last_login.op(IS)(None) )\ .all() here's my problem. i want to do execute a command like this: stmt = model.core.Useraccount.update()\ .where( model.core.Useraccount.id.in_( list_of_uids ) )\ .values( last_login = 'now()' ) connection.execute(stmt) I'm not actually updating the last_login field. just using this as an example. Is your problem possibly that you are not commiting your changes? Without that your transaction is aborted when your script ends. Try adding this at the end of your script: import transaction transaction.get().commit() and see if that helps. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Docs in PDF are not being generated
On Jan 30, 2013, at 17:53 , Michael Bayer mike...@zzzcomputing.com wrote: I've asked people before about this seemingly ridiculous generate twice requirement of LaTeX and I get these incredulous answers like why? what's wrong with running it twice?, as though I'm being unreasonable. It is an artefact if how TeX works: it makes a single pass over your document, so if it is missing something that is defined later, like TOC entries, it can't fill them in and skips them. It does write status files during processing so that a later run will have the missing information. In some case you may even need to run it more than two times to get page number references to stabilise. This strategy was probably an excellent choice in the late 70s / early 80s when TeX was created and memory was a scarce resource. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Couple of questions about filtering...
On Jan 22, 2013, at 08:26 , Alexey Vihorev viho...@gmail.com wrote:
Hi. Couple of questions...
1. Does SQLA support deep filtering, i.e. something like this:
query(Invoice).filter(Invoice.Customer.Country.name=='France')
You'll need to use a join to do this:
query(Invoice).join(Customer).join(Country).filter(Country.name == 'France')
2.Can I use hybrid properties for filtering? I tried to do that, but that’s
what I got:
[..]
p = Person('John', 'Doe')
s.commit()
res = s.query(Person).filter(Person.full_name=='John Doe').all()
output:
Person.first_name Person.last_name
[]
Note that you never added p to the session, so it was never stored. Try this:
p = Person('John', 'Doe')
s.add(p)
print s.query(Person).all()
Regards,
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.8.0b2 released
On Dec 14, 2012, at 22:11, Michael Bayer mike...@zzzcomputing.com wrote: But the effect of only publishing 0.8.0b1 on Sourceforge was that I didn't get the impression that we had so many people going out and testing the beta as we normally do. I was reminded by Chris McDonough that a project which really can't afford to jump to 0.8 on an automatic basis should have a requirement set up to keep them on 0.7. So we'll see how it goes -if your project needs to stay on 0.7, *please* set up a requirement for SQLAlchemy 0.8 in your requirements.txt and/or install_requires. Otherwise, you'll be a beta tester for 0.8.0b2. There is a small problem here: 0.8b2 0.8 with python's versioning rules. There is an alternative spelling though: use SQLAlchemy 0.8dev to make sure all pre-releases for 0.8 are also rejected. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mixing association proxies and mixin classes
That works like a charm, thanks!
On 06/21/2012 12:46 AM, Michael Bayer wrote:
do it like this for now:
class FilterMixin(object):
@declared_attr
def _filters(cls):
cls.filters = association_proxy('_filters', 'filter')
return relationship(cls.filter_class,
cascade='all,delete,delete-orphan')
there's a patch for 0.8 only in http://www.sqlalchemy.org/trac/ticket/2517 .
On Jun 20, 2012, at 1:13 PM, Wichert Akkerman wrote:
I am struggling a little bit with mixin classes. The pattern I am trying to
implement is a mixin-class that adds a list of validated search queries to a
model. A minimised version of the code is below. The problem I am running into
is that putting an association_proxy on a mixin class does not appear to work
here: it always picks the first seen class type to create new values instead of
picking up what the relationship of the current instance requires. With the
example below that results in this error:
AssertionError: Attribute '_filters' on class 'class '__main__.TypeB'' doesn't
handle objects of type'class '__main__.FilterA''
My initial though was that this might be fixed by making the association_proxy
instance itself a declared_attr, but that results in other problems.
Is there an alternative way to implement this, or is this a bug in the
declarative logic?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
metadata = MetaData()
BaseObject = declarative_base(metadata=metadata)
class BaseFilter(BaseObject):
__abstract__ = True
id = Column(Integer(), primary_key=True, autoincrement=True)
filter = Column(UnicodeText(), nullable=False)
@declared_attr
def __tablename__(cls):
return '%s_filter' % cls.parent_tablename
@declared_attr
def parent_id(cls):
return Column(Integer(),
ForeignKey('%s.id' % cls.parent_tablename,
ondelete='CASCADE', onupdate='CASCADE'),
nullable=False, index=True)
def __init__(self, filter, **kw):
super(BaseFilter, self).__init__(filter=filter, **kw)
@validates('filter')
def validate_filter(self, key, value):
assert len(value) 2
return value
class FilterA(BaseFilter):
parent_tablename = 'type_a'
class FilterB(BaseFilter):
parent_tablename = 'type_b'
class FilterMixin(object):
@declared_attr
def _filters(cls):
return relationship(cls.filter_class,
cascade='all,delete,delete-orphan')
filters = association_proxy('_filters', 'filter')
#@declared_attr
#def filters(cls):
#return association_proxy('_filters', 'filter')
class TypeA(BaseObject, FilterMixin):
__tablename__ = 'type_a'
filter_class = FilterA
id = Column(Integer(), primary_key=True, autoincrement=True)
class TypeB(BaseObject, FilterMixin):
__tablename__ = 'type_b'
filter_class = FilterB
id = Column(Integer(), primary_key=True, autoincrement=True)
engine = create_engine('sqlite://')
metadata.bind = engine
metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
session.add(TypeA(filters=[u'foo']))
session.add(TypeB(filters=[u'foo']))
session.flush()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] mixing association proxies and mixin classes
I am struggling a little bit with mixin classes. The pattern I am trying
to implement is a mixin-class that adds a list of validated search
queries to a model. A minimised version of the code is below. The
problem I am running into is that putting an association_proxy on a
mixin class does not appear to work here: it always picks the first seen
class type to create new values instead of picking up what the
relationship of the current instance requires. With the example below
that results in this error:
AssertionError: Attribute '_filters' on class 'class '__main__.TypeB'' doesn't
handle objects of type'class '__main__.FilterA''
My initial though was that this might be fixed by making the
association_proxy instance itself a declared_attr, but that results in
other problems.
Is there an alternative way to implement this, or is this a bug in the
declarative logic?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
metadata = MetaData()
BaseObject = declarative_base(metadata=metadata)
class BaseFilter(BaseObject):
__abstract__ = True
id = Column(Integer(), primary_key=True, autoincrement=True)
filter = Column(UnicodeText(), nullable=False)
@declared_attr
def __tablename__(cls):
return '%s_filter' % cls.parent_tablename
@declared_attr
def parent_id(cls):
return Column(Integer(),
ForeignKey('%s.id' % cls.parent_tablename,
ondelete='CASCADE', onupdate='CASCADE'),
nullable=False, index=True)
def __init__(self, filter, **kw):
super(BaseFilter, self).__init__(filter=filter, **kw)
@validates('filter')
def validate_filter(self, key, value):
assert len(value) 2
return value
class FilterA(BaseFilter):
parent_tablename = 'type_a'
class FilterB(BaseFilter):
parent_tablename = 'type_b'
class FilterMixin(object):
@declared_attr
def _filters(cls):
return relationship(cls.filter_class,
cascade='all,delete,delete-orphan')
filters = association_proxy('_filters', 'filter')
#@declared_attr
#def filters(cls):
#return association_proxy('_filters', 'filter')
class TypeA(BaseObject, FilterMixin):
__tablename__ = 'type_a'
filter_class = FilterA
id = Column(Integer(), primary_key=True, autoincrement=True)
class TypeB(BaseObject, FilterMixin):
__tablename__ = 'type_b'
filter_class = FilterB
id = Column(Integer(), primary_key=True, autoincrement=True)
engine = create_engine('sqlite://')
metadata.bind = engine
metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
session.add(TypeA(filters=[u'foo']))
session.add(TypeB(filters=[u'foo']))
session.flush()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to (quick) check if a table exists in schema?
On 04/25/2012 03:57 PM, Massi wrote: Hi everyone, in my script I have to deal with a huge database with thousands of tables. Given a table name (a python string) I would have to now if such a table exists or not. Up to now I have written this function: def DBGetTableByName(table_name) : metadata = MetaData(engine) try : table = Table(table_name, metadata, autoload=True) return table except NoSuchTableError : return None I use its return value to check if the table exists, but the problem is that it is too slow. Since I have to repeat this operation several times I wonder if there is a faster (and smarter) way to perform this control. Any hints? Use the inspector: from sqlalchemy.engine.reflection import Inspector inspector = Inspector.from_engine(engine) print table_name in inspector.get_table_names() You can find the documentation here: http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=inspector#sqlalchemy.engine.reflection.Inspector Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] stucco_evolution 0.4 released
On 04/19/2012 07:43 PM, Michael Bayer wrote: If you've seen my recent talks you saw that I'm a little skeptical of what you're terming non-monolithic databases.Let's say this means, a database with a set of tables maintained by entirely different packages, but with the possibility of dependencies between those tables.If I understand correctly, if we were dealing with sets of tables that didn't have any dependency, you wouldn't need a distributed migration tool, each package would handle migrations for its own set of tables independently, is that right ? That suggests that every package would have its own migration tool, which is not very practical from a sysadmin point of view. I am an upgrading an application I want to be able to run all necessary migrations for all components of an application in one run. I do not want to be required to figure out which packages an application was running and then migrate them all separately. So I definitely see a need for an upgrade framework that can deal with multiple packages. I think what I need to see here are, what exactly are these packages, outside of the Django community, that actually create their own tables yet encourage dependencies between those tables and your app's own tables ? I know people are working on them since I see people asking questions about those use cases, but what are they ? What's the openid and user/groups package you're thinking of here ? s4u.image is such an example: https://github.com/2style4you/s4u.image . That package implements an image store which supports on-demand scaling of images. Metadata is stored in a SQL database and commonly you add references to images to other tables. Every site we build uses s4u.image to manage image handling. This happens to be in-house developed by us, but for all intents and purposes it is a third-party package to our front-end developers. In the development world I've always lived in, we just don't have third party libraries that bring in their own sub-schemas. Up until now the thinking has been, if it's significant enough that it is part of your datamodel, it's part of what you should own yourself, though certainly drawing upon past recipes. I suspect a difference is that we are often building different sites that build on shared common functionality. Our main business is building sites that deal with online fashion, so everything we build has to deal with things like images and clothing articles. The code to handle those has been split out to separate packages (s4u.image is one of those) that define core datamodels and some logic, and our sites build on those. Sometimes we extend the base models, for example when for a particular site we need to track extra data for clothing, and sometimes we use the base models as-is and reference them directly via relationships and foreign keys. That results in an ecosystem of many different packages and sites that each have their own evolve in their own way and require their own migrations. When we upgrade a site our process is pretty simple: upgrade version pins for buildout, rerun buildout, run upgrade-script, tell mod_wsgi to reload. The upgrade-script walks through all migrations from all packages a site uses so we have a single interface for administrators to upgrade everything. The upgrade framework itself is extremely minimal (see https://github.com/2style4you/s4u.upgrade ), but works well enough for us. Note that we deviate from stucco_evolution in three important ways: we do not use versioning but require upgrade steps to test if an upgrade is necessary, our upgrade framework is not tied to SQLAlchemy but has a more generic requirements-system so you can use it for other things (we use it for filesystem changes and SOLR configuration as well for example), and it does not support dependencies. Personally I consider the first two to be desirable qualities for an upgrade framework. Dependencies are something that we will probably need to add at some point. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] dogpile.cache 0.1.0 released
On 2012-4-9 17:28, Michael Bayer wrote: There's a decent README up now at http://pypi.python.org/pypi/dogpile.cache and you can read all the docs at http://dogpilecache.readthedocs.org/. I'm hoping to get some testers and initial feedback. Can you shed some light on how this differs from retools (http://readthedocs.org/docs/retools/en/latest/), other than that dogpile does not support redis and retools only supports redis? Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] dogpile.cache 0.1.0 released
On 2012-4-9 18:28, Michael Bayer wrote: On Apr 9, 2012, at 11:49 AM, Wichert Akkerman wrote: On 2012-4-9 17:28, Michael Bayer wrote: There's a decent README up now at http://pypi.python.org/pypi/dogpile.cache and you can read all the docs at http://dogpilecache.readthedocs.org/. I'm hoping to get some testers and initial feedback. Can you shed some light on how this differs from retools (http://readthedocs.org/docs/retools/en/latest/), other than that dogpile does not support redis and retools only supports redis? Basically the caching and distributed locking features of retools should be a dogpile backend, and in fact they can be if retools wants to publish a dogpile.cache backend. Though looking at the source he'd have to rip out some more rudimental functions out of CacheRegion.load(), which seems to be totally inlined right now. The redis lock itself could be used but needs a wait flag. Would you also be willing to accept a pull request (assuming you use git, otherwise a patch?) that adds a redis backend to dogpile directly? It seems like we'd almost be better off taking the stats logic wired into load and just making that an optional feature of dogpile, so that all the backends could get at hit/miss stats equally. The get/set/lock things i see in retools would only give us like a dozen lines of code that can actually be reused, and putting keys into redis and locking are obviously almost the same as a memcached backend in any case. It's just a question of, which project wants to maintain the redis backend. The statistics are certainly very useful. He appears to have a region invalidate feature taking advantage of being able to query across a range in redis, that's not something we can generalize across backends so I try not to rely on things like that, but dogpile can expose this feature via the backend directly. Region invalidate is very very useful in my experience: for us it allows us to have a management-system invalidate caches for a running website without having to make it aware of all the implementation details of the site. We can now simple say 'invalidate everything related to magazines' instead of invalidating 15 different functions separately (which will get out of sync as well). The function decorators and the dogpile integration in retools are of course derived from Beaker the same way dogpile's are and work similarly. Dogpile's schemes are generalized and pluggable. A problem I have with the Beaker and retools decorators is that they make it very hard to include context from a view into a cache key. For example for complex views it is very common that you want to cache a helper method for the view class, but you want the context and things like request.application_url to be part of the cache key, but those are never passed to the method. That leads to code like this: class MyView: @some_cache_decorator def _slow_task(self, context_id): # Do something def slow_task(self): return self._slow_task(self.context.id) one approach I used to take was to use a decorator which could take a function parameter which returned extra cache keys. You could use that like this: class MyView: def _cachekey(self, *a, **kw): return (self.request.application_url, self.context.id) @some_cache_decorator(extra_keys=_cachekey) def slow_task(self): # Do things here It seems like Ben stuck with the @decorate(short-term, namespace) model we first did in Beaker, whereas with dogpile.cache though the API looks more like flask-cache (http://packages.python.org/Flask-Cache/), where you have a cache object that provides the decorator. That sounds like the dogpile approach does not supported environments where you have multiple copies of the same application in the same process space but using different configurations? That's a rare situation, but to some people appears to be to important. Queue/job/etc appears to be something else entirely, I'd ask how that compares to celery-redis and other redis-queue solutions. I can already answer that one :) Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- 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] late-binding association proxy fixes error with ordered many-to-many list
I have been looking at making an ordered many-to-many list. The example
from Jason Kirkland from 2008
(http://groups.google.com/group/sqlalchemy/browse_thread/thread/611c88ee27354246
) worked, but our version of it did not. I have modified his version
(see code at the end of the mail) to use declarative syntax to make it
more similar to others, which ended up revealing something interesting.
Writing the associationproxy like this:
movies = association_proxy('usermovies', 'movie')
works fine. But writing it like this:
movies = association_proxy(usermovies, 'movie')
which should be identical gives this error:
Traceback (most recent call last):
File x.py, line 43, inmodule
class User(BaseObject):
File x.py, line 49, in User
movies = association_proxy(usermovies, 'movie')
File
/home/wichert/lib/buildout/eggs/SQLAlchemy-0.7.5-py2.7-linux-x86_64.egg/sqlalchemy/ext/associationproxy.py,
line 76, in association_proxy
return AssociationProxy(target_collection, attr, **kw)
File
/home/wichert/lib/buildout/eggs/SQLAlchemy-0.7.5-py2.7-linux-x86_64.egg/sqlalchemy/ext/associationproxy.py,
line 136, in __init__
type(self).__name__, target_collection, id(self))
File
/home/wichert/lib/buildout/eggs/SQLAlchemy-0.7.5-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
line 734, in __str__
return str(self.parent.class_.__name__) + . + self.key
AttributeError: 'RelationshipProperty' object has no attribute 'parent'
I am not sure if that is a bug in SQLAlchemy or a wrong expectation on
my side.
Regards,
Wichert.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.ext.associationproxy import association_proxy
meta = MetaData('sqlite://')
users = Table('users', meta,
Column('id', Integer, primary_key=True),
Column('name', String(128)))
movies = Table('movies', meta,
Column('id', Integer, primary_key=True),
Column('name', String(128)))
user_movies = Table('user_movies', meta,
Column('user_id', Integer,
ForeignKey('users.id'),
primary_key=True),
Column('movies_id', Integer,
ForeignKey('movies.id'),
primary_key=True),
Column('position', Integer))
meta.create_all()
BaseObject = declarative_base(metadata=meta)
class Movie(BaseObject):
__table__ = movies
def __init__(self, name):
self.name = name
def __repr__(self):
return 'Movie %r' % self.name
class UserMovie(BaseObject):
__table__ = user_movies
movie = relation(Movie)
def __init__(self, movie):
self.movie = movie
class User(BaseObject):
__table__ = users
usermovies = relation(UserMovie, backref='user',
cascade='all, delete-orphan',
collection_class=ordering_list('position'))
movies = association_proxy('usermovies', 'movie')
session = create_session()
u = User()
u.movies.extend([Movie('a'), Movie('b'), Movie('c')])
session.add(u)
session.flush()
print list(user_movies.select().execute())
del u.movies[2]
session.flush()
print list(user_movies.select().execute())
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Anticipating an IntegrityError before it happens (or noticing it immediately after)
On 2012-1-23 03:23, Jackson, Cameron wrote: Anyway, it kind of looks to me like any attempt to do this in some sort of clever automatic way is going to be more trouble than its worth, so I think I'll just bite the bullet and put backrefs on all of the relationships that are going to the table in question, and then just check all of the backrefs before deletion. This was always the most obvious solution to me, but I was hoping for some perfect magical function that would do it automatically. One can dream! :) Doing this by hand is always going to be complex and error-prone I would expect. If your backend support it you might be able to get away with using savepoints (see http://www.sqlalchemy.org/docs/orm/session.html#using-savepoint ). Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] a question about engine in sqlalchemy
On 2012-1-12 06:55, 贾晓磊 wrote: hi, all: when i try to make a connection to my mysql-server with salchemy, a problem as follows appears: from sqlalchemy import * db=create_engine(mysql://db=drone-005/LN_PABB2,user=pabb,passwd=pabb) Try this URL: mysql://pabb:pabb@drone-006/LN_PABB2 See http://www.sqlalchemy.org/docs/core/engines.html#database-urls for documentation on database URLs. Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] migration to trac 0.12
On 12/30/2011 02:36 AM, Michael Bayer wrote:
Hi all -
Just in the interests of keeping fresh, I've upgraded Trac from 0.11 to 0.12,
as part of a migration of all of sqlalchemy.org to a new host. Assuming your
dns has switched, you'll be browsing around the updated interface which has
some newer bells and whistles.
Also of note is that I've installed the Trac AccountManager plugin, so that people can
now create accounts to log in. The guest login is gone, and I'd like for new
ticket posters to create accounts, add their email, that way we know who everyone is and
can do followups and all that.
I have some other tentative plans regarding auth but for now I think this is an
improvement over just using guest.
It's typical that there are glitches when a hosting move/trac upgrade occurs so
if anyone has problems let me know !
I don't know if it is related or not, but searching the documentation no
longer works. This appears to be due to javascript errors. For example
searchtools.js has this in it:
var stopwords = {{ search_language_stop_words }};
which suggest that some templating step is not being run.
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] add results in a python object
On 11/17/2011 01:42 PM, raulna wrote:
Hi,
i need save results in a python object from multiples querys, like:
for user in users:
phones = DBSession.query(Phone).filter('...
python_object = python_object + phones ?
How can i append this results in unique object?
The simplest is to fetch all results from a query and concatenate the lists:
results = query_1.all() + query_2.all() + query_3.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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Properly handling sessions in rollback
On 11/13/2011 06:51 PM, Vlad K. wrote: Yes, again thanks for joining the topic there. Savepoints are doable by zope.transaction I am just not sure to what extent. I can't seem to find any docs and I have to walk through the code and not all features are commented or docstring'd. They should be fully supported. I'm assuming you're mixing the terms ZODB transaction and zope.transaction here, as its the latter which integrates with SQLAlchemy, from my understanding. The ZODB is just one of many kinds of data sources that can participate in a zope.transaction. Actually the Transaction package is part of ZODB, at least it is listed as such both in the PyPi and any available docs I managed to find. There is no zope.transaction package. transaction was split out from ZODB a while ago. You can use transaction with many different backends: ZODB, SQLAlchemy (via zope.sqlalchemy), the filesystem (via repoze.filesafe), mongo (via mongopersist) and others. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] default NULL
On 11/11/2011 11:20 AM, Alex K wrote:
Thanks, but if I need allow nullable primary_keys it not works.
I tried:
user_id = db.Column(db.Integer, db.ForeignKey('user.id
http://user.id', ondelete='SET NULL'), primary_key=True,
nullable=True, server_default=text('NULL'))
A primary key can never be null. The PostgreSQL documentation describes
this as follows: Technically, a primary key constraint is simply a
combination of a unique constraint and a not-null constraint..
I suggest that you replace primary_key=True with unique=True.
Wichert.
--
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] association proxy not persisting?
I am fighting a bit of an odd issue (using SQLAlchemy 0.7.3). I have an
account class, where each account has a list of strings managed via an
association proxy. For a reason I can't seem to find the values are not
persisted. The test case below demonstrates this: SQLAlchemy prints a
Object of type Specialism not in session, delete operation along
'Account._specialism' will not proceed error and the first assert
fails. Strangely enough if I remove the cascade on the _specialism
relationship adding items does work correctly, but trying to remove them
fails with a Dependency rule tried to blank-out primary key column
'specialism.account_id' error (which is expected).
from sqlalchemy import create_engine
from sqlalchemy import orm
from sqlalchemy import schema
from sqlalchemy import types
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///')
metadata = schema.MetaData()
BaseObject = declarative_base(metadata=metadata)
Session = orm.sessionmaker(bind=engine)
class Specialism(BaseObject):
__tablename__ = 'specialism'
account_id = schema.Column(types.Integer(),
schema.ForeignKey('account.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
type = schema.Column(types.String(16), primary_key=True)
def __init__(self, type):
self.type = type
class Account(BaseObject):
__tablename__ = 'account'
id = schema.Column(types.Integer(),
schema.Sequence('account_id_seq', optional=True),
primary_key=True, autoincrement=True)
_specialism = orm.relationship(Specialism,
cascade='delete, delete-orphan')
#: List of :term:`specialismsarticle specialism` this account is
#: authorized for.
specialisms = association_proxy('_specialism', 'type')
metadata.create_all(engine)
session = Session()
account = Account()
session.add(account)
account.specialisms = ['foo', 'bar']
assert session.query(Specialism).count() == 2
session.flush()
account.specialisms = ['buz']
session.flush()
assert session.query(Specialism).count() == 1
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] deferrable constraints
I hate to do this kind of thing, but I haven't gotten any feedback on
this and I would love to hear some feedback/opinions. How do other
people handle deferrable constraints?
On 2011-9-14 13:59, Wichert Akkerman wrote:
On 09/14/2011 12:25 PM, Wichert Akkerman wrote:
Constraints marked as deferrable result in a syntax error when using
SQLite. Is this deliberate, or a bug in the sqlite dialect?
As a workaround I figured I could use events to only add deferrable
constraint variants on PostgreSQL and use the non-deferrable version on
other database. That resulted in this code:
_generic_constraints = [UniqueConstraint('article_id', 'colour, 'size')]
_ deferrable_variant_constraints = [
UniqueConstraint('article_id', 'uuid', deferrable=True,
initially='DEFERRED'),
]
class Article(BaseObject):
__table_args = (_generic_constraints[0], _deferrable_constraints[0], {})
def deferrable_supported(ddl, target, bind, **kw):
Check if deferrable constraints are supported.
This function can be used as a callable for
:ref:`execute_ifsqlalchemy:sqlalchemy.schema.DDLElement.execute_if` to
only run DDL statements on databases that support deferrable constraints.
return bind.dialect == 'postgresql'
def deferrable_not_supported(ddl, target, bind, **kw):
Check if deferrable constraints are not supported.
This function can be used as a callable for
:ref:`execute_ifsqlalchemy:sqlalchemy.schema.DDLElement.execute_if` to
only run DDL statements on databases that do not support deferrable
constraints.
return not deferrable_supported(ddl, target, bind, **kw)
for constraint in _generic_variant_constraints:
listen(Article.__table__, 'after_create',
AddConstraint(constraint)
.execute_if(callable_=deferrable_not_supported))
for constraint in _deferrable_variant_constraints:
listen(Article.__table__, 'after_create',
AddConstraint(constraint)
.execute_if(callable_=deferrable_supported))
But this fails as well since SQLite does not support ALTER TABLE .. ADD
CONSTRAINT. Is there another way to create deferrable constraints only
on databases that support it?
Wichert.
--
Wichert Akkerman wich...@wiggy.net It is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] obtaining * field when more than 1 table
On 2011-9-15 18:58, RVince wrote: Suppose I wish to do something like: Session.query(Files.original_name, MSPResponse.* because MSPResponse table has so many fields, and I want to get them all. How do I do this given that I am also picking field(s) from other tables ? Thanks RVince You can use the power of python: Session.query(Files.original_name, *[c.name for c in MSPResponse.__table__.c]) Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- 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] update for polymorphic types
I am correct in assuming that update() does not work on polymorphic classes? I'm getting a ArgumentError: Only update via a single table query is currently supported error which seems to suggest that is the case. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] update for polymorphic types
On 09/15/2011 04:13 PM, Michael Bayer wrote:
that is the case you'd need to emit UPDATE against each table
individually
I tried that quickly but still got the same error. My model looks like
this (the full thing is at
https://github.com/euphorie/Euphorie/blob/master/buildout/src/Euphorie/euphorie/client/model.py
):
class SurveyTreeItem(BaseObject):
__tablename__ = tree
__table_args__ = (schema.UniqueConstraint(session_id, path), {})
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
type = schema.Column(Enum([risk, module ]),
nullable=False, index=True)
__mapper_args__ = dict(polymorphic_on=type)
class Risk(SurveyTreeItem):
Answer to risk.
__tablename__ = risk
__mapper_args__ = dict(polymorphic_identity=risk)
identification = schema.Column(Enum([None, uyes, uno, n/a]))
frequency = schema.Column(types.Integer())
effect = schema.Column(types.Integer())
probability = schema.Column(types.Integer())
priority = schema.Column(Enum([None, ulow, umedium, uhigh]))
comment = schema.Column(types.UnicodeText())
and I'm trying to update it with this:
old_tree = orm.aliased(SurveyTreeItem, name='old_tree')
in_old_tree = sql.and_(
old_tree.session_id == other.id,
SurveyTreeItem.zodb_path == old_tree.zodb_path,
SurveyTreeItem.profile_index == old_tree.profile_index)
old_risk = orm.aliased(Risk, name='old_risk')
is_old_risk = sql.and_(in_old_tree, old_tree.id == old_risk.id)
identification = sql.select([old_risk.identification], is_old_risk)
new_risks = session.query(Risk.__table__)\
.filter(Risk.session == self)\
.filter(sql.exists(
sql.select([SurveyTreeItem.id]).where(sql.and_(
SurveyTreeItem.id == Risk.id,
sql.exists([old_tree.id]).where(sql.and_(
in_old_tree, old_tree.type == 'risk'))
new_risks.update({'identification': identification},
synchronize_session=False)
I have tried to replace the JOINs with EXISTS tests so I can do the
update against just Risk.__table__, but that still results in the same
error.
Wichert.
--
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] deferrable constraints
Constraints marked as deferrable result in a syntax error when using SQLite. Is this deliberate, or a bug in the sqlite dialect? Regards, Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] deferrable constraints
On 09/14/2011 12:25 PM, Wichert Akkerman wrote:
Constraints marked as deferrable result in a syntax error when using
SQLite. Is this deliberate, or a bug in the sqlite dialect?
As a workaround I figured I could use events to only add deferrable
constraint variants on PostgreSQL and use the non-deferrable version on
other database. That resulted in this code:
_generic_constraints = [UniqueConstraint('article_id', 'colour, 'size')]
_ deferrable_variant_constraints = [
UniqueConstraint('article_id', 'uuid', deferrable=True,
initially='DEFERRED'),
]
class Article(BaseObject):
__table_args = (_generic_constraints[0], _deferrable_constraints[0], {})
def deferrable_supported(ddl, target, bind, **kw):
Check if deferrable constraints are supported.
This function can be used as a callable for
:ref:`execute_ifsqlalchemy:sqlalchemy.schema.DDLElement.execute_if` to
only run DDL statements on databases that support deferrable constraints.
return bind.dialect == 'postgresql'
def deferrable_not_supported(ddl, target, bind, **kw):
Check if deferrable constraints are not supported.
This function can be used as a callable for
:ref:`execute_ifsqlalchemy:sqlalchemy.schema.DDLElement.execute_if` to
only run DDL statements on databases that do not support deferrable
constraints.
return not deferrable_supported(ddl, target, bind, **kw)
for constraint in _generic_variant_constraints:
listen(Article.__table__, 'after_create',
AddConstraint(constraint)
.execute_if(callable_=deferrable_not_supported))
for constraint in _deferrable_variant_constraints:
listen(Article.__table__, 'after_create',
AddConstraint(constraint)
.execute_if(callable_=deferrable_supported))
But this fails as well since SQLite does not support ALTER TABLE .. ADD
CONSTRAINT. Is there another way to create deferrable constraints only
on databases that support it?
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] use session inside the class
On 09/02/2011 03:31 AM, Michael Bayer wrote: With SQLAlchemy you should have an ongoing transaction/session defined externally to individual operations on your mapped objects - SQLA uses the unit of work pattern which specifically is about grouping related persistence/query operations together. The usage you have above is still thinking in the active record style of things - session per individual persistence operation - and won't take full advantage of SQLA's way of doing things. I can see this being useful if you need a id generated by a serial though. In places where I need something like that I use object_session(self), which seems to work well. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: group_by argument as a result of a query
On 08/15/2011 10:20 AM, Eduardo wrote: Here is the example: column_names = session.query(tab.c.name).filter(tab.c.value==354) column_names = [column_name for (column_name,) in column_names] query=sess.query(func.max(tab.columns['name']),datab.columns['article_id']).group_by(*column_names).all() I'm not sure what you are trying to do here. I can see two problems: * You are asking here to group the results by the value of the 'name' column, where I suspect you want to group based on the name column itself. * You are grouping based on something you are not selecting, which is not allowed Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Softcoding .filter(...)
On 08/12/2011 05:52 PM, NiL wrote:
say you want to filter on the 'field' (field would be a string
representing the name of the field) on objects of class == Klass
field_attr = getattr(Klass, field)
would give you the instrumented attribute
then
Session.query(Klass).filter(field_attr == searchString)
or
Session.query(Klass).filter(field_attr.endswith(searchString))
would run
Alternatively if you are only interested in equality you can skip the
getattr and use filter_by in combination with python's keyword argument
handling:
Session.query(klass).filter_by(**{field: value})
Wichert.
--
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] ordered many-to-many relation
I have a model where I have articles and images, with a many-to-many
relation between them. Since the order of images for an article is
important this relation should be ordered from the article site. My
naieve implementation looks like this:
article_images = Table('article_image', BaseObject.metadata,
Column('article_id', Integer(),
ForeignKey('article.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True),
Column('image_id', Integer(),
ForeignKey('image.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True),
Column('position', Integer()))
class Image(BaseObject):
__tablename__ = 'image'
id = Column(Integer(),
Sequence('image_id_seq', optional=True),
primary_key=True, autoincrement=True)
path = Column(String(128), nullable=False, unique=True)
class Article(BaseObject):
__tablename__ = 'article'
id = Column(Integer(),
Sequence('article_id_seq', optional=True),
primary_key=True, autoincrement=True)
#: An ordered list of images for this article. The first image
#: is considered to be the *key* image.
images = relationship(Image,
order_by=[article_images.c.position],
secondary=article_images,
collection_class=ordering_list('position'))
Unfortunately this breaks since OrderingList assumes that the position
attribute is set on Image instead of the article_images table. From what
I can see this is not easily fixed since OrderingList only gets the list
of Image instances and doesn't have access to the related article_images
row. Is there another way to accomplish this?
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] ordered many-to-many relation
On 08/09/2011 02:38 PM, Wichert Akkerman wrote:
I have a model where I have articles and images, with a many-to-many
relation between them. Since the order of images for an article is
important this relation should be ordered from the article site. My
naieve implementation looks like this:
article_images = Table('article_image', BaseObject.metadata,
Column('article_id', Integer(),
ForeignKey('article.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True),
Column('image_id', Integer(),
ForeignKey('image.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True),
Column('position', Integer()))
class Image(BaseObject):
__tablename__ = 'image'
id = Column(Integer(),
Sequence('image_id_seq', optional=True),
primary_key=True, autoincrement=True)
path = Column(String(128), nullable=False, unique=True)
class Article(BaseObject):
__tablename__ = 'article'
id = Column(Integer(),
Sequence('article_id_seq', optional=True),
primary_key=True, autoincrement=True)
#: An ordered list of images for this article. The first image
#: is considered to be the *key* image.
images = relationship(Image,
order_by=[article_images.c.position],
secondary=article_images,
collection_class=ordering_list('position'))
Unfortunately this breaks since OrderingList assumes that the position
attribute is set on Image instead of the article_images table. From
what I can see this is not easily fixed since OrderingList only gets
the list of Image instances and doesn't have access to the related
article_images row. Is there another way to accomplish this?
I found a possible workaround in an earlier post to this list:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/611c88ee27354246
. The approach is interesting: it hides the many-to-many relationship
behind an extra association proxy, giving OrderingList something to work
with. I've copied a modified version of my test below to demonstrate how
this works with declarative syntax. Is this still the recommended
approach to do this?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.ext.associationproxy import association_proxy
engine = create_engine('sqlite:///')
metadata = MetaData(engine)
BaseObject = declarative_base(metadata=metadata)
class Image(BaseObject):
__tablename__ = 'image'
id = Column(Integer(),
Sequence('image_id_seq', optional=True),
primary_key=True, autoincrement=True)
path = Column(String(128), nullable=False, unique=True)
class ArticleImage(BaseObject):
__tablename__ = 'article_image'
article_id = Column(Integer(),
ForeignKey('article.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
image_id = Column(Integer(),
ForeignKey('image.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
image = relationship(Image)
position = Column(Integer())
def __init__(self, image=None, **kw):
if image is not None:
kw['image'] = image
BaseObject.__init__(self, **kw)
class Article(BaseObject):
__tablename__ = 'article'
id = Column(Integer(),
Sequence('article_id_seq', optional=True),
primary_key=True, autoincrement=True)
_images = relationship(ArticleImage,
order_by=[ArticleImage.position],
collection_class=ordering_list('position'))
images = association_proxy('_images', 'image')
metadata.create_all()
session = create_session()
article = Article()
session.add(article)
article.images.append(Image(path='one'))
article.images.append(Image(path='two'))
article.images.append(Image(path='three'))
print session.query(ArticleImage).count()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Declarative Field Type 'Alias'
On 08/05/2011 10:46 PM, Mark Erbaugh wrote: This is more of a Python issue than a SA issue, but I had trouble getting this to work. I did, but the code seems a little awkard to mesigh. In addition to the requirements already, I also wanted toe default value to be a class level 'constant'. The problem, as I see it, is that since the class definition isn't complete, it's namespace isn't avaialble. Since the default value 'constant' is a class data member, it would make sense if the function were a @classmethod, but I couldn't get python to accept: class Table(Base): ... DEFAULT = 2 @classmethod def CustomColumn(cls): return Column(Integer, default=DEFAULT) that should be cls.DEFAULT ... field1 = CustomColumn() Python complained 'classmethod object is not callable' on the last line above. You can only call a class method on a class. In this case that would be Table.CustomColumn(). However since the Table class is not available at this point you can't do that. You can do this sort of thing with metaclasses, but I would not recommend going down that paht. What I finally ended up with that works is: class Table(Base): ... DEFAULT = 2 def CustomColumn(default=DEFAULT): return Column(Integer, default=default) ... field1 = CustomColumn() That looks like a pretty good solution. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mortar_rdb 1.2.0 released!
On 2011-7-17 11:07, Chris Withers wrote: On 30/06/2011 09:30, Wichert Akkerman wrote: On 06/30/2011 10:14 AM, Chris Withers wrote: - Specify sqlalchemy 0.6 as a requirement, until zope.sqlalchemy is ported, mortar_rdb shouldn't be used with :mod:`sqlalchemy` 0.7. What is missing? zope.sqlalchemy seems to work fine with sqlalchemy 0.7 as far as I've seen. It uses an old-fashioned SessionExtension, it needs to be ported over to the new events stuff that arrived in 0.7. That might be a nice change, but the 'old-fashioned SessionExtension' still works, so I see no reason not to use it. Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mortar_rdb 1.2.0 released!
On 06/30/2011 10:14 AM, Chris Withers wrote: - Specify sqlalchemy 0.6 as a requirement, until zope.sqlalchemy is ported, mortar_rdb shouldn't be used with :mod:`sqlalchemy` 0.7. What is missing? zope.sqlalchemy seems to work fine with sqlalchemy 0.7 as far as I've seen. WIchert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] I'm missing something with the session...
On 3/23/11 23:11 , Hector Blanco wrote: Yeah... the closing thing is because this is going to be in a webserver, and the framework that controls the requests really, really messed up everything (mysql daemon, sqlalchemy...) if the http request was canceled (If I had a user pressing F5 in Firefox all the time, I got coredumps that stopped the server) That was me: http://groups.google.com/group/sqlalchemy/browse_thread/thread/5a3c7c8056cf6a60/6805bbe38667b9be?lnk=gstq=Hector+Blanco#6805bbe38667b9be That's why I decided to commit, close, and such as soon as possible (leave the sessions opened as little as possible) The problem seems to have improved lately (there was an update of the Zope framework recently, and the problem seems to have relaxed a bit)... but I'm still scared!! According to some other documents/posts I've read, maybe a commit (without the closing) would still work, though. FWIW I do a fair bit of SQLAlchemy things in Zope and have never seen such problems, nor have I ever heard of anyone seeing problems like that using Zope and SQL, which is a fairly common setup. Perhaps the missing trick here is to use zope.sqlalchemy and/or z3c.saconfig to handle the SQLAlchemy/Zope integration. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Pypi release policy
On 2/16/11 09:20 , Chris Withers wrote: On 15/02/2011 14:27, Eric Lemoine wrote: But aren't apps supposed to use=0.6.99 to avoid backward compats issues? Well, I wish I could just say 0.7 but I guess that would suck in 0.7 betas? Tarek? 0.7dev is the right restriction to use. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.7 beta 1 released
Hi Mike, I grabbed SQLAlchemy from pypi when it was still there so I could do a quick test with my current projects (yay for buildout with version pinning). I'm happy to be able to report that the upgrade was seemless, and all my tests were passing with 0.7b1. Wichert. On 2/13/11 01:51 , Michael Bayer wrote: Hey list - The first beta release of SQLAlchemy 0.7 is available for download. 0.7 is the latest iteration of our yearly cycle where each new version brings lots of new features and enhancements, refines APIs and patterns while deprecating others, and removes old APIs that have been deprecated for at least one major revision series (i.e. throughout 0.6). The initial push in 0.7 focuses on a reorganization of the event system. Over the years we've accumulated various Extension classes like MapperExtension, SessionExtension, as well as core constructs like ConnectionProxy, PoolListener, and then a bunch of other more surreptitious systems like class instrumentation events, schema association events that were not fully public. In 0.7, everything resembling an event is now available through a single unified API called sqlalchemy.event. It's a single import of a single function listen() that serves as the gateway to associating user-defined callables with all events throughout the core and ORM. All the Extension classes and similar still remain for the time being, their underlying implementations now routing through event. At the same time as event was going on, I found myself using Ants Aasma's derived attributes example like crazy in my day job. This system allows the construction of Python class attributes that produce a Python result at the object level and a SQL expression at the class level, building upon the paradigm already present in a SQLAlchemy mapped class.I enhanced the example in 0.6 such that special cases where the two situations weren't completely symmetrical could be implemented using some additional directives.It became apparent that the extremely simple idea in derived attributes was really a superset of synonym, comparable_property, and to some degree composite().So in 0.7 we've mainstreamed derived attributes as sqlalchemy.ext.hybrid. The synonym, comparable_property, and composite APIs have been refactored such that their usage is mostly the same as it was previously, but their underlying implementation uses approximately the sam e concept as ext.hybrid, and their implementations have been moved out of the core ORM modules, allowing us to simplify some things internally. The documentation now encourages the usage of sqlalchemy.ext.hybrid for customized attributes that produce new expressions derived from the class and also function at the instance level, in lieu of synonym and comparable_property. The third story of 0.7 is that we did a lot more work on speed, which includes that we've come up with a viable replacement for the mutable behavior of certain types, mainly PickleType, composite(), and postgresql.ARRAY. The mutable flag on these types is now turned off by default - while the old system of detecting in place mutation was fine for small jobs, it completely cripples the application as it begins to handle higher volumes of data, as it relied upon scanning all mutable objects in the Session in order to detect changes in these attributes. The autoflush operation basically becomes O(N) instead of O(1) the minute an object with a mutable attribute is placed in the session. A new system which allows one to construct on change events specific to the kind of data structure being stored in a scalar value is added in sqlalchemy.ext.mutable. This is a brand new system that hopefully should grow as we move through 0.7 to support various Python structures out of the box, like dicts, lists and composites of those. The turning off of mutable is likely the one most major hard change in the system - if your application relies upon in-place mutation of PickleType or postgresql.ARRAY, you'd need to turn that flag on until your app can be altered to use the new system. If your application relies upon in-place mutability of composite() (I'm assuming this is a very rare scenario, composite() isn't that common in the first place), the new system has to be used for that, which requires a little bit of modification to the user-defined composite class. Heavy users of mutable should notice an immediate effect by switching off of the old system. A long list of everything to be aware of is ready for viewing at http://www.sqlalchemy.org/trac/wiki/07Migration .While there are many changes, it is my impression that the vast majority of applications coded against 0.6 will run on 0.7 without modification, Very few changes are backwards incompatible, and of those fifteen changes which are, most are things that were never documented or were essentially silent failures. The purpose of the beta is to get community
Re: [sqlalchemy] Pypi release policy
On 2/14/11 10:57 , M3nt0r3 wrote: Yesterday my life become an hell. :) On saturday ( :O ) SA0.7.b1 is released and easy_install start to use it. The problem is that it broke some installer and doesn't work with my app. That sounds like a bug in your installer, not in SQLAlchemy. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Pypi release policy
On 2/14/11 11:25 , M3nt0r3 wrote: My installer for windows download during the process some libs. I use easy_install sqlalchemy and it works. Now too works but 0.7.b1 is not working with the app itself now so it create a lot of problem. I thought that if 0.7.b1 is a beta and brokes API maybe it should be better to use sqlalchemy==0.7 or sqlalchemy == dev. It sounds like the dependencies in your package are too liberal. I would change your dependency to SQLAlchemy =0.6, 0.7dev. That way you can never accidentally install an incompatible SQLAlchemy version. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Pypi release policy
On 2/14/11 11:39 , Tarek Ziadé wrote: On Mon, Feb 14, 2011 at 11:31 AM, Wichert Akkermanwich...@wiggy.net wrote: It sounds like the dependencies in your package are too liberal. I would change your dependency to SQLAlchemy=0.6,0.7dev. That way you can never accidentally install an incompatible SQLAlchemy version. If the changes that break the code did not have a deprecation step in 0.6, that's still an issue to fix imo Sure. you don't release at pypi a version that breaks the latest stable. or if you do, you check the hidden attribute on that release, to avoid this problem with installers SQLAlchemy 0.7b1 is hidden. The hidden flag only hides it from humans though, not from setuptools. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.6.6 Released
Hi Mike, On 1/9/11 00:14 , Michael Bayer wrote: The majority of my time is now spent developing 0.7, which is nearly ready for beta releases pending a few more little features I'd like to try to get in. 0.7 is really exciting with its new event API, lots of other nice touches and of course the most radical reduction in callcounts we've had in a few years. Has been there a decision on enabling the C extensions by default in 0.7, or is that still too controversial? Regards, Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy-Future
On 12/28/10 20:54 , Michael Bayer wrote:
On Dec 28, 2010, at 2:22 AM, Wichert Akkerman wrote:
On 2010-12-28 08:18, Hong Minhee wrote:
I don’t know why setuptools provides pkg_resources, implements another
incompatible way to declare namespace packages, additionally in spite of
existence of pkgutil.extend_path, the standard way to do it. IMO
sqlalchemy.contrib namespace have to be declared by using
pkgutil.extend_path, because it is a part of Python standard library so
it don’t force users to install setuptools.
The vast majority of packages using namespaces that I have seen support both
with this snippet:
try:
__import__('pkg_resources').declare_namespace(__name__)
except ImportError:
from pkgutil import extend_path
__path__ = extend_path(__path__, __name__)
I would suggest that SQLAlchemy does the same thing.
what are some of these packages ? Can I see some examples so I can get a feel
for how this is used and what naming schemes are common ?
Every zope.* and zope.app.* package on pypi as well as most repoze.*
packages.
Wichert.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy-Future
On 12/29/10 16:45 , Michael Bayer wrote: Yeah this is what I'm seeing, that top level packagenames are used. I.e. I would just say, Use sqlalchemycontrib as your package name, that's what everyone should use. It would appear this approach means nothing needs to happen at all witihin sqlalchemy core. I suspect you mean use sqlalchemycontrib as your package namespace? Otherwise you will get 50 packages all called sqlalchemycontrib, which would be a bit confusing :) Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy-Future
On 2010-12-28 08:18, Hong Minhee wrote:
I don’t know why setuptools provides pkg_resources, implements another
incompatible way to declare namespace packages, additionally in spite of
existence of pkgutil.extend_path, the standard way to do it. IMO
sqlalchemy.contrib namespace have to be declared by using
pkgutil.extend_path, because it is a part of Python standard library so
it don’t force users to install setuptools.
The vast majority of packages using namespaces that I have seen support
both with this snippet:
try:
__import__('pkg_resources').declare_namespace(__name__)
except ImportError:
from pkgutil import extend_path
__path__ = extend_path(__path__, __name__)
I would suggest that SQLAlchemy does the same thing.
Wichert.
--
Wichert Akkerman wich...@wiggy.net It is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.
--
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] rowcount not populated?
On an environment using SQLAlchemy 0.6.5 and psycopg 2.2.2 I run the following code: session.execute(Article.__table__.update() .where(Article.retailer_id==self.retailer_id) .where(Article.publish_end=datetime.date.today()) .values(publish_end=yesterday())) this returns a ResultProxy object, which always has rowcount set to 0: (Pdb) p result.supports_sane_rowcount() True (Pdb) p result.rowcount 0 doing the same thing manually in a psql session shows that 858 rows are updated by this command. Is my expectation that result.rowcount should be set here incorrect? Wichert. -- 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] ...kill_hung_threads status...
On 9/30/10 16:26 , dobrysmak wrote: Hi guys. I keep getting this message: [paste.httpserver.ThreadPool] kill_hung_threads status: 10 threads (1 working, 9 idle, 0 starting) ave time 0.03sec, max time 0.03sec, killed 0 workers that's just the paste httpserver checking if any of your application threads are stuck. It's a purely diagnostic message that you can safely ignore. It has no relation to SQLAlchemy. Wichert. -- 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] forcing an insert
On 8/3/10 16:50 , Benjamin Peterson wrote: Is there a way to force the ORM to insert a new row instead of updating? Something like the must_insert argument to model's save method in Django's ORM. The use case is I must create a unique session key (for a cookie) and want an error when the key isn't unique, so perhaps there's a better way? Declare the key to be unique? Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: open session blocks metadata create_all method
On 7/29/10 17:18 , Faheem Mitha wrote: Hi Simon, Thanks. Do you understand why this blocking takes place? I assume by default create_all tries to make a different connection, and fails for some reason? My guess is that it does not fail, but your database is blocking the create_all statements while another transaction is active. Wichert. -- 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] Creating sequence
What is the preferred method to have metadata.create_all() create sequences? I tried to find something in the wiki but could not find anything. For indexes you can do this: schema.Index(public_event_idx, Event.workflow, Event.deleted) but a similar statement for a sequence: schema.Sequence(invoice_number, metadata=meta.metadata) does not do anything. From what I gather from the source Sequence is not derived from SchemaItem, so it is not picked up automatically. I could do a DDL construct like this (untested, but basic idea should work): from sqlalchemy.schema import DDL def sequenceSupported(event, schema_item, connection): return connection.dialect.sequence_supported: DDL(CREATE SEQUENCE invoice_number, on=sequenceSupported)\ .execute_at(after-create, metadata) but I would rather not have to hardcode the SQL statement, especially since SQLAlchemy is capable of generating the statement as well. Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: joining sessions / two phase commit
On 2/14/10 19:02 , Antoine Pitrou wrote: Le dimanche 14 février 2010 à 17:47 +, Chris Withers a écrit : Wichert Akkerman wrote: On 2010-2-9 09:48, Chris Withers wrote: I know that zope's transaction package aims to do just this, I wonder if anyone's used that, or anything else, with SA to solve this problem? You mean ZODB's transaction package? it's actually now just a standalone package ;-) http://pypi.python.org/pypi/transaction I've found it to be an active nuisance. For example it will forbid the use of commit() on the session, forcing you to use transaction.commit() instead. That is by design: zope.sqlalchemy (which is really the thing you are complaining about) forces you to commit the entire transaction. This is required to coordinate transactions between multiple participants in a transaction, prevent one of them from getting out of sync. And transaction.commit() has the annoying side effect that it will also purge the session afterwards, making all your objects unusable unless you go out of your way to fetch them again manually. Iirc it starts a new session, making sure that you do not leak things between transactions. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: joining sessions / two phase commit
On 2/14/10 19:54 , Antoine Pitrou wrote: Le dimanche 14 février 2010 à 19:45 +0100, Wichert Akkerman a écrit : That is by design: zope.sqlalchemy (which is really the thing you are complaining about) forces you to commit the entire transaction. This is required to coordinate transactions between multiple participants in a transaction, prevent one of them from getting out of sync. I understand this is by design, but better again would have been a flag to disable it. I have never seen a request for such a flag before, which makes me suspect it is not a widely requested feature. You are more than welcome to contribute such a flag to zope.sqlalchemy though. And transaction.commit() has the annoying side effect that it will also purge the session afterwards, making all your objects unusable unless you go out of your way to fetch them again manually. Iirc it starts a new session, making sure that you do not leak things between transactions. Well, there are situations where you'd want to leak things between transactions... If SQLAlchemy itself doesn't enforce it, I'm not sure why the transaction package thinks it should. If you are trying to put blame anywhere do it in the right place: the 'transaction' package does not do this. The SQLAlchemy transaction glue in the zope.sqlalchemy package does. Wichert. -- 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] getting the actual sql used for a session.execute(sql,subs)
On 2010-2-11 12:13, Chris Withers wrote: Hi All, How can I get the actual sql executed by a: session.execute(sql,subs) ? I tried turning on echo in the engine, but that just shows %s where the substitutions should happen. Is there any way to get the post-substitution sql? SQLAlchemy does not do the substitutions, the DB-API driver does that. So you'll need to look at your database driver to see if that supports logging of commands. You can make postgres log all its commands, perhaps that is an option for you. Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: joining sessions / two phase commit
On 2010-2-9 09:48, Chris Withers wrote: I know that zope's transaction package aims to do just this, I wonder if anyone's used that, or anything else, with SA to solve this problem? You mean ZODB's transaction package? :). I use that all the time to get transactions working across multiple storage systems. Most commonly using repoze.tm2 to integrate with a WSGI stack, zope.sqlalchemy to integrate SQLAlchemy with transaction and repoze.filesafe to do transaction-safe file creation. Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- 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] Timezone handling with postgres
Postgres can handle timezones fairly well. Using a direct select you can
see how it handles daylight saving correctly:
test=# select
'2010-01-15 12:30 Europe/Amsterdam'::timestamp with time zone,
'2010-01-15 12:30 Europe/Amsterdam'::timestamp with time zone +
interval '7 months',
'2010-08-15 12:30 Europe/Berlin'::timestamp with time zone;
timestamptz |?column?| timestamptz
++
2010-01-15 12:30:00+01 | 2010-08-15 12:30:00+02 | 2010-08-15 12:30:00+02
(1 row)
When using a table to store a timestamp this still works properly:
tribaspace=# create table test (moment timestamp with time zone);
CREATE TABLE
tribaspace=# insert into test values ('2010-01-15 12:30
Europe/Berlin'::timestamp with time zone);
INSERT 0 1
tribaspace=# select moment + interval '7 months' from test;
?column?
2010-08-15 12:30:00+02
(1 row)
However that extra timezone information is lost when I use SQLAlchemy.
After adding a primary key column I use this bit of python to test the
timezone handling:
import datetime
from sqlalchemy import orm
from sqlalchemy import schema
from sqlalchemy import types
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Test(Base):
__tablename__ = test
id = schema.Column(types.Integer(), primary_key=True,
autoincrement=True)
moment = schema.Column(types.Time(timezone=True))
engine = create_engine('postgres:///test')
Base.metadata.create_all(engine)
Session = orm.sessionmaker(bind=engine)
session = Session()
row = session.query(Test).first()
print row.moment
print row.moment + datetime.timedelta(days=212)
Which outputs:
2010-01-15 12:30:00+01:00
2010-08-15 12:30:00+01:00
The second timestamp should have +02:00 as timezone due do daylight
saving differences. Unfortuantely the timezone information reported on
the column has a fixed offset instead of the more informative
Europe/Amsterdam time.
I am guessing that this is mostly due to psycopg2 not handling timezones
properly. I am wondering if SQLAlchemy itself will handle this correctly
if psycopg2 would do the right thing, and if other dialects implement
this better?
Wichert.
--
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] Off Topic: Declarative-style for XML?
On 2009-12-18 20:50, AF wrote: Hello, I've become quite used to SQLAlchemy's Declarative style notation for defining data to be stored in SQL. Does anyone know of library that will do something similar for simple XML? Basically I need objects (and attributed collections of objects) that are effectively records for groups of simple data types like strings, dates, numbers, etc. lxml with its objectify mode? Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- 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] dynamic relation forgets join condition on .count() ?
I have a data model which has accounts and events, and a many-to-many relation between the two using a CalendarEvent class. It boils down to this: class Account(BaseObject): __tablename__ = account id = schema.Column(id, types.Integer(), primary_key=True) class Event(BaseObject): __tablename__ = event id = schema.Column(id, types.Integer(), primary_key=True) class CalendarEvent(mBaseObject): __tablename__ = calendar account_id = schema.Column(types.Integer(), schema.ForeignKey(Account.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) account = orm.relation(Account, backref=orm.backref(calendar, lazy=dynamic)) event_id = schema.Column(types.Integer(), schema.ForeignKey(Event.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) event = orm.relation(Event, lazy=False) the calendar backref works fine when you generate a query for it and generates SQL like this: SELECT calendar.account_id AS calendar_account_id, calendar.event_id AS calendar_event_id, event_1.id AS event_1_id FROM event, calendar LEFT OUTER JOIN event AS event_1 ON event_1.id = calendar.event_id WHERE :param_1 = calendar.account_id but if you use acount.calendar.count() the join conditions disappears and you end up with this: SELECT count(1) AS count_1 FROM calendar, event WHERE %(param_1)s = calendar.account_id which results in an incorrect result. Am I doing something wrong here, or could this be a SQLALchemy bug? If so I can try to boil this down to a failing testcase. Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- 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] dynamic relation forgets join condition on .count() ?
On 2009-11-25 16:15, Michael Bayer wrote: Wichert Akkerman wrote: I have a data model which has accounts and events, and a many-to-many relation between the two using a CalendarEvent class. It boils down to this: class Account(BaseObject): __tablename__ = account id = schema.Column(id, types.Integer(), primary_key=True) class Event(BaseObject): __tablename__ = event id = schema.Column(id, types.Integer(), primary_key=True) class CalendarEvent(mBaseObject): __tablename__ = calendar account_id = schema.Column(types.Integer(), schema.ForeignKey(Account.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) account = orm.relation(Account, backref=orm.backref(calendar, lazy=dynamic)) event_id = schema.Column(types.Integer(), schema.ForeignKey(Event.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) event = orm.relation(Event, lazy=False) the calendar backref works fine when you generate a query for it and generates SQL like this: SELECT calendar.account_id AS calendar_account_id, calendar.event_id AS calendar_event_id, event_1.id AS event_1_id FROM event, calendar LEFT OUTER JOIN event AS event_1 ON event_1.id = calendar.event_id WHERE :param_1 = calendar.account_id but if you use acount.calendar.count() the join conditions disappears and you end up with this: SELECT count(1) AS count_1 FROM calendar, event WHERE %(param_1)s = calendar.account_id why is event in either of those queries ? In the first case its valid for the LEFT OUTER JOIN since that's the eager load, but I also see it stated a second time, by itself, not associated to anything. Both queries are incorrect. The mapping you illustrate doesn't involve event at all between Account and Calendar, which wouldn't be rendered as a standalone FROM object unless you are specifically saying filter(Event.foo == 'bar') without establishing your join(). I should have been more explicity, sorry about that. This is the full query: today = datetime.date.today() query = self.context.calendar\ .filter(models.Event.start_date=today)\ .filter(models.Event.state==active)\ .order_by(models.Event.start_date, models.CalendarEvent.start_time, models.Event.start_time) I was expecting the join to automatically created since the Account.calendar relation depends on it. Adding a .join(model.Event) to the query before the filter() calls indeed fixes this. Does SQLAlchemy indeed assume that my usage of models.Event as query filters has no relation at all to the calendar relation? Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Unsupported Type
On 11/2/09 14:04 , Sir Rawlins wrote:
Hello Guys,
I'm getting an exception thrown when trying to save an entity into a
SQLite database, this is a database/app which has been ported over
from a MySQL backend.
The exception looks like this:
InterfaceError: (InterfaceError) Error binding parameter 0 - probably
unsupported type. u'INSERT INTO bluetooth_session
(bluetooth_session_id, result, address, message_id, campaign_id,
created, modified) VALUES (?, ?, ?, ?, ?, ?, ?)' [UUID
('ed6ce6a6-4918-421e-9fa5-b41972931713'), dbus.String
(u'org.openobex.Error.LinkError'), dbus.String(u'00:1F:6B:58:4A:F5'),
1686, 274, '2009-11-02 12:59:03.521275', '2009-11-02 12:59:03.521275']
)
Now, my first instincts tell me that the issue here is probably the
UUID which I'm using as the PK for the table, the column datatype is
set to varchar(50) and the definition in the mapper for the class
looks like this:
bluetooth_session_id = Column(String, primary_key=True,
default=uuid.uuid4)
Now, do I have to make a change somewhere here? do I perhaps have to
change that default statement somehow to format the UUID object as a
string?
Try this:
bluetooth_session_id = Column(String, primary_key=True,
default = lambda: str(uuid.uuid4()))
Wichert.
--~--~-~--~~~---~--~~
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: Relation w/ declarative
On 8/6/09 09:30 , werner wrote:
IIRC correctly the __init__ section is only needed if you want to do:
add = Address('an email address')
I never do this, i.e. I assign like this
add = Address()
add.email_address = 'an email address'
You can also do this with the default declarative base constructor:
add = Address(email_address='j...@example.com')
All columns can be used as named parameters.
Wichert.
--~--~-~--~~~---~--~~
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] missing parenthesis on generated query?
I have a fairly complex SQL query which looks like this: sql.or_( sql.and_(model.SurveyTreeItem.type==module, model.SurveyTreeItem.skip_children==False, sql.exists().correlate(model.Risk.__table__).where(sql.and_( model.Risk.session_id==model.SurveyTreeItem.session_id, model.Risk.type==risk, model.Risk.inventory==no, model.Risk.depthmodel.SurveyTreeItem.depth, model.Risk.path.like(model.SurveyTreeItem.path+%, sql.and_(model.Risk.id==model.SurveyTreeItem.id, model.Risk.type==risk, model.Risk.inventory==no)) When printing the resulting clause list I get this: tree.type = :type_1 AND tree.skip_children = :skip_children_1 AND (EXISTS (SELECT * FROM tree WHERE tree.session_id = tree.session_id AND tree.type = :type_2 AND risk.inventory = :inventory_1 AND tree.depth tree.depth AND tree.path LIKE tree.path || :path_1)) OR tree.id = tree.id AND tree.type = :type_3 AND risk.inventory = :inventory_2 From what I can see this is not correct: there should be parenthesis to make sure the ANDs and ORs are grouped correctly. Does this look like a bug, or am I missing something? Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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] descriptor design question
I am struggling a bit with a design question. I have a database of articles, where each article has a price in a specific currency. The currency and their conversion rates are in a separate table. In order to be able to do fast queries I also keep the price in Euros in the article table. The classes look like this: class Currency(BaseObject): A currency Currencies are identified by their ISO 4217 three letter currency code. __tablename__ = currency code = schema.Column(types.String(3), primary_key=True) rate = schema.Column(types.Numeric(precision=6, scale=2), nullable=False) def __repr__(self): return Currency %s rate=%.2f % (self.code, self.rate) class Article(BaseObject): __tablename__ = clothing_image id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) title = schema.Column(types.Unicode(80), nullable=False) currency = schema.Column(types.String(3), schema.ForeignKey(currency.code, ondelete=RESTRICT), nullable=False, default=EUR) _price = schema.Column(price, types.Numeric(precision=6, scale=2), nullable=False) price_euro = schema.Column(types.Numeric(precision=6, scale=2), nullable=False, index=True) def _get_price(self): return self._price def _set_price(self, value): currency=meta.Session.query(Currency).get(self.currency) self.price_euro=value*currency.rate self._price=value price = orm.synonym(_price, descriptor=property(_get_price, _set_price)) this breaks down when you create a new Article instance: as soon as you set the price on the newly created instance the _set_price method is called. The query to get the currency in there triggers a flush, which fails since the (non-nullable) title is not set at that point. If this happens in a unittest the result appears to be a hang or python segfault. I am wondering what the best solution to this issue is. I can think of several options: - create INSERT and UPDATE triggers and use those to update the price_euro column. Downside: code is less portable and you can no longer use SQLite for testing. - require a manual updated for price_euro. Downside: this needlessly complicates the internal API - forget about the price_euro column and always do a table join. Downside: SQL queries become more complex and expensive. I am hoping someone here has encountered this pattern before and came up with a good solution. Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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: Generating INTEGER PRIMARY KEY AUTOINCREMENT column in sqlite
Previously Werner F. Bruhin wrote:
Hi Leonard,
paniq303 wrote:
Hello,
how is the progress with this feature - how can I use it? My
application strongly depends on AUTOINCREMENT being available.
To explain:
Without AUTOINCREMENT, a deleted primary key will be reassigned.
As an example: three records are created, with ids 1, 2 and 3. Now the
record with id 3 is being deleted. We create a new record, and this
record will get id 3 again, instead of id 4. In this way, there can be
confusion between the old and the new record with id 3.
It is available in SA, i.e. I use it with Firebird SQL and in my model I
just do this:
sa.Column(u'langid', sa.Integer(),
sa.Sequence('gen_language_langid'), primary_key=True, nullable=False),
Can someone explain what the differences between using a sequence like
this or autoincrement=True on the column are?
Wichert.
--
Wichert Akkerman wich...@wiggy.netIt is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.
--~--~-~--~~~---~--~~
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: Multiple self-referential relations
Previously Michael Bayer wrote: On May 18, 2009, at 7:01 AM, Wichert Akkerman wrote: session = orm.relation(SurveySession, cascade=all, remote_side=[SurveySession.id], primaryjoin=SurveySession.id==TreeNode.session_id) parent = orm.relation(TreeNode, cascade=all, remote_side=[TreeNode.id], primaryjoin=TreeNode.id==TreeNode.parent_id) you want to say 'remote_side=TreeNode.id' here, or 'remote_side=[TreeNode.id]'. Or just 'remote_side=id'. the argument to remote_side is one of: 1. a string that is evaluated entirely 2. a column 3. a list of columns Doing that for the remote_side for the parent relation results in the exact same error unfortunately. Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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] Multiple self-referential relations
I am building a tree datastructure using parent pointers. The only differece from a standard adjancancy list is that I can have multiple trees, each identified by a root session node. In order to be allow fast queries each tree node has a pointer to the session id (ie the root node). Unfortunately I can't seem to get the relation declared properly. My latest attempt looks like this: import sqlalchemy from sqlalchemy import orm from sqlalchemy import schema from sqlalchemy import types from sqlalchemy.ext.declarative import declarative_base session = orm.create_session() db_engine = sqlalchemy.create_engine(sqlite:///) BaseObject = declarative_base() class TreeNode(BaseObject): __tablename__ = tree id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) type = schema.Column(types.String(10), nullable=False, index=True) session_id = schema.Column(types.Integer(), schema.ForeignKey(session.id, onupdate=CASCADE, ondelete=CASCADE), nullable=False, index=True) parent_id = schema.Column(types.Integer(), schema.ForeignKey(tree.id, onupdate=CASCADE, ondelete=CASCADE), nullable=True, index=True) __mapper_args__ = dict(polymorphic_on=type) session = orm.relation(SurveySession, cascade=all, remote_side=[SurveySession.id], primaryjoin=SurveySession.id==TreeNode.session_id) parent = orm.relation(TreeNode, cascade=all, remote_side=[TreeNode.id], primaryjoin=TreeNode.id==TreeNode.parent_id) class SurveySession(TreeNode): __tablename__ = session __mapper_args__ = dict(polymorphic_identity=session) id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) query=session.query(TreeNode) print query.all() but running that results in this error message: sqlalchemy.exc.ArgumentError: Relation TreeNode.parent could not determine any local/remote column pairs from remote side argument set([sqlalchemy.sql.expression.ColumnClause at 0x1120bd0; TreeNode.id]) I can't seem to figure out what the correct spelling is. Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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] bindparams not resolved?
I am wondering if this is a bug in my reasoning, or in SQLAlchemy (0.5.3). I have a model which is pretty simple: class ClothingArticle(BaseObject): __tablename__ = clothing_article id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) currency = schema.Column(types.String(3), nullable=False, default=EUR, index=True) price = schema.Column(types.Numeric(precision=6, scale=2), nullable=False) price_euro = schema.Column(types.Numeric(precision=6, scale=2), nullable=False, index=True) When a currerency rate changes I am trying to update it, using a simple prepared statement: update=ClothingArticle.__table__.update().\ where(ClothingArticle.currency==sql.bindparam(currency)).\ values(dict(price_euro=ClothingArticle.price*sql.bindparam(newrate))) The statement is processed correctly: (Pdb) print update UPDATE clothing_article SET price_euro=(clothing_article.price * :newrate) WHERE clothing_article.currency = :currency But when I try to use it: session.execute(update, currency=currency[code], newrate=newrate) I get an error: TypeError: get_bind() got an unexpected keyword argument 'currency' I find it hard to believe parameter binding does not work, since SA uses that internally, but I also can't spot my mistake unfortunately. Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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: Seeing Queries in Postgres
Previously Alex Ezell wrote: Sorry the double and top post, but please disregard. I've been informed by a coworker that this is a PostgreSQL limitation on the length of the current_query column and that thus far, the PostgreSQL devs will not change it. Postgres has a log_statement configuration settings which you can use to make it log all queries to its logfile. Perhaps that would be useful for you? Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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: What's the use of expunge?
Previously Michael Bayer wrote: On Feb 26, 2009, at 8:21 AM, Wichert Akkerman wrote: What happens if you do not call expunge on it, but pickle the object in a cache, load it later and then merge it? the state of the newly unpickled object, that is the current value of its mapped attributes, would be merged with the persistent version in the session. merge() will load the object from the database into an in-session, persistent instance before merging the external state. because your unpickled instance never actually enters the session, conflicts with its previous session or an already present in-session object are nicely avoided. I actually skip that and invalidate the cache entry on changes to prevent that SQL hit. What I meant was: does it matter if you never explicitly call expunge? Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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: join_depth in two directions with adjancency lists
On 12/25/08 4:37 PM, Michael Bayer wrote: On Dec 25, 2008, at 6:45 AM, Wichert Akkerman wrote: I have a reasonably standard hierarchical datastructure which I'm trying to store in SQL. The basic model looks like this: class Page(BaseObject): __tablename__ = page id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) path = schema.Column(types.Unicode(128), nullable=False, index=True) children = orm.relation(Page, cascade=all, collection_class=attribute_mapped_collection(path)) This works fine. As shown in the basic_tree example you can configure the children relation with eager loading and a join_depth to load entire tree structure efficiently. this can't work as stated above. You have a relation() from Page to itself but no notion of foreign key between the page table and itself is specified. You'll get an error. That is mostly due to a copypaste error. I forgot this line: parent_id = schema.Column(types.Integer(), schema.ForeignKey(page.id, onupdate=CASCADE, ondelete=CASCADE), index=True) I want to do the reverse: build a relation which returns a list of all parents of an object. I figured this would work: parents = orm.relation(Page, remote_side=[id], lazy=False, join_depth=5) That only returns the first parent, not a list of successive parents. Is it possible to build that parent list like that? if this is a standard adjacency list relation, Page.parent is a many to one.Joining up five levels will give you a Parent referencing its Parent referencing its Parent, etc. It did not though: I only got the direct parent. To roll those up into a single set of result columns in a single SQL statement would be very involved - a recursive iterator would be way easier, i.e. @property def parents_iterator(self): s = self.parent while s: yield s s = s.parent that suggests that join_depth the other way (recursive children) is also expensive, which the documentation does not mention. Is that correct? Wichert. --~--~-~--~~~---~--~~ 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] join_depth in two directions with adjancency lists
I have a reasonably standard hierarchical datastructure which I'm trying to store in SQL. The basic model looks like this: class Page(BaseObject): __tablename__ = page id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) path = schema.Column(types.Unicode(128), nullable=False, index=True) children = orm.relation(Page, cascade=all, collection_class=attribute_mapped_collection(path)) This works fine. As shown in the basic_tree example you can configure the children relation with eager loading and a join_depth to load entire tree structure efficiently. I want to do the reverse: build a relation which returns a list of all parents of an object. I figured this would work: parents = orm.relation(Page, remote_side=[id], lazy=False, join_depth=5) That only returns the first parent, not a list of successive parents. Is it possible to build that parent list like that? Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
