[sqlalchemy] Re: Multiple encodings in my database
Am Freitag, 27. Juni 2008 01:20 schrieb Michael Bayer:
first of all, the stack trace suggests you have not set the encoding
parameter on create_engine() as it's still using UTF-8.
If you mean that a single database column may have different encodings
in different rows, you want to do your own encoding/decoding with
encoding errors set to something liberal like ignore. You also
need to use your own custom type, as below:
from sqlalchemy import types
class MyEncodedType(types.TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
assert isinstance(value, unicode)
return value.encode('latin-1')
def process_result_value(self, value, dialect):
return value.decode('latin-1', 'ignore')
then use MyEncodedType() as the type for all your columns which
contain random encoding. No convert_unicode setting should be used
on your engine as this type replaces that usage.
Perfect, that works, thanks!
Best Regards,
Hermann
--
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Divide columns with possible zeroDivisionError
I just want to add some significant fact: it really seems that the ordering is not correct only because the result of the columns division is (or at least should ) a float between 0 and 1. If I put figures that give a result to 1, it seems worling fine (ie 10 / 2 -- 5, 12 / 2 -- 6 will be correctly ordered) If this can give you hints to help me... Thanks Dominique --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Pickling/unpickling mapped class
Hi, I'm new in SQLAlchemy (and also in Python in fact), can anyone help me with this (maybe silly) problem, please (it's just experiments, so please don't say - use framework XY instead your solution): Situation: - I have SimpleXMLRPCServer up and running (based on xmlrpclib) - working ok. - I have PostgreSQL database and script with SQLAlchemy mapping to classes with autoload = True - reflecting, so there is no class definition, just: class NAME(object):pass - mapped class is nested because of relations, for example: mapper(KLASS,klasstab, properties = 'rel1':relation(SUBKLASS,lazy=False)}) - this works ok, so there is KLASS().rel1[0].attribute.something construction possible. - I need to process some data from database with SQLAlchemy help on XMLRPC server with remote procedure and send it to client. Problems: - xmlrpclib cannot marshall new-style class - SQLAlchemy cannot use old-style class So I tried to pickle the SQLAlchemy reflected class and send it by XMLRPC after pickling - this works, if there is lazy=False. But client doesn't know the structure of the reflected class, so it cannot be unpickled... I get this traceback while unpickling at client side: Traceback (most recent call last): File C:\Documents and Settings\...\xmlrpctest2\client.py, line 72, in module xx = pickle.loads(a) File c:\python25\lib\pickle.py, line 1374, in loads return Unpickler(file).load() File c:\python25\lib\pickle.py, line 858, in load dispatch[key](self) File c:\python25\lib\pickle.py, line 1217, in load_build setstate(state) File c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg \sqlalchemy\orm\collections.py, line 610, in __setstate__ self.attr = getattr(d['owner_state'].obj().__class__, d['key']).impl AttributeError: type object 'SUBKLASS' has no attribute 'attributes' Is there some easy solution of this problem? And additional question - is there some simple way how to convert (marshal, serialize) reflected sqlalchemy class to human- readable XML? Thanks for any answer Peter --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sharding id_chooser query_chooser
Lilo wrote: My understanding of this query_chooser is that it's used when you want to execute orm's sql rather than raw sql. I don't quite understand what is visit_binary function do from attribute_shard.py example. What does it mean binary.operator, binary.left, binary.right.clause and query._criterion? The sharding design behind our application is that we have a master lookup table and shards. What shard to execute sql is based on querying master lookup table. taken from sqlalchemy attribute_shard.py example: def query_chooser(query): ids = [] # here we will traverse through the query's criterion, searching # for SQL constructs. we'll grab continent names as we find them # and convert to shard ids class FindContinent(sql.ClauseVisitor): def visit_binary(self, binary): if binary.left is weather_locations.c.continent: if binary.operator == operators.eq: ids.append(shard_lookup[binary.right.value]) elif binary.operator == operators.in_op: for bind in binary.right.clauses: ids.append(shard_lookup[bind.value]) FindContinent().traverse(query._criterion) if len(ids) == 0: return ['north_america', 'asia', 'europe', 'south_america'] else: return ids thank you. Hi, (I'm probably going to get the details wrong here, but hopefully the general idea will be right) SQLAlchemy represents SQL expressions as objects, a bit like a parse tree. For example, there are classes that represent tables, joins, functions and so on. It uses a Visitor pattern (http://en.wikipedia.org/wiki/Visitor_pattern) to traverse these structures. A binary clause is an SQL expression with an operator, a left half and a right half. For example, in the clause 'id = 5', binary.left is 'id', binary.right is '5', and binary.operator is '=' (or rather, operators.eq, which is the object that represents '='). The query_chooser function above uses a Visitor to look through all the SQL expressions that make up the query that is about to be executed. Because the only overridden method is 'visit_binary', anything other than binary clauses are ignored. The method body could be written in long-hand as: If the left part of the expression is 'weather_locations.continent': If the expression is 'continent = XXX': add the shard for continent XXX Else if the expression is 'continent IN (XXX, YYY)': add the shards for XXX and YYY (operators.in_op corresponds to the 'IN' operator, and binary.right.clauses contains the right-hand-side of that expression) The fallback case (if len(ids) == 0) happens if the visitor failed to find any expressions that it could handle, in which case all the shards will be queried. I don't understand your situation well enough to know how to adapt the example. If your master lookup table is basically doing the same job as the shard_lookup dictionary in the example, then you could replace shard_lookup above with a function call that does the query. I hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] How to totally close down db connection?
Within my application (FB SQL) I use kinterbasdb.services to do backup and restore operations. No problem with the backup, but the restore is not working as SA seems to hang on to a connection. I do basically this to connect: self.engine = db.sa.create_engine(dburl, encoding='utf8', echo=False) self.Session = db.sao.sessionmaker() self.Session.configure(bind=self.engine) self.ds = self.Session() Then this to close: self.ds.close() self.Session.close_all() del self.ds del self.Session del self.engine Do backup and/or restore and then reconnect to the database. However at this point if I pause the execution in the debugger (Boa) and check with e.g. IBExpert I see that the db connection from SA is still open. I guess it has to do with the connection pool, is there anyway to tell the pool to close all connections or what other options do I have? Werner P.S. This is with SA 0.5.beta1. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Operations on instance identity criterion with Many-to-Many self-relational table
Hello! I have table wordforms and WordformMapping associated with it. Bi- directional self-relation is expressed by using association table with attributes named infinitives and forms. I am using Elixir for mapping definition so I cannot access association table with it's columns explicitly. I am trying to rewrite following query, which operates on table columns directly (InfinitivesMapping is a present association table's name): query = transaction.query( database.WordFormsMapping ) query = query.filter( database.WordFormsMapping.wordform_id == database.InfinitivesMapping.wordform_id ) query = query.filter( database.InfinitivesMapping.infinitive_id.in_( [ i.form.mapping.wordform_id for i in form.infinitives ] ) ) results = query.all( ) In other words for a given WordformMapping I want to select all WordformMappings associated with it through infinitives relation and then select all WordformMappings associated with these infinitives through forms relation. I expressed it using python code and available relations: reduce(lambda acc, cur : acc + cur.forms, form.mapping.infinitives, []) but generated SQL is far from optimal. Is there a way to express it using relational operators combination to reduce the ammount of SQL-queries? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Searching in all fields
Hi. I want to do robust algorithm for searching in tables...the simplest example is with table with no relations: stmt = u'SELECT * FROM ' stmt += str(b.clients.name) stmt += ' WHERE ' for c in b.Client.c: stmt += str(c)+' like \'%value%\' or ' clients = session.query(Client).from_statement(stmt).all() There is one big problem using the '%' sign, because python is using it to replace values in string like: 'Welcom %s to my site' % 'john' Afterwards I want to search in tables with relations, like: session.query(Client).add_entity(Address).. Can anyone help me with this problem? What is the sqlalchemy way to make multisearch ?? Thx in advance m_ax --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Searching in all fields
If you do them as double percent signs ( '%%') then the python string formatting will replace them with single percent signs. On Fri, Jun 27, 2008 at 9:12 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi. I want to do robust algorithm for searching in tables...the simplest example is with table with no relations: stmt = u'SELECT * FROM ' stmt += str(b.clients.name) stmt += ' WHERE ' for c in b.Client.c: stmt += str(c)+' like \'%value%\' or ' clients = session.query(Client).from_statement(stmt).all() There is one big problem using the '%' sign, because python is using it to replace values in string like: 'Welcom %s to my site' % 'john' Afterwards I want to search in tables with relations, like: session.query(Client).add_entity(Address).. Can anyone help me with this problem? What is the sqlalchemy way to make multisearch ?? Thx in advance m_ax --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple encodings in my database
If I am using the mysql-specific Column constructs with the charset
option, will things be automatically encoded/ decoded by SA using that
charset? Or is the charset option only used for Create Table?
On Thu, Jun 26, 2008 at 7:20 PM, Michael Bayer [EMAIL PROTECTED] wrote:
first of all, the stack trace suggests you have not set the encoding
parameter on create_engine() as it's still using UTF-8.
If you mean that a single database column may have different encodings
in different rows, you want to do your own encoding/decoding with
encoding errors set to something liberal like ignore. You also
need to use your own custom type, as below:
from sqlalchemy import types
class MyEncodedType(types.TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
assert isinstance(value, unicode)
return value.encode('latin-1')
def process_result_value(self, value, dialect):
return value.decode('latin-1', 'ignore')
then use MyEncodedType() as the type for all your columns which
contain random encoding. No convert_unicode setting should be used
on your engine as this type replaces that usage.
On Jun 26, 2008, at 6:55 PM, Hermann Himmelbauer wrote:
Hi,
I'm trying to access a database via SA, which contains varchars with
different, arbitrary encodings. Most of them are ascii or ISO-8859-2
encoded,
however, many are windows-1252 encoded and there are also some other
weird
ones.
In my engine setup, I set the encoding to latin1 and set
convert_unicode to
True, as I my application requires the database values in unicode
format.
If SA now tries to retrieve such a key, the following traceback
occurs:
--
File /home/dusty/prog/python_modules/sqlalchemy/engine/base.py,
line 1605,
in _get_col
return processor(row[index])
File /home/dusty/prog/python_modules/sqlalchemy/databases/
maxdb.py, line
112, in process
return value.decode(dialect.encoding)
File /local/home/dusty/python/Python-2.4.4/lib/python2.4/encodings/
utf_8.py,
line 16, in decode
return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 3-6:
invalid
data
-
What can I do? It's not so important that all characters are correctly
displayed, but it's vital that such improper encodings do not crash my
application. Perhaps, there's some universal encoding that is able
to deal
with such problems?
Best Regards,
Hermann
--
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to totally close down db connection?
Werner F. Bruhin wrote: Within my application (FB SQL) I use kinterbasdb.services to do backup and restore operations. No problem with the backup, but the restore is not working as SA seems to hang on to a connection. I do basically this to connect: self.engine = db.sa.create_engine(dburl, encoding='utf8', echo=False) self.Session = db.sao.sessionmaker() self.Session.configure(bind=self.engine) self.ds = self.Session() Then this to close: self.ds.close() self.Session.close_all() del self.ds del self.Session del self.engine Do backup and/or restore and then reconnect to the database. However at this point if I pause the execution in the debugger (Boa) and check with e.g. IBExpert I see that the db connection from SA is still open. I guess it has to do with the connection pool, is there anyway to tell the pool to close all connections or what other options do I have? Werner P.S. This is with SA 0.5.beta1. After searching through the doc and doing some googling and trial and error I added an engine.dispose() to the closing routine and that seems to do the trick. Is this the correct approach? New version of closing db: self.ds.close() self.Session.close_all() self.engine.dispose() # added this del self.ds del self.Session del self.engine Werner --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Searching in all fields
what is multisearch? sort of patternmatching?
http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_querying_common
query(A).filter( or_(
A.c1.startswith(),
A.c2.endswith(),
A.c3.like('%alabal%'),
...
))
u can generate the arguments of or_(...)
On Friday 27 June 2008 16:12:57 [EMAIL PROTECTED] wrote:
Hi.
I want to do robust algorithm for searching in tables...the
simplest example is with table with no relations:
stmt = u'SELECT * FROM '
stmt += str(b.clients.name)
stmt += ' WHERE '
for c in b.Client.c:
stmt += str(c)+' like \'%value%\' or '
clients = session.query(Client).from_statement(stmt).all()
There is one big problem using the '%' sign, because python is
using it to replace values in string like:
'Welcom %s to my site' % 'john'
Afterwards I want to search in tables with relations, like:
session.query(Client).add_entity(Address)..
Can anyone help me with this problem?
What is the sqlalchemy way to make multisearch ??
Thx in advance
m_ax
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Searching in all fields
1)
multisearch... I meant, that i want to create piece of code, that
will automaticly search in all columns of a table
So if I can use this function (or whatever it will be) for different
tables with no change..
for example:
I have a client(table) with address(related table one-to-one) and
persons(related table one-to-many) and make:
clients = my_function(clients_table, 'anna')
to return me all clients from database, 'anna' works in
2)
How can I generate the fields in or_() statement??
thx
On Jun 27, 3:41 pm, [EMAIL PROTECTED] wrote:
what is multisearch? sort of
patternmatching?http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_queryi...
query(A).filter( or_(
A.c1.startswith(),
A.c2.endswith(),
A.c3.like('%alabal%'),
...
))
u can generate the arguments of or_(...)
On Friday 27 June 2008 16:12:57 [EMAIL PROTECTED] wrote:
Hi.
I want to do robust algorithm for searching in tables...the
simplest example is with table with no relations:
stmt = u'SELECT * FROM '
stmt += str(b.clients.name)
stmt += ' WHERE '
for c in b.Client.c:
stmt += str(c)+' like \'%value%\' or '
clients = session.query(Client).from_statement(stmt).all()
There is one big problem using the '%' sign, because python is
using it to replace values in string like:
'Welcom %s to my site' % 'john'
Afterwards I want to search in tables with relations, like:
session.query(Client).add_entity(Address)..
Can anyone help me with this problem?
What is the sqlalchemy way to make multisearch ??
Thx in advance
m_ax
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Pickling/unpickling mapped class
On Jun 27, 2008, at 3:25 AM, Nabla wrote: Is there some easy solution of this problem? yes, the receiving application needs to have the same mapper() setup as the sender. If you use the declarative extension to setup your classes, this task is made easier. And additional question - is there some simple way how to convert (marshal, serialize) reflected sqlalchemy class to human- readable XML? we dont have an XML serializer but there might be something on Pypi for that. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Hi all
I made a web page . when i want to delete ant case then it shows this
error.
class 'sqlalchemy.exceptions.InvalidRequestError': ('The transaction
is inactive due to a rollback in a subtransaction and should be
closed', bound method Admin.deletestudy of
radspeed.controllers.Admin instance at 0x0193E490)
args = ('The transaction is inactive due to a rollback in a
subtransaction and should be closed', bound method Admin.deletestudy
of radspeed.controllers.Admin instance at 0x0193E490)
message = 'The transaction is inactive due to a rollback in a
subtransaction and should be closed' .
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Operations on instance identity criterion with Many-to-Many self-relational table
On Fri, Jun 27, 2008 at 2:44 PM, MuTPu4 [EMAIL PROTECTED] wrote:
I have table wordforms and WordformMapping associated with it. Bi-
directional self-relation is expressed by using association table with
attributes named infinitives and forms. I am using Elixir for
mapping definition so I cannot access association table with it's
columns explicitly.
FWIW, you can... Assuming the following class:
class A(Entity):
aa = ManyToMany('A')
A.aa.property.secondary gets you to the association table
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Mapping not classes but just their keys
I've got a scenario here that seems as if it would be simple to solve,
but it baffling me so far. Pointers in the right direction would be
appreciated.
I have a table of Foo objects, and FooCollection objects. In a classic
many-to-many, a collection may contain a number of Foos, and any given
Foo may belong to multiple collections. So far so simple. So we create
an intermediate table and map accordingly:
class Foo (object):
...
class FooCollection (object):
...
table_foocollections = Table ('foocollections', mymetadata, ...)
table_foo = Table ('foo', mymetadata, ...)
table_foocollections_foo = Table ('foocollection_foo',
mymetadata,
Column ('collection_id',
None,
ForeignKey ('foocollections.id'),
primary_key=True
),
Column ('foo_id',
None,
ForeignKey ('foo.id'),
primary_key=True,
),
)
mapper (FooCollection, table_foocollections,
properties={
'members': relation (FooCollection,
secondary=table_foocollections_foo,
),
},
)
So when I retrieve or update a FooCollection, members has all the
contained Foos.
But, I have a situation where I don't want FooCollection to actually
contain the actual Foos, just a list of their keys. That is, Foos and
FooCollections have to be handled separately. Obviously it will
involve more paperwork, but how can I make the members attribute
just contain the keys? Is a mapper extension required, or is there a
simpler way?
p
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping not classes but just their keys
sorry to ride your thread but just to add related question: how can a
reference be seen as its key/id and never the referred instance? turn
off some postprocessing in properties/instrumentedAttr, or not map it
at all?
On Friday 27 June 2008 17:12:21 Paul-Michael Agapow wrote:
I've got a scenario here that seems as if it would be simple to
solve, but it baffling me so far. Pointers in the right direction
would be appreciated.
I have a table of Foo objects, and FooCollection objects. In a
classic many-to-many, a collection may contain a number of Foos,
and any given Foo may belong to multiple collections. So far so
simple. So we create an intermediate table and map accordingly:
class Foo (object):
...
class FooCollection (object):
...
table_foocollections = Table ('foocollections', mymetadata,
...)
table_foo = Table ('foo', mymetadata, ...)
table_foocollections_foo = Table ('foocollection_foo',
mymetadata,
Column ('collection_id',
None,
ForeignKey ('foocollections.id'),
primary_key=True
),
Column ('foo_id',
None,
ForeignKey ('foo.id'),
primary_key=True,
),
)
mapper (FooCollection, table_foocollections,
properties={
'members': relation (FooCollection,
secondary=table_foocollections_foo,
),
},
)
So when I retrieve or update a FooCollection, members has all the
contained Foos.
But, I have a situation where I don't want FooCollection to
actually contain the actual Foos, just a list of their keys. That
is, Foos and FooCollections have to be handled separately.
Obviously it will involve more paperwork, but how can I make the
members attribute just contain the keys? Is a mapper extension
required, or is there a simpler way?
p
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Searching in all fields
On Friday 27 June 2008 16:55:22 [EMAIL PROTECTED] wrote:
1)
multisearch... I meant, that i want to create piece of code, that
will automaticly search in all columns of a table
So if I can use this function (or whatever it will be) for
different tables with no change..
for example:
I have a client(table) with address(related table one-to-one) and
persons(related table one-to-many) and make:
clients = my_function(clients_table, 'anna')
to return me all clients from database, 'anna' works in
thats quite a wide search - any column in any relation contains 'anna'
or what?
2)
How can I generate the fields in or_() statement??
for prop in class_mapper(yourclas).iterate_properties:
...
are all mapped props.
u may do some filtering, are they plain columns -
isinstance(orm.properties.ColumnProperty), or relations -
isinstance(orm.properties.PropertyLoader), and on relations, use
prop.uselist to distinguish references from collections
On Jun 27, 3:41 pm, [EMAIL PROTECTED] wrote:
what is multisearch? sort of
patternmatching?http://www.sqlalchemy.org/docs/05/ormtutorial.htm
l#datamapping_queryi...
query(A).filter( or_(
A.c1.startswith(),
A.c2.endswith(),
A.c3.like('%alabal%'),
...
))
u can generate the arguments of or_(...)
On Friday 27 June 2008 16:12:57 [EMAIL PROTECTED] wrote:
Hi.
I want to do robust algorithm for searching in tables...the
simplest example is with table with no relations:
stmt = u'SELECT * FROM '
stmt += str(b.clients.name)
stmt += ' WHERE '
for c in b.Client.c:
stmt += str(c)+' like \'%value%\' or '
clients = session.query(Client).from_statement(stmt).all()
There is one big problem using the '%' sign, because python is
using it to replace values in string like:
'Welcom %s to my site' % 'john'
Afterwards I want to search in tables with relations, like:
session.query(Client).add_entity(Address)..
Can anyone help me with this problem?
What is the sqlalchemy way to make multisearch ??
Thx in advance
m_ax
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping not classes but just their keys
On Jun 27, 2008, at 10:12 AM, Paul-Michael Agapow wrote: But, I have a situation where I don't want FooCollection to actually contain the actual Foos, just a list of their keys. That is, Foos and FooCollections have to be handled separately. Obviously it will involve more paperwork, but how can I make the members attribute just contain the keys? Is a mapper extension required, or is there a simpler way? we have a class-level extension called associationproxy thats used for this: http://www.sqlalchemy.org/docs/05/plugins.html#plugins_associationproxy --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to totally close down db connection?
On Jun 27, 2008, at 9:42 AM, Werner F. Bruhin wrote: After searching through the doc and doing some googling and trial and error I added an engine.dispose() to the closing routine and that seems to do the trick. Is this the correct approach? New version of closing db: self.ds.close() self.Session.close_all() self.engine.dispose() # added this del self.ds del self.Session del self.engine thats what its for yup --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping not classes but just their keys
My, that's fast turnaround :^) Thanks Michael. If I understand associationproxy right, it requires changes to be made to the class, which is a nuisance since one of SA's great strengths is that you don't have alter mapped classes. My classes are being used in non-DB applications, so I'd prefer to steer clear of that. Are there any other possible solutions? p On 27 Jun 2008, at 15:57, Michael Bayer wrote: On Jun 27, 2008, at 10:12 AM, Paul-Michael Agapow wrote: But, I have a situation where I don't want FooCollection to actually contain the actual Foos, just a list of their keys. That is, Foos and FooCollections have to be handled separately. Obviously it will involve more paperwork, but how can I make the members attribute just contain the keys? Is a mapper extension required, or is there a simpler way? we have a class-level extension called associationproxy thats used for this: http://www.sqlalchemy.org/docs/05/ plugins.html#plugins_associationproxy -- Dr Paul-Michael Agapow, VieDigitale / Institute of Animal Health [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pickling/unpickling mapped class
I use Gnosis Utils for a similar purpose. The API is similar to pickle, but if you need to control the details of the serialization (which fields get serialized and how), then that is quite different. Gnosis has a concept called mutators for this. Barry - Original Message From: Michael Bayer [EMAIL PROTECTED] To: sqlalchemy@googlegroups.com Sent: Friday, June 27, 2008 9:56:59 AM Subject: [sqlalchemy] Re: Pickling/unpickling mapped class On Jun 27, 2008, at 3:25 AM, Nabla wrote: Is there some easy solution of this problem? yes, the receiving application needs to have the same mapper() setup as the sender. If you use the declarative extension to setup your classes, this task is made easier. And additional question - is there some simple way how to convert (marshal, serialize) reflected sqlalchemy class to human- readable XML? we dont have an XML serializer but there might be something on Pypi for that. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping not classes but just their keys
On Jun 27, 2008, at 11:55 AM, Paul-Michael Agapow wrote: My, that's fast turnaround :^) Thanks Michael. If I understand associationproxy right, it requires changes to be made to the class, which is a nuisance since one of SA's great strengths is that you don't have alter mapped classes. My classes are being used in non-DB applications, so I'd prefer to steer clear of that. Are there any other possible solutions? well its a class behavior you're looking for so its not part of mapper(). However, the mapper is applied to a class after the fact, so theres no reason you can't add the associationproxy behavior in the exact same way, i.e. monkeypatching after the fact, i.e. class MyClass(object): ... # elsewhere mapper(MyClass, sometable, ...) MyClass.someproxy = associationproxy(...) if the only concern you have it that MyClass.someproxy = is not two characters above its current position, inside the parenthesis for mapper(), I'd suggest getting out more :). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple encodings in my database
my understanding is that mysql works a little differently here. the
column-level character sets are storage encodings only. all data to and
from the database is encoded in the database connection's configured
encoding. that can either be left as-is or converted to Unicode for you.
Bobby Impollonia wrote:
If I am using the mysql-specific Column constructs with the charset
option, will things be automatically encoded/ decoded by SA using that
charset? Or is the charset option only used for Create Table?
On Thu, Jun 26, 2008 at 7:20 PM, Michael Bayer [EMAIL PROTECTED] wrote:
first of all, the stack trace suggests you have not set the encoding
parameter on create_engine() as it's still using UTF-8.
If you mean that a single database column may have different encodings
in different rows, you want to do your own encoding/decoding with
encoding errors set to something liberal like ignore. You also
need to use your own custom type, as below:
from sqlalchemy import types
class MyEncodedType(types.TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
assert isinstance(value, unicode)
return value.encode('latin-1')
def process_result_value(self, value, dialect):
return value.decode('latin-1', 'ignore')
then use MyEncodedType() as the type for all your columns which
contain random encoding. No convert_unicode setting should be used
on your engine as this type replaces that usage.
On Jun 26, 2008, at 6:55 PM, Hermann Himmelbauer wrote:
Hi,
I'm trying to access a database via SA, which contains varchars with
different, arbitrary encodings. Most of them are ascii or ISO-8859-2
encoded,
however, many are windows-1252 encoded and there are also some other
weird
ones.
In my engine setup, I set the encoding to latin1 and set
convert_unicode to
True, as I my application requires the database values in unicode
format.
If SA now tries to retrieve such a key, the following traceback
occurs:
--
File /home/dusty/prog/python_modules/sqlalchemy/engine/base.py,
line 1605,
in _get_col
return processor(row[index])
File /home/dusty/prog/python_modules/sqlalchemy/databases/
maxdb.py, line
112, in process
return value.decode(dialect.encoding)
File /local/home/dusty/python/Python-2.4.4/lib/python2.4/encodings/
utf_8.py,
line 16, in decode
return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 3-6:
invalid
data
-
What can I do? It's not so important that all characters are correctly
displayed, but it's vital that such improper encodings do not crash my
application. Perhaps, there's some universal encoding that is able
to deal
with such problems?
Best Regards,
Hermann
--
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Pickling/unpickling mapped class
I don't understand how can remote client application have the same
mapper setup in situation where there is no direct connection to
database to reflect the tables and map to the classes.
In addition at the server side the classes are declared only like
class XYZ(object):pass and the internal structure of the class is
created from reflected (autoload) tables.
Maybe I missed something, but I think, that I need the engine
connected to database and then metadata to map the classes and get
their internal structure (from reflected (autoload) tables) - but the
client side cannot connect to database.
I don't know what means declarative extension. This is the part of
the mapping code at server side:
class KLASS(object):pass
class SUBKLASS(object):pass
class SUBSUBKLASS(object):pass
table_T = Table('table', metadata,autoload = True)
subtable_T = Table('subtable', metadata,autoload = True)
subsubtable_T = Table('subsubtable', metadata, autoload = True)
mapper(KLASS,table_T,properties={'atr':relation(SUBKLASS,lazy=False)})
mapper(SUBKLASS,subtable_T,properties={'subatr':relation(SUBSUBKLASS,lazy=False)})
mapper(SUBSUBKLASS,subsubtable_T)
So, I don't understand how to repeat this definitions at client side
if there is no database connection (no metadata etc.)
Thank you very much, btw SQLAlchemy is great, although I'm not
programmer so I have never seen any ORM before :-)
Peter
On Fri, Jun 27, 2008 at 3:56 PM, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 27, 2008, at 3:25 AM, Nabla wrote:
Is there some easy solution of this problem?
yes, the receiving application needs to have the same mapper() setup
as the sender. If you use the declarative extension to setup your
classes, this task is made easier.
And additional question - is there some simple way how to
convert (marshal, serialize) reflected sqlalchemy class to human-
readable XML?
we dont have an XML serializer but there might be something on Pypi
for that.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Pickling/unpickling mapped class
Thank you for the tip, I'll try Peter On Fri, Jun 27, 2008 at 6:09 PM, Barry Hart [EMAIL PROTECTED] wrote: I use Gnosis Utils for a similar purpose. The API is similar to pickle, but if you need to control the details of the serialization (which fields get serialized and how), then that is quite different. Gnosis has a concept called mutators for this. Barry - Original Message From: Michael Bayer [EMAIL PROTECTED] To: sqlalchemy@googlegroups.com Sent: Friday, June 27, 2008 9:56:59 AM Subject: [sqlalchemy] Re: Pickling/unpickling mapped class On Jun 27, 2008, at 3:25 AM, Nabla wrote: Is there some easy solution of this problem? yes, the receiving application needs to have the same mapper() setup as the sender. If you use the declarative extension to setup your classes, this task is made easier. And additional question - is there some simple way how to convert (marshal, serialize) reflected sqlalchemy class to human- readable XML? we dont have an XML serializer but there might be something on Pypi for that. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pickling/unpickling mapped class
u need to recreate the same graph of objects on the client side
without the db underneath?
just serialize all the objects u have then and send them over.
what is to be serialized - maybe something like
dict( (p.key, getattr(obj,key) )
for p in object_mapper(obj).iter_properties )
for each obj
On Friday 27 June 2008 19:23:57 Petr Dlabal wrote:
I don't understand how can remote client application have the same
mapper setup in situation where there is no direct connection to
database to reflect the tables and map to the classes.
In addition at the server side the classes are declared only like
class XYZ(object):pass and the internal structure of the class is
created from reflected (autoload) tables.
Maybe I missed something, but I think, that I need the engine
connected to database and then metadata to map the classes and get
their internal structure (from reflected (autoload) tables) - but
the client side cannot connect to database.
I don't know what means declarative extension. This is the part
of the mapping code at server side:
class KLASS(object):pass
class SUBKLASS(object):pass
class SUBSUBKLASS(object):pass
table_T = Table('table', metadata,autoload = True)
subtable_T = Table('subtable', metadata,autoload = True)
subsubtable_T = Table('subsubtable', metadata, autoload = True)
mapper(KLASS,table_T,properties={'atr':relation(SUBKLASS,lazy=False
)})
mapper(SUBKLASS,subtable_T,properties={'subatr':relation(SUBSUBKLAS
S,lazy=False)}) mapper(SUBSUBKLASS,subsubtable_T)
So, I don't understand how to repeat this definitions at client
side if there is no database connection (no metadata etc.)
Thank you very much, btw SQLAlchemy is great, although I'm not
programmer so I have never seen any ORM before :-)
Peter
On Fri, Jun 27, 2008 at 3:56 PM, Michael Bayer
[EMAIL PROTECTED] wrote:
On Jun 27, 2008, at 3:25 AM, Nabla wrote:
Is there some easy solution of this problem?
yes, the receiving application needs to have the same mapper()
setup as the sender. If you use the declarative extension to
setup your classes, this task is made easier.
And additional question - is there some simple way how to
convert (marshal, serialize) reflected sqlalchemy class to
human- readable XML?
we dont have an XML serializer but there might be something on
Pypi for that.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Divide columns with possible zeroDivisionError
For those who may get stuck on this kind of things in the future:
This works fine:
session.execute(SELECT * ,(CASE WHEN Mytable.ColC = :i THEN :i WHEN
Mytable.ColC :i THEN CAST(Mytable.ColB AS FLOAT) / Mytable.ColC END)
AS Calcul FROM Mytable ORDER BY Calcul, {'i':0})
Thanks
Dominique
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Beginner: printing out queries
Why is this so hard?? In SQLObject, this problem consists entirely of: print queryobj Bye all, --Buck On Jun 25, 11:23 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 25, 2008, at 2:14 PM, bukzor wrote: Thanks. Trying to do this in 0.5, it seems someone deleted the Query.compile() method without updating the rest of the code: Traceback (most recent call last): File ./test1.py, line 139, in ? try: exit(main(*argv)) File ./test1.py, line 121, in main print_query(q) File ./test1.py, line 20, in print_query print str(q) File /python-2.4.1/lib/python2.4/site-packages/ SQLAlchemy-0.5.0beta1-py2.4.egg/sqlalchemy/orm/query.py, line 1448, in __str__ return str(self.compile()) AttributeError: 'Query' object has no attribute 'compile' its been fixed in trunk. Keep an eye onhttp://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/CHANGES . beta2 is out very soon (like, this week). Reverting to 0.4, there are other problems. Statement.params is a function, so I added some ()'s, but it just returns the same query again. Statement.positiontup doesn't exist, and the string stmt doesn't have formatting to make use of python's % operator. my code example used the 0.5 APIs, but in all cases you have to get the compiled object first. This is described in the tutorials for both 0.4 and 0.5, such as athttp://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_insert . --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pickling/unpickling mapped class
On Jun 27, 2008, at 12:23 PM, Petr Dlabal wrote:
I don't understand how can remote client application have the same
mapper setup in situation where there is no direct connection to
database to reflect the tables and map to the classes.
In addition at the server side the classes are declared only like
class XYZ(object):pass and the internal structure of the class is
created from reflected (autoload) tables.
Maybe I missed something, but I think, that I need the engine
connected to database and then metadata to map the classes and get
their internal structure (from reflected (autoload) tables) - but the
client side cannot connect to database.
I don't know what means declarative extension. This is the part of
the mapping code at server side:
class KLASS(object):pass
class SUBKLASS(object):pass
class SUBSUBKLASS(object):pass
table_T = Table('table', metadata,autoload = True)
subtable_T = Table('subtable', metadata,autoload = True)
subsubtable_T = Table('subsubtable', metadata, autoload = True)
mapper(KLASS,table_T,properties={'atr':relation(SUBKLASS,lazy=False)})
mapper
(SUBKLASS
,subtable_T,properties={'subatr':relation(SUBSUBKLASS,lazy=False)})
mapper(SUBSUBKLASS,subsubtable_T)
So, I don't understand how to repeat this definitions at client side
if there is no database connection (no metadata etc.)
Thank you very much, btw SQLAlchemy is great, although I'm not
programmer so I have never seen any ORM before :-)
theres a module that defines your classes. Any application that
wishes to use these classes, including your client, must import this
module in order for Pickle to work. But when you instrument those
classes with a toolkit like SQLAlchemy, the module that defines your
classes is not enough; you must additionally define a module that
creates Table/mappers as well, and applies itself in the same way.
Since you're using autoload=True, this would imply a database
connection on both sides, but if that's not an option, you can either
not use autoload=True, or you can serialize the MetaData object itself
into a file (using Pickle again) and load it up in your client.
pickle.dumps(metadata) specifically is made to work for this reason.
One way or another, since the shape of your database is determining
the shape of your classes, that information needs to get sent over to
the client in some way, preferably in the same way that the class
definitions themselves are available (i.e. as a module level metadata
= pickle.loads(mymetadata.txt)).
The declarative extension, which may be helpful but is orthogonal to
the issue of autoload=True here, is described at
http://www.sqlalchemy.org/docs/05/plugins.html#plugins_declarative
.
Peter
On Fri, Jun 27, 2008 at 3:56 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
On Jun 27, 2008, at 3:25 AM, Nabla wrote:
Is there some easy solution of this problem?
yes, the receiving application needs to have the same mapper() setup
as the sender. If you use the declarative extension to setup your
classes, this task is made easier.
And additional question - is there some simple way how to
convert (marshal, serialize) reflected sqlalchemy class to human-
readable XML?
we dont have an XML serializer but there might be something on Pypi
for that.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Beginner: printing out queries
On Jun 27, 2008, at 1:49 PM, bukzor wrote: Why is this so hard?? In SQLObject, this problem consists entirely of: print queryobj in SQLAlchemy it is also: print queryobj how is that harder ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] whats wrong with this query?
some aliasing is missing or what? see attached file (sa 0.4) print session.query( PVOwnership).join( 'param_value').all() ... sqlalchemy.exceptions.OperationalError: (OperationalError) no such column: ParamValue.db_id u'SELECT PVOwnership.param_value_id AS PVOwnership_param_value_id, PVOwnership.which_owner AS PVOwnership_which_owner, PVOwnership._someowner_id AS PVOwnership__someowner_id, PVOwnership.db_id AS PVOwnership_db_id \n FROM PVOwnership JOIN (Model JOIN ParamValue ON ParamValue.db_id = Model.db_id) ON PVOwnership.param_value_id = ParamValue.db_id ORDER BY PVOwnership.oid' [] --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- a.py Description: application/python
[sqlalchemy] Re: whats wrong with this query?
0.4 has trouble joining *to* a joined-table mapper, as below you can see its failing to convert model join paramvalue into a subquery. what's 0.5 say ? On Jun 27, 2008, at 2:45 PM, [EMAIL PROTECTED] wrote: some aliasing is missing or what? see attached file (sa 0.4) print session.query( PVOwnership).join( 'param_value').all() ... sqlalchemy.exceptions.OperationalError: (OperationalError) no such column: ParamValue.db_id u'SELECT PVOwnership.param_value_id AS PVOwnership_param_value_id, PVOwnership.which_owner AS PVOwnership_which_owner, PVOwnership._someowner_id AS PVOwnership__someowner_id, PVOwnership.db_id AS PVOwnership_db_id \n FROM PVOwnership JOIN (Model JOIN ParamValue ON ParamValue.db_id = Model.db_id) ON PVOwnership.param_value_id = ParamValue.db_id ORDER BY PVOwnership.oid' [] a.py --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: whats wrong with this query?
On Friday 27 June 2008 21:58:22 Michael Bayer wrote: 0.4 has trouble joining *to* a joined-table mapper, as below you can see its failing to convert model join paramvalue into a subquery. what's 0.5 say ? seems to work, on the simplified case at least. On Jun 27, 2008, at 2:45 PM, [EMAIL PROTECTED] wrote: some aliasing is missing or what? see attached file (sa 0.4) print session.query( PVOwnership).join( 'param_value').all() ... sqlalchemy.exceptions.OperationalError: (OperationalError) no such column: ParamValue.db_id u'SELECT PVOwnership.param_value_id AS PVOwnership_param_value_id, PVOwnership.which_owner AS PVOwnership_which_owner, PVOwnership._someowner_id AS PVOwnership__someowner_id, PVOwnership.db_id AS PVOwnership_db_id \n FROM PVOwnership JOIN (Model JOIN ParamValue ON ParamValue.db_id = Model.db_id) ON PVOwnership.param_value_id = ParamValue.db_id ORDER BY PVOwnership.oid' [] a.py --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: whats wrong with this query?
On Jun 27, 2008, at 3:11 PM, [EMAIL PROTECTED] wrote: On Friday 27 June 2008 21:58:22 Michael Bayer wrote: 0.4 has trouble joining *to* a joined-table mapper, as below you can see its failing to convert model join paramvalue into a subquery. what's 0.5 say ? seems to work, on the simplified case at least. how many times are you going to bang your head against this wall ? :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: whats wrong with this query?
On Friday 27 June 2008 22:09:39 Michael Bayer wrote: On Jun 27, 2008, at 3:11 PM, [EMAIL PROTECTED] wrote: On Friday 27 June 2008 21:58:22 Michael Bayer wrote: 0.4 has trouble joining *to* a joined-table mapper, as below you can see its failing to convert model join paramvalue into a subquery. what's 0.5 say ? seems to work, on the simplified case at least. how many times are you going to bang your head against this wall ? :) eh-heh. once or maybe twice... btw u haven't commented at all at the multiple-aspects revisited topic? it's all about that.. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] ProgrammingError: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block
I'm getting a ProgrammingError (I've pasted the last part of the
traceback at the bottom of the page).
The error comes from my first heavy-AJAX page in Pylons (postgres
backend). If I cause too many AJAX requests at a time, or even after
doing 3 non-overlapping AJAX requests, I get the error. I wonder if
there's some sort of handle releasing I need to do... or transaction
releasing?
As a side note... I test using SQLite and put in production with
Postgres, and the page works wonderfully in SQLite -- it's only
Postgres that has the problem.
Thanks,
Matt
Here are the methods called during the AJAX request (it starts on
toggle_property(option_id, 'select')):
# session is the user's session (browser stuff)
# Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=config['pylons.g'].sa_engine))
def _single_row(self, option_id, message='', withrow=False):
opt = Session.query(Option).filter_by(id=option_id).first()
if opt:
return render('tpp_manager/option_row.mtl', option=opt,
updateid=option_%s%opt.id, message=message, withrow=withrow)
else:
if not message:
message = Doesn't exist
return render('tpp_manager/option_row.mtl',
message=message, withrow=withrow)
def _toggle_property(self, option_id, prop):
if prop == 'select':
option_id = int(option_id)
if session['tpp_select'].get(option_id, False):
del(session['tpp_select'][option_id])
else:
session['tpp_select'][option_id] = True
session.save()
return True
else:
opt =
Session.query(Option).filter_by(id=option_id).first()
if opt:
if prop == 'whitelisted':
opt.whitelisted = not opt.whitelisted
if opt.whitelisted and opt.blacklisted:
opt.blacklisted = False
elif prop == 'blacklisted':
opt.blacklisted = not opt.blacklisted
if opt.blacklisted and opt.whitelisted:
opt.whitelisted = False
elif prop == 'approved':
opt.approved = not opt.approved
else:
return False
Session.commit()
Session.refresh(opt)
else:
return False
return True
def toggle_property(self, option_id, prop):
message = ''
if not self._toggle_property(option_id, prop):
message = 'Failed to change flag.'
return self._single_row(option_id)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 719 in first
ret = list(self[0:1])
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 748 in __iter__
return self._execute_and_instances(context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 751 in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/session.py', line 535 in execute
return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 844 in execute
return Connection.executors[c](self, object, multiparams, params)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 895 in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 907 in _execute_compiled
self.__execute_raw(context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 916 in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 953 in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 935 in _handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) current transaction is aborted,
commands ignored until end of transaction block
'SELECT anon_1.tpp_options_question_id AS
anon_1_tpp_options_question_id, anon_1.tpp_options_option AS
anon_1_tpp_options_option, anon_1.tpp_options_id AS
anon_1_tpp_options_id, anon_1.tpp_options_approved AS
[sqlalchemy] Re: whats wrong with this query?
On Friday 27 June 2008 22:09:39 Michael Bayer wrote: On Jun 27, 2008, at 3:11 PM, [EMAIL PROTECTED] wrote: On Friday 27 June 2008 21:58:22 Michael Bayer wrote: 0.4 has trouble joining *to* a joined-table mapper, as below you can see its failing to convert model join paramvalue into a subquery. what's 0.5 say ? seems to work, on the simplified case at least. how many times are you going to bang your head against this wall ? :) (picking a screwdriver from pocket and.. beware,wall!) 0.5 - what's the equivalent of 0.4's someproperty.expression_element() i.e. how to get the clause-column of a (mapped) property? for both columnProp or PropLoader/*to1 or in 0.5 myclass.mycol is really a good substitute for mytable.c.mycol for any clause-expression? btw a suggestion on that 0.5 subselect: the subselect for (A join B) is auto-aliased as anonxxx, but might be more useful to be aliased as B_xxx. i have 10 of those and it is very hard to see which anonxxx is actualy which B, C, D, or F. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ProgrammingError: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block
On Jun 27, 2008, at 4:03 PM, Matt Haggard wrote: I'm getting a ProgrammingError (I've pasted the last part of the traceback at the bottom of the page). The error comes from my first heavy-AJAX page in Pylons (postgres backend). If I cause too many AJAX requests at a time, or even after doing 3 non-overlapping AJAX requests, I get the error. I wonder if there's some sort of handle releasing I need to do... or transaction releasing? As a side note... I test using SQLite and put in production with Postgres, and the page works wonderfully in SQLite -- it's only Postgres that has the problem. PG has this issue if you attempt to INSERT a row which throws an integrity constraint; a rollback() is required after this occurs. I can't see it in your code below but it would imply that such an exception is being caught and then thrown away. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ProgrammingError: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block
INSERT or UPDATE? I don't do any inserts with this code... only changing what's already there. Is an integrity constraint a PG thing, or SQLAlchemy model thing? And can I do Session.rollback() ? Thanks! On Jun 27, 2:19 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 27, 2008, at 4:03 PM, Matt Haggard wrote: I'm getting a ProgrammingError (I've pasted the last part of the traceback at the bottom of the page). The error comes from my first heavy-AJAX page in Pylons (postgres backend). If I cause too many AJAX requests at a time, or even after doing 3 non-overlapping AJAX requests, I get the error. I wonder if there's some sort of handle releasing I need to do... or transaction releasing? As a side note... I test using SQLite and put in production with Postgres, and the page works wonderfully in SQLite -- it's only Postgres that has the problem. PG has this issue if you attempt to INSERT a row which throws an integrity constraint; a rollback() is required after this occurs. I can't see it in your code below but it would imply that such an exception is being caught and then thrown away. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] relation with same class/table on both sides
Hello, I have the following scenario where I want the same class/table on both sides of a relation. person table - id - name manager table - person_id - manager_id I define both tables and a Person class, then create a relation in the person mapper like: 'manager' : relation(Person, secondary=managers, backref='direct_reports') When I do this, the 'manager' attribute doesn't show up in Person objects when I query on people. The error I get is: AttributeError: 'Person' object has no attribute 'manager' # query is something like session.query(Person).filter_by(name='Joe').one() Any hints on how I can do this. I have other basic relations working (1-1, 1-M, M-1) but they all have different classes/tables on each end of the relation. Regards, -Tom --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ProgrammingError: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block
I've fixed it by calling Session.clear() at the end of every controller action (it's in __after__()). I'm gonna go read about what that does -- right now it's just magic as far as I can tell :) Thanks again for the help, Michael On Jun 27, 3:30 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 27, 2008, at 5:03 PM, Matt Haggard wrote: INSERT or UPDATE? I don't do any inserts with this code... only changing what's already there. Is an integrity constraint a PG thing, or SQLAlchemy model thing? its a PG thing. Other things can likely cause PG to get into this state as well. But unless you're squashing exceptions, SQLA can't really let the DB get into this state without complaining loudly. And can I do Session.rollback() ? sure ! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: relation with same class/table on both sides
this relation will require you to configure primaryjoin and
secondaryjoin (it should be raising an error without them). an
example is attached.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
metadata = MetaData(engine)
class Person(object):
def __init__(self, name):
self.name = name
people = Table('people', metadata, Column('id', Integer, primary_key=True), Column('name', String(50)))
managers = Table('managers', metadata, Column('person_id', Integer, ForeignKey('people.id')),
Column('manager_id', Integer, ForeignKey('people.id')))
metadata.create_all()
mapper(Person, people, properties={
'manager':relation(Person,
secondary=managers,
primaryjoin=people.c.id==managers.c.person_id,
secondaryjoin=people.c.id==managers.c.manager_id,
uselist=False,
backref=backref('direct_reports',
primaryjoin=people.c.id==managers.c.manager_id, # the redundant 'primaryjoin'
secondaryjoin=people.c.id==managers.c.person_id,# and 'secondaryjoin' are not needed in 0.5
collection_class=set
)
)
})
sess = sessionmaker(transactional=True)()
p1 = Person('p1')
p2 = Person('p2')
p3 = Person('p3')
m1 = Person('m1')
m2 = Person('m2')
p1.manager = m1
p2.manager = m1
p3.manager = m2
m2.direct_reports.add(m1)
sess.save(m2)
sess.commit()
sess.clear()
[m1, m2] = sess.query(Person).filter(Person.name.like('m%')).order_by(Person.name).all()
[p1, p2, p3] = sess.query(Person).filter(Person.name.like('p%')).order_by(Person.name).all()
assert m2.direct_reports == set([p3, m1])
assert m1.manager == m2
assert m1.direct_reports == set([p1, p2])
On Jun 27, 2008, at 5:30 PM, Tom Hogarty wrote:
Hello,
I have the following scenario where I want the same class/table on
both sides of a relation.
person table
- id
- name
manager table
- person_id
- manager_id
I define both tables and a Person class, then create a relation in the
person mapper like:
'manager' : relation(Person, secondary=managers,
backref='direct_reports')
When I do this, the 'manager' attribute doesn't show up in Person
objects when I query on people. The error I get is:
AttributeError: 'Person' object has no attribute 'manager'
# query is something like
session.query(Person).filter_by(name='Joe').one()
Any hints on how I can do this. I have other basic relations working
(1-1, 1-M, M-1) but they all have different classes/tables on each end
of the relation.
Regards,
-Tom
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] time to remove create_session?
I replaced create_session in sqlsoup with scoped_session, which appears to have been the only reference to it. -Jonathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: time to remove create_session?
two things are needed: 1. the official way to create a Session when all you want is a Session, with no custom builder class or anything like that. It should not be grossly inconsistent with the default arguments of the current sessionmaker() call. 2. all the myriad create_session() calls in the unit tests need to call something that acts like the old create_session(), i.e. with all the newfangled things turned off by default. My vote would be for 1. just call Session(), and 2. we just put a create_session() function in the testlib. On Jun 27, 2008, at 7:43 PM, Jonathan Ellis wrote: I replaced create_session in sqlsoup with scoped_session, which appears to have been the only reference to it. -Jonathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: time to remove create_session?
Michael Bayer wrote: two things are needed: 1. the official way to create a Session when all you want is a Session, with no custom builder class or anything like that. It should not be grossly inconsistent with the default arguments of the current sessionmaker() call. 2. all the myriad create_session() calls in the unit tests need to call something that acts like the old create_session(), i.e. with all the newfangled things turned off by default. My vote would be for 1. just call Session(), and 2. we just put a create_session() function in the testlib. So creating the Session class directly is the right way to go? I switched from using that to create_session when I found out about the existence of this function, but I can switch back. Note that I really need to create sessions themselves; I cannot use sessionmaker nor scoped_session, as I have a special scoped session that needs to be able to create sessions. Regards, Martijn --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: relation with same class/table on both sides
Wow, thank you very much for the detailed example. It looks like just what I need. I look forward to trying it out very soon. -Tom On Jun 27, 6:12 pm, Michael Bayer [EMAIL PROTECTED] wrote: this relation will require you to configure primaryjoin and secondaryjoin (it should be raising an error without them). an example is attached. test.py 1KDownload On Jun 27, 2008, at 5:30 PM, Tom Hogarty wrote: Hello, I have the following scenario where I want the same class/table on both sides of a relation. person table - id - name manager table - person_id - manager_id I define both tables and a Person class, then create a relation in the person mapper like: 'manager' : relation(Person, secondary=managers, backref='direct_reports') When I do this, the 'manager' attribute doesn't show up in Person objects when I query on people. The error I get is: AttributeError: 'Person' object has no attribute 'manager' # query is something like session.query(Person).filter_by(name='Joe').one() Any hints on how I can do this. I have other basic relations working (1-1, 1-M, M-1) but they all have different classes/tables on each end of the relation. Regards, -Tom --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: time to remove create_session?
On Jun 27, 9:10 pm, Martijn Faassen [EMAIL PROTECTED] wrote: So creating the Session class directly is the right way to go? I switched from using that to create_session when I found out about the existence of this function, but I can switch back. well I think when you're calling create_session(), you're pretty much getting Session back. These days we have one official alternate subclass of Session called ShardedSession, and sessionmaker() introduces the idea of ad hoc Session subclasses. It seems that if you have your own we make a Session for you system, it would benefit most by getting at the actual Session class so that supporting alternate Session implementations and ad-hoc subclasses remain straightforward. The argument *for* create_session() is that SQLA in most cases uses facade functions to create classes, except in the case of schema elements. Though sessionmaker() (which doesn't work for you) is the official facade for Session these days. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
