[sqlalchemy] sqlite and max_overflow
Hi all, I'm trying to use turbogears and sqlite as: sqlalchemy.dburi=sqlite:// but I got this error: TypeError: Invalid argument(s) 'max_overflow' sent to create_engine(), using configuration SQLiteDialect_pysqlite/SingletonThreadPool/Engine. Please check that the keyword arguments are appropriate for this combination of components. What can I do, to avoid it? thanks for any help. j -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
Hello.
I get a warning like this:
usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py:2276: SAWarning:
Column 'id' on table sqlalchemy.sql.expression.Select at 0xa3e19cc; Select
object being replaced by another column with the same key. Consider use_labels
for select() statements.
use_labels() or apply_labels() should help but both work only on the core
constructs. How can I do this on a Query level?
Also, can this alone cause a cartesian product in a query?
My entire (recursive) query:
def find_subtree(self, max_depth=None, eager=False):
cls = self.__class__
i0 = literal_column('1').label('max_depth')
q_base = session.query(cls.id.label('partner_id'),
i0).filter(cls.sponsor_id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
partner_alias = aliased(cls, name='partner_alias')
i1 = literal_column('max_depth + 1').label('max_depth')
q_rec = session.query(partner_alias.id.label('partner_id'), i1)
q_rec = q_rec.filter(partner_alias.sponsor_id ==
q_cte_alias.c.partner_id)
if max_depth is not None:
q_rec = q_rec.filter(q_cte_alias.c.max_depth max_depth)
q_cte_union = q_cte.union_all(q_rec)
if eager:
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
q = q.join(PersonalContact, cls.id == PersonalContact.partner_id)
q = q.options(
subqueryload_all(cls.partner_regions),
joinedload_all(cls.personal_data, PersonalData.address,
innerjoin=True),
subqueryload_all(PersonalContact.contact_tags, ContactTag.tag),
)
else:
q = session.query(cls)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
return q
Thank you,
Ladislav Lenart
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
The Query usually does apply_labels automatically. if you are getting that
warning with your query below, there's too much going on there for me to
identify by sight where that might be happening, I would need actual code which
I can run in order to diagnose.
On May 10, 2013, at 11:10 AM, Ladislav Lenart lenart...@volny.cz wrote:
Hello.
I get a warning like this:
usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py:2276: SAWarning:
Column 'id' on table sqlalchemy.sql.expression.Select at 0xa3e19cc; Select
object being replaced by another column with the same key. Consider
use_labels
for select() statements.
use_labels() or apply_labels() should help but both work only on the core
constructs. How can I do this on a Query level?
Also, can this alone cause a cartesian product in a query?
My entire (recursive) query:
def find_subtree(self, max_depth=None, eager=False):
cls = self.__class__
i0 = literal_column('1').label('max_depth')
q_base = session.query(cls.id.label('partner_id'),
i0).filter(cls.sponsor_id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
partner_alias = aliased(cls, name='partner_alias')
i1 = literal_column('max_depth + 1').label('max_depth')
q_rec = session.query(partner_alias.id.label('partner_id'), i1)
q_rec = q_rec.filter(partner_alias.sponsor_id ==
q_cte_alias.c.partner_id)
if max_depth is not None:
q_rec = q_rec.filter(q_cte_alias.c.max_depth max_depth)
q_cte_union = q_cte.union_all(q_rec)
if eager:
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
q = q.join(PersonalContact, cls.id == PersonalContact.partner_id)
q = q.options(
subqueryload_all(cls.partner_regions),
joinedload_all(cls.personal_data, PersonalData.address,
innerjoin=True),
subqueryload_all(PersonalContact.contact_tags, ContactTag.tag),
)
else:
q = session.query(cls)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
return q
Thank you,
Ladislav Lenart
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
Hello.
My main concern was that the query creates a cartesian product and I thought the
warning might have something to do with it. It haven't. The problem is related
to the use of select_from():
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
This part renders the following SQL:
SELECT *
FROM
q_cte
JOIN partner ON q_cte.partner_id = q_cte.partner_id
As you can see, the JOIN condition is wrong though I have no idea why. The
following works, however:
q = session.query(cls)
q = q.add_entity(PersonalContact)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
Is my usage of select_from() bad for some reason? Do you know what's going on? I
can provide you a fully runnable test case if you still need it (on Monday).
Note also that I use SA 0.7.9. Is this fixed in later versions?
Thank you,
Ladislav Lenart
On 10.5.2013 17:17, Michael Bayer wrote:
The Query usually does apply_labels automatically. if you are getting that
warning with your query below, there's too much going on there for me to
identify by sight where that might be happening, I would need actual code
which I can run in order to diagnose.
On May 10, 2013, at 11:10 AM, Ladislav Lenart lenart...@volny.cz wrote:
Hello.
I get a warning like this:
usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py:2276: SAWarning:
Column 'id' on table sqlalchemy.sql.expression.Select at 0xa3e19cc; Select
object being replaced by another column with the same key. Consider
use_labels
for select() statements.
use_labels() or apply_labels() should help but both work only on the core
constructs. How can I do this on a Query level?
Also, can this alone cause a cartesian product in a query?
My entire (recursive) query:
def find_subtree(self, max_depth=None, eager=False):
cls = self.__class__
i0 = literal_column('1').label('max_depth')
q_base = session.query(cls.id.label('partner_id'),
i0).filter(cls.sponsor_id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
partner_alias = aliased(cls, name='partner_alias')
i1 = literal_column('max_depth + 1').label('max_depth')
q_rec = session.query(partner_alias.id.label('partner_id'), i1)
q_rec = q_rec.filter(partner_alias.sponsor_id ==
q_cte_alias.c.partner_id)
if max_depth is not None:
q_rec = q_rec.filter(q_cte_alias.c.max_depth max_depth)
q_cte_union = q_cte.union_all(q_rec)
if eager:
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
q = q.join(PersonalContact, cls.id == PersonalContact.partner_id)
q = q.options(
subqueryload_all(cls.partner_regions),
joinedload_all(cls.personal_data, PersonalData.address,
innerjoin=True),
subqueryload_all(PersonalContact.contact_tags,
ContactTag.tag),
)
else:
q = session.query(cls)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
return q
Thank you,
Ladislav Lenart
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
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] [Q][0.7.9] How to issue apply_labels() on an ORM query?
On May 10, 2013, at 3:03 PM, Ladislav Lenart lenart...@volny.cz wrote: Hello. My main concern was that the query creates a cartesian product and I thought the warning might have something to do with it. It haven't. The problem is related to the use of select_from(): q = session.query(cls, PersonalContact).select_from(q_cte_union) q = q.join(cls, cls.id == q_cte_union.c.partner_id) This part renders the following SQL: SELECT * FROM q_cte JOIN partner ON q_cte.partner_id = q_cte.partner_id As you can see, the JOIN condition is wrong though I have no idea why. The following works, however: q = session.query(cls) q = q.add_entity(PersonalContact) q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id) Is my usage of select_from() bad for some reason? Do you know what's going on? I can provide you a fully runnable test case if you still need it (on Monday). Note also that I use SA 0.7.9. Is this fixed in later versions? I've no idea why you're getting it without more context - if everything is generated from a query(), typically the labels are already applied, with the exception of some operations such as subquery() (maybe cte? not sure, I'd need to try it, hence an already existing example saves me lots of time tinkering around trying to guess what you're doing). Things have been improved in 0.8 regarding same-named columns but that only applies when the labels are already present. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Extending SQL Alchemy core (0.7.x) with new Select type statement
I'm trying to extend SQLA with a construct for using SQL Server's PIVOT functionality. I've written a class and a @compiles function to generate the query for this and this produces the correct query (e.g., given a sqlalchemy.Table and some Column objects it produces the right query). My problem is that the return type is a string, not a sqlalchemy.sql.expression.Select like object, so I can't do something like x = Pivot(table.c.key_col, table.c.pivot_col, ['attribute1, 'attribute2'], from_query) y = sqlalchemy.select([x.c.attribute1]) After Googling around I really couldn't find any examples like this. The examples on the documentation page seem to also just produce strings (and are Executable rather than Selectable anyway). (Mainly http://www.sqlalchemy.org/trac/wiki/UsageRecipes and http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html) Anyone have some tips on how to make the above do-able? An explanation for how to produce any kind of custom select statement (that could then be selected from) would be fine. Or if this isn't possible currently please let me know. Thanks, Dan -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
