On Wed, Dec 6, 2017 at 7:42 AM, <shrey.chau...@invicto.in> wrote:
> Hi,
>
> I am using flask-sqlalchemy in my project, but I am not able to understand
> how to query(filter_by) on a json column
>
> test_example:
>
> #I am using Postgresql backend
> app = Flask(__name__)
> app.config['SQLALCHEMY_DATABASE_URI'] =
> 'postgresql://postgres:postgres@localhost:5432/test_db'
> db = SQLAlchemy(app)
>
>
> #this is my test class
> class Student(db.Model):
> __tablename__ = 'students'
> id=db.Column(db.Integer, primary_key=True, autoincrement=True)
> name=db.Column(db.String(200))
> roll_no=db.Column(db.Integer)
> data_test=db.Column(db.JSON)
>
> #to put data in table is used
> s= Student(name='shrey',roll_no=100, data_test={'foo':'bar'})
> db.session.add(s)
> db.session.commit()
>
> #I read in some links and i tried this
>
> a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()
>
> here the error I am getting is :
> Traceback (most recent call last):
> File "sqlalchemyjson.py", line 44, in <module>
> a = Student.query.filter(Student.data_test["foo"].astext
> =="bar").first()
> File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line
> 682, in __getattr__
> key)
> AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object
> has an attribute 'astext'
>
> I tried few other operations also, but nothing worked
"astext" is part of the psycopg2 variant of JSON, use it like this:
from sqlalchemy dialects import postgresql
class Student(db.Model):
# ...
data_test=db.Column(postgresql.JSON)
if you're using plain JSON, you should use cast:
from sqlalchemy import cast
cast(
data_table.c.data['some_key'], String
) == '"some_value"'
>
>
> can someone help me on this?
>
> --
> 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 http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
