[sqlalchemy] generating SQL for postgresql upsert issue
hi,
for the background, pls refer to this link.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3384/how-to-generate-complicated-nested-sql
I just follow Mike's suggestion, that is, only send sql templates and
json/hstore/string/datetime objects to database instead of raw SQL
statements. (if i understand it correctly. pls correct me if i am wrong)
the stored procedure function(only for testing) and test script are:
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
select test_upsert_operation('update app set name=%L where app_id=%L',
VARIADIC ARRAY['upsert-sub-pgsql-again', '2']);
CREATE OR REPLACE FUNCTION test_upsert_operation(update_exprssion text,
variadic params text[]) RETURNS VOID AS
$$
BEGIN
-- execute format('update app set name=%L where app_id=%L',
'upsert-sub-pgsql', 2);
execute format(update_exprssion, VARIADIC params);
END;
$$
LANGUAGE plpgsql;
The problem is that
1. the number of objects sent to stored procedure is not fixed,
2. they have different data types, string/datetime/jsonb/hstore, etc. BUT
variadic
only accept the same type[1] (even if for anyarray, anyelment etc [2])
[1]
http://www.postgresql.org/docs/9.4/static/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS
[2] http://www.postgresql.org/docs/current/static/extend-type-system.html
Any tips for this function declaration so that we can pass objects to the
stored procedure and do the formatting for escape literals???
thx again.
wenlong
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Preserve mapped entities after wrapping the query
Michael, thank you for you reply, I expected you to mention from_self :) I
know about it, it is a handy trick indeed
But I deliberately don't use it, because this way I don't know how to
mention a column which I want to filter on
This is due to the fact, that it is calculated i.e. there is no table to
refer to! I might resert to using literals(filter('avg_110')), but 'd
prefer to stay in the more ORM-style
суббота, 25 апреля 2015 г., 2:37:11 UTC+5 пользователь Michael Bayer
написал:
On 4/24/15 5:25 PM, Пайков Юрий wrote:
q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...)
I have a query which selects some mapped entity as well as other columns.
I then refer to the name of that entity when working with the result of
the query:
for entry in q.all():
recipe=entry.Recipe
Now, I want to add filtering by some calculated criteria to my query, and
so I wrap it in an additional query:
q = q.subquery();
q = session.query(q).filter(q.c.avg_1 10 )
However, this way I can no longer access entry.Recipe! Is there a way to
make sqlalchemy adapt names? I tried aliased and select_entity_from, but no
luck :(
this is getting into less reliable stuff, but instead of subquery() -
session.query(q), use the from_self() method. It's designed to work this
way, and your Recipe entity will be adapted into the subquery.
I've observed that the vast majority of my users don't seem to get into
queries like these, so from_self() is not as popular (or widely tested) as
it should be, but it is at the base of a lot of widely used functions like
count() and subquery eager loading, so give it a try:
http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=from_self#sqlalchemy.orm.query.Query.from_self
apparently it needs some documentation too :)
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Preserve mapped entities after wrapping the query
Ok, I seemed to figure out how to deal with it -
row_number_column = func.row_number().over(
partition_by=Recipe.id
).label('row_number')
query = query.add_column(
row_number_column
)
query = query.from_self().filter(row_number_column == 1)
Using an explicit column construct
суббота, 25 апреля 2015 г., 11:39:08 UTC+5 пользователь Юрий Пайков написал:
Michael, thank you for you reply, I expected you to mention from_self :) I
know about it, it is a handy trick indeed
But I deliberately don't use it, because this way I don't know how to
mention a column which I want to filter on
This is due to the fact, that it is calculated i.e. there is no table to
refer to! I might resert to using literals(filter('avg_110')), but 'd
prefer to stay in the more ORM-style
суббота, 25 апреля 2015 г., 2:37:11 UTC+5 пользователь Michael Bayer
написал:
On 4/24/15 5:25 PM, Пайков Юрий wrote:
q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...)
I have a query which selects some mapped entity as well as other columns.
I then refer to the name of that entity when working with the result of
the query:
for entry in q.all():
recipe=entry.Recipe
Now, I want to add filtering by some calculated criteria to my query, and
so I wrap it in an additional query:
q = q.subquery();
q = session.query(q).filter(q.c.avg_1 10 )
However, this way I can no longer access entry.Recipe! Is there a way to
make sqlalchemy adapt names? I tried aliased and select_entity_from, but no
luck :(
this is getting into less reliable stuff, but instead of subquery() -
session.query(q), use the from_self() method. It's designed to work this
way, and your Recipe entity will be adapted into the subquery.
I've observed that the vast majority of my users don't seem to get into
queries like these, so from_self() is not as popular (or widely tested) as
it should be, but it is at the base of a lot of widely used functions like
count() and subquery eager loading, so give it a try:
http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=from_self#sqlalchemy.orm.query.Query.from_self
apparently it needs some documentation too :)
--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: H2 database
2015-04-24 18:06 GMT+02:00 Jonathan Vanasco jonat...@findmeon.com: SqlAlchemy needs 2 things for a database work: 1. A python database driver 2. A SqlAlchemy dialect (that tells SqlAlchemy how to write sql for the driver) So... 1. H2 doesn't seem to have any Python drivers or other support. I just came across an old forum post that talked about possibly using Postgresql client since they supported similar protocols, but that's about all I could find. 2. There are a handful of posts and articles on writing custom dialects. Several recent ones as well. If you're just trying to handle the dialect, and their syntax is reasonably similar to an existing dialect... I think you could do it alone. If you have to write the general Python support as well though, that's a bigger task. I inquired also on the H2 list and got the following response: H2 supports the PostgeSQL wire protocol, so you can use the PostgreSQL database driver. Although you will probably need to tweek the dialect description that SqlAlchemy needs. So it should be doable. I have another project. :-D -- Cecil Westerhof -- 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. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Declarative setup failing on upgrade to 1.0.1
Still getting the same error with 1.0.2. It's using a custom base class passed to declarative.declarative_base(). The class is pretty simple -- mostly just a declared_attr to set the __tablename__ and a custom __repr__. I'll see if I can pare it down to a simple test case. -- Bill -- 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. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Preserve mapped entities after wrapping the query
On 4/25/15 6:05 AM, Юрий Пайков wrote:
Ok, I seemed to figure out how to deal with it -
|
row_number_column = func.row_number().over(
partition_by=Recipe.id
).label('row_number')
query = query.add_column(
row_number_column
)
query = query.from_self().filter(row_number_column == 1)
|
Using an explicit column construct
OK, more like your original though you can still put the window function
on the inside, and refer to it on the outside:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
field = Column(Integer)
s = Session()
avg = func.avg(A).over(partition_by=A.id).label('avg') # 'avg' label
is optional, will be auto-labeled anyway
q = s.query(A, avg).from_self().filter(avg 10)
print(q)
in the output, we can see that referring to avg the from_self() picks
up on this and adapts it to the inner query:
SELECT anon_1.a_id AS anon_1_a_id, anon_1.a_field AS anon_1_a_field,
anon_1.avg AS anon_1_avg
FROM (SELECT a.id AS a_id, a.field AS a_field, avg(:avg_1) OVER
(PARTITION BY a.id) AS avg
FROM a) AS anon_1
WHERE anon_1.avg :param_1
суббота, 25 апреля 2015 г., 11:39:08 UTC+5 пользователь Юрий Пайков
написал:
Michael, thank you for you reply, I expected you to mention
from_self :) I know about it, it is a handy trick indeed
But I deliberately don't use it, because this way I don't know how
to mention a column which I want to filter on
This is due to the fact, that it is calculated i.e. there is no
table to refer to! I might resert to using
literals(filter('avg_110')), but 'd prefer to stay in the more
ORM-style
суббота, 25 апреля 2015 г., 2:37:11 UTC+5 пользователь Michael
Bayer написал:
On 4/24/15 5:25 PM, Пайков Юрий wrote:
q = session.query(Recipe,
func.avg(Recipe.field1).over(...)).join(...)
I have a query which selects some mapped entity as well as
other columns.
I then refer to the name of that entity when working with the
result of the query:
for entry in q.all():
recipe=entry.Recipe
Now, I want to add filtering by some calculated criteria to
my query, and so I wrap it in an additional query:
q = q.subquery();
q = session.query(q).filter(q.c.avg_1 10 )
However, this way I can no longer access entry.Recipe! Is
there a way to make sqlalchemy adapt names? I tried aliased
and select_entity_from, but no luck :(
this is getting into less reliable stuff, but instead of
subquery() - session.query(q), use the from_self() method.
It's designed to work this way, and your Recipe entity will be
adapted into the subquery.
I've observed that the vast majority of my users don't seem to
get into queries like these, so from_self() is not as popular
(or widely tested) as it should be, but it is at the base of a
lot of widely used functions like count() and subquery eager
loading, so give it a try:
http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=from_self#sqlalchemy.orm.query.Query.from_self
http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=from_self#sqlalchemy.orm.query.Query.from_self
apparently it needs some documentation too :)
--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy
http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout
https://groups.google.com/d/optout.
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Declarative setup failing on upgrade to 1.0.1
This may have been an instance of working by accident. In setting up declarative_base, we're passing in a mapper function. Our mapper function wasn't returning the result of the SA mapper(). It didn't cause any (explicit) errors through 0.9.8, but I have a feeling that was just luck. On Friday, April 24, 2015 at 5:07:58 PM UTC-7, Michael Bayer wrote: give 1.0.2 a try since we adjusted some things regarding __declare_first__ and __declare_last__. Further than that it depends a lot on what your basic Base setup looks like, mixins in use, extensions like AbstractConcreteBase, stuff like that. Any details you can share would help. On 4/24/15 7:02 PM, Bill Schindler wrote: I'm trying to upgrade from SA 0.9.8 to 1.0.1 and getting a traceback. I'm not sure what's going on here, but the declarative setup is obviously not happy with something. (On 0.9.8, everything runs fine, so I've obviously run afoul of something new/different/fixed.) File /opt/certwise-lcs/eggs/lcs.content.user-1.0.2dev_r10-py2.7.egg/lcs/content/user/makeorm.py, line 89, in make_orm class Principals(Base): File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/api.py, line 55, in __init__ _as_declarative(cls, classname, cls.__dict__) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 87, in _as_declarative _MapperConfig.setup_mapping(cls, classname, dict_) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 102, in setup_mapping cfg_cls(cls_, classname, dict_) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 134, in __init__ self._early_mapping() File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 137, in _early_mapping self.map() File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 530, in map del mp_.class_manager.info['declared_attr_reg'] AttributeError: 'NoneType' object has no attribute 'class_manager' This gets fired off on every ORM class, so I'm guessing the cause is somewhere deeper in our code. Any thoughts on what I might look for to find the cause? -- Bill -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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. For more options, visit https://groups.google.com/d/optout.
