def nzb_remove(id): to_remove = id files_subquery = db.session.query(func.json_array_elements(WorkQueue.field_data['files']).label('files')).subquery() query = db.session.query(WorkQueue.id, WorkQueue.field_data, files_subquery.c.files).filter(files_subquery.c.files.op('->>')('file_id').cast(Integer) == id) for q in query: print q[0] return jsonify({"success": True})
def find_by_holding(cls, **kwargs): """Find item versions based on their holdings information. Every given kwarg will be queried as a key-value pair in the items holding. :returns: List[(UUID, version_id)] with `version_id` as used by `RecordMetadata.version_id`. """ def _get_filter_clause(obj, key, value): val = obj[key].astext CASTS = { bool: lambda x: cast(x, BOOLEAN), int: lambda x: cast(x, INTEGER), datetime.date: lambda x: cast(x, DATE), } if (not isinstance(value, six.string_types) and isinstance(value, collections.Sequence)): if len(value) == 2: return CASTS[type(value[0])](val).between(*value) raise ValueError('Too few/many values for a range query. ' 'Range query requires two values.') return CASTS.get(type(value), lambda x: x)(val) == value RecordMetadataVersion = version_class(RecordMetadata) data = type_coerce(RecordMetadataVersion.json, JSONB) path = ('_circulation', 'holdings') subquery = db.session.query( RecordMetadataVersion.id.label('id'), RecordMetadataVersion.version_id.label('version_id'), func.json_array_elements(data[path]).label('obj') ).subquery() obj = type_coerce(subquery.c.obj, JSONB) query = db.session.query( RecordMetadataVersion.id, RecordMetadataVersion.version_id ).filter( RecordMetadataVersion.id == subquery.c.id, RecordMetadataVersion.version_id == subquery.c.version_id, *(_get_filter_clause(obj, k, v) for k, v in kwargs.items()) ) for result in query: yield result
def apply(self, query, value, alias=None): from ..scheme.object import ExternalObject similar = list(ExternalObject.query.get(int(value)).similar()) json_data = json.dumps([s._asdict() for s in similar]) c = column('data', JSON) subquery = select([ c['into'].astext.cast(Integer).label('into'), c['score'].astext.cast(Float).label('score') ])\ .select_from(func.json_array_elements(json_data).alias('data'))\ .alias('similar') query = query.join(subquery, subquery.c.into == ExternalObject.id) # TODO: way to order_by similar.score return query
def apply(self, query, value, alias=None): from ..scheme.object import ExternalObject from matcher.app import db similar = list( db.session.query(ExternalObject).get(int(value)).similar()) json_data = json.dumps([s._asdict() for s in similar]) c = column("data", JSON) subquery = (select([ c["into"].astext.cast(Integer).label("into"), c["score"].astext.cast(Float).label("score"), ]).select_from( func.json_array_elements(json_data).alias("data")).alias("similar") ) query = query.join(subquery, subquery.c.into == ExternalObject.id) # TODO: way to order_by similar.score return query
Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() # user_json = UserJson() # user_json.content = data # session.add(user_json) # session.commit() # user_jsons = s.query(UserJson).all() # for user_json in user_jsons: # data['code'] = data['code'] + 1 # user_json.content = data subquery = session.query( UserJson.id.label('user_json_id'), func.json_array_elements( UserJson.content['data']['users']).label('users')).subquery() query = session.query(UserJson.id, subquery.c.users).filter( subquery.c.users.op('->>')('Age').cast(Integer) >= 25, UserJson.id == subquery.c.user_json_id) count = 0 for item in query.all(): count += 1 print(item) print(count) # query = session.query(UserJson.content.op('->>')('code')).filter(UserJson.content.op('->>')('code').cast(Integer) == 1).all() # for item in query: # print(item)
import datetime from sqlalchemy import Column, Integer, DateTime from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Test(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) created_date = Column(DateTime, default=datetime.datetime.utcnow) from sqlalchemy import select, func stmt = select(func.json_array_elements('["one", "two"]').column_valued("x")) print(stmt) print(func.now()) if __name__ == '__main__': T = Test() # print(T.created_date) print(datetime.datetime.utcnow())