[sqlalchemy] sqlalchemy.exc.InvalidRequestError: Mapper properties (i.e. deferred,column_property(), relationship(), etc.) must be declared as @declared_attr callables on declarative mixin classes.

[Andreas Jung]

Hi,

I currently have the following mix-in class construction (based on the 
documentation 
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html
)

Base = declarative_base() 

class BaseCountry(object): 
   """ Managed through Kotti """ 

   @declared_attr 
   def __tablename__(cls): 
   return cls.__name__.lower() 

   dpis_id = Column(Integer, primary_key=True) 
   title =  Column(Unicode(80)) 
   regions = relationship("Region", 
primaryjoin="Country.dpis_id==Region.country_id") 



class Country(BaseCountry, Base): 
   """ Managed through Kotti """ 

   __tablename__ = 'countries'

This gives me 

  File 
"/data/home/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py",
 
line 55, in __init__ 
   _as_declarative(cls, classname, cls.__dict__) 
 File 
"/data/home/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
 
line 88, in _as_declarative 
   _MapperConfig.setup_mapping(cls, classname, dict_) 
 File 
"/data/home/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
 
line 103, in setup_mapping 
   cfg_cls(cls_, classname, dict_) 
 File 
"/data/home/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
 
line 123, in __init__ 
   self._scan_attributes() 
 File 
"/data/home/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
 
line 216, in _scan_attributes 
   "Mapper properties (i.e. deferred," 
sqlalchemy.exc.InvalidRequestError: Mapper properties (i.e. 
deferred,column_property(), relationship(), etc.) must be declared as 
@declared_attr callables on declarative mixin
classes.

Where is the difference to the documentation?

Python 2.7, Sqlalchemy 1.0.11

Andreas

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: SA 0.8.0 UnicodeDecodeError with Postgres 9.1.9

[Andreas Jung]

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.

-aj

On Friday, April 19, 2013 8:36:58 AM UTC+2, Andreas Jung wrote:
>
> Hi there,
>
> I receive the following weird UnicodeDecodeError after copying our 
> production database into a
> testing environment. On the production server everything is working fine, 
> on the testing server
> I get this error. Both installation including OS are basically identically 
> (except SA 0.8.0b2 vs 0.8.0 -
> but this does not make a different).
>
> Any clue?
>
> Andreas
>
>   File 
> "/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/tweens.py", line 
> 21, in excview_tween
> response = handler(request)
>   File 
> "/home/ajung/.buildout/eggs/pyramid_tm-0.7-py2.7.egg/pyramid_tm/__init__.py", 
> line 82, in tm_tween
> reraise(*exc_info)
>   File 
> "/home/ajung/.buildout/eggs/pyramid_tm-0.7-py2.7.egg/pyramid_tm/__init__.py", 
> line 63, in tm_tween
> response = handler(request)
>   File 
> "/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/router.py", line 
> 161, in handle_request
> response = view_callable(context, request)
>   File 
> "/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/config/views.py", 
> line 375, in viewresult_to_response
> result = view(context, request)
>   File 
> "/home/ajung/.buildout/eggs/Kotti-0.8b1-py2.7.egg/kotti/views/view.py", 
> line 24, in view_content_default
> response = render_view_to_response(context, request, name=view_name)
>   File "/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/view.py", 
> line 83, in render_view_to_response
> return view(context, request)
>   File 
> "/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/config/views.py", 
> line 237, in _secured_view
> return view(context, request)
>   File 
> "/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/config/views.py", 
> line 345, in rendered_view
> result = view(context, request)
>   File 
> "/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/config/views.py", 
> line 491, in _requestonly_view
> response = view(request)
>   File 
> "/home/ajung/sandboxes/mib.pyramid_zopyx/src/mib/pyramid/food_folder_views.py",
>  
> line 47, in recipes_view
> if query:
>   File 
> "/home/ajung/.buildout/eggs/SQLAlchemy-0.8.0-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
>  
> line 2140, in all
> return list(self)
>   File 
> "/home/ajung/.buildout/eggs/SQLAlchemy-0.8.0-py2.7-linux-x86_64.egg/sqlalchemy/orm/loading.py",
>  
> line 65, in instances
> fetch = cursor.fetchall()
>   File 
> "/home/ajung/.buildout/eggs/SQLAlchemy-0.8.0-py2.7-linux-x86_64.egg/sqlalchemy/engine/result.py",
>  
> line 748, in fetchall
> l = self.process_rows(self._fetchall_impl())
>   File 
> "/home/ajung/.buildout/eggs/SQLAlchemy-0.8.0-py2.7-linux-x86_64.egg/sqlalchemy/engine/result.py",
>  
> line 715, in _fetchall_impl
> return self.cursor.fetchall()
> UnicodeDecodeError: 'ascii' codec can't decode byte 0xc2 in position 18: 
> ordinal not in range(128)
>
>

-- 
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] SA 0.8.0 UnicodeDecodeError with Postgres 9.1.9

[Andreas Jung]

Hi there,

I receive the following weird UnicodeDecodeError after copying our 
production database into a
testing environment. On the production server everything is working fine, 
on the testing server
I get this error. Both installation including OS are basically identically 
(except SA 0.8.0b2 vs 0.8.0 -
but this does not make a different).

Any clue?

Andreas

  File 
"/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/tweens.py", line 
21, in excview_tween
response = handler(request)
  File 
"/home/ajung/.buildout/eggs/pyramid_tm-0.7-py2.7.egg/pyramid_tm/__init__.py", 
line 82, in tm_tween
reraise(*exc_info)
  File 
"/home/ajung/.buildout/eggs/pyramid_tm-0.7-py2.7.egg/pyramid_tm/__init__.py", 
line 63, in tm_tween
response = handler(request)
  File 
"/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/router.py", line 
161, in handle_request
response = view_callable(context, request)
  File 
"/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/config/views.py", 
line 375, in viewresult_to_response
result = view(context, request)
  File 
"/home/ajung/.buildout/eggs/Kotti-0.8b1-py2.7.egg/kotti/views/view.py", 
line 24, in view_content_default
response = render_view_to_response(context, request, name=view_name)
  File "/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/view.py", 
line 83, in render_view_to_response
return view(context, request)
  File 
"/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/config/views.py", 
line 237, in _secured_view
return view(context, request)
  File 
"/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/config/views.py", 
line 345, in rendered_view
result = view(context, request)
  File 
"/home/ajung/.buildout/eggs/pyramid-1.4-py2.7.egg/pyramid/config/views.py", 
line 491, in _requestonly_view
response = view(request)
  File 
"/home/ajung/sandboxes/mib.pyramid_zopyx/src/mib/pyramid/food_folder_views.py", 
line 47, in recipes_view
if query:
  File 
"/home/ajung/.buildout/eggs/SQLAlchemy-0.8.0-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
 
line 2140, in all
return list(self)
  File 
"/home/ajung/.buildout/eggs/SQLAlchemy-0.8.0-py2.7-linux-x86_64.egg/sqlalchemy/orm/loading.py",
 
line 65, in instances
fetch = cursor.fetchall()
  File 
"/home/ajung/.buildout/eggs/SQLAlchemy-0.8.0-py2.7-linux-x86_64.egg/sqlalchemy/engine/result.py",
 
line 748, in fetchall
l = self.process_rows(self._fetchall_impl())
  File 
"/home/ajung/.buildout/eggs/SQLAlchemy-0.8.0-py2.7-linux-x86_64.egg/sqlalchemy/engine/result.py",
 
line 715, in _fetchall_impl
return self.cursor.fetchall()
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc2 in position 18: 
ordinal not in range(128)

-- 
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] Storing a two dimensional list structure with different item types in Postgres 9.1

[Andreas Jung]

Using Postgres 9.1, SqlAlchemy 0.8.

I need to store information like

[
[10, 'liters', 'tea'],
[20, 'milliliters', 'salt'],
]

as one column into Postgres.

What is the best way to do this? Of course I use a second table with a 1:N 
relationship
I am thinking about using the Postgres Array type for this...is there a 
better option?
Right now there are numeric and string values to be stored. I can of course
convert numeric values to strings if needed.

Any options?

Andreas

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/9jnn5iZY68IJ.
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] XX and YY are both of the same direction error

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

I am getting the following error after upgrading from post-0.5.2 version
(SVN) to 0.5.3:

==
ERROR: testToolLookupVisitedBy (tool.tests.test_tool_lookup.TestToolLookup)
- --
Traceback (most recent call last):
  File "/HRS2/local/lib/python2.4/unittest.py", line 260, in run
testMethod()
  File
"/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/toolbox/tool/tests/test_tool_lookup.py",
line 47, in testToolLookupVisitedBy
rows = TL.toolsVisitedBy('ajung')
  File
"/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/toolbox/tool/__init__.py",
line 70, in toolsVisitedBy
visited_tools = session.query(VT).filter_by(benutzer=username.lower())
  File
"/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/session.py",
line 895, in query
return self._query_cls(entities, self, **kwargs)
  File
"/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/query.py",
line 91, in __init__
self._set_entities(entities)
  File
"/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/query.py",
line 100, in _set_entities
self.__setup_aliasizers(self._entities)
  File
"/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/query.py",
line 114, in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity)
  File
"/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/util.py",
line 492, in _entity_info
mapper = class_mapper(entity, compile)
  File
"/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/util.py",
line 567, in class_mapper
mapper = mapper.compile()
  File
"/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/mapper.py",
line 653, in compile
raise sa_exc.InvalidRequestError("One or more mappers failed to
compile.  Exception was probably "
InvalidRequestError: One or more mappers failed to compile.  Exception
was probably suppressed within a hasattr() call. Message was: One or
more mappers failed to compile.  Exception was probably suppressed
within a hasattr() call. Message was: One or more mappers failed to
compile.  Exception was probably suppressed within a hasattr() call.
Message was: One or more mappers failed to compile.  Exception was
probably suppressed within a hasattr() call. Message was:
Hierarchies.subscribed_by and back-reference Hierarchies.subscriber are
both of the same direction .  Did you mean to set
remote_side on the many-to-one side ?


The related code is:

class Hierarchies(Base, AsDictMixin):
__tablename__ = 'hierarchies'
__table_args__ = ( { 'autoload' : True, })
__mapper_args__ = ({'extension' : HierachiesDeletionLogger()})

id = Column(Integer, Sequence('hierarchies_seq'), primary_key=True)
parent_id = Column(Integer, ForeignKey('hierarchies.id'))
hierarchyshare_id = Column(Integer, ForeignKey('hierarchies.id'))
pos = Column(Integer)


Hierarchies.subscribed_by = relation('Hierarchies',

primaryjoin=Hierarchies.hierarchyshare_id==Hierarchies.id,
 backref=backref("subscriber",
remote_side=Hierarchies.hierarchyshare_id),
 remote_side=Hierarchies.hierarchyshare_id,
 uselist=True,
 )


Anything I am missing or something that changed over the last two weeks
at this point?

Andreas


- -- 
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: i...@zopyx.com - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK
- 
E-Publishing, Python, Zope & Plone development, Consulting

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknUzXIACgkQCJIWIbr9KYwdZgCfVfo9ZN2bNPM4iaxZoFXdcuuE
yPoAoMaqN2Wr219oL+kviY7dtotIqh/M
=RG8E
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793

[sqlalchemy] Re: Mapping an existing database

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 29.03.2009 2:44 Uhr, j...@robinlea.com wrote:
> Hi,
> 
> I'm new to SQLAlchemy. I am trying to use Pylons to expose an existing
> database in a browser. All the introductory material assumes I am
> going to create the database from python object definitions, but I
> want to create the python object definitions from the database. Am I
> on my own, or is there a well-worn path through these woods?
>

'autoload' support is your friend.

- -aj
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknPnWgACgkQCJIWIbr9KYyH4gCeKuRKLTKhrRUF5UgkaBPKz0Mo
bncAoMJsThPf4k5+tkk6iZEfIEdNTlBz
=mSV5
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: sqlalchemy and json

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25.03.2009 7:17 Uhr, alex wrote:
> sorry, but I need numeric of dicts {1:{"id":"1","name":"Alex"},2:
> {"id":"2","name":"Nick"}} to use this in ajax-call

JSON does not know about date-ish types. You have to convert such
fields explicitly e.g. to a timestamp - means you need to iterate over
the instance dict and check the types of the values and convert
them accordingly.

- -aj
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknKzXAACgkQCJIWIbr9KYwUzwCeNYfjf7CfnArX8iYRCCKjVESv
H48AnikvpBz+RQjmuJq49JEIA5k+T9Gx
=4sYk
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: session of an object

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24.03.2009 23:01 Uhr, Alessandro Dentella wrote:
> Hi,
> 
>   is there a way to get the session an object belongs to?
>   I have been looking around and cannot find any function/way for that...
> 

object_session(obj)

- -aj
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknJ1kcACgkQCJIWIbr9KYw7jgCgyZtDLWSja9NBFQc+ksbcChlr
QjoAoNmzUD8NNq6zi7ZSIVD6xVJqTWU3
=Duu+
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: sqlalchemy and json

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24.03.2009 13:45 Uhr, alex wrote:
> Hello.
> 
> I need to get query from sqlalchemy in json format.
> 
> query like
> query_name = session.query(model.Users);
> 
> How to format like
> {{"id":"1","name":"Alex"},{"id":"2","name":"Nick"}.}

The result format does not make much sense (dict of dicts). You
want a list of dicts. Something like that might work

result = list()
for row in session.query(...).all():
d = row.__dict__.copy()
del d['_sa_instance_state']
result.append(d)

- -aj
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknI7S0ACgkQCJIWIbr9KYxTOgCgkz4t1xTmu/5/7ND20SPfak5f
vrMAoJWtzssD7TnTxkSTfmnPYmYwqWJQ
=sdOD
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Application performance

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19.03.2009 11:14 Uhr, Alex K wrote:
> Well, it does not affect the performance and I still get:

The Python profiler will tell you exactly about bottlenecks.
So go and profile your code.

- -aj
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknCHHwACgkQCJIWIbr9KYxYDQCg0Iv9BHkS0h/21SJf3duluOhB
61kAn3DDo/OtLn5aU8gldAH4PGxpoK5w
=zBz2
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Any one tried to use SqlAlchemy with XML RPC?

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19.03.2009 8:32 Uhr, Arthur Pemberton wrote:
> 2009/3/19 Andreas Jung :
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 19.03.2009 6:49 Uhr, Arthur Pemberton wrote:
>>
>>> SQLAlchemy needs a concept of Data Transfer Objects so that it can be
>>> easily transmitted and reconstituted as necessary and everyone doesn't
>>> have to reimplemented the idea themselves. There are many cases where
>>> one might want to transfer a model from a data source on a server side
>>> to a client, and vice verse.
>> Which usecases? If you want to transfer objects over the wire from one
>> app to another, you can also attach the database directly. You you work
>> with serialized
>> versions of the objects in some way...should be sufficient enough for most
>> cases.
> 
> 
> Any use case when the client doesn't have access to the database, but
> the client app needs to manipulate the data of the model but not the
> model itself.
> 
> Client requests something
> Server uses SQLAlchemy to query data
> Server sends resulting entity collection to client via some form of RPC
> Only the entity data is transmitted
> Client makes modification, and wants to save the changes
> Model, with changes is sent back to server
> Server remaps it and saves it
> 
> The client shouldn't be able to save the entity from their end. But
> the developer shouldn't have to worry about making the entity lighter
> every time as well.
>

As said: serialization is easy to implemented and JSON is your friend
for transfering stuff over the net.

Andreas
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknB9YoACgkQCJIWIbr9KYxewACfdrBHZ82+gEbV8lGavkXe7zWe
RpIAoIXGlqhitwwiRFgWdVzGbT8sT/8P
=sbo+
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Any one tried to use SqlAlchemy with XML RPC?

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19.03.2009 6:49 Uhr, Arthur Pemberton wrote:

> 
> SQLAlchemy needs a concept of Data Transfer Objects so that it can be
> easily transmitted and reconstituted as necessary and everyone doesn't
> have to reimplemented the idea themselves. There are many cases where
> one might want to transfer a model from a data source on a server side
> to a client, and vice verse.

Which usecases? If you want to transfer objects over the wire from one
app to another, you can also attach the database directly. You you work
with serialized
versions of the objects in some way...should be sufficient enough for most
cases.

- -aj

- -- 
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: i...@zopyx.com - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Gesch
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknB3j4ACgkQCJIWIbr9KYysHQCfTuXFRZDIF4IBsL6UGA7ZYbEO
CPIAoOFK4wA3mAmfuoR6byTWh9ngUpGc
=U7aQ
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Any one tried to use SqlAlchemy with XML RPC?

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19.03.2009 5:24 Uhr, 一首诗 wrote:
> This is my ORM class definition:
> 
> #=
> class User(Base):
> __tablename__ = 'b_taa_user'
> 
> user_id = Column(Integer, primary_key=True, autoincrement=True,
> nullable=False) # 用户ID
> user_name = Column(Unicode(255), unique=True, nullable=False,
> server_default="") # 用户名称
> user_password = Column(Unicode(255), nullable=False,
> server_default="") # 用户密码
> email = Column(Unicode(255), nullable=False, server_default="") # 邮
> 箱
> mobile = Column(Unicode(255), nullable=False, server_default="") #
> 手机
> phone = Column(Unicode(255), nullable=False, server_default="") # 电
> 话
> 
> def __init__(self, user_name = ""):
> self.user_name = username
> #=
> 
> I made some debug by the code below:
> 
> #=
> 
> #!/usr/bin/python
> 
> import xmlrpclib
> from blf.model import User
> 
> u = User('333')
> s = xmlrpclib.dumps((u,), methodresponse=True, allow_none=True)
> print s
> #=
> 
> Finally, I found that the first problem is :  xmlrpclib only serialize
> type *instance*.
> But to my surprise, when I check type of u, it's a *class* object.
> Don't know why ...
> 
> 

Sending mapped instances over XMLRPC is basically nonsense. XMLRPC is
designed for transporting elementary datatypes over the wire in order to
invoke a remote method. It is not designed for sending instances of some
class and not for sending Python pickles. No idea what you are trying to
accomplish. Consider converting a mapped instance to dict and sending
this dict serialized in JSON format to a remote XMLRPC server.

- -aj
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAknB17UACgkQCJIWIbr9KYwARgCeJ1VuU6Gi9p+GVkYQzZiVWMd3
aJoAoMwJUc8lIyOBhDwJVq3/L3QzuRoK
=aihX
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: newbie problem

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 18.02.2009 15:26 Uhr, Marcin Krol wrote:


> 
> This is pretty obvious, 'id' integer column has not been filled. But I 
> have no idea how to remedy this, since this seems to be dependent on 
> smth in the guts of sqlalchemy. Help!
>

Column('id',Integer, Sequence('some_sequence', primary_key=True),

?

- -aj
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkmcJmoACgkQCJIWIbr9KYwhKwCfa7I2TUUekmZ+JtPqIqcr9gyt
YdwAnj1iOgnmkE4YezlDxLdob8w+uEd6
=XlOJ
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: How to write a self 2 self relationship?

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

This is covered by the decl. layer documentation (including examples):
 - either use strings for the parameter
 or
 - you write outside the class scope
   User.sons = relation()

- -aj

On 16.02.2009 9:57 Uhr, 一首诗 wrote:
> I tried to write :
> 
> #
> class User(Base):
> __tablename__ = 'users'
> 
> id = Column(Integer, primary_key=True)
> name = Column(String)
> fullname = Column(String)
> password = Column(String)
> sons = relation(User, order_by=User.id, backref="parent")
> #
> 
> But the as 'User' is not defined at the line "... relation ... " is
> processed, the code above doesn't work.
> 
> So, does sqlalchemy support self 2 self relationship ?
> If the answer is "YES", how to do it?
> 

- -- 
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: i...@zopyx.com - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK
- 
E-Publishing, Python, Zope & Plone development, Consulting

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkmZLQkACgkQCJIWIbr9KYxMRgCeODPG7oL4uj/HmmGinx9E1q3r
xJsAoLq11zIMhRsSqLZfm2RwlLqWB6yA
=ND9E
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] [SA0.5.2/PG] KeyError: pg_expression_2

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi there,

using SA 0.5.2/Postgres 7.4.22.

Reflecting an existing database gives me this:

(Pdb) c
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.2-py2.4.egg/sqlalchemy/engine/base.py:1265:
SAWarning: Skipped unsupported reflection of expression-based index
produkt_user_idx
  self.dialect.reflecttable(conn, table, include_columns)
2009-02-02 08:09:07 ERROR Zope Couldn't install HaufeCMS
Traceback (innermost last):
  Module OFS.Application, line 806, in install_product
   - __traceback_info__: HaufeCMS
  Module Products.HaufeCMS, line 80, in initialize
  Module Products.HaufeCMS, line 75, in setupSQLAlchemyWrappers
  Module haufe.hrs.bausteindb.database.setup, line 29, in setupBausteinDB
  Module z3c.sqlalchemy.util, line 70, in createSAWrapper
  Module z3c.sqlalchemy.base, line 81, in __init__
  Module haufe.hrs.bausteindb.database.model, line 32, in getModel
  Module sqlalchemy.ext.declarative, line 543, in __init__
  Module sqlalchemy.ext.declarative, line 477, in _as_declarative
  Module sqlalchemy.schema, line 113, in __call__
  Module sqlalchemy.schema, line 241, in __init__
  Module sqlalchemy.engine.base, line 1265, in reflecttable
  Module sqlalchemy.databases.postgres, line 658, in reflecttable
  Module sqlalchemy.util, line 634, in __getitem__
KeyError: u'pg_expression_2'

The related table and the index are defined as follows:

CREATE TABLE produkt
(
  id integer NOT NULL,
  id_produktgruppe integer,
  id_medium integer,
  redakteur character varying(50) DEFAULT ''::text,
  assistenz character varying(50) DEFAULT ''::text,
  piep character varying(50) DEFAULT ''::text,
  entwickler character varying(50) DEFAULT ''::text,
  materialnr character varying(50),
  id_land integer,
  satzdl character varying(50) DEFAULT ''::text, -- uid des
Satzdienstleisters
  piep_sv character varying(50) DEFAULT ''::text, -- uid des Piep-
Stellvertreters
  assistenz_sv character varying(50) DEFAULT ''::text, -- uid des
Assistenz- Stellvertreters
  redakteur_sv character varying(50) DEFAULT ''::text,
  fred character varying(50) DEFAULT ''::text,
  fred_sv character varying(50) DEFAULT ''::text,
  is_big boolean DEFAULT false,
  piep_temp character varying(50),
  CONSTRAINT produkt_pkey PRIMARY KEY (id),
  CONSTRAINT fk_id_baustein_id FOREIGN KEY (id)
  REFERENCES baustein (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_produktgruppe_produktgruppe_id FOREIGN KEY
(id_produktgruppe)
  REFERENCES produktgruppe (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_produkt_land FOREIGN KEY (id_land)
  REFERENCES land (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_produkt_medium FOREIGN KEY (id_medium)
  REFERENCES medium (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;
ALTER TABLE produkt OWNER TO postgres;
GRANT ALL ON TABLE produkt TO postgres WITH GRANT OPTION;
GRANT SELECT, UPDATE, INSERT ON TABLE produkt TO bausteinuser;
GRANT SELECT ON TABLE produkt TO bausteinreader;
GRANT SELECT ON TABLE produkt TO pcsuser;
COMMENT ON COLUMN produkt.satzdl IS 'uid des Satzdienstleisters';
COMMENT ON COLUMN produkt.piep_sv IS 'uid des Piep- Stellvertreters';
COMMENT ON COLUMN produkt.assistenz_sv IS 'uid des Assistenz-
Stellvertreters';


- -- Index: produkt_user_idx

- -- DROP INDEX produkt_user_idx;

CREATE INDEX produkt_user_idx
  ON produkt
  USING btree
  (lower(redakteur::text), lower(assistenz::text), lower(piep::text),
lower(entwickler::text), lower(piep_sv::text),
lower(assistenz_sv::text), lower(fred::text), lower(redakteur_sv::text),
lower(fred_sv::text));


Andreas




- -- 
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: i...@zopyx.com - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK
- 
E-Publishing, Python, Zope & Plone development, Consulting

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkmGnK4ACgkQCJIWIbr9KYw+UgCfVNgspoHHorL9/qGMRzIBszUU
QksAnjd+DAFU8jkDiUt2BApK9qD61ca2
=Eb8B
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:C

[sqlalchemy] Re: parent/child relationship: what am I doing wrong?

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

The syntax is basically same. Look at the standard documentation and
examples for relation() and apply it to the decl layer.

- -aj

On 01.02.2009 16:17 Uhr, Eric Ongerth wrote:
> Kevin, the default behavior is for relations to be represented by
> lists.  If what you want is a tree structure where a directory can
> only have a single parent, you would use backref=backref("parentdir",
> uselist=False).  Or at least that's how you'd do it in plain SA; i
> haven't used the declarative base yet so I don't know if there's a
> different syntax.
> 
> 
> 
> On Feb 1, 6:35 am, Kevin Dangoor  wrote:
>> I've done many many-to-one relationships with SQLAlchemy, but there
>> must be something obvious I'm doing wrong here:
>>
>> class Directory(Base):
>> __tablename__ = "directories"
>>
>> id = Column(Integer, primary_key=True)
>> name = Column(String, unique=True)
>> subdirs = relation('Directory', backref="parentdir")
>> parent_id = Column(Integer, ForeignKey('directories.id'))
>>
>> def __str__(self):
>> return "Dir: %s" % (self.name)
>>
>> "Base" is standard declarative Base.
>>
>> The odd behavior that I'm seeing is that on a newly created Directory
>> instance, both subdirs and parentdir are lists when I would expect
>> parentdir to just be null or a Directory.
>>
>> This is using SQLAlchemy 0.5.2.
>>
>> I do hope there's something obvious that I missed...
>>
>> Thanks,
>> Kevin
>>
>> --
>> Kevin Dangoor
>>
>> email: k...@blazingthings.com
>> blog:http://www.BlueSkyOnMars.com
> 

- -- 
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: i...@zopyx.com - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK
- 
E-Publishing, Python, Zope & Plone development, Consulting

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkmFvewACgkQCJIWIbr9KYxuAwCeMzPu3fyzoba+Cwbws508398M
A9cAoJT/BDc77RQ3TDMC8ulBWCMp00kC
=lETo
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: INTERVAL 1 DAY in ORM

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26.01.2009 6:23 Uhr, Jan Koprowski wrote:
> Hi !
> 
>   I get some query:
> 
> DELETE FROM passwordrequest WHERE requested_at <= (NOW() - INTERVAL 1
> DAY)
> 
> which delete all requests older then one day. How can I get this
> effect in SQLAlchemy ?
>

untested:

session = ...
PR = 
for pr in
session.query(PR).filter(PR.requested_at<=(datetime.now()-datetime.timedelta(1)):
session.delete(pr)

- -aj

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkl9UuQACgkQCJIWIbr9KYwtwQCgwciGZ91zVag/s9JRrQMdOXk2
nl4AoIz5ixXW9h3L4U7D2z6rMpmtwv9s
=oFIk
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: How to improve performance of sqlalchemy based application?

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 18.01.2009 16:32 Uhr, Victor Lin wrote:
> Hi,
> So I think that would be better to buffer data and flush them.

This is easily spoken the implementation pattern of SA: unit-of-work.
You might check the documentation.

- -aj
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAklzTXYACgkQCJIWIbr9KYxVqgCgt/3nMpCIzobob2S8KkZTCOs9
JVMAnAlOqCU9g5Np/zm8pWvzpBRiwlk4
=PoRx
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: [Decl. Layer] Multiple mappers for one table?

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 13.01.2009 15:52 Uhr, Michael Bayer wrote:
> 
> On Jan 13, 2009, at 8:19 AM, Andreas Jung wrote:
> 
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> Is it possible to define multiple mappers for one table?
>>
>> Usecase: I have one big table (60 cols) and one big mapper class.
>> For some reporting I don't need only a small number of cols. For
>> performance reasons I would like to load the related rows using a very
>> minimal mapper. However there is no obvious way to configure a
>> non-primary mapper using the declarative layer?
> 
> declarative can't create non primary mappers (it would be a  
> contradiction), but "non primary mapper" only applies to multiple  
> mappers for a class, not for a table.any number of mappers of any  
> kind can point to a single table.
> 
> to map to any table just set the __table__ argument to the Table.   if  
> you already have another declarative class, youd say __table__ =  
> MyOtherClass.__table__.
> 
>

This fails:

This is full-fledged mapper:

class Arbeitsmittel(Base, AsDictMixin):

__tablename__ = 'arbeitsmittel'
__table_args__ = (
{ 'autoload' : True, })

# column redefinitions needed for relation()s below
idfassung = Column(Integer, ForeignKey('fassung.id'))
format = Column(String, ForeignKey('format.format'))
status = Column(Integer, ForeignKey('status.id'))
idsachgebiet = Column(Integer, ForeignKey('sachgebiet.id'))
idzielgruppe = Column(Integer, ForeignKey('zielgruppen.id'))
idgattung = Column(Integer, ForeignKey('gattung.id'))
styleguide_compliant = Column(Integer,
ForeignKey('styleguide_compliant.id'))

The minimal mapper looks like this:

 class ArbeitsmittelMinimal(Base, AsDictMixin):
__table__ = Arbeitsmittel.__table__
hidx = Column(String, primary_key=True)



/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0-py2.4.egg/sqlalchemy/engine/base.py:1265:
SAWarning: Predicate of partial index hierarchies_pid_is_root_true
ignored during reflection
  self.dialect.reflecttable(conn, table, include_columns)
2009-01-13 16:03:47 ERROR Zope Couldn't install Toolbox
Traceback (innermost last):
  Module OFS.Application, line 806, in install_product
   - __traceback_info__: Toolbox
  Module Products.Toolbox, line 20, in initialize
  Module toolbox.database.setup, line 74, in setup
  Module z3c.sqlalchemy.util, line 70, in createSAWrapper
  Module z3c.sqlalchemy.base, line 81, in __init__
  Module toolbox.database.model, line 791, in getModel
  Module toolbox.database.model, line 779, in modelAll
  Module sqlalchemy.ext.declarative, line 372, in __init__
  Module sqlalchemy.ext.declarative, line 364, in _as_declarative
  Module sqlalchemy.orm, line 736, in mapper
  Module sqlalchemy.orm.mapper, line 196, in __init__
  Module sqlalchemy.orm.mapper, line 481, in _configure_properties
  Module sqlalchemy.orm.mapper, line 578, in _compile_property
  Module sqlalchemy.schema, line 618, in __str__
  Module sqlalchemy.util, line 1358, in __get__
  Module sqlalchemy.sql.expression, line 2805, in description
AttributeError: 'NoneType' object has no attribute 'encode'

Traceback (most recent call last):
  File "", line 1, in ?
  File
"/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/Zope2/__init__.py",
line 55, in app
startup()
  File
"/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/Zope2/__init__.py",
line 51, in startup
_startup()
  File
"/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/Zope2/App/startup.py",
line 138, in startup
OFS.Application.initialize(application)
  File
"/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/OFS/Application.py",
line 303, in initialize
initializer.initialize()
  File
"/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/OFS/Application.py",
line 332, in initialize
self.install_products()
  File
"/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/OFS/Application.py",
line 612, in install_products
return install_products(app)
  File
"/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/OFS/Application.py",
line 647, in install_products
raise_exc=raise_on_error,
  File
"/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/OFS/Application.py",
line 806, in install_product
initmethod(context)
  File
"/local/HRS2/Devel/junga/tb-dev/parts/products-svn/Toolbox/__init__.py",
line 20, in initialize
setup(forZope=True, echo=echo)
  File
"/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/toolbox/database/setup.py",
line 74, in setup
name=TOOLBOX_WRAPPER_NAME)
  File

[sqlalchemy] [Decl. Layer] Multiple mappers for one table?

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Is it possible to define multiple mappers for one table?

Usecase: I have one big table (60 cols) and one big mapper class.
For some reporting I don't need only a small number of cols. For
performance reasons I would like to load the related rows using a very
minimal mapper. However there is no obvious way to configure a
non-primary mapper using the declarative layer?

Andreas

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAklslOgACgkQCJIWIbr9KYyjxQCeMHZjliuQ0quo8JYeBLYIuA8f
//gAoIN6MBhy5bz+Z9uiqYANBGzv+jRX
=vafk
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Suggestions for abbreviations

[Andreas Jung]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11.01.2009 12:13 Uhr, Cito wrote:
> I wonder why declarative_base() doesn't simply set __tablename__ to
> the name of the class by default (maybe translating camelcase to
> lowercase with underscores).


Please no implicit magic under the hood. You can have multiple mappers
for the same table. Citing Guido: explicit is better than implicit.

My-2-cent,
Andreas
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAklp16AACgkQCJIWIbr9KYzroQCgxVi70Tb7Bf+qMd5uRcrfbNtf
+sEAnjRkrmhGjyt18Igv5f3RacGjDkzD
=ibCE
-END PGP SIGNATURE-

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: [SA0.5/Postgres 7.4] SAVEPOINT sent although not supported through Postgres 7.4

[Andreas Jung]

On 02.01.2009 18:11 Uhr, Michael Bayer wrote:
>
> On Jan 2, 2009, at 4:24 AM, Andreas Jung wrote:
>
>> Hi there,
>>
>> running SA 0.5rc4, psycopg2 together with Postgres 7.4.22.
>>
>> I am currently migrating a Zope application from SA 0.3 to SA 0.5.
>> While the migration worked for a bigger application I have an issue
>> with a smaller application. SA issues a "SAVEPOINT XXX" command
>> although
>> Postgres 7.4 obviously does not support savepoints.
>
>
> SAVEPOINT is only issued if you do so explicitly, via begin_nested()
> on Session or Connection.   You'll have to modify your application to
> not use these methods.

This is not true. According to the traceback, being_nested() is called 
from within SA itself.

Andreas

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] [SA0.5/Postgres 7.4] SAVEPOINT sent although not supported through Postgres 7.4

[Andreas Jung]

Hi there,

running SA 0.5rc4, psycopg2 together with Postgres 7.4.22.

I am currently migrating a Zope application from SA 0.3 to SA 0.5.
While the migration worked for a bigger application I have an issue
with a smaller application. SA issues a "SAVEPOINT XXX" command although 
Postgres 7.4 obviously does not support savepoints.

Andreas



aj...@suxmac2:~/Desktop cat out.txt
 > 
/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/mediendb/versions.py(394)copyToWorkspace()
-> if generate_thumbnail:
(Pdb) n
 > 
/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/mediendb/versions.py(395)copyToWorkspace()
-> self.generateThumbnail(lidx, 0, mediumObj)
(Pdb)
2009-01-02 10:15:01 INFO sqlalchemy.engine.base.Engine.0x...6110 
SAVEPOINT sa_savepoint_1
2009-01-02 10:15:01 INFO sqlalchemy.engine.base.Engine.0x...6110 {}
ProgrammingError: 
 > 
/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/mediendb/versions.py(395)copyToWorkspace()
-> self.generateThumbnail(lidx, 0, mediumObj)
(Pdb) where
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/ZServer/PubCore/ZServerPublisher.py(23)__init__()
-> response=response)
 
/local/HRS2/Devel/junga/tb-dev/parts/products-svn/ZopeProfiler/MonkeyPatcher.py(35)__call__()
-> return self._function(*args,**kw)
 
/local/HRS2/Devel/junga/tb-dev/parts/products-svn/ZopeProfiler/ZopeProfiler.py(353)_profilePublishModule()
-> request=request, response=response)
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/ZPublisher/Publish.py(494)publish_module()
-> environ, debug, request, response)
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/ZPublisher/Publish.py(287)publish_module_standard()
-> response = publish(request, module_name, after_list, debug=debug)
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/ZPublisher/Publish.py(148)publish()
-> request, bind=1)
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/ZPublisher/mapply.py(98)mapply()
-> if debug is not None: return debug(object,args,context)
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/ZPublisher/Publish.py(57)call_object()
-> result=apply(object,args) # Type s to step into published object.
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/Products/CMFCore/FSPythonScript.py(108)__call__()
-> return Script.__call__(self, *args, **kw)
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/Shared/DC/Scripts/Bindings.py(313)__call__()
-> return self._bindAndExec(args, kw, None)
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/Shared/DC/Scripts/Bindings.py(350)_bindAndExec()
-> return self._exec(bound_data, args, kw)
 
/local/HRS2/Devel/junga/tb-dev/parts/zope2/lib/python/Products/CMFCore/FSPythonScript.py(163)_exec()
-> result = f(*args, **kw)
 
/local/HRS2/Devel/junga/tb-dev/parts/products-svn/MedienDB/skins/mediendb/mdbMediumCopyToWorkspaceAction.xpy(9)mdbMediumCopyToWorkspaceAction()
-> versioner.copyToWorkspace(context.getId(), version)
 > 
/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/mediendb/versions.py(395)copyToWorkspace()
-> self.generateThumbnail(lidx, 0, mediumObj)
 
/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/mediendb/versions.py(252)generateThumbnail()
-> medium = self.mediendb.getMedium(lidx)
 
/local/HRS2/Devel/junga/tb-dev/parts/modules-svn/mediendb/mediendb.py(185)getMedium()
-> return session.query(Medium).filter_by(linkindex=lidx).one()
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/query.py(1048)one()
-> ret = list(self[0:2])
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/query.py(952)__getitem__()
-> return list(res)
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/query.py(1087)__iter__()
-> self.session._autoflush()
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/session.py(912)_autoflush()
-> self.flush()
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/session.py(1354)flush()
-> self._flush(objects)
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/session.py(1424)_flush()
-> flush_context.execute()
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/unitofwork.py(260)execute()
-> UOWExecutor().execute(self, tasks)
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/unitofwork.py(723)execute()
-> self.execute_save_steps(trans, task)
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/unitofwork.py(738)execute_save_steps()
-> self.save_objects(trans, task)
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/unitofwork.py(729)save_objects()
-> task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/mapper.py(1196)_save_obj()
-> connection = uowtransaction.transaction.connection(self)
 
/local/HRS2/Devel/junga/tb-dev/eggs/SQLAlchemy-0.5.0rc4-py

[sqlalchemy] Re: Sanity checks

[Andreas Jung]

On 01.01.2009 13:40 Uhr, vctr...@gmail.com wrote:
> Is there a way to do some sanity check through thr SQLAlchemy.
> Check like - SQL Service is running, DB exists etc.

huh? You can perform some stupid queries against the related
database or table and catch error using Python's exception handling.

-aj

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Callback on save when value of field has changed?

[Andreas Jung]

On 27.12.2008 11:06 Uhr, Jim Jones wrote:
> Hi List,
>
> I have this simple model (in declarative style):
>
> class Item( Base ):
>  __tablename__ = 'items'
>  desc  = Column( 'desc', UnicodeText(), nullable=False )
>  email = Column( 'email', UnicodeText(), nullable=False )
>
> How can I trigger an event when the value of 'desc' has changed
> and the instance is persisted (e.g. via 'session.save(item)')?

Please check the SA docs on using the decl. layer. It tells you how to 
define your own get/set methods using the property mechanism.

If this is not good enough: check the MapperExtension mechanism, or 
SessionExtensions or AttributeExtensions.

-aj

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Updating existing objects

[Andreas Jung]

On 27.12.2008 3:06 Uhr, Eric Abrahamsen wrote:
> Sorry for the very beginner question, but I haven't been able to find
> a good discussion of this issue online (possibly because it's too
> basic).
>
> I'm using sqlalchemy as part of a basic web-based CRUD-style CMS. I've
> got the Create, Read and Delete parts down, but I'm not sure how best
> to handle the Update part. Because the update page handler functions
> are generic and reused for many different models, all I can be sure of
> is having a single object instance, and a dictionary of new values
> with which to update the instances attributes. All I can think of
> doing is
>
> instance.__dict__.update(value_dict)

for k,v in value_dict.items().
setattr(instance, k, v)

-aj

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Efficient dictificationof result sets

[Andreas Jung]

On 19.12.2008 2:57 Uhr, Michael Bayer wrote:
>
> On Dec 18, 2008, at 3:04 PM, Andreas Jung wrote:
>
>> On 10.12.2008 20:36 Uhr, Michael Bayer wrote:
>>> On Dec 10, 2008, at 2:27 PM, Andreas Jung wrote:
>>>
>>>> Hi there,
>>>>
>>>> is there some more efficient way for dictifying a resultset other
>>>> than
>>>>
>>>> lst = list()
>>>> for row in session.query(...).all():
>>>>
>>>>  d = self.__dict__.copy()
>>>>  for k in d.keys():
>>>>  if k.startswith('_sa'):
>>>>  del d[k]
>>>>
>>>>  lst.append(d)
>>>>
>>>> Especially the loop of the keys takes pretty long for large result
>>>> sets
>>>> and tables with lots of columns.
>>> the most efficient way would be to use a ResultProxy instead of the
>>> ORM and to call dict() on each row.Otherwise a more succinct way
>>> of what you're doing there, not necessarily much faster, might be
>>>
>>>
>>> list = [
>>> dict((k, v) for k, v in obj.__dict__.iteritems() if not
>>> k.startswith('_sa'))
>>> for obj in session.query.all()
>>>
>> Does SA contain some official API to introspect the list of defined
>> synonyms for a particular class? The goal is to take values defined
>> as a
>> synonym also into account for the dictification (for backward
>> compatiblity reasons for an existing codebase).
>
> the mapper's get_property() method includes a "resolve_synonyms"
> keyword arg that indicates a given key which points to a synonym
> should return the actual referenced property, so a recipe that builds
> upon this would look like:
>
> set([mapper.get_property(p.key, resolve_synonyms=True) for p in
> mapper.iterate_properties])

However this does not apply when using the declarative layer. Any 
options within such a context?

Andreas

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Efficient dictificationof result sets

[Andreas Jung]

On 10.12.2008 20:36 Uhr, Michael Bayer wrote:
>
> On Dec 10, 2008, at 2:27 PM, Andreas Jung wrote:
>
>> Hi there,
>>
>> is there some more efficient way for dictifying a resultset other than
>>
>> lst = list()
>> for row in session.query(...).all():
>>
>>  d = self.__dict__.copy()
>>  for k in d.keys():
>>  if k.startswith('_sa'):
>>  del d[k]
>>
>>  lst.append(d)
>>
>> Especially the loop of the keys takes pretty long for large result
>> sets
>> and tables with lots of columns.
>
> the most efficient way would be to use a ResultProxy instead of the
> ORM and to call dict() on each row.Otherwise a more succinct way
> of what you're doing there, not necessarily much faster, might be
>
>
> list = [
>   dict((k, v) for k, v in obj.__dict__.iteritems() if not
> k.startswith('_sa'))
>   for obj in session.query.all()
>

Does SA contain some official API to introspect the list of defined 
synonyms for a particular class? The goal is to take values defined as a 
synonym also into account for the dictification (for backward
compatiblity reasons for an existing codebase).

Andreas

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: [SA0.5b4/Postgres 7.4] ValueError: 'need more than 0 values to unpack'

[Andreas Jung]

On 16.12.2008 17:09 Uhr, Michael Bayer wrote:
> I see that.  There is definitely other model code not seen here being
> compiled. Since I see you're in a unit test, perhaps a previous test has
> not been cleaned up completely ? You can see within the trace that its a
> PropertyLoader being initialized and it's trying to setup one of its
> backrefs. None of that is possible without invoking the relation()
> function, which does not occur within SQLAlchemy. I would try invoking
> pdb and possibly setting a breakpoint on line 157 of
> sqlalchemy.orm.properties.py (relation()) to locate the source of the call.
>
Thanks for the hint. In fact the model had been initialized serveral 
times over the tests.

Andreas

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: [SA0.5b4/Postgres 7.4] ValueError: 'need more than 0 values to unpack'

[Andreas Jung]

There is no relation() involved here. Every relation() related code is
commented right now.
(see attachment).

Andreas

On Tue, Dec 16, 2008 at 4:48 PM, Michael Bayer wrote:

>
> your mappers are failing to compile (i.e. if you were to call
> compile_mappers() manually), and the stack trace is specific to a
> relation(), which you haven't illustrated in your setup.
>
>
> On Dec 16, 2008, at 7:31 AM, Andreas Jung wrote:
>
> > Hi,
> >
> > I have a fairly complex model using the declarative layer.
> > For a simple table 'fassung'
> >
> > Toolbox2=# \d fassung
> >   Table "public.fassung"
> >Column| Type  | Modifiers
> >
> > -+---
> > +
> >  id  | bigint| not null default
> > nextval('"fassung_id_key"'::text)
> >  nr  | integer   |
> >  bezeichnung | character varying(50) |
> > Indexes:
> > "fassung_pkey" primary key, btree (id)
> >
> >
> > I habe the following definition
> >
> > 178 class Fassung(Base):$
> > 179 __tablename__ = 'fassung'
> > 180 __table_args__ = (
> > 181 { 'autoload' : True, }
> > 182 )
> >
> > A simple query fails badly from within a unittest. I have checked
> > the whole mode definition and could not find and further reference
> > to the 'Fassung' mapper or 'fassung' table.
> >
> > Bug or feature?
> >
> > Andreas
> >
> > ---
> >
> >
> >>
> > /local/HRS2/Devel/ajung/tb-dev/parts/modules-svn/toolbox/database/
> > tests/test_database.py(61)testSimpleSelects()
> > -> rows = session.query(mapper).limit(50).all()
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/session.py(908)query()
> > -> return self._query_cls(entities, self, **kwargs)
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/query.py(95)__init__()
> > -> self.__setup_aliasizers(self._entities)
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/query.py(109)__setup_aliasizers()
> > -> mapper, selectable, is_aliased_class = _entity_info(entity)
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/util.py(466)_entity_info()
> > -> mapper = class_mapper(entity, compile)
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/util.py(543)class_mapper()
> > -> mapper = mapper.compile()
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/mapper.py(679)compile()
> > -> mapper._post_configure_properties()
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/mapper.py(701)_post_configure_properties()
> > -> prop.init(key, self)
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/interfaces.py(404)init()
> > -> self.do_init()
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/properties.py(579)do_init()
> > -> self._post_init()
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/properties.py(838)_post_init()
> > -> self.backref.compile(self)
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/properties.py(989)compile()
> > -> mapper._compile_property(self.key, relation);
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/mapper.py(643)_compile_property()
> > -> prop.init(key, self)
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/interfaces.py(404)init()
> > -> self.do_init()
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/properties.py(578)do_init()
> > -> self._determine_local_remote_pairs()
> >
> > /home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/
> > orm/properties.py(814)_determine_local_remote_pairs()
> > -> self.local_side, self.remote_side = [util.OrderedSet(x) for x in
> > zip(*list(self.local_remote_pairs))]
&

[sqlalchemy] [SA0.5b4/Postgres 7.4] ValueError: 'need more than 0 values to unpack'

[Andreas Jung]

Hi,

I have a fairly complex model using the declarative layer.
For a simple table 'fassung'

Toolbox2=# \d fassung
   Table "public.fassung"
Column| Type  | Modifiers 

-+---+
  id  | bigint| not null default 
nextval('"fassung_id_key"'::text)
  nr  | integer   |
  bezeichnung | character varying(50) |
Indexes:
 "fassung_pkey" primary key, btree (id)


I habe the following definition

178 class Fassung(Base):$
179 __tablename__ = 'fassung'
180 __table_args__ = (
181 { 'autoload' : True, }
182 )

A simple query fails badly from within a unittest. I have checked
the whole mode definition and could not find and further reference
to the 'Fassung' mapper or 'fassung' table.

Bug or feature?

Andreas

---


 > 
/local/HRS2/Devel/ajung/tb-dev/parts/modules-svn/toolbox/database/tests/test_database.py(61)testSimpleSelects()
-> rows = session.query(mapper).limit(50).all()
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/session.py(908)query()
-> return self._query_cls(entities, self, **kwargs)
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/query.py(95)__init__()
-> self.__setup_aliasizers(self._entities)
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/query.py(109)__setup_aliasizers()
-> mapper, selectable, is_aliased_class = _entity_info(entity)
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/util.py(466)_entity_info()
-> mapper = class_mapper(entity, compile)
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/util.py(543)class_mapper()
-> mapper = mapper.compile()
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/mapper.py(679)compile()
-> mapper._post_configure_properties()
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/mapper.py(701)_post_configure_properties()
-> prop.init(key, self)
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/interfaces.py(404)init()
-> self.do_init()
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/properties.py(579)do_init()
-> self._post_init()
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/properties.py(838)_post_init()
-> self.backref.compile(self)
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/properties.py(989)compile()
-> mapper._compile_property(self.key, relation);
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/mapper.py(643)_compile_property()
-> prop.init(key, self)
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/interfaces.py(404)init()
-> self.do_init()
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/properties.py(578)do_init()
-> self._determine_local_remote_pairs()
 
/home/ajung/.buildout/eggs/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/orm/properties.py(814)_determine_local_remote_pairs()
-> self.local_side, self.remote_side = [util.OrderedSet(x) for x in 
zip(*list(self.local_remote_pairs))]

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:i...@zopyx.com
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: Setting up a self-referential mapper using the declarative layer

[Andreas Jung]

On 11.12.2008 11:10 Uhr, [EMAIL PROTECTED] wrote:
> afaik, u can supply strings instead of real things everywhere in those
> arguments.. they are eval()ed against some context at later time.
>

This works to some degree:

class Hierarchies(Base):
 __tablename__ = 'hierarchies'
 __table_args__ = (
 { 'autoload' : True, })

 parent_id = Column('parent_id', Integer, ForeignKey('hierarchies.id'))

 _children = relation('Hierarchies',
 
primaryjoin='Hierarchies.parent_id==Hierarchies.id',
  order_by=['Hierarchies.pos'],
  cascade="all",
  backref=backref("parent", 
remote_side=['Hierarchies.id'])


hso = session.query(Hierarchies).filter_by(id=1641).one()
print hso._children
for c in hso._children:
 print c

hso._children is a PropertyLoader instance in this case that can not be used
for iteration.

The "same" code implemented as standard mapper using SA 0.3 behaves as 
it should.
I can not see the difference how to make it work with SA 0.5.

Andreas

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:[EMAIL PROTECTED]
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Setting up a self-referential mapper using the declarative layer

[Andreas Jung]

Can the decl. layer be used to setup a self-referential mapper like


class Foo(Base):

__tablename__ = 'foo'
__table_args__ = {'autoload' : True}

children = relation(Foo, primaryjoin=Foo.parent_id==Foo.id)
parent = relation(Foo, primary_join=Foo.parent_id=Foo.id,
  remote_side=[Foo.id])

Basically using 'Foo' within the relation() does not work within the 
class definition of Foo itself.

Andreas

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:[EMAIL PROTECTED]
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Efficient dictificationof result sets

[Andreas Jung]

Hi there,

is there some more efficient way for dictifying a resultset other than

lst = list()
for row in session.query(...).all():

 d = self.__dict__.copy()
 for k in d.keys():
 if k.startswith('_sa'):
 del d[k]

 lst.append(d)

Especially the loop of the keys takes pretty long for large result sets
and tables with lots of columns.

Andreas


-- 
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:[EMAIL PROTECTED]
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: any easy way to make a just-out-of-db object readonly?

[Andreas Jung]

On 05.12.2008 17:10 Uhr, [EMAIL PROTECTED] wrote:
> i'm asking about SA-related stuff, i know how to handle the python
> side. how to "lock" relations, collections etc - how to make an
> instance readonly?
>
> and eventualy if it is poosible after that to unlock that instance at
> some point - so lock all the user-visible stuff but leave some
> flag "_locked" writable.

You might register a MapperExtension with your mapper. Inside the 
before_*() methods you could perform some checks in order to avoid any 
kind of write operations or modifications.

-aj

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:[EMAIL PROTECTED]
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] Re: ProgrammingError: (ProgrammingError) Fehler »syntax error« bei »SAVEPOINT«

[Andreas Jung]

Oopps...I was using the wrong database...someone switched the port 
numbers :->

Andreas

On 23.11.2008 15:17 Uhr, Andreas Jung wrote:
> I am getting the following error with SA 0.5.0rc4 and Postgres 8.1
> while trying some integration work with SQLAlchemy and Plone (on top of
> Zope). Any idea?
>
> Andreas
>

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

begin:vcard
fn:Andreas Jung
n:Jung;Andreas
org:ZOPYX Ltd. & Co. KG
adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany
email;internet:[EMAIL PROTECTED]
title:CEO
tel;work:+49-7071-793376
tel;fax:+49-7071-7936840
tel;home:+49-7071-793257
x-mozilla-html:FALSE
url:www.zopyx.com
version:2.1
end:vcard

[sqlalchemy] ProgrammingError: (ProgrammingError) Feh ler »syntax error« bei »SAVEPOINT«

[Andreas Jung]

I am getting the following error with SA 0.5.0rc4 and Postgres 8.1
while trying some integration work with SQLAlchemy and Plone (on top of 
Zope). Any idea?

Andreas

get():   
ab23df631c18ea2c9ae7be8321f647dd
2008-11-23 15:13:39 ERROR root Exception while rendering an error message
Traceback (most recent call last):
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/OFS/SimpleItem.py",
 
line 227, in raise_standardErrorMessage
 v = s(**kwargs)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/plone/CMFCore/FSPythonScript.py",
 
line 140, in __call__
 return Script.__call__(self, *args, **kw)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/Shared/DC/Scripts/Bindings.py",
 
line 313, in __call__
 return self._bindAndExec(args, kw, None)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/Shared/DC/Scripts/Bindings.py",
 
line 350, in _bindAndExec
 return self._exec(bound_data, args, kw)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/plone/CMFCore/FSPythonScript.py",
 
line 196, in _exec
 result = f(*args, **kw)
   File "Script (Python)", line 34, in standard_error_message
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/Shared/DC/Scripts/Bindings.py",
 
line 313, in __call__
 return self._bindAndExec(args, kw, None)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/Shared/DC/Scripts/Bindings.py",
 
line 350, in _bindAndExec
 return self._exec(bound_data, args, kw)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/plone/CMFCore/FSPageTemplate.py",
 
line 216, in _exec
 result = self.pt_render(extra_context=bound_names)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/plone/CMFCore/FSPageTemplate.py",
 
line 155, in pt_render
 result = FSPageTemplate.inheritedAttribute('pt_render')(
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/Products/PageTemplates/PageTemplate.py",
 
line 89, in pt_render
 return super(PageTemplate, self).pt_render(c, source=source)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/pagetemplate/pagetemplate.py",
 
line 117, in pt_render
 strictinsert=0, sourceAnnotations=sourceAnnotations)()
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 271, in __call__
 self.interpret(self.program)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 346, in interpret
 handlers[opcode](self, args)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 891, in do_useMacro
 self.interpret(macro)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 346, in interpret
 handlers[opcode](self, args)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 536, in do_optTag_tal
 self.do_optTag(stuff)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 521, in do_optTag
 return self.no_tag(start, program)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 516, in no_tag
 self.interpret(program)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 346, in interpret
 handlers[opcode](self, args)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 891, in do_useMacro
 self.interpret(macro)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 346, in interpret
 handlers[opcode](self, args)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tal/talinterpreter.py",
 
line 586, in do_setLocal_tal
 self.engine.setLocal(name, self.engine.evaluateValue(expr))
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_rdbms/buildout/trunk/parts/zope2/lib/python/zope/tales/tales.py",
 
line 696, in evaluate
 return expression(self)
   File 
"/home/ajung/src/marburg/zopyx.plone.archetypes_on_

[sqlalchemy] Re: Load capabilities with default pool_size and max_overflow

[Andreas Jung]

--On 15. Juli 2008 22:32:35 -0700 Fotinakis <[EMAIL PROTECTED]> wrote:



Hello SQLAlchemists,

What is (or what do you think is) the load that SQLAlchemy can handle
with the default engine options of pool_size=5 and max_overflow=10?
The application I'm working on has the potential for bursts of
thousands of requests in a few seconds, so I am concerned about load.


Default settings are called that way because they make sense to possibly
80% of all use-cases. Don't expect that a high-traffic environment works 
without tuning. So the answer is basically: unlikely.




Is it appropriate to increase these values to some arbitrary number?


Those parameters are exposed as part of the API for performing such a 
tuning?!




-aj

pgpjvt2cUDjxr.pgp
Description: PGP signature

[sqlalchemy] Re: Implementation patterns for initializing the model and obtaining the mappers

[Andreas Jung]

--On 29. Juni 2008 16:57:16 -0400 Michael Bayer <[EMAIL PROTECTED]> 
wrote:





On Jun 29, 2008, at 12:46 PM, Andreas Jung wrote:


This approach is ugly (because of putting the mapper within the local
scope into the global scope (in order to make them importable) and
because
of this code within main.py:

import model
model.setup(dsn)
from model import MyMapper, Session

This there any better pattern for implementing this?



you don't need to pass DSN as a parameter.  The "engine" argument to
declarative_base() is optional, and the docs have been revised in
recent months to reflect this.



However the engine is required in order to perform autoloading.

Andreas

pgpEpALHo4YAD.pgp
Description: PGP signature

[sqlalchemy] Implementation patterns for initializing the model and obtaining the mappers

[Andreas Jung]

I am coming from the Zope world and implemented a SA integration layer 
z3c.sqlalchemy. It basically provides a 'wrapper' exposing a thread-local 
session as property and as a registry for mappers that can be accessed 
through a getMapper(mapper_name) method. Now with the declarative layer I 
am trying to get rid of the approach and basically define the mapper classes

on the module level and importing them from the other modules as needed.

The problem with module-level initialization is that you have little control
about initialization parameter (e.g. I must be able to pass the DSN as 
parameter). My current code looks a bit like this:



main.py:


dsn = get_dsn_from_some_config_object(..)
import model
model.setup(dsn)
from model import MyMapper, Session
session = Session()
rows = session.query(MyMapper).filter_by().all()


model.py:
-

def setup(dsn):

  engine= create_engine(...)
  session = scoped_session(sessionmaker(...)))

  Base = declarative_base(engine)

  class MyMapper(Base):
   __table__ = Table('mytable', Base.metadata, autoload=True)

  for k,v in locals().items():
  globals()[k]=v

This approach is ugly (because of putting the mapper within the local
scope into the global scope (in order to make them importable) and because
of this code within main.py:

import model
model.setup(dsn)
from model import MyMapper, Session

This there any better pattern for implementing this?

Andreas



pgp8Sn4MBKtP9.pgp
Description: PGP signature

[sqlalchemy] Re: text

[Andreas Jung]

--On 11. Juni 2008 17:55:44 +0100 King Simon-NFHD78 
<[EMAIL PROTECTED]> wrote:





-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]

On Behalf Of jack2318

Sent: 11 June 2008 17:50
To: sqlalchemy
Subject: [sqlalchemy] text


I tried very simple test:

s = text("SELECT COUNT(*) FROM table WHERE field LIKE 'something'")
count = conn.execute(s).fetchone()

and this produced error
Unexpected error:  not enough arguments
for format string

not big deal there is few way around but just for completeness I
believe it should work (no parameters to expand)

regards
-- jacek
PS: I am using mysql



Are you sure you didn't try:

s = text("SELECT COUNT(*) FROM table WHERE field LIKE 'something%'")




'%' must be quoted as '%%' in Python.

-aj

pgpwdoqzxGU8V.pgp
Description: PGP signature

[sqlalchemy] Re: Using SQLAlchemy with Plone/Zope

[Andreas Jung]

--On 16. Mai 2008 18:06:46 +0200 Andreas Jung <[EMAIL PROTECTED]> wrote:




--On 16. Mai 2008 08:58:38 -0700 Mike <[EMAIL PROTECTED]> wrote:



Hi,

We are using SQLAlchemy 4.X in Zope using SQLAlchemyDA, found here:

http://opensource.zopyx.com/projects/SQLAlchemyDA

This uses SA 0.4.0. We are connecting to MS SQL 2000. While this works
in Zope for normal SQL commands, if we try to execute a stored
procedure, we get the following traceback:


[Traceback (most recent call last):
  File "sqltest.py", line 14, in ?
print cur.fetchall()
  File "/opt/Plone-2.5/Python-2.4.3/lib/python2.4/site-packages/
SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py", line 1231, in
__repr__
return repr(tuple(self))
  File "/opt/Plone-2.5/Python-2.4.3/lib/python2.4/site-packages/
SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py", line 1223, in
__iter__
yield self.__parent._get_col(self.__row, i)
  File "/opt/Plone-2.5/Python-2.4.3/lib/python2.4/site-packages/
SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py", line 1468, in
_get_col
rec = self._key_cache[key]
AttributeError: 'ResultProxy' object has no attribute '_key_cache'


I'm not sure how to go about troubleshooting this.


You're basically on the wrong list. This issue belong on the SQLAlchemy
mailing list. There is no indication for a anything related to Zope.


Sorry, you're on the right list. Somehow I saw this mail in the context
of the Zope lists.

Andreas



pgpIslnSPgDzR.pgp
Description: PGP signature

[sqlalchemy] Re: Using SQLAlchemy with Plone/Zope

[Andreas Jung]

--On 16. Mai 2008 08:58:38 -0700 Mike <[EMAIL PROTECTED]> wrote:



Hi,

We are using SQLAlchemy 4.X in Zope using SQLAlchemyDA, found here:

http://opensource.zopyx.com/projects/SQLAlchemyDA

This uses SA 0.4.0. We are connecting to MS SQL 2000. While this works
in Zope for normal SQL commands, if we try to execute a stored
procedure, we get the following traceback:


[Traceback (most recent call last):
  File "sqltest.py", line 14, in ?
print cur.fetchall()
  File "/opt/Plone-2.5/Python-2.4.3/lib/python2.4/site-packages/
SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py", line 1231, in
__repr__
return repr(tuple(self))
  File "/opt/Plone-2.5/Python-2.4.3/lib/python2.4/site-packages/
SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py", line 1223, in
__iter__
yield self.__parent._get_col(self.__row, i)
  File "/opt/Plone-2.5/Python-2.4.3/lib/python2.4/site-packages/
SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py", line 1468, in
_get_col
rec = self._key_cache[key]
AttributeError: 'ResultProxy' object has no attribute '_key_cache'


I'm not sure how to go about troubleshooting this.


You're basically on the wrong list. This issue belong on the SQLAlchemy 
mailing list. There is no indication for a anything related to Zope.



Would upgrading it
to SQLAlchemy 0.4.5 fix it?


Try it out yourself and see what's happening. Nobody know about your 
particular setup, code etc. - but you do.


Andreas



pgpeOiaMvryu8.pgp
Description: PGP signature

[sqlalchemy] Re: can I set the really default value ?

[Andreas Jung]

--On 31. März 2008 01:25:48 +0800 "张沈鹏(电子科大 
毕/就业倒计时...)" <[EMAIL PROTECTED]> wrote:



I just mean default value in the create table's sql .

such as
CREATE TABLE xxx
(
id INTEGER NOT NULL,
value INTEGER default 0
)




Table(..., default=xxx)

-aj

pgpR3GBJhaQEH.pgp
Description: PGP signature

[sqlalchemy] Re: Problems connecting to Postgresql using collective.lead

[Andreas Jung]

--On 18. Januar 2008 15:56:02 -0800 Alex Turner <[EMAIL PROTECTED]> wrote:



I am getting an error connecting to Postgresql:
ImportError: unknown database 'psycopg'




The canonical driver name for Postgres databases is 'postgres'.
Looks like a typo in Martin's book. psycopg(2) is name of the
underlying Python driver for Postgres but it is not part
of the DSN.

Andreas

pgpkKYBlPQyN5.pgp
Description: PGP signature

[sqlalchemy] Re: Filter by year in datetime column

[Andreas Jung]

--On 18. Januar 2008 12:08:46 -0500 Rick Morrison <[EMAIL PROTECTED]> 
wrote:



There are no generic date functions in SQLAlchemy (although work has begun
on them). So for now, you'll need to use date functions native to your
database.

For sqlite something like,

func.strftime('%Y', Mikropost.c.create_date) == '2008'

should work -- you may need to add additional percent signs depending on
how badly the '%Y' literal is mangled during parameter collection.




Such operations will likely trigger a full table scan. Depending on the 
size of your data you might adjust the data model to the need of your query 
and/or think about functional indexes (in case your database provides this 
functionality).


Andreas

pgpQvohGvKt7k.pgp
Description: PGP signature

[sqlalchemy] SA 0.3.11 producing wrong code for func.any()

[Andreas Jung]

Hi,

running SA 0.3.11 against PG 7.4.

A SA query like

session.query(Node).query(func.any(Node.c.tools=='1234')

produces the following SQL code:

select   from node where
where any(node.tools) = '1234'.

However Postgres does not support this syntax and complains
with a syntax error. Postgres requires:

...where '1234' = any(node.tools).

Is there a workaround to get the syntax right through configuration?

Andreas

pgp38fYgWnK0u.pgp
Description: PGP signature

[sqlalchemy] Re: [Postgres/SA 0.3.X] Support for arrays?

[Andreas Jung]

--On 2. Januar 2008 11:11:20 -0500 Michael Bayer <[EMAIL PROTECTED]> 
wrote:





On Jan 2, 2008, at 2:56 AM, Andreas Jung wrote:


I have a PG table containing a row

tools varchar(256)[][]

that stores a list of tuples ('HI1234', 'somestring').

Setting the tools property on Python level leads to bad SQL code:

ProgrammingError) Fehler »syntax error« bei »,« at character 52
'UPDATE hierarchies SET tools=%(tools)s WHERE hierarchies.id = %
(hierarchies_id)s' {'tools': [('HI641774', ''), ('HI508670', ''),
('HI788784', ''), ('HI952450', ''), ('HI884580', ''), ('HI642122',
''), ('HI1496415', '')], 'hierarchies_id': 7667}

Is there a workaround in SA 0.3 for working with arrays? I assume
the PGArray type in SA 0.4 would resolve this issue..however the
code base is too big right for doing the switch to SA 0.4 now.





Converting the list of tuple to the desired string representation like

'{{"HIXX", "foo"}, {"HIYY", "bar"}}'

and assigning the string to the 'tools' property did the job.

Andreas

pgpDWBzhTjWIO.pgp
Description: PGP signature

[sqlalchemy] [Postgres/SA 0.3.X] Support for arrays?

[Andreas Jung]

I have a PG table containing a row

tools varchar(256)[][]

that stores a list of tuples ('HI1234', 'somestring').

Setting the tools property on Python level leads to bad SQL code:

ProgrammingError) Fehler »syntax error« bei »,« at character 52 'UPDATE 
hierarchies SET tools=%(tools)s WHERE hierarchies.id = %(hierarchies_id)s' 
{'tools': [('HI641774', ''), ('HI508670', ''), ('HI788784', ''), 
('HI952450', ''), ('HI884580', ''), ('HI642122', ''), ('HI1496415', '')], 
'hierarchies_id': 7667}


Is there a workaround in SA 0.3 for working with arrays? I assume the 
PGArray type in SA 0.4 would resolve this issue..however the code base is 
too big right for doing the switch to SA 0.4 now.


Andreas



pgp0VUVvgWAuo.pgp
Description: PGP signature

[sqlalchemy] Re: Modelling connected trees

[Andreas Jung]

--On 30. Dezember 2007 18:02:13 -0500 Michael Bayer 
<[EMAIL PROTECTED]> wrote:




I am using this model since some years for the application (hand-
made Python code/SQL queries) but now I want to adopt it to SA.

The basic question is how I would extend the configuration for the
self-referential mapper in order to make Tree 1 appear as:

   A
  / \
 B   N
/ |   |\
D  E   P Q

To make it more clear: when obtaining the list of children for node
'A'
I want to get hold of the rows for the nodes 'B' and 'N', not 'B'
and 'C'.
So the node 'C' should be replaced with the node 'N' and its subtree.

Any idea how to model this in a sane way using SA?


without seeing much detail here it seems like youd just have a
relation along "share_id", which im assuming is a different kind of
relation than the one from A to B, and to "conceal" the C node and
make it look like an N you could use a proxying class attribute.or
you could do something similar at the SQL level by using a more
elaborate "primaryjoin" condition from parent node to child node,
which tests for the presence of "share_id" - possibly using a
"viewonly" relation which is separate from the "persistence" relation
if you also need to set up the "C" node attached to "A" explicitly for
a flush operation.



I found a perfectly solution for this issue. The mapper  looks like this:

   mapper(HierarchiesNode, HierarchiesTable, properties={


   '_children' : relation(
   HierarchiesNode,

primaryjoin=and_(HierarchiesTable.c.parent_id==HierarchiesTable.c.id),
   order_by=[HierarchiesTable.c.pos],
   cascade="all",
   viewonly=True, # working??
   backref=backref("parent", 
remote_side=[HierarchiesTable.c.id])


and the HierarchiesNode class provides a propery 'children' that deals with
links to other nodes:

   @property
   def children(self):

   lst = list()
   for n in self._children:

   # pointer (id) of a shared node back to its
   # original node within the current tree
   n.referenced_by_id = None

   if n.hierarchyshare_id is None:
   # node does not reference another node (no share)
   lst.append(n)
   else:
   # node points to another node -> obtain the referenced
   # node and return it as current child instead of the 
original

   # node
   session = object_session(self)
   shared_node = 
session.query(HierarchiesNode).filter_by(HierarchiesNode.c.id==n.hierarchyshare_id).one()

   shared_node.referenced_by_id = n.id
   lst.append(shared_node)

   return lst

So 'children' basically return the list of child nodes where nodes pointing
to other trees are replaced with the referenced node.

This works fine as read-only data-structure. Modifications of the tree 
structure itself happen outside (given by the nature of the whole 
application).


Andreas




pgplL769T8tEd.pgp
Description: PGP signature

[sqlalchemy] Modelling connected trees

[Andreas Jung]

Hi,

I habe a table where we store tree-ish data using a self-referential
mapper (same setup as within the SA docs using an id for the nodes
and a pointer back to the parent node given by its id)...this works
without a problem.

Now comes the tricky part: a node from one tree can point to a node
within another tree for sharing a common subtree. In this example tree 2
is something like a "master" tree and tree 1 derives the subtree 'N' from 
tree 2. The link between to nodes is a dedicated 'share_id' where in this
particular case the row for node 'C' contains the id of node 'N' as 
'share_id'.



  Tree 1  Tree 2

AM
   / \  / \
  B   C --->   N   O
 / \  /\
D   EP  Q


I am using this model since some years for the application (hand-made 
Python code/SQL queries) but now I want to adopt it to SA.


The basic question is how I would extend the configuration for the 
self-referential mapper in order to make Tree 1 appear as:


A
   / \
  B   N
/ |   |\
D  E   P Q

To make it more clear: when obtaining the list of children for node 'A'
I want to get hold of the rows for the nodes 'B' and 'N', not 'B' and 'C'.
So the node 'C' should be replaced with the node 'N' and its subtree.

Any idea how to model this in a sane way using SA?

Tnx,
Andreas




pgp8wdoCUqrR1.pgp
Description: PGP signature

[sqlalchemy] Re: Readonly objects/protecting objects from modifications

[Andreas Jung]

--On 21. Dezember 2007 16:33:34 -0500 Michael Bayer 
<[EMAIL PROTECTED]> wrote:




On Dec 21, 2007, at 3:13 PM, Rick Morrison wrote:





I think the only way something like this should be done is as a test
fixture which decorates classes during unit tests.It would be
fairly clumsy to have in production code.

If you have coworkers who write broken code, the way you solve that is
by having unit tests which will fail when the coworkers in question do
something theyre not supposed to.   If other people are writing code
that sets attrbutes its not supposed to and breaks things, you need
more tests to catch those conditions.  If youre putting code into
production that hasnt been tested, then you need a build process,
automated testing, etc.There is definitely a "best practice" here
and test driven development is it.


With all respect, this is not a useful answer. Even with tests (unittests 
and weeks of manual tests) I had the case that a simple programming error
(of my own) produced a data disaster after some weeks. There is no 100% 
test coverage. Tests don't solve all problems. There is sometimes the need 
for a better security belt.


Andreas

pgpDTXI5voi9F.pgp
Description: PGP signature

[sqlalchemy] Readonly objects/protecting objects from modifications

[Andreas Jung]

Is there a way to protect objects from modifications? I have a complex
self-referential data structure that is passed to some other modules 
written by co-workers. I need to avoid that the data structure is changed 
(because

of programming errors). Is there an easy way to accomplish this (other than
by using a dedicated read-only DB connection where the user has only
select grants)?

Andreas

pgpyUaTts2Z4U.pgp
Description: PGP signature

[sqlalchemy] Re: SQLAlchemy 0.4 beta5 (OperationalError) database table is locked '\nDROP TABLE os' () sqlite 3.4.2

[Andreas Jung]

--On 14. September 2007 10:07:27 -0700 jason kirtland <[EMAIL PROTECTED]> 
wrote:




jean-marc pouchoulon wrote:

helo,
I don't understand why my sqlite database is locked on a drop ( the code
is following)
thanks for your explanation.


 > [...snip...]

ordinateurs = computers_table.select(computers_table.c.prix <
100.0).execute()
ordinateur_pas_cher  = ordinateurs.fetchone()
print ("marque d'un ordi pas cher: %s ") % ordinateur_pas_cher.marque

ordinateurs = computers_table.select(and_((computers_table.c.prix >
1000.0),(computers_table.c.prix > 1000.0))).execute()
ordinateur_medium_price  = ordinateurs.fetchone()
print ("marque d'un ordi medium price: %s ") % ordinateur_pas_cher.marque

ordinateurs =
select([computers_table.c.marque,computers_table.c.prix]).execute()
for ordi in ordinateurs:
print "marque:%s prix:%s" % (ordi[0],ordi[1])

result_os.close()
ordinateurs.close()


I am currently facing a similar issue using SA 0.4b5. The following unittest
(taken from z3c.sqlalchemy) works with SA 0.3.X but fails with SA 0.4b5 
(fails using SQLite but works against a Postgres database):


   def testSimplePopulation(self):
   db = createSAWrapper(self.dsn)
   # obtain mapper for table 'users'

   User = db.getMapper('users')
   session = db.session

   session.save(User(firstname='udo', lastname='juergens'))
   session.save(User(firstname='heino', lastname='n/a'))
   session.flush()

   rows = session.query(User).all()
   self.assertEqual(len(rows), 2)


==
ERROR: testSimplePopulation (tests.testSQLAlchemy.WrapperTests)
--
Traceback (most recent call last):
 File "/opt/python-2.4.4/lib/python2.4/unittest.py", line 260, in run
   testMethod()
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/z3c/sqlalchemy/tests/testSQLAlchemy.py", 
line 79, in testSimplePopulation

   session.flush()
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/orm/session.py", 
line 665, in flush

   self.uow.flush(self, objects)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/orm/unitofwork.py", 
line 202, in flush

   flush_context.execute()
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/orm/unitofwork.py", 
line 414, in execute

   UOWExecutor().execute(self, head)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/orm/unitofwork.py", 
line 1033, in execute

   self.execute_save_steps(trans, task)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/orm/unitofwork.py", 
line 1047, in execute_save_steps

   self.save_objects(trans, task)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/orm/unitofwork.py", 
line 1038, in save_objects

   task.mapper.save_obj(task.polymorphic_tosave_objects, trans)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/orm/mapper.py", 
line 1155, in save_obj

   c = connection.execute(statement.values(value_params), params)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/engine/base.py", 
line 789, in execute

   return Connection.executors[c](self, object, multiparams, params)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/engine/base.py", 
line 821, in _execute_clauseelement
   return self._execute_compiled(elem.compile(dialect=self.dialect, 
parameters=param, inline=executemany), multiparams, params)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/engine/base.py", 
line 832, in _execute_compiled

   self.__execute_raw(context)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/engine/base.py", 
line 850, in __execute_raw
   self._cursor_execute(context.cursor, context.statement, parameters, 
context=context)
 File 
"/Users/ajung_data/sandboxes/haufe.sqlalchemy/lib/python/sqlalchemy/engine/base.py", 
line 867, in _cursor_execute

   raise exceptions.DBAPIError.instance(statement, parameters, e)
OperationalError: (OperationalError) database is locked u'INSERT INTO users 
(firstname, lastname) VALUES (?, ?)' ['udo', 'juergens']




pgpCYWmKk8b7Y.pgp
Description: PGP signature

[sqlalchemy] Re: "where"-based UPDATEs with ORM?

[Andreas Jung]

--On 8. September 2007 08:14:50 -0700 Adam Atlas <[EMAIL PROTECTED]> wrote:



I feel like I must be missing something obvious here, but is it
possible to do in-database batch UPDATEs with "WHERE" clauses using
the ORM/session API? Like, sess.query(User).filter(User.first_name ==
'Mackenzie').filter(User.preferred_hand ==
'left').update(banned=True). (Uh, sorry for the weird example. I have
nothing against left-handed people named Mackenzie; I'm just
tired. :P) Is that possible?



Yes, by reading the fine docs :-)



-aj

pgpSiib7PkMdp.pgp
Description: PGP signature

[sqlalchemy] [SA 0.4] Getting a mapper class from a mapper class

[Andreas Jung]

I've been using the following code for obtaining a mapper class
from some other mapper.

class_mapper(self.__class__).props[name].mapper.class

'props' is no longer available however I found '_Mapper__props'.

Is this the right replacement or is there a smarter way?

Andreas

pgpLkaS4WLfE3.pgp
Description: PGP signature

[sqlalchemy] Re: query date field

[Andreas Jung]

--On 6. Juli 2007 23:27:30 + jose <[EMAIL PROTECTED]> wrote:



I've got a question that I can't find the answer to.  I have a table
called seminars with a date field in it to hold the seminar dates.  I
want to query the table to find all the dates for a specific year. I
tried query(Seminars).filter(Seminars.c.date.year==2007) but this gave
me an error stating that the col does not have a year property.  So
how should I do this?
Jose




func.to_char(table.c.date, '') == '2007'

or by using between(table.c.date, datetime(2007,1,1) , datetime(2007,31, 
12)) or something like that...


-aj

pgpML11WJE8sW.pgp
Description: PGP signature

[sqlalchemy] Re: SA trying to update read-only views in Postgres

[Andreas Jung]

--On 6. Juli 2007 11:15:38 -0400 Michael Bayer <[EMAIL PROTECTED]> 
wrote:





On Jul 6, 2007, at 6:25 AM, Andreas Jung wrote:



I have a self-referential mapper where a view is involved within a
primaryjoin of a relation. Traversing a tree represented through
the mapper generates a bunch of UPDATE operations on the view (no
idea why
so far) although only SELECT operations are used during the query.
However views in Postgres are read-only. The error message is


also I would note that SQLAlchemy never issues any updates/inserts/
deletes of any kind unless flush() is explicitly called (or a
SessionTransaction is committed).


Right, but SA is integrated into the transaction system of Zope
in our environment so a ZODB commit/abort will trigger a flush(), commit()
operation on the SA level.

-aj

pgpeKR5fenBOE.pgp
Description: PGP signature

[sqlalchemy] Re: SA trying to update read-only views in Postgres

[Andreas Jung]

Clear()ing the session before the mandatory commit resolved the issue
(more a workaround than a solution :->).

Andreas

--On 6. Juli 2007 12:25:25 +0200 Andreas Jung <[EMAIL PROTECTED]> wrote:



I have a self-referential mapper where a view is involved within a
primaryjoin of a relation. Traversing a tree represented through the
mapper generates a bunch of UPDATE operations on the view (no idea why
so far) although only SELECT operations are used during the query.
However views in Postgres are read-only. The error message is


 (ProgrammingError)  kann eine Sicht nicht aktualisieren
HINT:  Sie benötigen eine ON UPDATE DO INSTEAD Regel ohne Bedingung.
 'UPDATE amhier_am_view SET bezeichnung=%(bezeichnung)s WHERE
amhier_am_view.hidx = %(amhier_am_view_hidx)s AND
amhier_am_view.versionsnr = %(amhier_am_view_versionsnr)s AND
amhier_am_view.idhierarchy = %(amhier_am_view_idhierarchy)s'
{'bezeichnung': 'Beiratsordnung einer GmbH (und GmbH & Co. KG)',
'amhier_am_view_hidx': 'HI100010', 'amhier_am_view_versionsnr': 0,
'amhier_am_view_idhierarchy': 166

Basically it means "Can't update View. HINT: you need an ON UPDATE DO
INSTEAD rule without condition".

Ok, I added a rule

CREATE RULE XXX as ON UPDATE TO amhier_am_view DO INSTEAD NOTHING.

This triggers this error message

   Updated rowcount 0 does not match number of objects updated 201

What to do?

Andreas

--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting




--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgp0E3aelacNr.pgp
Description: PGP signature

[sqlalchemy] SA trying to update read-only views in Postgres

[Andreas Jung]

I have a self-referential mapper where a view is involved within a 
primaryjoin of a relation. Traversing a tree represented through the mapper 
generates a bunch of UPDATE operations on the view (no idea why

so far) although only SELECT operations are used during the query.
However views in Postgres are read-only. The error message is


(ProgrammingError)  kann eine Sicht nicht aktualisieren
HINT:  Sie benötigen eine ON UPDATE DO INSTEAD Regel ohne Bedingung.
'UPDATE amhier_am_view SET bezeichnung=%(bezeichnung)s WHERE 
amhier_am_view.hidx = %(amhier_am_view_hidx)s AND amhier_am_view.versionsnr 
= %(amhier_am_view_versionsnr)s AND amhier_am_view.idhierarchy = 
%(amhier_am_view_idhierarchy)s' {'bezeichnung': 'Beiratsordnung einer GmbH 
(und GmbH & Co. KG)', 'amhier_am_view_hidx': 'HI100010', 
'amhier_am_view_versionsnr': 0, 'amhier_am_view_idhierarchy': 166


Basically it means "Can't update View. HINT: you need an ON UPDATE DO 
INSTEAD rule without condition".


Ok, I added a rule

CREATE RULE XXX as ON UPDATE TO amhier_am_view DO INSTEAD NOTHING.

This triggers this error message

  Updated rowcount 0 does not match number of objects updated 201

What to do?

Andreas

--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgpbDixzNVGcl.pgp
Description: PGP signature

[sqlalchemy] Re: Mapper based on a select generates wrong and unnecessary INSERTs

[Andreas Jung]

--On 2. Juli 2007 14:22:06 + Michael Bayer <[EMAIL PROTECTED]> wrote:





On Jul 2, 8:39 am, Andreas Jung <[EMAIL PROTECTED]> wrote:

So why the hell is SA trying to insert something into my 'zielgruppen'
table?


any table in your SELECT statement for which its full set of primary
key columns are represented in the columns clause of the SELECT, will
generate an INSERT.




What is the sense of this behavior?

-aj

pgpWoDeuyTOYO.pgp
Description: PGP signature

[sqlalchemy] Mapper based on a select generates wrong and unnecessary INSERTs

[Andreas Jung]

I have a case where SA 0.3.8 create wrong SQL code and an unneeded and 
unwanted

INSERT statement. My code inserts a new Arbeitsmittel into the DB using:

   Arbeitsmittel = wrapper.getMapper('arbeitsmittel')

   d = {'hidx' : hidx,
'zodb_path' : zodb_path,
'versionsnr' : 0,
'dateiname' : AM.dateiname.replace(AM.hidx, hidx),
'neudat' : datetime.datetime.now(),
'benutzer' : getSecurityManager().getUser().getUserName().lower(),
'status' : 4,
'idzielgruppe' : 0,
   }

   AM_new = Arbeitsmittel(**d)
   session.save(AM_new)


The 'Arbeitsmittel' mapper is defined against a select():


   ##
   # Arbeitsmittel
   ##

   FassungTable = Table('fassung', metadata, autoload=True)
   StatusTable = Table('status', metadata, autoload=True)
   SachgebietTable = Table('sachgebiet', metadata, autoload=True)
   ZielGruppenTable = Table('zielgruppen', metadata, autoload=True)
   ArbeitsmittelTable = Table('arbeitsmittel', metadata, autoload=True)

   s = select([ArbeitsmittelTable,
   func.to_char(ArbeitsmittelTable.c.gesperrt_bis, 
'DD.MM.').label('gesperrt_bis_string'),
   func.to_char(ArbeitsmittelTable.c.stand, 
'DD.MM.').label('stand_string'),
   func.to_char(ArbeitsmittelTable.c.aedat, 
'DD.MM.').label('aedat_string'),
   func.to_char(ArbeitsmittelTable.c.neudat, 
'DD.MM.').label('neudat_string'),
   func.to_char(ArbeitsmittelTable.c.rechner_stand, 
'DD.MM.').label('rechnerstand_string'),
   func.to_char(ArbeitsmittelTable.c.stand, 
'-MM-DD').label('stand_string_us'),
   func.to_char(ArbeitsmittelTable.c.aedat, 
'-MM-DD').label('aedat_string_us'),
   func.to_char(ArbeitsmittelTable.c.neudat, 
'-MM-DD').label('neudat_string_us'),
   func.to_char(ArbeitsmittelTable.c.chgdat, 
'-MM-DD').label('chgdat_string_us'),

   GattungTable.c.bezeichnung.label('gattung_bezeichnung'),
   GattungTable.c.gattung.label('gattung_gattung'),
   GattungTable.c.dtd.label('gattung_dtd'),
   GattungTable.c.typ.label('gattung_typ'),

GattungTable.c.template_dateiname.label('gattung_template_dateiname'),

GattungTable.c.template_bezeichnung.label('gattung_template_bezeichnung'),
   GattungTable.c.dtd_kuerzel,
   GattungTable.c.dtd_bezeichnung,
   GattungTable.c.dtd_version,
   GattungTable.c.dtd_identifier,
   ZielGruppenTable.c.id.label('zielgruppen_id'),

ZielGruppenTable.c.bezeichnung.label('zielgruppen_bezeichnung'),
   FassungTable.c.bezeichnung.label('fassung_bezeichnung'),

SachgebietTable.c.bezeichnung.label('sachgebiet_bezeichnung'),
   StatusTable.c.bezeichnung.label('status_bezeichnung'),
  ],
  and_(ArbeitsmittelTable.c.idgattung==GattungTable.c.id,
   ArbeitsmittelTable.c.idsachgebiet==SachgebietTable.c.id,
   ArbeitsmittelTable.c.status==StatusTable.c.status,

ArbeitsmittelTable.c.idzielgruppe==ZielGruppenTable.c.id,
  )).alias('arbeitsmittel_select')


   class Arbeitsmittel(object): pass
   mapper(Arbeitsmittel, s)



When generating the new instance above the following code SQL code is 
executed:



2007-07-02 12:02:07,836 INFO 
sqlalchemy.orm.unitofwork.UOWTransaction.0x..94 Task dump:


UOWTask(-0x511aef94, Arbeitsmittel/arbeitsmittel_select/None) (save/update 
phase)

  |- Save [EMAIL PROTECTED]
  |


2007-07-02 12:02:07,837 INFO sqlalchemy.engine.base.Engine.0x..74 BEGIN
2007-07-02 12:02:07,850 INFO sqlalchemy.engine.base.Engine.0x..74 select 
nextval('"arbeitsmittel_id_key"'::text)

2007-07-02 12:02:07,850 INFO sqlalchemy.engine.base.Engine.0x..74 None
2007-07-02 12:02:07,854 INFO sqlalchemy.engine.base.Engine.0x..74 INSERT 
INTO arbeitsmittel (id, bezeichnung, version, stand, format, status, 
faxabruf, dateiname, originalname, idautor, idfassung, neudat, aedat, 
bemerkung, summary, chgdat, signatur, idsachgebiet, rechtefrei, 
honorarfrei, markierung, benutzer, stellvertreter, versionsnr, hidx, 
versionskommentar, zodb_path, idzielgruppe, rechner_version, 
formular_version, hidx_master, gesperrt_bis, rechnerstand, rechner_stand) 
VALUES (%(id)s, %(bezeichnung)s, %(version)s, %(stand)s, %(format)s, 
%(status)s, %(faxabruf)s, %(dateiname)s, %(originalname)s, %(idautor)s, 
%(idfassung)s, %(neudat)s, %(aedat)s, %(bemerkung)s, %(summary)s, 
%(chgdat)s, %(signatur)s, %(idsachgebiet)s, %(rechtefrei)s, 
%(honorarfrei)s, %(markierung)s, %(benutzer)s, %(stellvertreter)s, 
%(versionsnr)s, %(hidx)s, %(versionskommentar)s, %(zodb_path)s, 
%(idzielgruppe)s, %(rechner_version)s, %(formular_version)s, 
%(hidx_master)s, %(gesperrt_bis)s, %(rechnerstand)s, %(rechner_stand)s)
2007-07-02 12:02:07,855 INFO sq

[sqlalchemy] Re: How to lock using sqlalchemy

[Andreas Jung]

At least the select() method has an optional parameter 'lockmode'.
You might check the docs and the release notes.

-aj

--On 29. Juni 2007 08:23:16 + mc <[EMAIL PROTECTED]> wrote:



Hi,
I need to lock a table for WRITE.
The scenario is that I have a SELECT followed by an INSERT, where the
INSERT depends on the outcome of the SELECT. I have to make sure that
no other process is modifying the table after my SELECT but before my
INSERT.

1) How do i do it via SQLAlchemy ?
2) Is there any other method (not via locks) to achieve my goal?

TIA


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group. To post to this group, send email to
sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED] For more options, visit this
group at http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---




--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgpMKX1HVFVru.pgp
Description: PGP signature

[sqlalchemy] Re: Group by? Still a problem

[Andreas Jung]

--On 27. Juni 2007 12:00:13 -0700 voltron <[EMAIL PROTECTED]> wrote:



I´m guessing a bit because I still could not find the group_by  entry
in the docs

This works:
user.select(links.c.id> 3, order_by=[user.c.id]).execute()

but this does not
user.select(links.c.id> 3,group_by=[user.c.dept]).execute()

What should be the right syntax?



Works for me:


for row in  session.query(am).select(am.c.hidx=='HI1561203', 
group_by=[am.c.hidx]):

   print row.hidx, row.versionsnr

-aj

pgpUJyI03GxCN.pgp
Description: PGP signature

[sqlalchemy] Re: Group by?

[Andreas Jung]

--On 27. Juni 2007 06:47:37 -0700 voltron <[EMAIL PROTECTED]> wrote:



How can I construct the clause "group by" using SQL construction?




By using the group_by parameter of the select() method?

-aj

pgpiPCUwENlRm.pgp
Description: PGP signature

[sqlalchemy] Checking for number of related items

[Andreas Jung]

I have a mapper defined as


mapper(HierNode, HierTable, properties={
'tools' : relation(ToolNode),

  })

Usually I am interested in iterating over all 'tools' and lazy loading
works fine. However sometimes I need to determine if one HierNode
has tools or not. Loading all tools would be too expensive for a has_tools
property. It would be enough to check for only one tools (LIMIT 1) and
return True/False. Is there any way to achieve that?

Andreas

pgp0EZ1gaasmp.pgp
Description: PGP signature

[sqlalchemy] Re: [RESOLVED]Re: SA returning indentical instances for distinct rows.

[Andreas Jung]

--On 15. Juni 2007 16:09:32 +0200 Andreas Jung <[EMAIL PROTECTED]> wrote:




--On 15. Juni 2007 09:46:27 -0400 Michael Bayer
<[EMAIL PROTECTED]> wrote:




On Jun 15, 2007, at 3:19 AM, Andreas Jung wrote:



Bug or feature?



I cant speak authoritatively here at all since youve given me no
information about your schema, ive no idea what AMH_View represents,
but AFAICT  the queries are correct, and  the results you illustate
in your console queries (which you can also get by turning "echo"
into "debug") are also consistent with the results youre
gettingim guessing that "hidx" is the primary key column, so you
are getting "HI435846" in both results.  that will correspond to the
same instance " 0xb75dc40c" in your sample object results.   if we're
talking about the "identity map", yes thats a feature.



I could resolve this issue on my own. The problem was the 
PrimaryKeyConstraint for the 'amhier_am_hier' view because the constraint

had to take another column into account.

Sorry for the trouble,
Andreas

pgpOIvXTWjlv4.pgp
Description: PGP signature

[sqlalchemy] Re: SA returning indentical instances for distinct rows.

[Andreas Jung]

--On 15. Juni 2007 09:46:27 -0400 Michael Bayer <[EMAIL PROTECTED]> 
wrote:





On Jun 15, 2007, at 3:19 AM, Andreas Jung wrote:



Bug or feature?



I cant speak authoritatively here at all since youve given me no
information about your schema, ive no idea what AMH_View represents,
but AFAICT  the queries are correct, and  the results you illustate
in your console queries (which you can also get by turning "echo"
into "debug") are also consistent with the results youre
gettingim guessing that "hidx" is the primary key column, so you
are getting "HI435846" in both results.  that will correspond to the
same instance " 0xb75dc40c" in your sample object results.   if we're
talking about the "identity map", yes thats a feature.



I doubt that the identity map works the right way.

The Hierarchy mapper represent the 'hierarchy' table:

Toolbox2Test=# \d hierarchy
Table "public.hierarchy"
Column  |Type | 
Modifiers

-+-+---
id  | integer | not null default 
nextval('public.hierarchy_id_seq'::text)

parentid| bigint  |
idprodukt   | bigint  |
produktkuerzel  | character varying(32)   |
bezeichnung | character varying(160)  |
neudat  | timestamp without time zone |
aedat   | timestamp without time zone |
benutzer| character varying(32)   |
pos | integer | default 0
linkindex   | character varying(20)   |
deleted | boolean | default false
visible | boolean |
sorting | boolean |
comment | character varying(265)  |
idhierarchy_share   | integer |
show_gattung_in_bauplan | boolean | default false
sortierung  | character varying(10)   | default 
'Given'::character varying

Indexes:
   "hierarchy_pkey" primary key, btree (id)
   "hierarchy_deleted_idx" btree (deleted)
   "hierarchy_idhierarchy_share_idx" btree (idhierarchy_share)
   "hierarchy_idprodukt_idx" btree (idprodukt)
   "hierarchy_produktkuerzel_idx" btree (produktkuerzel)



The ArbeitsmittelHierarchy mapper represents a view:

Toolbox2Test=# \d amhier_am_view
   View "public.amhier_am_view"
   Column |  Type  | Modifiers
---++---
idhierarchy   | integer|
id| bigint |
idgattung | integer|
bezeichnung   | character varying(255) |
version   | character varying(30)  |
stand | timestamp without time zone|
format| character varying(10)  |
status| integer|
umfang| integer|
seiten| integer|
faxabruf  | character varying(50)  |
dateiname | character varying(255) |
originalname  | character varying(255) |
idautor   | integer|
idfassung | integer|
neudat| timestamp without time zone|
aedat | timestamp without time zone|
bemerkung | text   |
summary   | text   |
chgdat| timestamp(0) without time zone |
signatur  | character varying(255) |
idsachgebiet  | integer|
rechtefrei| boolean|
honorarfrei   | boolean|
markierung| boolean|
benutzer  | character varying(32)  |
stellvertreter| character varying(32)  |
versionsnr| integer|
num_rtf   | integer|
num_hre   | integer|
num_pdf   | integer|
hidx  | c

[sqlalchemy] Re: Sequences

[Andreas Jung]

--On 15. Juni 2007 12:55:43 + voltron <[EMAIL PROTECTED]> wrote:



Could someone point me to the documentation about declaring and using
Sequence fields in Sqlalchemy?


Check yourself with the SA docs on sqlalchemy.org under "defining 
sequences".


-aj

pgpWMRtJ1NwMS.pgp
Description: PGP signature

[sqlalchemy] SA returning indentical instances for distinct rows.

[Andreas Jung]

Hi,

I have a self-referential mapper that looks like that.
Basicially a tree of 'HierarchyNode' where the leafs are
'ArbeitsmittelHierarchyNode' (accessed through the '_tools' property)

   mapper(HierarchyNode, HierarchyTable, properties={
   '_children' : relation(
   HierarchyNode,

primaryjoin=and_(HierarchyTable.c.parentid==HierarchyTable.c.id, 
HierarchyTable.c.deleted==False),

   order_by=[HierarchyTable.c.pos],
   cascade="all",
   backref=backref("parent", 
remote_side=[HierarchyTable.c.id])

),
   '_children_shared' : relation(
   HierarchyNode,

primaryjoin=and_(HierarchyTable.c.id==HierarchyTable.c.idhierarchy_share),
   remote_side=[HierarchyTable.c.id],
   order_by=[HierarchyTable.c.pos],
   cascade="all",
   uselist=True,
   backref=backref("parent", 
remote_side=[HierarchyTable.c.id])

),
   'parent' : relation(
   HierarchyNode,

primaryjoin=HierarchyTable.c.parentid==HierarchyTable.c.id,
   remote_side=[HierarchyTable.c.id],
   uselist=False,
),

# internal property, never use it directly
   '_tools' : relation(
   ArbeitsmittelHierarchyNode,

primaryjoin=AMH_View.c.idhierarchy==HierarchyTable.c.id,
   foreign_keys=[AMH_View.c.idhierarchy],
   uselist=True,
   backref=backref('parent', 
remote_side=[HierarchyTable.c.id]),

),
   }

I am using the following code to iterate over an example tree

   for h in tree._children:
   tools = h._tools
   print '-->',h, h.id, tools

-->  3 
[, 
]


->  10 
[]


The output shows me that both HierarchyNode have a child 
ArbeitsmittelHierarchyNode at 0xb75dc40c - which is definitely wrong.


With enabled SQL debugging I see the following queries:

2007-06-15 09:08:20,729 INFO sqlalchemy.engine.base.Engine.0x..b4 SELECT 
amhier_am_view.idautor AS amhier_am_view_idautor, 
amhier_am_view.idsachgebiet AS amhier_am_view_idsachgebiet, 
amhier_am_view.num_hre AS
amhier_am_view_num_hre, amhier_am_view.bemerkung AS 
amhier_am_view_bemerkung, amhier_am_view.versionsnr AS 
amhier_am_view_versionsnr, amhier_am_view.idzielgruppe AS 
amhier_am_view_idzielgruppe, amhier_am_view.
idfassung AS amhier_am_view_idfassung, amhier_am_view.dateiname AS 
amhier_am_view_dateiname, amhier_am_view.originalname AS 
amhier_am_view_originalname, amhier_am_view.rechnerstand AS 
amhier_am_view_rechnersta
nd, amhier_am_view.id AS amhier_am_view_id, amhier_am_view.hidx_master AS 
amhier_am_view_hidx_master, amhier_am_view.stammdatenverwaltung_tauglich AS 
amhier_am_view_stammdatenverwaltung_tauglich, amhier_am_vie
w.rechner_stand AS amhier_am_view_rechner_stand, 
amhier_am_view.rechner_version AS amhier_am_view_rechner_version, 
amhier_am_view.version AS amhier_am_view_version, 
amhier_am_view.generator_tauglich AS amhier_
am_view_generator_tauglich, amhier_am_view.aedat AS amhier_am_view_aedat, 
amhier_am_view.hidx AS amhier_am_view_hidx, amhier_am_view.honorarfrei AS 
amhier_am_view_honorarfrei, amhier_am_view.neudat AS amhier_a
m_view_neudat, amhier_am_view.rechtefrei AS amhier_am_view_rechtefrei, 
amhier_am_view.status AS amhier_am_view_status, amhier_am_view.signatur AS 
amhier_am_view_signatur, amhier_am_view.faxabruf AS amhier_am_v
iew_faxabruf, amhier_am_view.format AS amhier_am_view_format, 
amhier_am_view.idgattung AS amhier_am_view_idgattung, 
amhier_am_view.bezeichnung_custom AS amhier_am_view_bezeichnung_custom, 
amhier_am_view.stellv
ertreter AS amhier_am_view_stellvertreter, amhier_am_view.seiten AS 
amhier_am_view_seiten, amhier_am_view.zodb_path AS 
amhier_am_view_zodb_path, amhier_am_view.markierung AS 
amhier_am_view_markierung, amhier_a
m_view.umfang AS amhier_am_view_umfang, amhier_am_view.num_pdf AS 
amhier_am_view_num_pdf, amhier_am_view.chgdat AS amhier_am_view_chgdat, 
amhier_am_view.versionskommentar AS amhier_am_view_versionskommentar, a
mhier_am_view.aedat_string AS amhier_am_view_aedat_string, 
amhier_am_view.idhierarchy AS amhier_am_view_idhierarchy, 
amhier_am_view.versionsize AS amhier_am_view_versionsize, 
amhier_am_view.sperrgrund AS amhie
r_am_view_sperrgrund, amhier_am_view.summary AS amhier_am_view_summary, 
amhier_am_view.gesperrt_bis AS amhier_am_view_gesperrt_bis, 
amhier_am_view.pos AS amhier_am_view_pos, amhier_am_view.stand AS 
amhier_am_v
iew_stand, amhier_am_view.bezeichnung AS amhier_am_view_bezeichnung, 
amhier_am_view.num_rtf AS amhier_am_view_num_rtf, 
amhier_am_view.formular_version AS amhier_am_view_formular_version, 
amhier_am_view.benutze

r AS amhier_am_view_benutzer
FROM amhier_am_view
WHERE amhier_am_view.idhierarchy = %(lazy_1bca)s ORDER BY 
amhier_am_view.hidx
2007-06-15 09:08:20,729 INF

[sqlalchemy] Re: engine.base._convert_key() fails with long col names

[Andreas Jung]

--On 14. Juni 2007 14:13:47 -0400 Michael Bayer <[EMAIL PROTECTED]> 
wrote:





On Jun 14, 2007, at 9:16 AM, Andreas Jung wrote:



This happens when _convert_key() is called with
key='arbeitsmittelhierarchy_arbeitsmittel_view.stammdatenverwaltung_ta
uglich'



im confused.  are you passing that string directly to row[] ?  result column names cant have periods in them.


For course not. As you can see from the traceback this happens on the SA 
level (using a self-referential mapper).



When I look at the self._ResultProxy__keys then the key cache contains
shortend col names:


(Pdb) for x in  self._ResultProxy__keys:print x
arbeitsmittelhierarchy_arbeitsmittel_view_idautor
arbeitsmittelhierarchy_arbeitsmittel_view_idsachgebiet
arbeitsmittelhierarchy_arbeitsmittel_view_num_hre
arbeitsmittelhierarchy_arbeitsmittel_view_bemerkung
arbeitsmittelhierarchy_arbeitsmittel_view_versionsnr
arbeitsmittelhierarchy_arbeitsmittel_view_idzielgruppe
arbeitsmittelhierarchy_arbeitsmittel_view_idfassung
arbeitsmittelhierarchy_arbeitsmittel_view_dateiname
arbeitsmittelhierarchy_arbeitsmittel_view_originalname
arbeitsmittelhierarchy_arbeitsmittel_view_rechnerstand
arbeitsmittelhierarchy_arbeitsmittel_view_id
arbeitsmittelhierarchy_arbeitsmittel_view_hidx_master
arbeitsmittelhierarchy_arbeitsmittel_view_stammdatenverwaltung_
^^^


Using SA 0.3.8 and Postgres 7.4.7...what's the suggested way
to get around this issue (shortening the column names isn't an
option).


using column labels or using Column objects to get at the value.

result = select([table.c.longcolumnname.label('foo')]).execute()
result.fetchone()['foo']


No chance. As I wrote above the main mapper is a mapper for a 
self-referential where which an additional property where the table tp be 
join it the view above. So there is basically no  chance for defining an 
alias, or?


-aj



pgpc8hERmJ2qo.pgp
Description: PGP signature

[sqlalchemy] engine.base._convert_key() fails with long col names

[Andreas Jung]

From: Andreas Jung <[EMAIL PROTECTED]>
To: sqlalchemy@googlegroups.com
Subject: engine.base._convert_key() fails with long col names
Date-Sent: 14. Juni 2007 15:14:37

Hi,

I am getting the following traceback

   *  Module toolbox.product.export_bauplan2, line 96, in export_bauplan
   * Module toolbox.product.export_bauplan2, line 69, in xml_builder
   * Module toolbox.product.export_bauplan2, line 27, in xml_builder
   * Module toolbox.database.model, line 60, in tools
   * Module sqlalchemy.orm.attributes, line 53, in __get__
   * Module sqlalchemy.orm.attributes, line 208, in get
   * Module sqlalchemy.orm.strategies, line 226, in lazyload
   * Module sqlalchemy.orm.query, line 326, in select_whereclause
   * Module sqlalchemy.orm.query, line 955, in _select_statement
   * Module sqlalchemy.orm.query, line 857, in execute
   * Module sqlalchemy.orm.query, line 916, in instances
   * Module sqlalchemy.orm.mapper, line 1471, in _instance
   * Module sqlalchemy.orm.mapper, line 1508, in populate_instance
   * Module sqlalchemy.orm.interfaces, line 163, in execute
   * Module sqlalchemy.orm.strategies, line 39, in process_row
   * Module sqlalchemy.engine.base, line 1174, in __getitem__
   * Module sqlalchemy.engine.base, line 995, in _get_col
   * Module sqlalchemy.engine.base, line 932, in _convert_key

NoSuchColumnError: "Could not locate column in row for column 
'Column(u'stammdatenverwaltung_tauglich',PGBoolean())'"



This happens when _convert_key() is called with
key='arbeitsmittelhierarchy_arbeitsmittel_view.stammdatenverwaltung_tauglich'

When I look at the self._ResultProxy__keys then the key cache contains
shortend col names:


(Pdb) for x in  self._ResultProxy__keys:print x
arbeitsmittelhierarchy_arbeitsmittel_view_idautor
arbeitsmittelhierarchy_arbeitsmittel_view_idsachgebiet
arbeitsmittelhierarchy_arbeitsmittel_view_num_hre
arbeitsmittelhierarchy_arbeitsmittel_view_bemerkung
arbeitsmittelhierarchy_arbeitsmittel_view_versionsnr
arbeitsmittelhierarchy_arbeitsmittel_view_idzielgruppe
arbeitsmittelhierarchy_arbeitsmittel_view_idfassung
arbeitsmittelhierarchy_arbeitsmittel_view_dateiname
arbeitsmittelhierarchy_arbeitsmittel_view_originalname
arbeitsmittelhierarchy_arbeitsmittel_view_rechnerstand
arbeitsmittelhierarchy_arbeitsmittel_view_id
arbeitsmittelhierarchy_arbeitsmittel_view_hidx_master
arbeitsmittelhierarchy_arbeitsmittel_view_stammdatenverwaltung_
^^^


Using SA 0.3.8 and Postgres 7.4.7...what's the suggested way
to get around this issue (shortening the column names isn't an
option).

Andreas


--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgp3TcNEphdFD.pgp
Description: PGP signature

[sqlalchemy] Re: [MySQL] Checking if commit() is available

[Andreas Jung]

--On 8. Juni 2007 14:05:39 -0400 Rick Morrison <[EMAIL PROTECTED]> 
wrote:



try:
t.commit()
except:
print 'Holy cow, this database is lame'




This code is also lame :-) The code should work
for arbitrary DSNs and swallowing an exception while
committing is evil, evil, evil.

-aj

pgpZ2vlCZO15d.pgp
Description: PGP signature

[sqlalchemy] [MySQL] Checking if commit() is available

[Andreas Jung]

is there a way to determine if the underlying MySQL DB is able to perform
a commit() operation? The following code fails (likely because the 
underlying MySQL db is pretty old, V3ish).


Andreas
--


dsn = 'mysql://'
e = create_engine(dsn)
c=e.connect()
t = c.begin()
t.commit()

Traceback (most recent call last):
 File "", line 1, in ?
 File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 675, 
in commit
 File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 488, 
in _commit_impl
sqlalchemy.exceptions.SQLError: (ProgrammingError) (1064, "You have an 
error in your SQL syntax near 'commit' at line 1") None None


--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgpVDItFEqcMO.pgp
Description: PGP signature

[sqlalchemy] Re: stress tests / multithreading on SQLAlchemy 0.3.6

[Andreas Jung]

--On 27. Mai 2007 23:45:43 -0700 Alchemist <[EMAIL PROTECTED]> wrote:



@Andreas Jung
What SA configurations did you set/unset for your Zope application?


Nothing special. I am using my own z3c.sqlalchemy framework (Google,
search on Cheeseshop).




Which SA files should I modify in order for SA to support
multithreading and/or serial primary keys in postgresql database?



There is nothing to configure.

Primary keys are auto-generated using a Sequence() specification as
part of the primary key column of my model. I've never written any SA 
related code that deals with id generation - even for related tables.

This is doing SA for you automatically. You don't have to care about it.

-aj

pgpxcbsXouubj.pgp
Description: PGP signature

[sqlalchemy] Re: stress tests / multithreading on SQLAlchemy 0.3.6

[Andreas Jung]

--On 27. Mai 2007 23:27:30 -0700 Alchemist <[EMAIL PROTECTED]> wrote:



I developed my web application in TurboGears 1.0.1 (Python 2.4,
CherryPy 2.2.1 and SQLAlchemy 0.3.6) and used Postgresql 8.2 as my
backend.  Also, I am using psycopg2.

I am running stress tests (using tools such as WAPT and Web
Application Stress Tool) to test the load supported by the web
application.  When executing the stress tests I am setting 2
simultaneous threads forcing a few GET requests on the machine
(server) hosting my web app.

The results I obtained seem very bad:
1. the server seems to crash easily when more than 1 thread throws GET
requests
2. SQLAlchemy is missing some sequence (unique) ids and so some
database INSERTS are failing



I've done a lot of stress-testing with SA under Zope (also a threaded
environment) and I have not seen an issue. My configuration: Zope 2.8-2.10,
Python 2.4.4, latest SA, latest psycopg2, z3c.sqlalchemy, Postgres 7.4.6

-aj

pgprYhvQLBjEq.pgp
Description: PGP signature

[sqlalchemy] Re: cascade='all, delete-orphan' causing error about unsaved, pending instances

[Andreas Jung]

--On 17. Mai 2007 02:17:22 -0700 Glauco <[EMAIL PROTECTED]> wrote:



Try this:


don't use

files = []
files.append(vf)


but use directly parent mapper:

medium.versions[0].files.append( vf )




This won't solve my problem. I rewrote parts of the code in
the following way:


   version = Version(version=0,
 created=datetime.datetime.now(),
 creator=self.mediendb.current_user,
 comment=u'Automatischer Import aus %s' % imgdir)

   medium.versions.append(version)

   for f in files:
   medium.versions[-1].files.append(f)

This code works. I can even pass 'files' directly to the Version constructor
with just performs a setattr() operation for the key-value pairs part of 
the constructor call.


*However*

When I am interested to have only *one* Version instance using the 
following code:



   version = Version(version=0,
 created=datetime.datetime.now(),
 files=files,
 creator=self.mediendb.current_user,
 comment=u'Automatischer Import aus %s' % imgdir)

   medium.versions = [version]


I get the same error again :( This makes no sense to me.

Andreas



pgpjew1343FlW.pgp
Description: PGP signature

[sqlalchemy] Re: cascade='all, delete-orphan' causing error about unsaved, pending instances

[Andreas Jung]

--On 17. Mai 2007 10:16:17 +0200 Andreas Jung <[EMAIL PROTECTED]> wrote:




--On 16. Mai 2007 13:45:21 -0400 Michael Bayer <[EMAIL PROTECTED]>
wrote:


This code causes the trouble. There is no save() operation involved - just
a flush() operation driven by the Zope transaction integration of SA.



I also used pdb to make sure that Session.save() is not called under the 
hood. I can only see *one* save() call for the "top-level" Medium instance

as it is created during another transaction.

Andreas

pgpfiDN43wI6E.pgp
Description: PGP signature

[sqlalchemy] Re: Problem with using global metadata/connection in a threaded context

[Andreas Jung]

--On 17. Mai 2007 00:28:35 +0200 David Anderson <[EMAIL PROTECTED]> wrote:




I am guessing that somewhere in the mix the engine information is
severed from the metadata when jumping to the other thread, but I have
no idea how to fix this problem. Can anyone give me pointers on how to
get my threads talking to the database correctly?



You might look at z3c.sqlalchemy where we use a connection on a per-thread
basis.

-aj


pgpEcrRt4Mxp9.pgp
Description: PGP signature

[sqlalchemy] Re: cascade='all, delete-orphan' causing error about unsaved, pending instances

[Andreas Jung]

--On 16. Mai 2007 13:45:21 -0400 Michael Bayer <[EMAIL PROTECTED]> 
wrote:




new to the FAQ:

http://www.sqlalchemy.org/trac/wiki/
FAQ#FlushError:instancesomeinstanceisanunsavedpendinginstanceandisanorph
an




The FAQ does not explain the problem. Look at the real code:


   def importImagesFromFilesystem(self, lidx, imgdir):
   """ Import all images from a local filesystem into
   the staging area of the Medium.
   """

   # get hold of the Medium object and the persistent object
   medium = self.mediendb.getMedium(lidx)
   
   returns an existing Medium row

   Version, VersionFile = 
self.mediendb.connection.getMappers('versions', 'files')

   ^
   returns the mappers for Versions and Files

   files = []

   filelist = [list of some filenames]
   for name in filelist:

   vf = VersionFile(format_id=format_id,
width=width,
height=height,
size=size,
sha1hash=hash)


   files.append(vf)
   ^^^
   collect all VersionFile instances

   medium.versions.append(Version(version=0))
   ^
   Attach a new Version() to the medium

   medium.versions[0].files = files
   ^
   attach the collected VersionFile instances to the Version

This code causes the trouble. There is no save() operation involved - just
a flush() operation driven by the Zope transaction integration of SA.

-aj



pgpGTJidZwC4n.pgp
Description: PGP signature

[sqlalchemy] cascade='all, delete-orphan' causing error about unsaved, pending instances

[Andreas Jung]

I am building a media database using SA where the model basically maps

Medium --1:N--> Versions --1:N--> Files

My code for creating new Medium instances based on an import script 
basically is doing the following:


f = File(...)
v = Version()
v.files.append(f)
m = Medium()
m.versions.append(v)
session.save(m)

This works perfectly for the import however I have to deal with File orphans
(caused by some business logic of the media database).

To get rid of orphans I added relation(..., cascade='all, delete-orphan')
to the mapper definitions. However running the import with this change
causes the following error:


 File "lib/python/mediendb/misc/import_medien.py", line 86, in 
import_medien

   TH(import_medium, fullname)
 File 
"/local2/HRS2/HEAD.Zope28/HaufeCMS/Products/HaufeCMS/Transactions.py", line 
29, in __call__

   return TH.__call__(self, f, *args, **kw)
 File 
"/local2/HRS2/HEAD.Zope28/HaufeCMS/lib/python/Haufe/Transactions/TransactionHandler.py", 
line 94, in __call__

   else: tmgr.get().commit() # old: transaction.get().commit()
 File 
"/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py", 
line 390, in commit

   self._saveCommitishError() # This raises!
 File 
"/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py", 
line 388, in commit

   self._commitResources()
 File 
"/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py", 
line 433, in _commitResources

   rm.commit(self)
 File "build/bdist.linux-x86_64/egg/z3c/sqlalchemy/base.py", line 151, in 
commit
 File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 302, in 
flush
 File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 200, 
in flush
 File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 290, in 
_is_orphan
FlushError: instance 0xb3610eac> is an unsaved, pending instance and is an orphan (is not 
attached to any parent '_mapped_versions' instance via that classes' 
'files' attribute)


Why does this cascade rule causes this error?

Andreas

--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgpJz2ilK4tra.pgp
Description: PGP signature

[sqlalchemy] Re: Getting hold of a mapper class from within another mapper class?

[Andreas Jung]

--On 12. Mai 2007 11:22:51 -0400 Michael Bayer <[EMAIL PROTECTED]> 
wrote:



ok, you mean a "mapped" class, right ?  i.e.the History class.   i
dont exactly understand why you cant just say "History", so heres the
most generic way possible to go from 'self' to the class used on a
relation:


Basically this is a limitation (or call it a feature) of z3c.sqlalchemy 
where the complete mapper handling is handled through a wrapper which isn't 
available in the context of a mapper instance (unless you pass the wrapper 
around).




class_mapper(self.__class__).props['history'].mapper.class_


Thanks this works!

Andreas






--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group. To post to this group, send email to
sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED] For more options, visit this
group at http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---




--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgpKRN9gr0FGi.pgp
Description: PGP signature

[sqlalchemy] Getting hold of a mapper class from within another mapper class?

[Andreas Jung]

I have the following (scary) use-case:

Table 'media' has 1 : N relationship to table 'history'.

class Media(object):

  def addHistory(self, comment):
 self.history.append(History()))


Inside addHistory() I need to get hold of the mapper class History for 
'history' in order to append a new items to the media's history.


Unfortunately the complete mappers are hidden behind a wrapper and the 
mappers are only available to the application through a dedicated 
getMapper() call returning a registered mapper by name. However the wrapper 
instance

is not available to the Media mapper itself. Is there a way to get hold
of depending mapper classes - in this case the History mapper - directly
from the Media mapper class (since it knows that it depends on the History
mapper)?

Andreas

pgpXBK8hyHyTr.pgp
Description: PGP signature

[sqlalchemy] Duplicating a row?

[Andreas Jung]

Hi,

what is the common pattern to duplicate a row? I have several cases where I 
obtain a row and need to insert it with some slightly modifications back
into the table. I've written a small copy method for that but it is ugly 
code. What's the common way to do that?


Andreas

pgpOYYmiHG4WV.pgp
Description: PGP signature

[sqlalchemy] [ANN] z3c.sqlalchemy 1.0.0 released - Integration of SQLAlchemy with Zope 2/3

[Andreas Jung]

I am pleased to announce the first public release of

  z3c.sqlalchemy V 1.0.0


What is z3c.sqlalchemy?
===

z3c.sqlalchemy is yet another wrapper around SQLAlchemy. The functionality
of the wrapper is basically focused on easy integration with Zope 2 and
Zope 3. The wrapper cares about connection handling, optional transaction
integration with Zope 2/3 and wrapper management (caching, introspection).
z3c.sqlalchemy gives you flexible control over the mapper creation. Mapper
classes can be

   - auto-generated (with or without autodetection of table relationships)

   - configured by the developer


What z3c.sqlalchemy does not do and won't do:
=

   - no support for Zope 3 schemas

   - no support for Archetypes schemas


z3c.sqlachemy just tries to provide you with the basic functionalities you
need to write SQLAlchemy-based applications with Zope 2/3. Higher-level
functionalities like integration with Archetypes/Zope 3 schemas are subject
to higher-level frameworks.  z3c.sqlalchemy does not address these
frameworks.


Requirements:
=

   - Zope 2.8+, Zope 3.X

   - SQLAlchemy 0.3.6 or higher


Installation:
=

 - either download z3c.sqlalchemy from Cheeseshop

   <http://cheeseshop.python.org/pypi/z3c.sqlalchemy/>

 - or use easy_install:

   easy_install z3c.sqlalchemy


Andreas Jung

--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgpKYwQ6FWtRr.pgp
Description: PGP signature

[sqlalchemy] type_code/introspection the types of a ResultProxy

[Andreas Jung]

ResultProxy_instance.cursor.description returns the 7-tuple as defined in 
the Python DB API containing information about the result set.


The type_code (the second element of each tuple) is an integer:

(('id', 20, 5, 8, None, None, None), ('idgattung', 23, 2, 4, None, None, 
None), ('bezeichnung', 1043, 43, 255, None, None, None), ('version', 1043, 
0, 30, None, None, None), ('stand', 1114, 19, 8, None, None, None), 
('format', 1043, 3, 10, None, None, None), ('status', 23, 1, 4, None, None, 
None), ('umfang', 23, 1, 4, None, None, None), ('seiten', 23, 1, 4, None, 
None, None), ('faxabruf', 1043, 0, 50, None, None, None), ('dateiname', 
1043, 11, 255, None, None, None), ('originalname', 1043, 30, 255, None, 
None, None), ('idautor', 23, 1, 4, None, None, None), ('idfassung', 23, 1, 
4, None, None, None), ('neudat', 1114, 19, 8, None, None, None), ('aedat', 
1114, 19, 8, None, None, None), ('bemerkung', 25, 0, -1, None, None, None), 
('summary', 25, 74, -1, None, None, None))


AFAIK all type_codes are specific to the underlying Python DB module. Is 
there a way to determine the type of a column for a given resultset (as 
returned by executing a SQL statement directly) independent of the 
underlying database?


The reason why I am asking: I am building a generic database adapter for 
Zope 2 where I need map the types of the columns to some Zope internal

mapping.

Andreas

pgpl2nUfNnRQo.pgp
Description: PGP signature

[sqlalchemy] Re: Executing SQL directly inside a thread local transaction?

[Andreas Jung]

--On 30. April 2007 19:34:41 + Ian Charnas <[EMAIL PROTECTED]> wrote:



Yes!  While most *any* query can be done using sqlalchemy... and you
*should* try to use sqlalchemy because then if you change databases
you won't have to go through all your code and figure out if any of
the sql is different if you're absolutely sure you really really
need to do a bare sql query, you can do this:

from sqlalchemy import *
engine = create_engine("sqlite:///test.db")
engine.execute("insert into people(first_name, last_name)
values('ian', 'charnas')")
engine.execute("select * from people").fetchall()
[(1, 'ian', 'charnas')]

note that metadata keeps track of tables, and session keeps track of
instances of mapped classes, so to do this you don't need to use
metadata or session whatsoever.


Creating/re-using a connection on a pre-Zope-transaction basis resolved my
problem.

Andreas

pgpFgqQBpDCtx.pgp
Description: PGP signature

[sqlalchemy] Executing SQL directly inside a thread local transaction?

[Andreas Jung]

I have a scenario where we I need to execute SQL directly within a
transaction that is local to a Python thread. Is there some way to this in
SA? In this particular usecase there is no UOW involved. Or is it somehow
to pass a SQL statement somehow to the session and its underlaying 
framework?


Andreas

pgpGrJOQyXlqy.pgp
Description: PGP signature

[sqlalchemy] Informations about the connection state

[Andreas Jung]

Hi,

I am currently working on a deeper integration of SA into Zope 2.

Is it possible to determine if an engine has open connections to a database?

Is it possible to close all connections? This is necessary since a database 
adapter in Zope can be switched off in order to close connections (e.g. 
sometimes necessary when you need to drop a database without closing down
a Zope instances; keep in mind that Zope can be clustered and a Zope 
instance can have connections to multiple databases).


Andreas

pgptwtlq5634V.pgp
Description: PGP signature

[sqlalchemy] Re: sqlalchemy introspection How?

[Andreas Jung]

--On 28. April 2007 09:00:36 -0700 johnny <[EMAIL PROTECTED]> wrote:



What I was trying to get was, there isn't a python command line
option, that will create Object Mapper Classes for the tables,


What should that be?


so I
can import them within my applications.  I guess, I have to code these
every time I need to use a certain table like this:

metadata = BoundMetaData('dburi')
user_table = table('user', metadata, autoload=True)
class User(object):
pass
mapper(User, user_table)




huh You can of course write a convenience method to what ever
you like. In addition look at my module z3c.sqlalchemy.

-aj

pgpePCM3O94Kk.pgp
Description: PGP signature

[sqlalchemy] Re: sqlalchemy introspection How?

[Andreas Jung]

--On 27. April 2007 12:57:06 -0700 johnny <[EMAIL PROTECTED]> wrote:



Does this create a Object Mappers for those tables, so I can do the
following:

Lets say I have a table called "user" with columns "user_name" and
"password".

ed = User()
ed.user_name = 'Ed'
ed.password = 'edspassword'


You might read the fine SQLAlchemy tutorial first and learn about
mapper classes. sqlalchemy.org -> documentation->tutorial.

-aj

pgp1pZXteSEtn.pgp
Description: PGP signature

[sqlalchemy] Re: sqlalchemy introspection How?

[Andreas Jung]

--On 27. April 2007 07:02:39 -0700 johnny <[EMAIL PROTECTED]> wrote:



I have tables created using another orm.  I just want to know, how I
can create sqlalchemy classes based on the tables, that are already
there in the database?



Table('sometable', BoundMetaData(engine), autoload=True)

-aj

pgpaUvl3ZGxjy.pgp
Description: PGP signature

[sqlalchemy] Re: RESOLVED Re: Generated slow JOIN sql statement/specifying a left JOIN

[Andreas Jung]

I resolved this issue. Choosing a different mapper class as first arg
of the relation() call solved this issue and everything is working
perfectly.

-aj

--On 26. April 2007 08:44:22 +0200 Andreas Jung <[EMAIL PROTECTED]> wrote:




--On 25. April 2007 07:13:19 -0400 Michael Bayer
<[EMAIL PROTECTED]> wrote:


well i just noticed youre sticking an external table into your
primaryjoinbut i see nothing there that implies the usage of a
LEFT OUTER JOIN ?  are you saying that you dont need the rows from
AMH_View to be present ? how could SA possibly know that ?  (if not,
please send me the exact query being produced, and the exact query
you think should be produced)


My first posting contains all the informations:

The mapper code is:

class HierarchyNode(MappedClassBase):
 pass


mapper(HierarchyNode, HierarchyTable, properties={

..omitting the code for the self-referential table...

'tools' : relation(
HierarchyNode,
 primaryjoin=AMH_View.c.idhierarchy==HierarchyTable.c.id,
foreign_keys=[AMH_View.c.idhierarchy],
uselist=True,
 ),
}
)

Using 'tools' I am trying to obtain all rows from AMH_View
whose 'idhierarchy' col match the 'id' value of the 'current'
Hierarchy row.

SA generates:

select  from hierarchy, arbeitsmittelhierarchy_arbeitsmittel_view
where arbeitsmittelhierarchy_arbeitsmittel_view  = some_id


I would expect something like


select ...from hierarchy H left join
arbeitsmittelheirarchy_arbeitsmittel_view AMH on AMH.idhierarchy = H.id
where H.id = 

Andreas





--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgp2hi1Wsuan6.pgp
Description: PGP signature

[sqlalchemy] Re: Generated slow JOIN sql statement/specifying a left JOIN

[Andreas Jung]

--On 25. April 2007 07:13:19 -0400 Michael Bayer <[EMAIL PROTECTED]> 
wrote:


well i just noticed youre sticking an external table into your
primaryjoinbut i see nothing there that implies the usage of a
LEFT OUTER JOIN ?  are you saying that you dont need the rows from
AMH_View to be present ? how could SA possibly know that ?  (if not,
please send me the exact query being produced, and the exact query
you think should be produced)


My first posting contains all the informations:

The mapper code is:

   class HierarchyNode(MappedClassBase):
pass


   mapper(HierarchyNode, HierarchyTable, properties={

..omitting the code for the self-referential table...

   'tools' : relation(
   HierarchyNode,
primaryjoin=AMH_View.c.idhierarchy==HierarchyTable.c.id,
   foreign_keys=[AMH_View.c.idhierarchy],
   uselist=True,
),
   }
   )

Using 'tools' I am trying to obtain all rows from AMH_View
whose 'idhierarchy' col match the 'id' value of the 'current'
Hierarchy row.

SA generates:

select  from hierarchy, arbeitsmittelhierarchy_arbeitsmittel_view
where arbeitsmittelhierarchy_arbeitsmittel_view  = some_id


I would expect something like


select ...from hierarchy H left join 
arbeitsmittelheirarchy_arbeitsmittel_view AMH on AMH.idhierarchy = H.id

where H.id = 

Andreas



pgpglXzX3XZZ4.pgp
Description: PGP signature

[sqlalchemy] Re: Generated slow JOIN sql statement/specifying a left JOIN

[Andreas Jung]

--On 24. April 2007 08:54:55 -0400 Michael Bayer <[EMAIL PROTECTED]> 
wrote:





On Apr 24, 2007, at 4:27 AM, Andreas Jung wrote:



Because both tables are big the query takes forever. Using a LEFT JOIN
would definitely be faster. Is there a way to configure the 'tools'
property in a smarter way?



not within the relation(), we dont support self-referential eager
loads automatically.


hmm..Why has this to do with self-referential mappers? Wouldn't the 
generated SQL be same if it wasn't a self-referential mapper but just 
mapper with a property for a one-to-many relationship? And it's not about 
eager loading. I am perfectly fine with lazy loading. I am just saying that 
the generated SQL for lazy-loading the 'tools' property isn't perfect. A 
left join would be much faster but I don't know if it is possible to 
influence that on the configuration level?!


Andreas
--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgpeMEQbojBdP.pgp
Description: PGP signature

[sqlalchemy] Generated slow JOIN sql statement/specifying a left JOIN

[Andreas Jung]

Hi,

I have a self-referential table 'hierarchy' which has a 1:N relationship 
with a view 'arbeitsmittelhierarchy_arbeitsmittel_view'. They are defined 
as follows:


   # generate a mapper of the self-referential table 'hierarchy'
   HierarchyTable = Table('hierarchy', metadata,
  Column('parentid', Integer, 
ForeignKey('hierarchy.id')),

  autoload=True)

   AMH_View = Table('arbeitsmittelhierarchy_arbeitsmittel_view', metadata,
 autoload=True)

arbeitsmittelhierarchy_arbeitsmittel_view.idhierarchy references
hierarchy.id

The mapper code is:

   class HierarchyNode(MappedClassBase):
pass


   mapper(HierarchyNode, HierarchyTable, properties={
   'children' : relation(
   HierarchyNode,

primaryjoin=and_(HierarchyTable.c.parentid==HierarchyTable.c.id, 
HierarchyTable.c.deleted==False),

   cascade="all",
   backref=backref("parent", 
remote_side=[HierarchyTable.c.id])

),
   'parent' : relation(
   HierarchyNode,

primaryjoin=HierarchyTable.c.parentid==HierarchyTable.c.id,
   remote_side=[HierarchyTable.c.id],
   uselist=False,
),
   'tools' : relation(
   HierarchyNode,

primaryjoin=AMH_View.c.idhierarchy==HierarchyTable.c.id,
   foreign_keys=[AMH_View.c.idhierarchy],
   uselist=True,
),
   }
   )

The 'children' and 'parent' properties work like a charm however
when accessing 'tools' the generated query is slow because
it generates something like:

select  from hierarchy, arbeitsmittelhierarchy_arbeitsmittel_view
where arbeitsmittelhierarchy_arbeitsmittel_view  = some_id

Because both tables are big the query takes forever. Using a LEFT JOIN
would definitely be faster. Is there a way to configure the 'tools'
property in a smarter way?

Andreas



pgpj2HJErEYRa.pgp
Description: PGP signature

[sqlalchemy] Could not assemble any primary key columns for mapped table error for views

[Andreas Jung]

I have a view arbeitsmittelhierarchy_arbeitsmittle_view which is defined
as a left join of two tables. Trying to generate a mapper from the autoload 
view as Table() fails:


 File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 277, in 
query
 File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 1838, in 
class_mapper
 File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 321, in 
compile
 File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 336, in 
_compile_all
 File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 363, in 
_do_compile
 File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 518, in 
_compile_tables
sqlalchemy.exceptions.ArgumentError: Could not assemble any primary key 
columns for mapped table 'arbeitsmittelhierarchy_arbeitsmittel_view'


What is the workaround to deal with such views? Creating a 
PrimaryKeyConstraint for the view?


Andreas


pgpjUtzysXxlN.pgp
Description: PGP signature

[sqlalchemy] Re: ValueError: second must be in 0..59 error?

[Andreas Jung]

--On 23. April 2007 08:18:54 +0200 Andreas Jung <[EMAIL PROTECTED]> wrote:


Additional info: using SA 0.3.6, Postgres 7.4.6, psycopg 2.0.4

Andreas


  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 971,
in fetchall
ValueError: second must be in 0..59

Any idea where this is coming from?



This issue is caused by a TIMESTAMP column within our database with NULL
values. psycopg2 tried to convert them to Python datetime instances which
fails of course...any idea how to get around this issue on the SA level?

Andreas

pgpJ1K9lKlC6N.pgp
Description: PGP signature

[sqlalchemy] Re: ValueError: second must be in 0..59 error?

[Andreas Jung]

Additional info: using SA 0.3.6, Postgres 7.4.6, psycopg 2.0.4

Andreas

--On 23. April 2007 08:09:30 +0200 Andreas Jung <[EMAIL PROTECTED]> wrote:


During a select() I get the following error:

2007-04-23 08:02:24,447 INFO sqlalchemy.engine.base.Engine.0x..94
{'table': 24892275}

2007-04-23 08:02:24,480 INFO sqlalchemy.engine.base.Engine.0x..94 SELECT
arbeitsmittel.idsachgebiet AS arbeitsmittel_idsachgebiet,
arbeitsmittel.num_hre AS arbeitsmittel_num_hre, arbeitsmittel.bemerkung
AS arbeitsmittel_bemerkung, arbeitsmittel.versionsnr AS
arbeitsmittel_versionsnr, arbeitsmittel.idzielgruppe AS
arbeitsmittel_idzielgruppe, arbeitsmittel.idfassung AS
arbeitsmittel_idfassung, arbeitsmittel.dateiname AS
arbeitsmittel_dateiname, arbeitsmittel.originalname AS
arbeitsmittel_originalname, arbeitsmittel.rechnerstand AS
arbeitsmittel_rechnerstand, arbeitsmittel.id AS arbeitsmittel_id,
arbeitsmittel.hidx_master AS arbeitsmittel_hidx_master,
arbeitsmittel.stammdatenverwaltung_tauglich AS
arbeitsmittel_stammdaten_859d, arbeitsmittel.rechner_stand AS
arbeitsmittel_rechner_stand, arbeitsmittel.rechner_version AS
arbeitsmittel_rechner_version, arbeitsmittel.version AS
arbeitsmittel_version, arbeitsmittel.generator_tauglich AS
arbeitsmittel_generator__147c, arbeitsmittel.aedat AS
arbeitsmittel_aedat, arbeitsmittel.hidx AS arbeitsmittel_hidx,
arbeitsmittel.honorarfrei AS arbeitsmittel_honorarfrei,
arbeitsmittel.neudat AS arbeitsmittel_neudat, arbeitsmittel.rechtefrei AS
arbeitsmittel_rechtefrei, arbeitsmittel.status AS arbeitsmittel_status,
arbeitsmittel.signatur AS arbeitsmittel_signatur, arbeitsmittel.faxabruf
AS arbeitsmittel_faxabruf, arbeitsmittel.format AS arbeitsmittel_format,
arbeitsmittel.idgattung AS arbeitsmittel_idgattung,
arbeitsmittel.stellvertreter AS arbeitsmittel_stellvertreter,
arbeitsmittel.seiten AS arbeitsmittel_seiten, arbeitsmittel.zodb_path AS
arbeitsmittel_zodb_path, arbeitsmittel.markierung AS
arbeitsmittel_markierung, arbeitsmittel.umfang AS arbeitsmittel_umfang,
arbeitsmittel.num_pdf AS arbeitsmittel_num_pdf, arbeitsmittel.chgdat AS
arbeitsmittel_chgdat, arbeitsmittel.versionskommentar AS
arbeitsmittel_versionsko_c73d, arbeitsmittel.idautor AS
arbeitsmittel_idautor, arbeitsmittel.versionsize AS
arbeitsmittel_versionsize, arbeitsmittel.sperrgrund AS
arbeitsmittel_sperrgrund, arbeitsmittel.summary AS arbeitsmittel_summary,
arbeitsmittel.gesperrt_bis AS arbeitsmittel_gesperrt_bis,
arbeitsmittel.stand AS arbeitsmittel_stand, arbeitsmittel.bezeichnung AS
arbeitsmittel_bezeichnung, arbeitsmittel.num_rtf AS
arbeitsmittel_num_rtf, arbeitsmittel.formular_version AS
arbeitsmittel_formular_v_9df0, arbeitsmittel.benutzer AS
arbeitsmittel_benutzer
FROM arbeitsmittel ORDER BY arbeitsmittel.id
2007-04-23 08:02:24,480 INFO sqlalchemy.engine.base.Engine.0x..94 {}
Traceback (most recent call last):
  File "test.py", line 10, in ?
for row in S.query(AM).select():
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 301, in
select
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 309, in
select_whereclause
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 798, in
_select_statement
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 696, in
execute
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 750, in
instances
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 971,
in fetchall
ValueError: second must be in 0..59

Any idea where this is coming from?

Andreas






--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope & Plone development, Consulting


pgppxZ5aisCry.pgp
Description: PGP signature