[sqlalchemy] Re: Type of calculated columns
On Fri, 30 Nov 2007 21:28:32 -0800 (PST) Michael Bayer [EMAIL PROTECTED] wrote: yes...add type_=DateTime to your coalesce() call - func.coalesce(date1, date2, type_=DateTime) OOC, why type_ and not simply type? That is not a Python keyword like say class, just a builtin function (er, type! :-) and thus can be reused freely... ciao, lele. -- nickname: Lele Gaifax| Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas| comincerò ad aver paura di chi mi copia. [EMAIL PROTECTED] | -- Fortunato Depero, 1929. --~--~-~--~~~---~--~~ 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] literal inside a WHERE claus
A stupid example to start with: lt = literal( True ) Event.select( and_(True, lt)) This works fine. But the reason I am using literal is because I am using some advanced postgres features inside a Where clause: lt = literal(geographical_entity.path @ ARRAY( SELECT b.path FROM (virtual_region_has_geo_entity NATURAL JOIN virtual_region) a JOIN geographical_entity b USING (geographical_entity_id) WHERE virtual_region_id=1)) Event.select( and_(True, lt)) This does not work. I get a ProgrammingError indicating that the query is wrong. When I look at the log, and copy/paste the SQL-query that was generated, and paste that into the postgresql shell itself it works. So the generated query works. But it doesn't work through SQLAlchemy. What am I missing? Note: the @ operator comes from the ltree module! --~--~-~--~~~---~--~~ 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: Serializing (pickling) a ClauseList
What do you mean with expression?
I'm sorry, I am not yet really fluent in SQLA ;)
On Nov 25, 4:50 pm, Michael Bayer [EMAIL PROTECTED] wrote:
it should pickle just fine, even if youre using func. ...
from sqlalchemy import *
cl= expression.ClauseList(expression.column('one'), func.foo())
import pickle
pickle.dumps(cl)
ccopy_reg\n_reconstructor\np0\n(csqlalchemy.sql.expression\nClauseList
\np1\nc__builtin__\nobject
\np2\nNtp3\nRp4\n(dp5\nS'operator'\np6\ncsqlalchemy.sql.operators
\ncomma_op\np7\nsS'clauses'\np8\n(lp9\ng0\n(csqlalchemy.sql.expression
\n_ColumnClause
\np10\ng2\nNtp11\nRp12\n(dp13\nS'_is_oid'\np14\nI00\nsS'primary_key'\np15\nI00\nsS'is_literal'\np16\nI00\nsS'foreign_keys'\np17\n(lp18\nsS'key'\np19\nS'one'\np20\nsS'table'\np21\nNsS'type'\np22\ng0\n(csqlalchemy.types\nNullType\np23\ng2\nNtp24\nRp25\n(dp26\nS'_impl_dict'\np27\n(dp28\nsbsS'_ColumnClause__label'\np29\nNsS'name'\np30\ng20\nsbag0\n(csqlalchemy.sql.expression\n_Function\np31\ng2\nNtp32\nRp33\n(dp34\nS'clause_expr'\np35\ng0\n(csqlalchemy.sql.expression\n_Grouping\np36\ng2\nNtp37\nRp38\n(dp39\ng22\nNsS'elem'\np40\ng0\n(g1\ng2\nNtp41\nRp42\n(dp43\ng6\ng7\nsg8\n(lp44\nsS'group'\np45\nI01\nsS'group_contents'\np46\nI01\nsbsbsg45\nI01\nsg30\nS'foo'\np47\nsS'packagenames'\np48\n(lp49\nsS'oid_column'\np50\nNsS'_bind'\np51\nNsg22\ng25\nsbasg45\nI01\nsg46\nI01\nsb.
On Nov 25, 2007, at 9:32 AM, exhuma.twn wrote:
Is it possible to somehow pickle a ClauseList object (output of the
and_ function for example)?
I would like to store the where part of a query and re-use it later.
If I simply try to pickle a clause-list, I get some errors, that the
now method cannot be pickled. Which makes sense. But I wonder why
this is in there anyhow. Where clauses are not bound to time.
I suppose then that this has to do with SLQA-internals. But is there a
way to somehow store predicates?
--~--~-~--~~~---~--~~
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: literal inside a WHERE claus
On Dec 2, 1:30 pm, exhuma.twn [EMAIL PROTECTED] wrote: A stupid example to start with: lt = literal( True ) Event.select( and_(True, lt)) This works fine. But the reason I am using literal is because I am using some advanced postgres features inside a Where clause: lt = literal(geographical_entity.path @ ARRAY( SELECT b.path FROM (virtual_region_has_geo_entity NATURAL JOIN virtual_region) a JOIN geographical_entity b USING (geographical_entity_id) WHERE virtual_region_id=1)) Event.select( and_(True, lt)) This does not work. I get a ProgrammingError indicating that the query is wrong. When I look at the log, and copy/paste the SQL-query that was generated, and paste that into the postgresql shell itself it works. So the generated query works. But it doesn't work through SQLAlchemy. What am I missing? Note: the @ operator comes from the ltree module! Aaah I see what's going on. In the postgres logs, I see that the created statement puts quotes around the literal. Essentially passing it on as a string. Is it not possible to avoid 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: literal inside a WHERE claus
On Dec 2, 2:17 pm, exhuma.twn [EMAIL PROTECTED] wrote: On Dec 2, 1:30 pm, exhuma.twn [EMAIL PROTECTED] wrote: A stupid example to start with: lt = literal( True ) Event.select( and_(True, lt)) This works fine. But the reason I am using literal is because I am using some advanced postgres features inside a Where clause: lt = literal(geographical_entity.path @ ARRAY( SELECT b.path FROM (virtual_region_has_geo_entity NATURAL JOIN virtual_region) a JOIN geographical_entity b USING (geographical_entity_id) WHERE virtual_region_id=1)) Event.select( and_(True, lt)) This does not work. I get a ProgrammingError indicating that the query is wrong. When I look at the log, and copy/paste the SQL-query that was generated, and paste that into the postgresql shell itself it works. So the generated query works. But it doesn't work through SQLAlchemy. What am I missing? Note: the @ operator comes from the ltree module! Aaah I see what's going on. In the postgres logs, I see that the created statement puts quotes around the literal. Essentially passing it on as a string. Is it not possible to avoid that? Ah... using text() instead of literal() makes it all behave nicely... :D --~--~-~--~~~---~--~~ 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: Type of calculated columns
On Dec 2, 2007, at 4:50 AM, Lele Gaifax wrote: On Fri, 30 Nov 2007 21:28:32 -0800 (PST) Michael Bayer [EMAIL PROTECTED] wrote: yes...add type_=DateTime to your coalesce() call - func.coalesce(date1, date2, type_=DateTime) OOC, why type_ and not simply type? That is not a Python keyword like say class, just a builtin function (er, type! :-) and thus can be reused freely... only because def foo(type=None), now you have a local variable named type in the body of your foo() function, which conflicts with the type builtin (a pretty widely used builtin). i will grant that TextMate syntax highlighting has played a role in this decision :) . --~--~-~--~~~---~--~~ 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] Sessionthread problem
Hello. I have problem with select from database via sqlalchemy. First select is ok, but twice select is broken a i get this error: ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id -1236382832 and this is thread id -1244775536 Session is created in class with table and mappers definitions. Every select need unique session ? Or where is the problem ? Sorry for (maybe) stupid question and my bad english .. Thanks. --~--~-~--~~~---~--~~ 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] sqlalchemy,session,query problem
Hello, i have this code for pagination on my blog app ( cherrypy powered) (code is not complete) : http://www.pastebin.cz/show/2535 I have problem, selecting/filtering by category_id is ok, but limitoffset making trouble. When i will next list with results, i get this error: ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id -1236735088 and this is thread id -1253520496 Is this problem with session or what ? Please help and sorry for my bad english. --~--~-~--~~~---~--~~ 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: sqlalchemy,session,query problem
On Dec 2, 2007, at 9:03 AM, [EMAIL PROTECTED] wrote: Hello, i have this code for pagination on my blog app ( cherrypy powered) (code is not complete) : http://www.pastebin.cz/show/2535 I have problem, selecting/filtering by category_id is ok, but limitoffset making trouble. When i will next list with results, i get this error: ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id -1236735088 and this is thread id -1253520496 Is this problem with session or what ? Please help and sorry for my bad english. what is probably happening is that you are assigning the session as an instance variable (i.e. self.session), and reusing the same Session in two different threads. Since the Session is bound to a single sqlite connection, the same SQLite connection gets used in two different threads. this is not supported by sqlite, but also the Session itself is not threadsafe either and is best used for only a single web request at a time. your best bet with a web container like cherrypy is to use scoped_session so that you have a thread-local Session object. With this pattern, you could keep the code you pasted more or less the same. see the description at : http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual . --~--~-~--~~~---~--~~ 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 strategy based on primary key
On Dec 1, 2007, at 11:10 PM, Simon Wittber wrote:
I'd like to set up a sharding strategy where shards are chosen based
on this code:
SHARD_COUNT = 5
databases = dict((i,create_engine('sqlite://shard_%s.db' % i)) for i
in xrange(SHARD_COUNT))
def shard_chooser(mapper, instance, clause=None):
return instance.primary_key_id % SHARD_COUNT
Ie, I have 5 shards, and I choose the shard based on primary_key % 5.
To do this, I need to ensure primary keys are globally unique, so I
use a function similar to the id_generator function in the shard
example here:
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/sharding/attribute_shard.py
and use that function in my Table definitions with the 'default' named
argument.
The problem with this approach, is that the shard_chooser function is
called before the primary_key is created and set on the instance, and
therefore raises a TypeError.
return instance.user_id % SHARD_COUNT
TypeError: unsupported operand type(s) for %: 'NoneType' and 'int'
Is there any way around this issue, other than explicitly calling an
id_generator function, and setting the primary key before the instance
gets passed to the shard chooser?
I would call id_generator() *within* shard_chooser() if the pk is not
available, that way shard_chooser is guaranteed to have the info that
it needs. other options include MapperExtension.before_insert() but
I dont see that as any more convenient than just within shard_chooser.
--~--~-~--~~~---~--~~
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: concurent modification
On Dec 1, 7:24 pm, imgrey [EMAIL PROTECTED] wrote:
INFO sqlalchemy.engine.threadlocal.TLEngine.0x..6c ROLLBACK
in postgresql-8.2-main.log: EET LOG: unexpected EOF on client
connection
I'll note that if the issue here is an EOF error in the PG logs, we
can look into seeing why that is; we do explicitly close all cursors
and connections when we close things, although a ROLLBACK does not
close any connections, just cursors. so without any experimentation
its not clear what the source of your PG log error is, whether its a
natural side effect of how psycopg2 works, or whatever...but if the
ROLLBACK is failing as a result, thats not something we've observed
before and certainly not with a regular session.close().
heres a short test script:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('postgres://scott:[EMAIL PROTECTED]/test',
echo=True)
meta = MetaData(engine)
t = Table('foo', meta, Column('id', Integer))
t.create(checkfirst=True)
session = create_session(bind=engine)
session.connection() # this doesnt really affect much
session.begin()
session.execute(insert(t), {'id':5})
session.commit()
session.close()
engine.dispose()
heres the PG logs:
LOG: statement: SET DATESTYLE TO 'ISO'
LOG: statement: SHOW client_encoding
LOG: statement: SHOW default_transaction_isolation
LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG: statement: select relname from pg_class c join pg_namespace n on
n.oid=c.relnamespace where n.nspname=current_schema() and
lower(relname)='foo'
LOG: statement:
CREATE TABLE foo (
id INTEGER
)
LOG: statement: END
LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG: statement: INSERT INTO foo (id) VALUES (5)
LOG: statement: END
if the program ends without the engine.dispose() at the end, then you
get the EOF error, but thats just the python program ending without
psycopg2 cleanly disposing of opened connections.
--~--~-~--~~~---~--~~
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] ANNOUNCE: Spiff Guard 1.9.0
Introduction Spiff Guard is a library for implementing access lists in Python. It provides a clean and simple API and was implemented with performance and security in mind. It was originally inspired by phpGACL (http:// phpgacl.sourceforge.net/), but features an API that is significantly cleaner and easier to use. Spiff Guard is free software and distributed under the GNU GPLv2. Changes since 1.4.0: - The bad: o This release breaks API, hard. The good: o Spiff Guard is now type-aware. That means that you can create your own types and store them in the database; Spiff Guard will create an instance of the same type when you retrieve the object later. o Sections are now obsolete - instead, just use types as a section. o Spiff Guard makes now extensive use of caching. o The API is now a lot easier to use - if that is even possible. Example Code: -- class Website(ResourceGroup): pass guard = Guard(sqlalchemy_dbh) group = ResourceGroup(My Group) user= Resource(My User) website = Website(My Website) view= Action(View) write = Action(Edit) guard.grant(group, view, website) guard.grant(user, edit, website) if guard.has_permission(user, view, website): print 'Permission granted.' Dependencies - sqlalchemy (http://www.sqlalchemy.org/) Download - Tarball: http://pypi.python.org/packages/source/S/Spiff%20Guard/Spiff%20Guard-1.9.0.tar.gz#md5=a81ca3f310899ca8471d26ffbb58a83a SVN: svn checkout http://spiff.googlecode.com/svn/trunk/libs/Guard/ Links: --- Documentation: http://spiff.googlecode.com/svn/trunk/libs/Guard/README Example: http://spiff.googlecode.com/svn/trunk/libs/Guard/tests/DBTest.py Spiff project page: http://code.google.com/p/spiff/ Mailing list: http://groups.google.com/group/spiff-devel Bug tracker: http://code.google.com/p/spiff/issues/list Browse the source: http://spiff.googlecode.com/svn/trunk/libs/Guard/ If you have any questions, please do not hesitate to ask or file a bug. -Samuel --~--~-~--~~~---~--~~ 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 strategy based on primary key
On Dec 3, 3:31 am, Michael Bayer [EMAIL PROTECTED] wrote: On Dec 1, 2007, at 11:10 PM, Simon Wittber wrote: Is there any way around this issue, other than explicitly calling an id_generator function, and setting the primary key before the instance gets passed to the shard chooser? I would call id_generator() *within* shard_chooser() if the pk is not available, that way shard_chooser is guaranteed to have the info that it needs. other options include MapperExtension.before_insert() but I dont see that as any more convenient than just within shard_chooser. Ah good idea. As a side point, I've realised that choosing a shard based on an integer PK is probably not the best idea. In this particular case, it's better to choose a shard based on the first letter of the user_name field, as that lets me group similar user_names in the same shard, and therefore continue to use a unique constraint on that same field. This sharding stuff certainly changes the way I think about relational db systems. -Sw. --~--~-~--~~~---~--~~ 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 strategy based on primary key
On Dec 2, 2007, at 6:38 PM, Simon Wittber wrote: On Dec 3, 3:31 am, Michael Bayer [EMAIL PROTECTED] wrote: On Dec 1, 2007, at 11:10 PM, Simon Wittber wrote: Is there any way around this issue, other than explicitly calling an id_generator function, and setting the primary key before the instance gets passed to the shard chooser? I would call id_generator() *within* shard_chooser() if the pk is not available, that way shard_chooser is guaranteed to have the info that it needs. other options include MapperExtension.before_insert() but I dont see that as any more convenient than just within shard_chooser. Ah good idea. As a side point, I've realised that choosing a shard based on an integer PK is probably not the best idea. In this particular case, it's better to choose a shard based on the first letter of the user_name field, as that lets me group similar user_names in the same shard, and therefore continue to use a unique constraint on that same field. This sharding stuff certainly changes the way I think about relational db systems. I cant speak authoritatively on the best sharding algorithms since i dont have much direct experience with them. The sharding API is modeled after Hibernate's existing API as well as what users were asking for, and so far we've had one other user I know of working with it. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
