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())