[sqlalchemy] Re: Any libraries or examples of how to let users define custom fields on SA tables/objects
On Apr 18, 10:43 pm, Randy Syring ra...@rcs-comp.com wrote: So, this seems like a relatively common paradigm and I was hoping someone might already know of a library or example application for using SA to accomplish this. I am looking for something akin to Rail's acts_as_cutomizable plugin (http://github.com/trappist/ acts_as_customizable) for SA. The pattern is actually called an entity-attribute-value model. There's an example implementation with an EAV table in SQLAlchemy examples. [1] Depending on your particular circumstances you might want to use an alternate implementation. Things that might affect your decision are the distribution of values, datatype requirements, what kinds of queries are required, what features the database engine supports. I have a PostgreSQL hstore based implementation somewhere, but I can't find it right now. [1] http://www.sqlalchemy.org/trac/browser/examples/vertical -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: joining sessions / two phase commit
On Feb 9, 10:48 am, Chris Withers ch...@simplistix.co.uk wrote: Okay, but what does the transaction manager do that's different from calling commit on session1 and session2 in order? A TM should write to durable storage when a transaction group is prepared before committing any transaction. When doing crash recovery this information must be used to decide whether to commit or rollback the rest of the prepared transactions. The second transaction will remain in a prepared state (modifications not visible to other transactions, still holding any locks), even after a database crash and restart. So how do you un-f?$k it then? ;-) For MySQL you can obtain the list of prepared transactions with the XA RECOVER command. You can then use XA COMMIT or XA ROLLBACK commands as appropriate to handle them. I know that zope's transaction package aims to do just this, I wonder if anyone's used that, or anything else, with SA to solve this problem? I've only used two phase for the relatively trivial case of doing filesystem updates atomically along with metadata updates in the database. The zope transaction package doesn't seem to have any disaster recovery story, but maybe I'm missing something. Depending on your exact environment and requirements you might also find an easier way, but be very-very careful with distributed transactions. It's a really hard problem to get 100% correct in the face of arbitrary software, network and hardware failures. Ants -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: joining sessions / two phase commit
On Feb 4, 12:41 am, Chris Withers ch...@simplistix.co.uk wrote:
The problem is that session2 (and it's engine) are only created in a
small part of the code, while session1 is created in a much wider
encompassing framework. As such, there's no obvious way to get session1
to the piece of code that calls commit on session2.
(an aside: what happens here, assuming the first of your possibilities:
session1.commit()
raise RuntimeError('something goes bang')
session2.commit())
This is the reason why you need a transaction manager when using two-
phase transactions. The second transaction will remain in a prepared
state (modifications not visible to other transactions, still holding
any locks), even after a database crash and restart. The transaction
manager needs to ensure that all transactions in a group either get
committed or are rolled back. This should preferably be an automatic
process, as any prepared transactions left hanging will grind your
database to a halt pretty quickly.
Ants
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Simple example of checking for containment
With the SQL layer:
update(foo_tbl, not_(foo_tbl.c.explanation.in_(select
([turnover_tbl.c.reason],
whereclause=and_(turnover_tbl.c.datasource == 'blah',
turnover_tbl.c.turnover == 0)
))),
values={'turnover': 1})
With the ORM layer:
sess.query(Foo).filter(~Foo.explanation.in_(
sess.query(Turnover.reason).filter_by(datasource='blah',
turnover=0)
)).update({'turnover': 1})
On Aug 28, 11:47 pm, Victor Ng crankyco...@gmail.com wrote:
I can't seem to figure out how to tell sqlalchemy to do something
like :
UPDATE Foo
SET turnover = 1
WHERE EXPLANATION NOT IN(SELECT Reason FROM mbsIsTurnoverXLAT where
Datasource ='blah' and Isturnover=0)
AND DATASOURCE = 'blah'
The not in clause is the part that's tripping me up. I'm not sure
how to express that using the query api with raw Table objects.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM query with overlaps operator
This is something that could be improved in SQLAlchemy, but as a
workaround you can use the compiler extension to create the support
yourself. Here's some example code. It uses some private internals
from SQLAlchemy so you need to keep an eye on it that it doesn't break
when changing versions.
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ClauseElement
from sqlalchemy.sql.expression import _literal_as_binds, _CompareMixin
from sqlalchemy.types import NullType
class TupleClause(ClauseElement, _CompareMixin):
def __init__(self, *columns):
self.columns = [_literal_as_binds(col) for col in columns]
self.type = NullType()
@compiles(TupleClause)
def compile_tupleclause(element, compiler, **kw):
return (%s) % , .join(compiler.process(col) for col in
element.columns)
# Usage:
def overlaps(a_pair, b_pair):
return TupleClause(*a_pair).op('OVERLAPS')(TupleClause(*b_pair))
query.filter(overlaps((MappedClass.start_col, MappedClass.end_col),
(start_time, end_time)))
On Aug 21, 10:50 am, David Bolen db3l@gmail.com wrote:
Has anyone generated ORM queries using the OVERLAPS SQL operator that
reference columns in the tables in the query? I've been experimenting
with various approaches and can't seem to cleanly get the column names
(with their appropriate alias based on the rest of the query) into the
overlaps clause.
I'm basically issuing an ORM query and want to check that the date
range given by two columns in one of the objects being queried is
overlaps with a computed date range. In some cases the object whose
columns I am checking is the primary target of the query whereas in
others it's a joined class.
I found an older post from March where Michael suggested the form
somexpression.op('OVERLAPS', someotherexpression)
but I can't figure out how to apply that, and in particular what sort
of expression will produce a tuple at the SQL layer, yet still support
the op method? Namely the output needs to be of the form:
(start, stop) OVERLAPS (start, stop)
So I figured I'd try straight text, and was attempting something like:
query(MappedClass).
filter('(:c_start, :c_end) overlaps (:start, :end)').
params(c_start=MappedClass.start_col, c_end=MappedClass.end_col,
start=datetimevalue, end=datetimevalue)
but I'm having trouble identifying an appropriate value for the
c_start/c_end params to generate the column names in the resulting
SQL. The above gives can't adapt the InstrumentedAttribute references
in the params.
In the meantime I can fall back to a pure textual filter which will
have to assume how the mapped class will be aliased, but that feels
fragile and it'd be nice if I could let SQLAlchemy generate the column
names somehow. I get the feeling though that OVERLAPS is a bit
unusual in terms of the necessary support since it has non-scalar left
and right values for the operator.
Thanks for any help.
-- David
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Bug in ORM: SA 0.5.5.
On Aug 18, 2:37 am, Jon Nelson jnel...@jamponi.net wrote: Since the database has a default the insert works (although I would suggest that SA might consider grumping under circumstances like this). This is ticket #1457. (http://www.sqlalchemy.org/trac/ticket/1457) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: too eager loading
On Aug 17, 12:41 pm, matiskiva mati.sk...@mobileye.com wrote:
The problem being that i get a huge SQL statement with [effectively]
two joins, where the sequences and rects are joint to the detections.
It is very slow and creates extra data [as for every rect there is
also an occurance of sequence and so on]
The best thing to do is to perform an eager load of rects. Than in a
seperate statement an eager load of sequences and attach them to the
entities
But i do not know how to instruct SQL Alchemy to do that. Generally it
seems that the power of multiple queries and temporary tables is not
used much.
Yeah, two to-many eagerloads effectively generate a cartesian product
that can get slow pretty fast with increasing number of entities.
SQLAlchemy doesn't have enough information to figure out the correct
strategy automtically and no-one has had the time to implement an API
to create multi step queries.
You can still do that manually:
detections = query.options(eagerload('sequences')).all()
session.query(DetectionDB).filter(DetectionDB.id.in_([d.id for d in
detections])).options(eagerload('rects'))
This will populate the eagerloaded collections in two 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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: filtering eagerloaded properties?
There are two reasonable options for this, one is to select tuples of
group, user, task and join them together with appropriate conditions:
session.query(Group, User, Task).outerjoin(Group.users).outerjoin
((Task, (Task.user_id == User.id) ~Task.complete))
the other option is to create a new relation on User that encapsulates
the derived relation to incomplete tasks:
mapper(User, users_tbl, properties=dict(
incomplete_tasks = relation(Task, viewonly=True, primaryjoin=
(users_tbl.c.id == tasks_tbl.c.user_id) ~tasks_tbl.c.complete)
))
now you can eagerload that collection:
session.query(Group).options(eagerload_all('users.incomplete_tasks'))
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how sum elements with filters
Something like the following should work:
session.query(Position, func.sum(Stats.points)).join(Stats.league,
Player.position)\
.filter(Player.team == 'Some team').group_by(Position)
On Aug 11, 9:08 pm, Doron Tal doron.tal.l...@gmail.com wrote:
Hi sqlalchemy,
I'm a newbie to SA. I hope you could help me with the following problem.
Say that I have:
class Position(Base):
__tablename__ = 'Position'
id = Column('id', Integer, primary_key=True)
position = Column('name', String) #center, guard etc'
class Player(Base):
__tablename__ = 'Player'
id = Column('id', Integer, primary_key=True)
name = Column('name', String)
team = Column('team', String)
position_id = Column('position_id', Integer, ForeignKey('Position.id'))
position = relation('Position')
class Stats(Base):
__tablename__ = 'Stats'
id = Column('id', Integer, primary_key=True)
name = Column('name', String)
points = Column('points', Integer)
Player_id = Column('player_id', Integer, ForeignKey('Player.id'))
league = relation('Player')
How can I retrieve the sum of points for each of the player positions? In
other words:
Retrieving the sum of points of all of the guards, centers, etc'.
How can this query be further limited to a specific team (simple
join+filter, right?)
Thanks,
Doron.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: share objects with threads and scoped_session
On Aug 5, 10:59 pm, drakkan drakkan1...@gmail.com wrote: Seems that once I defined a scoped session as: Session = scoped_session(sessionmaker()) I can switch to a non scoped session simply calling: sess=Session() ... is this the intended behaviuor? Yes that is intended behavior. But if you are using a session across threads be aware that sessions are not thread-safe. You shouldn't do anything that modifies the session concurrently, that includes Session.add and Session.query, but also triggering lazyloads on objects or modifying their attributes. See http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-questions for some discussion. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: GIS support for SQLAlchemy
I don't have a lot of experience with geodatabases so I can't evaluate this from a practical perspective, but I do have a couple of thoughts about the general design. I'm not too keen on having the geometry functions as methods on the comparator. Having them there creates a namespace collision hazard. In my opinion it would be cleaner and closer to the OGC spec to have something akin to the generic function framework (sqlalchemy.sql.functions) that integrates into the compilation framework via sqlalchemy.ext.compiler. Looking at the db specific modules, a lot of the repetition there could be factored out by a data driven approach, having a base implementation and a per dialect dictionary that maps OGC function names to db implementation names. Also the GeometryDDL and Geometry classes could use some refactoring to support extensibility of adding new dialects. Probably via a registry that maps dialects to their geometry implementations. Also, the GeometryDDL doesn't account for the fact that dialects could be subclassed. But generally seems a useful endeavor. Best of luck to you on polishing it. On Aug 5, 5:56 pm, Sanjiv Singh singhsanj...@gmail.com wrote: Hi, I have been working on supporting spatial data types and spatial operations on SQLAlchemy as my summer of code project. The code is available athttp://bitbucket.org/sanjiv/geoalchemy/ and a demo TG2 app using it is athttp://geo.turbogears.org/. I started out by following the postgis example included in sqlalchemy package. Till now I have added support for PostGIS, MySQL and Spatialite. It would be nice to have some suggestions / criticisms before I make the first release. I am quite sure the code needs a lot of improvement. regards Sanjiv --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: GIS support for SQLAlchemy
On Aug 6, 4:09 pm, Sanjiv Singh singhsanj...@gmail.com wrote:
Also the GeometryDDL and Geometry classes could use some refactoring
to support extensibility of adding new dialects. Probably via a
registry that maps dialects to their geometry implementations. Also,
the GeometryDDL doesn't account for the fact that dialects could be
subclassed.
I am not sure I totally understand the solution you are suggesting.
Could you elaborate a bit more? Is there some code I could look at
that does something similar?
Regular refactoring of if's to a class hierarchy. Create a base
strategy class that defines the template methods for before-create,
after-create etc. and then put the DB specific implementations into db
specific subclasses that can live in the db specific modules.
(geoalchemy.postgis.PGGeometyDDL etc.) To create the objects use a
dict to map dialects to GeometryDDL implementations:
ddl_registry = {
('sqlalchemy.databases.postgres', 'PGDialect'): PGGeometryDDL,
...
}
def dialect_to_ddl_impl(dialect):
for cls in type(dialect).__mro__:
cls_id = cls.__module__, cls.__name__
if cls_id in ddl_registry:
return ddl_registry[cls_id]()
else:
raise NotImplementedError
This allows other implementers to add their class to the ddl_registry
and support other databases without changing your code. You could even
use setuptools entry points to handle the registering part. Something
like this is in sqlalchemy.engine.url.URL.get_dialect.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to filter by the result of a subquery?
On Jul 16, 2:55 am, The Devil's Programmer thedevilsprogram...@gmail.com wrote: I believe I am supposed to wrap the whole query inside another query and put the where clause on the outer query, would this be correct? I have tried messing around with this a little but haven't managed to get it to work yet. If somebody could just tell me that I'm on the right track, that would be great. First check that the database doesn't figure it out for you. A quick test on my sample database showed that on postgres this doesn't seem to be a problem (note the identical query plans): ants=# EXPLAIN ANALYZE SELECT users.id, (SELECT COUNT(*) FROM documents WHERE documents.user_id = users.id) AS doc_count FROM users WHERE (SELECT COUNT(*) FROM documents WHERE documents.user_id = users.id) 9; QUERY PLAN - Seq Scan on users (cost=0.00..236010.06 rows=7000 width=4) (actual time=0.066..190.913 rows=1883 loops=1) Filter: ((subplan) 9) SubPlan - Aggregate (cost=8.40..8.41 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=21000) - Index Scan using user_id_idx on documents (cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.005 rows=5 loops=21000) Index Cond: (user_id = $0) - Aggregate (cost=8.40..8.41 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1883) - Index Scan using user_id_idx on documents (cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.007 rows=10 loops=1883) Index Cond: (user_id = $0) Total runtime: 191.296 ms (10 rows) ants=# EXPLAIN ANALYZE SELECT * FROM (SELECT users.id, (SELECT COUNT (*) FROM documents WHERE documents.user_id = users.id) AS doc_count FROM users) AS x WHERE x.doc_count 9; QUERY PLAN - Seq Scan on users (cost=0.00..236010.06 rows=7000 width=4) (actual time=0.064..191.524 rows=1883 loops=1) Filter: ((subplan) 9) SubPlan - Aggregate (cost=8.40..8.41 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=21000) - Index Scan using user_id_idx on documents (cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.005 rows=5 loops=21000) Index Cond: (user_id = $0) - Aggregate (cost=8.40..8.41 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1883) - Index Scan using user_id_idx on documents (cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.007 rows=10 loops=1883) Index Cond: (user_id = $0) Total runtime: 191.931 ms (10 rows) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy as a FIFO buffer?
Depends on what kind of concurrency you are expecting. Simple answer is to just lock the table for queue updates. Another option is to do it with serializable transactions, but you'll have to handle serialization errors, and your database will pretty much halt and catch fire if you get heavy contention on queue removal. Another way would be to build upon PgQ from the SkyTools package, assuming Postgres is an option. This gets you high performance and high concurrency with ACID reliability, but you'll have to handle the annoying possibility of getting items multiple times. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: DB Redundancy
On May 7, 4:15 pm, goo...@venix.com goo...@venix.com wrote: MySQL has a mechanism for a database to read the log from a master database and replay the commands. This provides a loose coupling with near real-time backup of the data. Should the backup server stop or lose contact, the primary server is unaffected. When the backup server regains contact, it restarts the log processing from the point where it left off. MySQL log based replication is asynchronous. It's only useful if losing some transactions in case of a crash isn't a problem. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: DB Redundancy
On May 6, 9:07 am, Vic vctr...@gmail.com wrote:
I'm looking for a way to have my DB replicated in REAL TIME to be used
in case I lose my primary copy.
I saw that the two phase commit exist but I'm not sure if that is the
correct option. I have the feeling that it would be abusing a
mechanism purposed for correlating to separate DBs and not creating
replications.
The preferred way to replicate databases is to use ready-made database
replication tools. For postgres the easiest way to do synchronous
replication is currently pgpool-II. With mysql I'm not so sure, there
is the NDB cluster, but it has its own issues, possibly you can find
similar replication middleware for it.
If you must do the replication inside your application, then my advice
is to do it by subclassing sqlalchemy.engine.{Engine,Connection} to
handle distributing requests to multiple backends and managing
transactions across them. You still need 2 phase commits to achieve
consistency, and the corresponding separate transaction management
that goes with it (to rollback/commit prepared transactions in case of
crashes). Also to avoid inconsitencies you have to get sequence values
from one database, and cannot use volatile functions for inserts,
updates. Also, if you are doing this to get high availability, then
you need figure out, how to bring a replica up online. Don't expect
this to be anything near simple or transparent if you want to have any
kind of concurrency for write 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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: func keyword and pg scheme
On Mar 26, 5:24 pm, PD p...@dlabal.cz wrote: How can I use my own function located in scheme outside public? (PostgresSQL, SA 0.4.6) func.schema.function() --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Querying many-to-many relations
On Mar 20, 12:54 pm, Marcin Krol mrk...@gmail.com wrote: However, I have a problem doing a query at SQLA level that will select email, hwrep name and project name. The simple SQL query is straightforward: select email.Email, project.Project, hwrep.HWRep from email, project, hwrep, project_hwreps where hwrep.id = project_hwreps.HWRep_id and project.id = project_hwreps.Project_id and hwrep.Email_id = Email.id; I have tested that this query selects just what I want. However, I would like to be able to use SQLA query mech for this, but I have trouble constructing the query. Try session.query(Email.Email, Project.Project, HWRep.HWRep).join (HWRep.Projects, HWRep.Email) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: getting referenced *class* from relation
On Mar 17, 12:17 am, Alessandro Dentella san...@e-den.it wrote:
Now I want to get User class starting from Project and 'staff'.
Project.__mapper__.get_property('manager')._get_target().class_
seems to do that but the leading underscore in _get_target suggest it's
private, so I wandererd if that's the best way...
Project.manager.property.mapper.class_
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Ordering results of a WHERE x in y query by y
import sqlalchemy
def index_in(col, valuelist):
return sqlalchemy.case([(value,idx) for idx,value in enumerate
(valuelist)], value=col)
session.query(C).filter(C.someattr.in_(valuelist)).order_by(index_in
(C.someattr, valuelist))
Don't try to do this with huge lists of items.
On Feb 25, 5:53 pm, Gunnlaugur Briem gunnlau...@gmail.com wrote:
Hi all,
having a x IN y query, with y supplied as input to the query:
session.query(C).filter(C.someattr.in_(valuelist))
is there a way to tell SQLAlchemy to order the results according to
valuelist? I.e. not by the natural order of someattr, but by the
arbitrary order seen in valuelist? E.g.:
session.add(C(someattr='Abigail'))
session.add(C(someattr='Benjamin'))
session.add(C(someattr='Carl'))
valuelist = ['Benjamin', 'Abigail']
q = session.query(C).filter(C.someattr.in_(valuelist)).order_by(clever
(valuelist))
q.all()
# returns [C('Benjamin'), C('Abigail')]
The solution I can think of is to create a temporary table with
sess.execute('create temp table ...'), insert the valuelist into that
temp table along with a sequence index, join to that temporary table
and order by its index. Is there a less kludgy way?
Regards,
- Gulli
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: A question about best practices after the Object Relational Tutorial
I have written a descriptor for implementing hashed storage of passwords. I added some documentation and added it to the wiki as a recipe: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/HashProperty It doesn't depend on anything in SQLAlchemy and works with any object persistence framework. It could probably use some better null handling and error reporting, but should be usable as is. Ants --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selecting what properties of an object will be loaded
With
session.query(User).options(undefer(User.column_a),
defer(User.column_b), noload(User.column_c))
column_a will be loaded with the query, column_b will be loaded on
access and column_c will be None regardless of the value in the
database.
Ants
On Oct 16, 12:56 pm, Alex K [EMAIL PROTECTED] wrote:
Hi All,
I wonder if there is a way to set what columns of the object will be
used during this particular query, to reduce the query in case if I
need the object, but I don't need all object properties.
is something like this: session.query(User).load('column_a') possible?
session.query([...]) - won't apply, since i need mapped object.
Thanks,
Alex
--~--~-~--~~~---~--~~
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 print a constructed query with it's parameters?
This seems to come up often. I took a few minutes and threw together a
semi-robust way to do this on 0.5 series. I posted it under usage
recipes in the wiki: http://www.sqlalchemy.org/trac/wiki/DebugInlineParams
It has some flaws, but should be somewhat helpful for debugging.
Ants
On Oct 15, 2:42 pm, alex bodnaru [EMAIL PROTECTED] wrote:
hi friends,
i have a lot to learn from both approaches, but i have sadly appeared too
lazy.
there will be no problem to imagine what the sql will be, only by
looking at the
template statement (with ?'s) and at the list of parameters.
since the template is available to print (probably by __str__), i'd
onlu ask where
the bindparams list is. eventual quotes and escapes may be imagined by
the types of
the columns.
thanks in advance,
alex
On Wed, Oct 15, 2008 at 12:54, [EMAIL PROTECTED] wrote:
i have another approach, which may or may not serve you.
All those '?' are bindparams, and one can eventualy get them printed
with their names - and put names where there aren't.
that's what i needed, i guess replacing names with values would be
easy job.
the code is part of tests/convertertest.py of sqlalchemyAggregator,
http://dev.gafol.net/t/aggregator/
or
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/agg...
class T_mark( unittest.TestCase):
...
def setUp( self):
self.m = MetaData()
#hack for better visibility
def bp( self,bindparam):
if bindparam.value is not None:
return 'const('+repr(bindparam.value)+')'
k = bindparam.key
if k.startswith( Converter._pfx): #my own bindparams
k = k[ len( Converter._pfx):]
return 'BindParam('+k+')'
self.old_bp = DefaultCompiler._truncate_bindparam
DefaultCompiler._truncate_bindparam = bp
def tearDown( self):
DefaultCompiler._truncate_bindparam = self.old_bp
...
str(expression) then does things like
:const(True) AND :BindParam(oid) = movies.id
tags.tabl = :const('movies') AND tags.oid = :BindParam(oid)
there's some more stuff going on there around compatibility with SA
0.3--0.5, but that's core.
ciao
svil
On Wednesday 15 October 2008 13:33:46 King Simon-NFHD78 wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of alex bodnaru
Sent: 15 October 2008 11:00
To: SQLAlchemy
Subject: [sqlalchemy] how to print a constructed query with
it's parameters?
hello friends,
in order to debug my code, i wish to print my query sql.
it's in the fashion of
query =
table.query().filter(table.code='XL').filter(table.name.like('
%'+q+'%')
with unicode parameters.
by just printing query, i get the select with ? parameters, but
not the additional parameters list, that contains ['XL',
%q-value%]. since it doesn't presently work ok, i'd like to print
the list as well.
thanks in advance,
alex
This question comes up a lot. For example, see
http://groups.google.com/group/sqlalchemy/browse_thread/thread/a060
2ede8 18f55c7
Firstly, if you use echo=True in your call to create_engine, all
SQL will be printed to stdout. The parameters will be displayed as
a list AFTER the SQL is printed.
Eg. (fromhttp://www.sqlalchemy.org/docs/05/ormtutorial.html)
BEGIN
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
['ed', 'Ed Jones', 'edspassword']
SELECT users.id AS users_id, users.name AS users_name,
users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.name = ?
LIMIT 1 OFFSET 0
['ed']
You can control the logging more finely using the logging module -
see
http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_logging
for more details.
The problem is that SQLAlchemy doesn't ever replace those '?'
characters with the actual parameter values. Those strings are
passed directly to the DBAPI driver, along with the list of
parameter values. It is then up to the DBAPI driver how it passes
the query to the database. (This is why SQLAlchemy is fairly safe
from SQL Injection attacks).
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Random result
order by rand() doesn't scale too well unfortunately. http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/ has some better approaches. It's postgres specific but the approach should be generalizable by moving logic to client side. On Aug 23, 8:30 pm, GustaV [EMAIL PROTECTED] wrote: Ok, I wanted to know if there was a way to do it with no database specific code. Then you are right : I'll first query the result count (unknown a priori) and then use a random python generated offset. If anyone has a better idea (in 1 request only), that would be great! On 23 août, 18:34, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 23, 2008, at 12:10 PM, GustaV wrote: Hi all, Really, you do an amazing job on that stuff, it's a pleasure to work with! A short question though. It's possible to get a random order on selects in mysql using the RAND() in parameter of ORDER BY. I know similar (but different) was possible on others. What about sqlalchemy? The goal is to have only one result on the query, randomly. if the function is RAND(), you'd just say select.order_by(func.rand()). If you're looking for just the first result then you'd use limit/offset (the select() and Query() constructs both support limit() and offset() methods for this). I'm not familiar with the random function of other databases but the same techniques apply. SQLA could also could also support with a generic version of this function which calls the correct rand() function on each database, but only if very similar choices exist on most backends. --~--~-~--~~~---~--~~ 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: Exclude Autogenerated Timestamp Column
On Jan 12, 4:43 am, Rick Morrison [EMAIL PROTECTED] wrote: My experience with GUID PKs is that they almost always cause more troubles than they purport to solve, and 99% of the time a plain Integer PK will work just fine instead. The two rare exceptions are with multi-database synchronization (and even there integer PKs can work fine with an additional 'source' discriminator column) and humungo databases where overflowing a bigint col is a real fear. A bit offtopic, but I can't imagine a situation where overflowing a bigint col would be feasible. Even if you generate 1 billion rows per second, you still have about 300 years worth of keys available. Ants Aasma --~--~-~--~~~---~--~~ 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: rlike with assign_mapper?
To do this nicely ticket #767 would have to be fixed, but until then
this should work:
def rlike_match_all(entity, **kwargs):
return and_(*[getattr(entity,
key).comparator.expression_element().op('rlike')(value) for key,value
in kwargs.items()])
and use it by
Resource.select(rlike_match_all(Resource, query_dict))
Ants
So you can
On Nov 1, 8:12 pm, iain duncan [EMAIL PROTECTED] wrote:
Others gave some pointers a while back on using rlike with query
filters. I'm wondering whether there is some way to get rlike with
assign_mapper syntax?
I'm using right now
Resource.select_by( **query_dict )
where query dict is name/val pairs, I'd like to be able to make those
name/val pairs be re matches. Any tips most appreciated!
Thanks
Iain
--~--~-~--~~~---~--~~
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: non-blocking row locks?
Coincidentally I had to implement almost exactly the same thing today.
I used separate job checkout/checkin transactions, serializable
isolation level to find out when there is a collision and job checkout
time to see which jobs are currently running. By default the checkout
time is a special date way in the past instead of NULL to make it work
better with indexing. This implements a kind of optimistic locking
that throws serialization errors when two workers checkout
concurrently. I'm hoping that this won't hit performance problems,
because the checkout process is quite fast compared to the processing,
although there will be around 40 workers running concurrently in the
cluster.
The code I used is basically this: (slightly edited to omit
confidential/superfluous stuff)
def check_out_dataset_for_processing():
for retries in xrange(MAX_RETRIES):
try:
session = Session()
# This is necessary to avoid duplicate checkouts
session.connection(Dataset).execute(SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE)
# Database side CURRENT_TIMESTAMP would be a lot better
now = datetime.now()
timeout_horizon = now - timedelta(seconds=TIMEOUT)
dataset =
session.query(Dataset).filter_by(is_processed=False)\
.filter(Dataset.last_checkout
timeout_horizon).first()
if dataset:
# If found something mark it checked out
dataset.last_checkout = now
result = dataset.id, dataset.data_for_processing
else:
result = None
session.commit()
return result
except sqlalchemy.exceptions.ProgrammingError, e:
if e.orig.pgcode != '40001': # Ignore serialization
conflicts
raise
logger.error('Failed to checkout a dataset')
def store_processing_result(dataset_id, processing_result):
session = Session()
dataset = session.query(Dataset).filter_by(id=dataset_id).first()
dataset.result = processing_result
dataset.is_processed = True
session.commit()
In my case duplicate execution is only a performance issue so when a
worker times out due to crashing or just being slow that dataset is
handed to another worker. Though this code can easily be modified to
do something different in case of timeout. I don't have a separate job
table and the datasets table is millions of rows so to get good
performance I used a partial index (currently only in trunk):
Index('unprocessed_datasets_idx', datasets.c.last_checkout,
postgres_where=datasets.c.is_processed == False)
This reduces the job lookup to a simple index lookup.
--~--~-~--~~~---~--~~
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 serial(auto increment) Postgres to bigserial
On Oct 2, 10:06 am, voltron [EMAIL PROTECTED] wrote: How does one specify that the auto incrementing field should map to big serial and not serial? Use the sqlalchemy.databases.postgres.PGBigInteger datatype for that field. --~--~-~--~~~---~--~~ 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: postgres POLYGON data type
On Sep 17, 3:03 pm, [EMAIL PROTECTED] wrote:
Has anyone added support for the POLYGON data type in PostgreSQL? If
so, is there any code that can be shared?
Here's a patch implementing PGPolygon. The conversion from/to python
types should be done in dbapi IMHO (i.e. psycopg2) but this will work
until it is.
Ants
PS: any idea why the google groups web api doesn't allow attaching of
files
Index: lib/sqlalchemy/databases/postgres.py
===
--- lib/sqlalchemy/databases/postgres.py(revision 3500)
+++ lib/sqlalchemy/databases/postgres.py(working copy)
@@ -140,6 +140,28 @@
def get_col_spec(self):
return self.item_type.get_col_spec() + '[]'
+class PGPolygon(sqltypes.TypeEngine):
+def dialect_imp(self, dialect):
+return self
+
+def bind_processor(self, dialect):
+def process(value):
+if value is None:
+return value
+return '(' + ','.join('(%G,%G)' % (x,y) for x,y in value)
+ ')'
+return process
+
+def result_processor(self, dialect):
+points = re.compile(r'\(([0-9.Ee]+),([0-9.Ee]+)\)')
+def process(value):
+if value is None:
+return value
+return tuple(map(lambda v:tuple(map(float, v)),
points.findall(value)))
+return process
+
+def get_col_spec(self):
+return POLYGON
+
colspecs = {
sqltypes.Integer : PGInteger,
sqltypes.Smallinteger : PGSmallInteger,
@@ -177,6 +199,7 @@
'bytea' : PGBinary,
'boolean' : PGBoolean,
'interval':PGInterval,
+'polygon': PGPolygon,
}
def descriptor():
--~--~-~--~~~---~--~~
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: relative insert performance
On Jul 11, 9:02 pm, Justus Pendleton [EMAIL PROTECTED] wrote: I've noticed some large (10x) performance differences between sqlalchemy (no ORM) and plain DB API when using sqlite and was wondering if that is something expected even when I'm (trying to) avoid the ORM stuff or if I'm just doing something wrong. Looking at http://www.sqlalchemy.org/trac/attachment/wiki/ProfilingResults/sqla.insert.prof.png this is in the ballpark of whats expected. You can about double your performance by compiling the insert and using the result instead of the insert clause. And if that is not enough you can buffer insertable data into a list and execute many rows at a time, this should dramatically decrease SQLAlchemy overhead. Ants --~--~-~--~~~---~--~~ 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: pattern about nested transactions?
On May 23, 6:44 pm, Michael Bayer [EMAIL PROTECTED] wrote: add on top of this versioning/bitemporalism and things get tough... big words again...hold on...zero google hits for 'bitemporalism'...OK i guess that means two things happening at once I think he means the distinction between actual time and record time of temporal objects as nicely described by Fowler (http:// www.martinfowler.com/eaaDev/timeNarrative.html). I'm currently developing an application that needs that distinction and I can say that it can get pretty confusing at times. Any ideas/patterns how to transfer this nesting-of-user-transactions into DB / alchemy? I dont have a deep insight since i havent done persistent GUI stuff since the late 90s, but definitely dont use a real DB transaction for the dialog windows...you dont want any long-running DB transactions (but i think you knew that). If you know really well what you're doing then it may be simpler to offload all the lock management, transaction isolation and consistency to the database. But as a rule of thumb, long-running transactions are bad, mkay. the two options that come to mind, for the nested scenarios particularly, are using multiple Sessions / copies of the objects to keep their changes separate, and the other is to have a separate model configured for your GUI windows...probably a command pattern that encapsulates each click/state change in a distinct command instance (without modifying domain objects). when youre ready to actually write to the database, you loop through your accumulated command objects and apply each change to the appropriate domain object and then flush() as approrpriate. Actually unit of work can be regarded as a kind of command pattern. So what could work here is nested units of work. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Column to default to itself
On Apr 13, 1:47 pm, Koen Bok [EMAIL PROTECTED] wrote:
request_table = Table('request', metadata,
Column('id', Integer, primary_key=True),
Column('number', Integer, unique=True, nullable=True,
default=text('(SELECT coalesce(max(number), 0) + 1 FROM
request)')))
This seems to work well. But is this a good way to do this or can it
cause complications?
This will start to throw duplicate key errors under heavier load.
There is a window of time between insert and commit when another
insert will get the same duplicate id. It might not be a problem in
your case, but I have had to deal with a similar problem when creating
sequentally hashed database table (for provable temporal ordering and
integrity) which will see very high loads. You'll either occasionally
get duplicate values in the database or you have to serialize all
inserts. The best that can be done, is try to occasionally get holes
and assume that usually transactions succeed and catch rollbacks. That
is a lot harder.
Ants
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to [EMAIL PROTECTED]
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: IN() bug bit hard
On Apr 13, 6:40 pm, Rick [EMAIL PROTECTED] wrote: Sorry, it looks like is already being discussed. (Serve me right for reading in threaded mode.) From my SA-newbie POV, I'd love it if col.in_() compiled down to false or 0 (whatever works). col.in_() is easy to get working correctly, the problem is not_(col.in_()). In order to be consistent with regular IN behaviour this should only return rows with non null values. By example of mysql (because it has the nicest display, others work the same): mysql SELECT null IN (1,2,3), 0 IN (1,2,3), NOT null IN (1,2,3), NOT 0 IN (1,2,3)\G *** 1. row *** null IN (1,2,3): NULL 0 IN (1,2,3): 0 NOT null IN (1,2,3): NULL NOT 0 IN (1,2,3): 1 To be locally consistent with this behaviour the expression should return null when col is null, else return false. The case statement mentioned before is actually the most straightforward encoding of this behaviour. It is big only due to verbosity of SQL syntax. In Python it would be None if col is None else False. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 13, 10:16 pm, Michael Bayer [EMAIL PROTECTED] wrote: OK, first thing i wasnt sure about, is CASE supported on every DB that we support. I took a look at our CASE unit test and it appears it applies to all DBs...(although i cant verify it passes on firebird). Works on Firebird 1.5.3. I haven't verified older MySQL and PostreSQL releases, but I'll try to verify those too, or at least check changelogs for related changes. really, the work to do here is 5% the patch to the in_() method, and 95% making me a really nice unit test suite that will generatively test IN for every contingencyincluding the bind param stuff in #476 (but simpler code than whats patched there). otherwise its giong to sit in the queue for awhile since i still have a lot of other more pressing patches i havent yet had the time to test/apply. I'll try to create a test suite for it next week, I have already identified most of the test cases. Should I attach it to #476 or create a new ticket? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 12, 1:59 am, Michael Bayer [EMAIL PROTECTED] wrote: agreed, as long as we know that saying somecolumn != somecolumn is valid and produces False on all dbs (including frequent-offenders firebird and ms-sql) ? (thats how you interpreted IN (), right ?) It works (almost) ok in MSSQL-8, Sqlite-2/3, PostgreSQL 8.1/8.2, MySQL 5.0, Oracle 10g and Firebird 1.5.3. It works with literals, columns, expressions, subselect expressions, no rows subselects in all of them. It fails when the expression is a volatile function or a function with side effects (e.g. func.random().in_()). The latter two cases will work ok, if you compile it as ((CASE WHEN expr IS NULL THEN NULL ELSE 0 END) = 1) Ants --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 12, 6:37 pm, svilen [EMAIL PROTECTED] wrote: how about whatever.in() - (whatever and False) maybe problem with Null then? (null and false) gives false, so not(null and false) is true. This means that not_(col.in_()) returns all rows, while not_(col.in_(nonexistantvalue)) returns all that are not null. One other option would be to return _BooleanExpression(self._compare_self(), null(), '=', negate='IS NOT'). The only problem is and edge case when someone uses an in_ with an comparison expression like so: col.in_() == False. This should return a list of non-null entries to be consistent, but instead returns an empty list. IMHO the expected behaviour is quite clear in all cases - just use the standard SQL idioms and relational idioms, comparison with null is null and no value is in an empty list. The CASE WHEN expr IS null THEN null ELSE false END expresses it quite nicely. The comparison is there to satisfy Oracle Firebird and Mssql, which don't like plain case expressions in where. I'd say that it makes the API conceptually simpler by removing a special case at the expense of some minor (compared to some other constructs that SA ORM emits) confusion when deciphering produced SQL statements. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] 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: Column to default to itself
On Apr 13, 2:47 am, Jorge Godoy [EMAIL PROTECTED] wrote: IF you insist on doing that at your code, make the column UNIQUE (or a PK...) and write something like this pseudocode: def save_data(): def insert_data(): try: unique_column_value = get_max_from_unique_column Class(unique_column_value + 1, 'other data') except YourDBExceptionForConstraintViolation: sleep(random.random()) insert_data() The 'sleep(random.random())' is there to avoid constant clashes and to be fair to all connections that are inserting data on your table. To get an uninterrupted number sequence you need to serialize your inserts to that specific entity, for which you basically need locking. The quoted approach is optimistic locking, where you hope that no one tries to insert another row between when you use the get_max_from_unique_column and do the database commit, but are ready to retry if that expectation fails. Another way would be to use pessimistic locking, by doing the get_max_from_unique_column query with lockmode='update'. Then any other thread trying to insert another row while you're busy inserting yours will have to wait. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 11, 6:05 pm, Rick Morrison [EMAIL PROTECTED] wrote: Most of the way it works is great: col.in_(v1, v2, v2) COL IN (v1, v2, v3) col.in_(v1) COL = v1 but this one: col.in_() COL IS NULL is a mapping I don't think is right. In our case, it caused an unexpected enormous table scan that loaded over 800MB of data and set our server to swapping. I'd say this is a definite bug. The semantics of col.in_(list) should be column value equals any of the values in the list. For an empty list it should be a constant false, because no value exists in an empty list, not even an missing value (null). What makes it a bit tricky is the fact that for null values the result should actually also be null. When straight .in_() is used it doesn't really matter, but when the negation is used then it starts to matter. That's because not_(col.in_()) should be the equivalent of COL IS NOT NULL. I think the best way would be to compile it to COL != COL, it has the correct behaviour when negated as well as not negated. Diff follows: Index: lib/sqlalchemy/sql.py === --- lib/sqlalchemy/sql.py (revision 2494) +++ lib/sqlalchemy/sql.py (working copy) @@ -895,5 +895,5 @@ def in_(self, *other): if len(other) == 0: -return self.__eq__(None) +return self.__ne__(self) elif len(other) == 1 and not hasattr(other[0], '_selectable'): return self.__eq__(other[0]) Ants --~--~-~--~~~---~--~~ 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: IN() bug bit hard
On Apr 11, 11:55 pm, Michael Bayer [EMAIL PROTECTED] wrote: but why dont we want to just have it compile just as it says - to IN () on the database side ? im not sure if trying to guess what the user means here is the best approach (refuse the temptation to guess...) Because that is not valid SQL. AFAIK the only database that implements it is Sqlite and there it behaves exactly as expr != expr. It's quite probable that users will try to use empty .in_(). The options are: a) immediately raise an error + errors are easy(er) to find - invalidates sqlite behaviour b) produce IN () SQL + 1:1 mapping between python and sql - most databases will give obscure errors, possibly far away from source of error - different behaviour between databases c) do a natural extension of SQL IN syntax + makes user code simpler in non negligible amount of cases + behaves the same in all databases - some one could possibly expect different semantics Did I miss anything? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
