[sqlalchemy] generating SQL for postgresql upsert issue

[Wenlong LU]

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.

[sqlalchemy] how to handle the ARRAY(JSON) -JSON[] column type

[Wenlong LU]

hi,

I have the following model:

class Product(Base):
 skus = Column(ARRAY(JSON))


p = Product(skus=[{skuPrice: 44.88, skuStock: true, 
aeopSKUProperty: [], skuCode: 060314}])

session.add(p)
t.commit()

Error comes out:
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been 
rolled back due to a previous exception during flush. To begin a new 
transaction with this Session, first issue Session.rollback(). Original 
exception was: (raised as a result of Query-invoked autoflush; consider 
using a session.no_autoflush block if this flush is occuring prematurely) 
(ProgrammingError) column skus is of type json[] but expression is of 
type text[] ..

'INSERT INTO product (created_at, updated, skus) VALUES (%(created_at)s, 
%(updated_at)s, %(skus)s) RETURNING product.id' {'skus': ['{skuPrice: 
44.88, skuStock: true, aeopSKUProperty: [], skuCode: 060314}'], 
'created_at':   } 


it seems sqlalchemy only support array(Unicode), it convert Json into str...

any suggestion?


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.