Re: [sqlalchemy] SQLALchemy 1.0.13: JSONB insert into PostgreSQL fails with internal error 'unsupported jsonb version number 123'

2016-05-31 Thread Andrew M
On Wednesday, June 1, 2016 at 10:39:22 AM UTC+10, Mike Bayer wrote: > have you tried psycopg2? pypostgresql is not a well supported dialect. > That's fixed it - thanks Mike (and thanks for creating SQLAlchemy and Mako). -- You received this message because you are subscribed to the Google

[sqlalchemy] SQLALchemy 1.0.13: JSONB insert into PostgreSQL fails with internal error 'unsupported jsonb version number 123'

2016-05-31 Thread Andrew M
Hi, Thanks for SQLAlchemy. I'm trying to insert some JSONB into a PostgreSQL database. My insert succeeds if the column type is JSON but fails if the type is JSONB. SQLAlchemy version: sqlalchemy-1.0.13 PostgreSQL: 9.5.3 DBAPI: py-postgresql Python: 3.4 OS: Windows 8.1 64-bit Here's my test

Re: [sqlalchemy] Implementation of params for textual SQL prevents its use in column names (use Python format as workaround)

2016-07-27 Thread Andrew M
Okay, thanks Mike for your comprehensive reply. There is still so much to learn ... *sigh*. Perhaps it's worth including a sentence or two in the docs, helping overconfident people like myself to understand the benefits / importance of params (that it's not just .format)? -- You received

[sqlalchemy] Implementation of params for textual SQL prevents its use in column names (use Python format as workaround)

2016-07-27 Thread Andrew M
FYI, there seems to be a limitation of the implementation of params for textual SQL - params appears to silently insert single quotes, preventing the use of params to describe column names in the SQL query. The workaround is to use a conventional string with Python .format (which in my mind

[sqlalchemy] Re: Implementation of params for textual SQL prevents its use in column names (use Python format as workaround)

2016-07-27 Thread Andrew M
My recommendation is to leave params unchanged in the code but update the SQLAlchemy documentation so that .format is recommended over params (or, at least, is recommended for this particular case). On Thursday, July 28, 2016 at 12:11:13 PM UTC+10, Andrew M wrote: > > FYI, there

[sqlalchemy] Type mismatch on a GeoAlchemy2 query?

2017-02-08 Thread Andrew M
Hi, I'm stuck on a query which might be a PostGIS problem, sorry, but in case it relates to the SQLAlchemy side (or someone can help regardless) I'm posting it here. I want to run a query which returns every point which falls within a rectangle, where the points and the rectangle are based on

Re: [sqlalchemy] Type mismatch on a GeoAlchemy2 query?

2017-02-08 Thread Andrew M
. Thanks again, Andrew On Thursday, February 9, 2017 at 7:18:16 AM UTC+11, Mike Bayer wrote: > > > > On 02/08/2017 03:06 PM, Andrew M wrote: > > Hi, > > > > I'm stuck on a query which might be a PostGIS problem, sorry, but in > > case it relates to the SQLAlchemy

[sqlalchemy] Please mention ilike more prominently in the documentation

2017-01-04 Thread Andrew M
Hi Mike, Thanks for SQLAlchemy. As a relatively new user I wanted case-insensitive querying but the only reference for this that I could find in the documentation was custom comparators. I spent a lot of time trying to get these to work when, it turns out, ilike suited my needs perfectly.

Re: [sqlalchemy] Re: Wierdness with JSONB / Python dictionary

2017-06-29 Thread Andrew M
And, for that matter, the trouble I was having should have warned me about my data structure - I need to use an association table, not bury data in a JSONB column. Thanks again. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example

Re: [sqlalchemy] Re: Wierdness with JSONB / Python dictionary

2017-06-28 Thread Andrew M
It helps a great deal, thank you Simon. Thanks for taking the time to explain it for me. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See

[sqlalchemy] Re: Wierdness with JSONB / Python dictionary

2017-06-27 Thread Andrew M
I can also replicate it with JSON, with a table defined as follows: class Test(Base): __tablename__ = 'test' id = Column(INT, primary_key=True, index=True) json = Column(JSON, default={}) And running the following code: p0 = Test() p1 = Test() session.add(p0) session.add(p1)

[sqlalchemy] Wierdness with JSONB / Python dictionary

2017-06-27 Thread Andrew M
Hi, I'm getting some unexpected results when using a JSONB column to store the results of a Python dictionary. I have defined a column as follows: rrp_i8n = Column(JSONB, default={}) What I'm finding is that the method of setting the value is critical (when it should not be). Test case as

[sqlalchemy] Distinct within group by query

2019-09-04 Thread Andrew M
Hi, I would like to use distinct inside a group_by query, e.g.: session.query(Product.attribute_x, func.min(Product.price), distinct(Product.color)). group_by(Product.attribute_x) That is, for each value of attribute_x, I want to find the lowest price and all of the colors available. I don't

Re: [sqlalchemy] Distinct within group by query

2019-09-04 Thread Andrew M
Thank you Varun - what you've shown in the table is exactly what I'm looking for. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See

Re: [sqlalchemy] Distinct within group by query

2019-09-05 Thread Andrew M
Yes, I'm using Postgres. This does exactly what I need: session.query(Product.attribute_x, func.min(Product.price), func.array_agg(func.distinct(Product.color))).group_by(Product.attribute_x) Thanks Varun, that's saved me a real headache. I appreciate your help. -- SQLAlchemy - The Python