[sqlalchemy] delete multiple objects
Hello, I'm using the declarative extension and was wondering (after searching the docs) how to elegantly delete multiple objects at once, preferably without loading them in first. Suppose you've got a mapped class like this: class Person(Base): __tablename__ = 'persons' stamp = sa.Column(sa.DateTime) ... Now you could delete multiple objects like this (I think): Person.__table__.delete().where(stamp somedate) or like this with loading all objects: persons = session.query(Person).filter(Person.stamp somedate) for person in persons: session.delete(person) Is there something like session.delete(Person).filter(Person.stamp somedate) ?? i.e. something similar to the session.query construct but instead of selecting performing a delete? Regards, Ids --~--~-~--~~~---~--~~ 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: delete multiple objects
query has both bulk delete and update methods http://www.sqlalchemy.org/docs/05/reference/orm/query.html#the-query-object count = session.query(Person).filter(... whatever criteria ...).delete(synchronize_session=False) On Fri, Feb 20, 2009 at 5:22 AM, Ids idsvandermo...@gmail.com wrote: Hello, I'm using the declarative extension and was wondering (after searching the docs) how to elegantly delete multiple objects at once, preferably without loading them in first. Suppose you've got a mapped class like this: class Person(Base): __tablename__ = 'persons' stamp = sa.Column(sa.DateTime) ... Now you could delete multiple objects like this (I think): Person.__table__.delete().where(stamp somedate) or like this with loading all objects: persons = session.query(Person).filter(Person.stamp somedate) for person in persons: session.delete(person) Is there something like session.delete(Person).filter(Person.stamp somedate) ?? i.e. something similar to the session.query construct but instead of selecting performing a delete? Regards, Ids --~--~-~--~~~---~--~~ 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 and scrollable cursors
On Feb 19, 7:23 pm, Rick Morrison rickmorri...@gmail.com wrote: Unfortunately, AFAICT, MS-SQL does not have an OFFSET clause (it uses TOP instead of LIMIT). How does SQLA handle this situation? For mssql2005 and higher, (those versions of mssql that support window functions using OVER, row_number(), rank(), etc.), we simulate an OFFSET by wrapping the query with an outer query, and using where row_number() = offset and row_number() offset + limit For mssql 2000 and lower, you'll be limited to use of LIMIT, and the mssql dialect will know to use TOP instead of the LIMIT syntax. For this to work in SA 0.4 and 0.5, you'll need to add the engine keyword has_window_funcs=1 to your connection string. From what I understand, SA 0.6+ will sniff out the mssql version and automatically toggle the behavior. Rick Thanks for the info - much appreciated. Frank --~--~-~--~~~---~--~~ 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 and scrollable cursors
On Feb 19, 2009, at 12:23 PM, Rick Morrison wrote: For this to work in SA 0.4 and 0.5, you'll need to add the engine keyword has_window_funcs=1 to your connection string. From what I understand, SA 0.6+ will sniff out the mssql version and automatically toggle the behavior. well, in 0.4/0.5, if you dont set the flag, and try to do LIMIT/ OFFSET, it raises an exception. So we just took the whole thing out. The effect is, if you try to do LIMIT/OFFSET and the DB doesn't support it, it raises an exception. Which is close enough to the previous behavior minus the flag ;). --~--~-~--~~~---~--~~ 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] Is there a way to get a list of bindparam() names from a pre-constructed select clause?
I have a select clause containing a subselect that may or may not have a bindparam... I haven't been able to dig up a way to get the list of bindparams in said clause. Is there a way to do this? --~--~-~--~~~---~--~~ 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: puzzling setup with ForeignKey - SOLVED
On Thu, Feb 19, 2009 at 12:57:07PM +0100, Alessandro Dentella wrote:
Hi,
in a working setup I added a ForeignKey to table 'cliente_cliente' as
follows (client_id):
class Project(Base):
__tablename__ = ticket_project
__table_args__ = {'useexisting' : True}
id = Column(Integer, primary_key=True)
date_create = Column(Date(),
server_default=text(CURRENT_TIMESTAMP), nullable=False)
date_last_modify = Column(Date(), onupdate=func.now(),
default=func.now())
status = Column(ForeignKey('ticket_status.id'),
nullable=False)
name = Column(String(30), nullable=False)
description= Column(Text, nullable=False)
date_start = Column(Date, default=func.now())
date_end = Column(Date)
client_id = Column(ForeignKey(Cliente.id), nullable=True)
this brakes session.query(Project) with the message I report below.
Of course I *did* create new field on table in the Postgres database.
Note that if I use autoload on all Tables everything works correctly, so I
tend to think I made a mistake in the definition, but I already spent some
hours w/o any better understanding.
I'll try to reproduce the error on a simpler situation but is not that
easy and I'd like to understand if the error message tells something that
could address me to the solution
thanks in advance
sandro
*:-)
Traceback (most recent call last):
File /home/misc/src/hg/py/fossati/fossati/job.py, line 86, in jobs_mask
m.reload()
File ../../sqlkit/widgets/mask/mask.py, line 67, in reload
File /misc/src/hg/py/sqlkit/sqlkit/widgets/common/sqlwidget.py, line 703,
in reload
self.records = query.all()
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line
1007, in all
return list(self)
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line
1097, in __iter__
context = self._compile_context()
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line
1569, in _compile_context
from_clause = sql_util.splice_joins(from_clause, eager_join,
eager_join.stop_on)
File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/util.py,
line 252, in splice_joins
right.onclause = adapter.traverse(right.onclause)
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/visitors.py,
line 158, in traverse
return replacement_traverse(obj, self.__traverse_options__, replace)
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/visitors.py,
line 250, in replacement_traverse
obj = clone(obj)
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/visitors.py,
line 241, in clone
newelem = replace(element)
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/visitors.py,
line 155, in replace
e = v.replace(elem)
File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/util.py,
line 473, in replace
return self._corresponding_column(col, True)
File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/util.py,
line 451, in _corresponding_column
newcol = self.selectable.corresponding_column(col,
require_embedded=require_embedded)
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py,
line 1834, in corresponding_column
if self.c.contains_column(column):
File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/util.py,
line 1358, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py,
line 1867, in _columns
self._export_columns()
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py,
line 1897, in _export_columns
self._populate_column_collection()
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py,
line 2594, in _populate_column_collection
for col in self.element.columns:
File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/util.py,
line 1358, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py,
line 1867, in _columns
self._export_columns()
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py,
line 1897, in _export_columns
self._populate_column_collection()
File
/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py,
line 3428, in _populate_column_collection
c._make_proxy(self, name=self.use_labels and c._label or None)
File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/schema.py,
line 743, in _make_proxy
[c._init_items(f) for f in fk]
File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/schema.py,
[sqlalchemy] connectionless queries with Spatial data (PostGIS)
Hello,
I need to query, insert, update and delete from already existing
PostGIS tables.
After much trial and error, nothing worked. Then I came across this
message:
http://groups.google.com/group/sqlalchemy/msg/424d9aa10d30abaf
Following that, I've confirmed that the following works:
f = func.GeomFromText('POINT(-118.0 34.0)',4326)
ins = tbl_test.insert().values(id=97, location=f)
conn = engine.connect()
trans = conn.begin()
conn.execute(ins)
trans.commit()
conn.close()
which is nice, but I like to work with connectionless or implicit
execution (as described in SQLAlchemy 0.5.3 Documentation). So I
tried:
tbl_test.insert().execute(id=42, location=f)
but it doesn't work. The error returned was:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt
'INSERT INTO tbl_test (id, location) VALUES (%(id)s, %(location)
s)' {'id': 42, 'location': sqlalchemy.sql.expression.Function at
0x830dd8c; GeomFromText}
So I suspect this has to do with how slqalchemy understands my table
schema?
I currently am declaring the table as
tbl_test = Table('tbl_test', self.metadata, Column('event_id',
Integer), Column('location', Geometry(2, 4326), nullable=False))
where Geometry is copied and pasted from some post found using Google:
class Geometry(TypeEngine):
Base PostGIS Geometry column type
name = 'GEOMETRY'
def __init__(self, dimension, srid=-1):
self.dimension = dimension
self.srid = srid
def bind_processor(self, dialect):
def process(value):
return value
return process
def result_processor(self, dialect): #not used yet
def process(value):
return value
return process
Can anyone help? According to Michael Bayer in that old post, the
connectionless statement should work.
Thank you
--~--~-~--~~~---~--~~
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: connectionless queries with Spatial data (PostGIS)
the func. call is a SQL expression which can't be bound to a bind
parameter. that has to stay in the values() clause. e.g.
table.insert().values(location=f, id=97).execute(), or
table.insert().values(location=f).execute(id=97).
if you wanted everything inside of 'f' to be bound:
table.insert().values(location=func.GeomFromText(bindparam('a'),
bindparam('b')).execute(id=97, a='POINT(2,3)', b=4326).
Also I've built an ORM extension for postgis which is incomplete but
demonstrates how to round trip and create PostGIS expressions in a
clean way, thats in the distribution in examples/postgis/postgis.py .
On Feb 20, 2009, at 4:03 PM, quaker4lyf wrote:
Hello,
I need to query, insert, update and delete from already existing
PostGIS tables.
After much trial and error, nothing worked. Then I came across this
message:
http://groups.google.com/group/sqlalchemy/msg/424d9aa10d30abaf
Following that, I've confirmed that the following works:
f = func.GeomFromText('POINT(-118.0 34.0)',4326)
ins = tbl_test.insert().values(id=97, location=f)
conn = engine.connect()
trans = conn.begin()
conn.execute(ins)
trans.commit()
conn.close()
which is nice, but I like to work with connectionless or implicit
execution (as described in SQLAlchemy 0.5.3 Documentation). So I
tried:
tbl_test.insert().execute(id=42, location=f)
but it doesn't work. The error returned was:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt
'INSERT INTO tbl_test (id, location) VALUES (%(id)s, %(location)
s)' {'id': 42, 'location': sqlalchemy.sql.expression.Function at
0x830dd8c; GeomFromText}
So I suspect this has to do with how slqalchemy understands my table
schema?
I currently am declaring the table as
tbl_test = Table('tbl_test', self.metadata, Column('event_id',
Integer), Column('location', Geometry(2, 4326), nullable=False))
where Geometry is copied and pasted from some post found using Google:
class Geometry(TypeEngine):
Base PostGIS Geometry column type
name = 'GEOMETRY'
def __init__(self, dimension, srid=-1):
self.dimension = dimension
self.srid = srid
def bind_processor(self, dialect):
def process(value):
return value
return process
def result_processor(self, dialect): #not used yet
def process(value):
return value
return process
Can anyone help? According to Michael Bayer in that old post, the
connectionless statement should work.
Thank you
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
