Re: [sqlalchemy] Casting an overlap filter as an array
On Mon, 2013-05-06 at 11:04:57 -0700, Glenn Yonemitsu wrote: In Postgresql I have a CMS entry model with a tag column varchar(20)[]. I want to do a query so a row with any of the tags will be returned. I know overlap is the method to use but I can't get the casting done correctly. Right now I am trying (and a lot of searching revealed similar solutions): query.filter(ContentEntry.tag.overlap(cast(tags, ARRAY(VARCHAR(20) But I am getting the error: AttributeError: 'SQLCompiler' object has no attribute 'visit_array'. Any variation involving ARRAY() or array() says there is no attribute visit_ARRAY or visit_array. The following hand crafted SQL works, so for now I'm trying to at least get to this point (and as I understand, overlap will use the operator): SELECT content_entry.title, content_entry.tag FROM content_entry WHERE content_entry.tag cast(array['foo', 'bar'] as varchar(20)[]); How can I get this casted correctly? This is strange, casting to varchar(20)[] works here without problems. Given the following model: class Test(Base): __tablename__ = 'test_arr' id = Column(Integer, primary_key=True) data = Column(ARRAY(VARCHAR(20))) The query session.query(Test).\ filter(Test.data.overlap(cast(['foo'], ARRAY(VARCHAR(20) is compiled to SELECT test_arr.id AS test_arr_id, test_arr.data AS test_arr_data FROM test_arr WHERE test_arr.data CAST(%(param_1)s AS VARCHAR(20)[]) I'm using PostgreSQL 9.2.4, psycopg2 2.5 and SQLAlchemy 0.8.1, though somehow I doubt it has anything to do with different version numbers of any of those. On a side note, if I were you, I wouldn't bother with varchar(20)[] and use text[] instead. Internally varchar and text are stored the same way, the only difference is the former sets the length constraint if it is provided. -- Audrius Kažukauskas http://neutrino.lt/ pgp2RVAZ6Mfbz.pgp Description: PGP signature
Re: [sqlalchemy] ORM Join with Group By
Hi, Thijs,
On Thu, 2013-04-04 at 21:36:56 +0200, Thijs Engels wrote:
Assume a database with two tables; sessions and events. Sessions has a
primary key id and some additional information. Events has a primary key
which consists of session_id (foreign key to Sessions) and event_id
which is using a sequence. This table contains an additional column
which contains the value I am interested in.
SESSIONS
- SESSION_ID [PK]
- DATE
EVENTS
- SESSION_ID [PK + FK]
- EVENT_ID [PK]
- CODE
Each session has multiple events, what I am interested in is the code of
the last event per session (event with highest event id).
I managed to come up with the SQL which will do this:
SELECT
SSN.SESSION_ID,
SSN.DATE
FROM
SESSIONS SSN
LEFT JOIN
(SELECT
EVT.SESSION_ID,
EVT.CODE
FROM
EVENTS EVT
INNER JOIN
(SELECT
SESSION_ID,
MAX(EVENT_ID) AS EVENT_ID
FROM
EVENTS
GROUP BY
SESSION_ID) LAST_EVENT
ON
AEL.SESSION_ID = LAST_EVENT.SESSION_ID AND
AEL.EVENT_ID = LAST_EVENT.EVENT_ID) EVENT
ON
EVENT.SESSION_ID = SSN.SESSION_ID
ORDER BY
SSN.SESSION_ID;
Hence initially getting the max event_id, which is then joined with the
events table to extract the code, which in the end is join with the
sessions table.
I tried to transform this query to SQLAlchemy, but am getting stuck.
Looking at the various examples as found in the documentation I gathered
that I should start with the creation of a sub-query:
stmt = oracle.query(Event.session_id,
func.max(Event.id).label(last_event)).group_by(Event.session_id).subquery()
But I can not figure out how to use this sub-query in the two remaining
joins...
You were on correct path, what you're missing is the fact that result of
.subquery() is an object which can be used like a Table in another
query. So to access last_event in your stmt subquery, you would write
it like this:
stmt.c.last_event
Following is the declaration of two tables (using declarative extension)
and your full query:
class Session(Base):
__tablename__ = 'sessions'
session_id = Column(Integer, primary_key=True)
date = Column(DateTime())
class Event(Base):
__tablename__ = 'events'
session_id = Column(Integer, ForeignKey('sessions.session_id'),
primary_key=True)
event_id = Column(Integer, primary_key=True)
code = Column(Integer)
last_event = db_session.query(
Event.session_id,
func.max(Event.event_id).label('event_id')
).group_by(Event.session_id).subquery()
event = db_session.query(
Event.session_id,
Event.code
).join(last_event, and_(
Event.session_id == last_event.c.session_id,
Event.event_id == last_event.c.event_id
)).subquery()
q = db_session.query(
Session,
event.c.code
).outerjoin(event).order_by(Session.session_id)
--
Audrius Kažukauskas
http://neutrino.lt/
pgpIvTrDJMaJd.pgp
Description: PGP signature
Re: [sqlalchemy] CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
On Thu, 2013-03-14 at 20:29:18 -0700, Jonathan Vanasco wrote: i need to replace my calls to `sqlalchemy.func.current_timestamp()` with something that generates CURRENT_TIMESTAMP AT TIME ZONE 'UTC' anyone have quick advice ? I have answered this question on StackOverflow, if others are interested: http://stackoverflow.com/questions/15424199/howto-current-timestamp-at-time-zone-utc -- Audrius Kažukauskas http://neutrino.lt/ pgpZSyc5TfDlc.pgp Description: PGP signature
Re: [sqlalchemy] how to use ForeignKeyConstraint ?
On Sun, 2013-02-24 at 13:34:10 +0100, robert rottermann wrote:
The following tables
tblPerson, tblFlag
are linked using the association table tblPerson_has_Flag.
this works fine if I explicitly define columns to be part of foreign
keys as shown in tblPerson_has_Flag below.
what I wanted to do, but failed was:
in tblPerson_has_Flag insted of marking the columns to be part of a
fk to have the following construct
__table_args__ = (
sa.ForeignKeyConstraint(
['tblPerson_id'], [u'tblPerson.id'],
['tblFlag_id'], [u'tblFlag.id'],
),
sa.PrimaryKeyConstraint(u'tblPerson_id', u'tblFlag_id'), {}
)
If I understand correctly, you can't use ForeignKeyConstraint, because
it is used to create a single foreign key (which can be a composite
foreign key if it consists of multiple columns), while in your case
there are two separate foreign keys pointing to two different tables.
Define your association object like this:
class tblPerson_has_Flag(Base):
__tablename__ = 'tblPerson_has_Flag'
tblPerson_id = sa.Column(
mysql.INTEGER(display_width=11),
sa.ForeignKey('tblPerson.id'),
primary_key=True, autoincrement=False
)
tblFlag_id = sa.Column(
mysql.INTEGER(display_width=11),
sa.ForeignKey('tblFlag.id'),
primary_key=True, autoincrement=False
)
This will create a composite primary key constraint and two separate
foreign key constraints.
--
Audrius Kažukauskas
http://neutrino.lt/
pgpmldaVSb3TD.pgp
Description: PGP signature
Re: [sqlalchemy] Re: How to create a temporary table in SA ORM
On Sun, 2013-02-10 at 10:59:30 -0800, junepeach wrote:
Thanks very much for the explanation about the difference between view
and temporary table. But now, I am thinking about how to realize it in
sqlalchemy and use it in as many databases as possible.
If you're talking about temporary tables, then this will do:
class Test(Base):
__tablename__ = 'test_table'
__table_args__ = {'prefixes': ['TEMPORARY']}
# ...
Haven't tested this thoroughly, you may need to bind session to a single
connection instead of engine and use that same connection to create the
table, as at least in PostgreSQL temporary tables exist only for a
connection they were created in. Not sure how they work in other RDBMS.
If you meant the views, then map and query them as ordinary tables.
--
Audrius Kažukauskas
http://neutrino.lt/
pgpNQdc7Wuvvk.pgp
Description: PGP signature
Re: [sqlalchemy] how can i use bind parameters with an ilike ?
On Tue, 2013-01-29 at 10:08:28 -0800, Jonathan Vanasco wrote: i need to search something like this: select name from users where name ilike '%jonathan%'; i know i could do this: dbSession.query( models.User )\ .filter( models.User.name.ilike( %%%s%% % 'jonathan' ) but the name is coming from the web, so i want treat it with a bind, like this... dbSession.query( models.User )\ .filter( models.User.name.ilike( %:name% ) .params( name = 'jonathan' ) anyone know if something this is possible ? ( the above does not work ) How about using dbSession.query(models.User).filter(models.User.contains(name)) -- Audrius Kažukauskas http://neutrino.lt/ pgpoIzBxhu_El.pgp Description: PGP signature
Re: [sqlalchemy] how can i use bind parameters with an ilike ?
On Tue, 2013-01-29 at 20:19:45 +0200, Audrius Kažukauskas wrote: How about using dbSession.query(models.User).filter(models.User.contains(name)) Scratch that, this generates LIKE, while you need ILIKE. -- Audrius Kažukauskas http://neutrino.lt/ pgpWASdq7rqCV.pgp Description: PGP signature
Re: [sqlalchemy] Couple of questions about filtering...
On Tue, 2013-01-22 at 09:26:35 +0200, Alexey Vihorev wrote:
1. Does SQLA support deep filtering, i.e. something like this:
query(Invoice).filter(Invoice.Customer.Country.name=='France')
This does not work as it is, but is there something along this lines (except
of going with query.filter(.query.filter(.query.filter(query.get()?
SQLAlchemy tries to follow SQL closely, hence the SQL in its name. So
it's helpful to think in terms of SQL when trying to construct a query.
In this case you would need to use JOINs:
# Assuming that you have all these tables with properly defined
# relations.
session.query(Invoice).join(Customer, Country).\
filter(Country.name == u'France')
2.Can I use hybrid properties for filtering? I tried to do that, but that's
what I got:
class Person(Base):
first_name = Column(String(50))
last_name = Column(String(50))
def __init__(self, first, last):
self.first_name = first
self.last_name = last
@hybrid_property
def full_name(self):
print(self.first_name, self.last_name)
return self.first_name + + self.last_name
p = Person('John', 'Doe')
s.commit()
res = s.query(Person).filter(Person.full_name=='John Doe').all()
output:
Person.first_name Person.last_name
[]
Apparently, Person.full_name receives a class as an argument instead of an
instance. Is there other way?
That's what you want in this case, that this hybrid property would get a
class, because Query works with classes. Try adding echo=True to
create_engine() to see the actual SQL emitted, it should be correct.
In your example you forgot to add newly created p object to a session:
s.add(p)
s.commit()
Your Person class is missing __tablename__ as well.
--
Audrius Kažukauskas
http://neutrino.lt/
pgp5HoNMwVzps.pgp
Description: PGP signature
Re: [sqlalchemy] Couple of questions about filtering...
On Tue, 2013-01-22 at 19:58:15 +0200, Alexey Vihorev wrote: That's what you want in this case, that this hybrid property would get a class, because Query works with classes. Try adding echo=True to create_engine() to see the actual SQL emitted, it should be correct. No, that's definitely not what I want. My hybrid property relies on a content of an instance to return a value, so it needs that 'self' parameter to be an instance. But the whole idea of hybrid properties and methods is to work on both classes and their instances. Since you're using class in a query, you get a class as self argument. When that query returns a result (an instance of this class), the hybrid property will work on that instance as well. I also recreated your hybrid example and I can't see any problems: the query emits correct SQL, and hybrid on returned instance also works. -- Audrius Kažukauskas http://neutrino.lt/ pgpI5G9CTPKnC.pgp Description: PGP signature
Re: [sqlalchemy] How I can filter out Saturday and Sunday?
On Mon, 2012-12-17 at 11:54:20 -0800, Gabriel Pozo wrote: I am a new user of sqlalchemy, I need to know how I can filter the weekdays, to exclude Saturdays and Sundays. I look for something similar to the function dayofweek of MySQL. In SQLAchemy you can refer to DB functions via func[0] construct. Say, you have the following model: class Model(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) date = Column(DateTime) You can query it like this: session.query(Model).filter( # 1 = Sunday, 2 = Monday, ..., 7 = Saturday. func.dayofweek(Model.date).between(2, 6) ).all() [0] http://docs.sqlalchemy.org/en/rel_0_8/core/expression_api.html#sqlalchemy.sql.expression.func -- Audrius Kažukauskas http://neutrino.lt/ pgpCIaDFcPczZ.pgp Description: PGP signature
Re: [sqlalchemy] Postgresql trim-function
On Thu, 2012-12-13 at 02:03:42 -0800, Zwieberl wrote:
I have strings like 'POINT(-141.1643 32.42112)' and want to extract the
numbers between the brackets.
Note1: I can not use python to manipulate the string, since the string gets
created from a Postgresql-function-call within the select-statement, and
the substring has to be available in the same select.
Note2: the length of the numbers is not constant, therefore a simple
substring()-call is not possible, since I dont know the end-value.
How about this:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
db_engine = create_engine('postgresql://localhost')
Session = sessionmaker(bind=db_engine)
db_session = Session()
Base = declarative_base()
class Point(Base):
__tablename__ = 'points'
id = Column(Integer, primary_key=True)
data = Column(Text)
Base.metadata.create_all(bind=db_engine)
p1 = Point(data=u'POINT(-141.1643 32.42112)')
p2 = Point(data=u'POINT(-42.1337 99.9)')
db_session.add_all([p1, p2])
db_session.commit()
q = db_session.query(func.regexp_split_to_array(
func.substring(Point.data, r'^POINT\((.*)\)$'), r'\s'))
for row in q:
print row
q = db_session.query(cast(func.regexp_split_to_table(
func.substring(Point.data, r'^POINT\((.*)\)$'), r'\s'), Float))
for row in q:
print row
Any help would be appreciated! (Also if you have a different approach
towards extracting the substring from the string)
Although I'm not familiar with this stuff, but perhaps this would be
better suited for PostGIS and GeoAlchemy?
--
Audrius Kažukauskas
http://neutrino.lt/
pgpsdWeHED66y.pgp
Description: PGP signature
Re: [sqlalchemy] Postgresql trim-function
On Thu, 2012-12-13 at 03:58:22 -0800, Zwieberl wrote:
But somehow I am a little bit worried about the performance. Wouldn't
trim() be much faster than regex? (I need to do this quite a lot of times)
Here's version which uses trim (actually, ltrim and rtrim), but the best
way to tell what's faster is to try both versions with your data:
func.rtrim(func.ltrim(Point.data, 'POINT('), ')')
--
Audrius Kažukauskas
http://neutrino.lt/
pgpGGVCOIFEdu.pgp
Description: PGP signature
Re: [sqlalchemy] Re: Querying postgres array ?
On Thu, 2012-12-13 at 12:46:28 -0800, Michael Bayer wrote:
the array() function builds an ad-hoc array, its separate from the ARRAY
type.
Using 0.8.0b1 (full release is soon) we have improved support for ARRAY:
http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html?highlight=array#sqlalchemy.dialects.postgresql.ARRAY
http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html?highlight=array#sqlalchemy.dialects.postgresql.array
In 0.7.9 (using psycopg2 as DBAPI driver) this works:
db.session.query(Mark.tags).filter(Mark.tags.op('@')(['foo'])).all()
Although in 0.8 it would look nicer:
db.session.query(Mark.tags).filter(Mark.tags.contains(['foo'])).all()
--
Audrius Kažukauskas
http://neutrino.lt/
pgpMP658DTbXp.pgp
Description: PGP signature
Re: [sqlalchemy] selecting from a subquery in sqlalchemy
On Tue, 2012-12-11 at 12:41:20 -0800, Jonathan Vanasco wrote: does anyone have a good reference for this ? i'm trying to select Table.Column , count(Table.Column) however I'm not querying the Table, instead the raw sql is more like SELECT a , COUNT(a) FROM ( SELECT FROM TABLE ) AS sq1 i've never had to select from a subquery in sqlalchemy before. I can't wrap my head around this and I can't find relevant docs Something like the following should do the trick: subq = db_session.query(Model1.field1).\ filter(Model1.field2 == 'bar').subquery() q = db_session.query(subq.c.field1, func.count(subq.c.field1)).\ group_by(subq.c.field1) You may want to look at http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-subqueries for more examples and explanations. -- Audrius Kažukauskas http://neutrino.lt/ pgpm5J0P9Pr7X.pgp Description: PGP signature
Re: [sqlalchemy] Re: Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines
On Thu, 2012-12-06 at 17:43:45 -0800, junepeach wrote: How to upgrade to a newer sqlalchemy version? I can not find a related document. Should I just use pip to install the current one? Will both version conflict? The answer depends on how and where SA is installed already. The best way IMHO is to use separate virtualenvs for each of your projects (if you're not doing this already). Since SQLAlchemy 0.8 is not released yet, you'd need to issue pip (from within virtualenv) as follows: pip install -U -e hg+https://bitbucket.org/sqlalchemy/sqlalchemy#egg=SQLAlchemy -- Audrius Kažukauskas http://neutrino.lt/ pgpexjZMNQjVb.pgp Description: PGP signature
Re: [sqlalchemy] Re: Best practice for faves/likes counting?
On Wed, 2012-12-05 at 17:16:01 -0800, Hong Minhee wrote: But for work in works: work.like_query.count() causes inefficient 1+N queries even if we have the right indices for it. Of course I could query like session.query(Work, count()).join(Work.like_set).group_by(Work) but it’s somewhat complicated to read and write for me (is it only me?). I want to reject such uses in ORM… Anyway I use PostgreSQL. But using JOINs is how relational DBs are meant to be used. I had similar mindset like yours not that long ago. I didn't really understand how the database was working, it was kind of blackbox to me. What helped me immensely was writing pure SQL queries for Postgres in one of my jobs. Not only that, but also reading EXPLAIN query plans, seeing how and when the indices were used, etc. There are still many things to learn, but I have much better understanding of relational DBs now. This also helped me to see why SQLAlchemy is as it is and appreciate it even more. Its exposure of SQL is a good thing, contrary to other ORMs, which try to hide it as much as possible, limiting their flexibility and power in result. So I strongly encourage you to learn more about database you are using and SQL in general. This will help you to write more efficient queries and better leverage the power of your RDBMS. In case of your JOIN query, even if you have the right indices, it could still be slow, if it's not selective enough (it selects big part of the table). In that case index simply won't be used by the query planner. -- Audrius Kažukauskas http://neutrino.lt/ pgpEoDeVdzBKx.pgp Description: PGP signature
Re: [sqlalchemy] Do we have a sqlalchemy code or migration script to enforce foreign key constraint for sqlite3?
On Wed, 2012-12-05 at 06:32:46 -0800, junepeach wrote: When I logged in sqlite by 'sqlite3 mydb', and checked 'PRAGMA foreign_keys', it is still 0. There is no change yet. What is wrong? It doesn't work like that. From http://www.sqlite.org/foreignkeys.html: Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately. That's the purpose of the event handler you have included in env.py -- to execute 'PRAGMA foreign_keys=ON' for each new connection. -- Audrius Kažukauskas http://neutrino.lt/ pgp9jWpyujWpW.pgp Description: PGP signature
Re: [sqlalchemy] Do we have a sqlalchemy code or migration script to enforce foreign key constraint for sqlite3?
On Wed, 2012-12-05 at 06:43:33 -0800, junepeach wrote: I think one of my problems is after defining: def set_sqlite_pragma(dbapi_connection, connection_record) in env.py, Where I should call it and what are the values of 2 arguments: dbapi_connection, connection_record? You don't have to call it, the event handler is called every time when new connection is made to your SQLite database (note the decorator above it). -- Audrius Kažukauskas http://neutrino.lt/ pgpDoppfbOy1V.pgp Description: PGP signature
Re: [sqlalchemy] Do we have a sqlalchemy code or migration script to enforce foreign key constraint for sqlite3?
On Wed, 2012-12-05 at 07:04:57 -0800, junepeach wrote: Thank you Audrius.Maybe when I ran 'alembic upgrade head', 'PRAGMA foreign_keys' value of the current sqlite DB connection was already changed to 1 which I need to test in a python code. However when I manually logged in by typing 'sqlite3 mydb', this is another connection, so the 'PRAGMA foreign_keys' value became '0' again. Not sure if I am correct about that? That's right, you need to execute 'PRAGMA foreign_keys=ON' every time you connect to your DB, doesn't matter what client you use. There's no permanent way to turn foreign keys constraint on. -- Audrius Kažukauskas http://neutrino.lt/ pgpa1ChHh8lDI.pgp Description: PGP signature
Re: [sqlalchemy] Do we have a sqlalchemy code or migration script to enforce foreign key constraint for sqlite3?
On Wed, 2012-12-05 at 09:07:20 -0800, junepeach wrote:
I just did a testing, basically copied and ran the code of below link:
http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html
in the middle of process, I copied and ran below code:
from sqlalchemy.engine import Engine
from sqlalchemy import event
@event.listens_for(Engine, connect)
def set_sqlite_pragma(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute(PRAGMA foreign_keys=ON)
cursor.close()
then insert a couple rows to both user and addresses tables. I didn't
insert user id '3', however I could insert foreign key user id '2'
into addresses table. So foreign key constraint was not enforced.
You need to register the event handler before you make any attempts to
connect to your database. See attached the example that works as
expected (fails with IntegrityError exception due to foreign key
constraint).
--
Audrius Kažukauskas
http://neutrino.lt/
from sqlalchemy import *
from sqlalchemy import event
from sqlalchemy.orm import *
from sqlalchemy.engine import Engine
from sqlalchemy.ext.declarative import declarative_base
db_engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=db_engine)
db_session = Session()
@event.listens_for(Engine, connect)
def set_sqlite_pragma(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute(PRAGMA foreign_keys=ON)
cursor.close()
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String, unique=True, nullable=False)
class Email(Base):
__tablename__ = 'emails'
id = Column(Integer, primary_key=True)
address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey(User.id))
user = relationship(User, backref='emails')
Base.metadata.create_all(bind=db_engine)
email = Email()
email.user_id = 42
email.address = u'f...@example.org'
db_session.add(email)
# Will raise IntegrityError exception.
db_session.commit()
pgprKFlSuHYfk.pgp
Description: PGP signature
Re: [sqlalchemy] Do we have a sqlalchemy code or migration script to enforce foreign key constraint for sqlite3?
On Tue, 2012-12-04 at 08:53:43 -0800, junepeach wrote: We can turn on 'PRAGMA foreign_key = on' when running sqlite3 database system manually. However it is not a convenient way. So I wonder when I run the migration script using sqlalchemy and alembic tool, targeting sqlite3, is there a way to enforce the foreign key constraint in the migration script, or configuration script. In SQLAlchemy it can be done using events, see http://docs.sqlalchemy.org/en/rel_0_7/dialects/sqlite.html#foreign-key-support. Can't say anything about Alembic, as I haven't played with it yet (though I'm planning to). -- Audrius Kažukauskas http://neutrino.lt/ pgpj5yyZ61cGu.pgp Description: PGP signature
Re: [sqlalchemy] Do we have a sqlalchemy code or migration script to enforce foreign key constraint for sqlite3?
On Tue, 2012-12-04 at 10:13:22 -0800, junepeach wrote: Thanks Audrius. Do you have a simple working example in sqlalchemy? I am not familiar with sqlalchemy. My migration script will separate from other python development codes. So it is better to put 'PRAGMA foreign_keys=ON' in a configuration file. I am pretty new to all of those stuff. I assume that by configuration script you mean env.py (as I said in my previous mail, I'm not familiar with Alembic). According to the docs, that's the place where SA engine is created. Have you tried to place the code snippet from the link that I gave you in env.py? Perhaps that is all that's needed for SQLite's foreign key constraint to take effect in Alembic migration scripts. -- Audrius Kažukauskas http://neutrino.lt/ pgp0qKR68LGn1.pgp Description: PGP signature
Re: [sqlalchemy] Trying to apply result of raw sql to an 'in_' clause of mapped table
On Wed, 2012-11-21 at 13:05:22 +0100, Ralph Heinkel wrote: The latter was my favorite, but this seems to fail with [...] File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py, line 1390, in in_ return self._in_impl(operators.in_op, operators.notin_op, other) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py, line 1409, in _in_impl for o in seq_or_selectable: TypeError: '_TextClause' object is not iterable Any idea how to fix this? My first guess is that you're using too old version of SQLAlchemy (0.5.8). I suggest to try out the latest version 0.7.9. -- Audrius Kažukauskas http://neutrino.lt/ pgpsGOaXAu2Ds.pgp Description: PGP signature
Re: [sqlalchemy] right way to do ::interval queries with orm?
On Thu, 2012-11-08 at 10:06:54 +, Chris Withers wrote: What's the right way to do a query such as the following using the orm layer? select * from event where date now() - '3 years'::interval Here's how I do it in one of my projects: from sqlalchemy import func from sqlalchemy.sql.expression import FunctionElement from sqlalchemy.ext.compiler import compiles class subtract_interval(FunctionElement): type = Date() name = 'subtract_interval' @compiles(subtract_interval) def compile_subtract_interval(element, compiler, **kwargs): return '(%s::date - %s::interval)' % ( compiler.process(element.clauses.clauses[0]), compiler.process(element.clauses.clauses[1]), ) res = session.query(Foo).filter( Foo.date subtract_interval(func.now(), '3 years') ).all() I have borrowed the technique from an example that appeared on this same mailing list in the past. -- Audrius Kažukauskas http://neutrino.lt/ pgpzeyUapHmVy.pgp Description: PGP signature
Re: [sqlalchemy] PostgreSQL hstore support for SQLAlchemy
On Mon, 2012-08-13 at 10:59:59 -0400, Michael Bayer wrote: very nice job understanding quite a number of SQLAlchemy APIs, this is good work. It reminds me also of how we still have a weakness in the Core API, that we can't add new comparison methods at the Core level onto Column objects and such. The column_property() here is a well-designed workaround for that. Ideally MutationDict would be part of SQLAlchemy, from the mutable extension. the extension should have mutable versions of basic types list, dict, set.That's a TODO and also we'd need lots of tests. Sounds really great (especially the ability to add comparison methods onto Column), looking forward to it. The contains() method is addressed by allowing the escape argument through: Thanks, I pushed the fix. as for __getitem__ I don't want to expose that as a potential operator right now, I'd rather look into a deeper change to the operator system first so that hacks like _HStoreBinaryExpression aren't needed, then we can figure out if there's a space for __getitem__ types of things. I decided to remove it for now, as there's get() method which works in all cases (albeit looks not as nice as using __getitem__), and I would like to keep the symmetry between Core and ORM methods. Will add it back in the future, if (when) support for it comes. Thanks for your comments! -- Audrius Kažukauskas http://neutrino.lt/ pgp8JwLxlfE5e.pgp Description: PGP signature
Re: [sqlalchemy] Getting maximum recursion depth exception when creating a model instance with two foreign keys
On Sun, 2012-08-12 at 11:07:18 -0700, Zhe Wu wrote: def __init__(self, body, author, story): Here arguments are body, author, story. comment = Comment(author, story, body) And here they are author, story, body. I believe that's the source of your problem. -- Audrius Kažukauskas http://neutrino.lt/ pgphMREoXwNDB.pgp Description: PGP signature
