I'm trying to perform this MongoDB's search query in pyDAL:
db.stores.find({
'menu.items.food.category': 'Pizza'
})
The stores collection mapping to a pyDAL table is:
Store = db.define_table(
'stores',
Field('name'),
Field('date', type='datetime'),
Field('description', type='json'),
Field('address'),
Field('featured', type='boolean'),
Field('lat', type='double'),
Field('lng', type='double'),
Field('image'),
Field('city'),
Field('menu', type='json'),
Field('country'),
Field('category'),
Field('URI'),
Field('reviews', type='list:string')
)
I was doing:
1st attempt:
db(Store.menu.items.food.category == 'Pizza').select())
output:
Traceback (most recent call last):
File "/home/mmagni/www/appicar/qa-automation/main.py", line 6, in <module>
print(db(Store.menu.items.food.category == 'Pizza').select())
AttributeError: 'Field' object has no attribute 'items'
2nd attempt:
db(Store.menu['items']['food']['category'] == 'Pizza').select()
output:
Traceback (most recent call last):
File "/home/mmagni/www/appicar/qa-automation/main.py", line 6, in <module>
print(db(Store.menu['items']['food']['category'] == 'Pizza').select())
File "/usr/local/lib/python3.6/dist-packages/pydal/objects.py", line 1338, in __getitem__
return self[i:i + 1]
TypeError: must be str, not int
Finally, I tried to bring the stores that has a menu:
for store in db(Store.menu).select():
pprint(store.as_dict())
output:
/usr/bin/python3.6 /home/mmagni/www/appicar/qa-automation/main.py
{'URI': 'lerot',
'address': 'Av. Arístides Villanueva 263',
'category': 'DB.bar-restaurant',
'city': 'Mendoza',
'country': 'Argentina',
'date': datetime.datetime(2017, 8, 5, 1, 29, 52, 789000),
'description': {'en': 'A friendly place, where you feel comfortable and at '
'home is what matters to us. Attended by its owners, '
'the most important thing is that you eat and drink for '
'a very convenient price. What you can not stop eating '
'are the "LeRot Fries", a classic of our place. We '
'expect you! Closing time: you define it. Food: the '
'tastiest and most economical of the street. Drinks: '
'our bartender prepares you whatever you want. If you '
'say you found us on TripAdvisor, you have a 15% '
'discount on your account.',
'es': 'Un lugar amigable, donde sentirte cómodo y como en '
'casa es lo que nos importa. Atendido por sus dueños , '
'lo más importante es que comés y tomás por un precio '
'muy conveniente. Lo que no podés dejar de comer son '
'las “Papas LeRot", un clásico de nuestro lugar ¡Te '
'esperamos! Hora de cierre: la ponés vos. Comida: la '
'más rica y económica de la calle. Tragos: nuestro '
'bartender te prepara lo que vos quieras. Si decís que '
'nos encontraste en TripAdvisor, tenés un 15% de '
'descuento en tu cuenta.',
'it': 'Un luogo accogliente, dove ti senti a tuo agio e a '
'casa è ciò che conta per noi. Frequentato dai suoi '
'proprietari, la cosa più importante è che mangi e bevi '
'per un prezzo molto conveniente. Quello che non puoi '
'smettere di mangiare sono le "LeRot Fries", un '
'classico del nostro locale. Vi aspettiamo! Tempo di '
'chiusura: lo definisci. Cibo: il più gustoso e più '
'economico della strada. Bevande: il nostro barista ti '
'prepara tutto ciò che vuoi. Se dici che ci hai trovato '
'su TripAdvisor, hai uno sconto del 15% sul tuo '
'account.'},
'featured': True,
'id': 28202411502443212115215594329,
'image': 'http://cdn.appicar.com/stores/ZPoYEMmanZgmFvYpC.jpg',
'lat': -32.8923525,
'lng': -68.8540556,
'menu': {'items': {'drink': [{'_id': ObjectId('5aa06d7784b554537f88885a'),
'category': 'Drinks with Alcohol',
'date': datetime.datetime(2018, 3, 6, 2, 21, 4, 958000),
'name': {'en': 'Fernet with Coke to prepare',
'es': 'Fernet con Coca para preparar',
'it': 'Fernet con Coca-Cola da '
'preparare'},
'paymentMethods': ['Cash', 'Credit Card'],
'picture': 'http://cdn.appicar.com/stores/menu/ZPoYEMmanZgmFvYpC/5aa06d7784b554537f88885a.jpg',
'price': [{'currency': 'ARS', 'value': 120.0}]}],
'food': [{'_id': ObjectId('5aa06d7784b554537f888858'),
'category': 'Pizza',
'date': datetime.datetime(2018, 3, 6, 2, 21, 4, 958000),
'name': {'en': 'Mozzarella pizza',
'es': 'Pizza Mozzarella',
'it': 'Pizza alla Mozzarella'},
'paymentMethods': ['Cash', 'Credit Card'],
'picture': 'http://cdn.appicar.com/stores/menu/ZPoYEMmanZgmFvYpC/5aa06d7784b554537f888858.jpg',
'price': [{'currency': 'ARS', 'value': 150.0}]},
{'_id': ObjectId('5aa06d7784b554537f888859'),
'category': 'Lomito',
'date': datetime.datetime(2018, 3, 6, 2, 21, 4, 958000),
'name': {'en': 'Provolone Sandwich',
'es': 'Provolomo',
'it': 'Panino al Provolone'},
'paymentMethods': ['Cash', 'Credit Card'],
'picture': 'http://cdn.appicar.com/stores/menu/ZPoYEMmanZgmFvYpC/5aa06d7784b554537f888859.png',
'price': [{'currency': 'ARS',
'value': 180.0}]}]}},
'name': 'LeRot',
'reviews': [{'_id': ObjectId('5aa943ea602e8b6921d0bb66'),
'clientId': 'TGTbYMoThGpZWveKa',
'clientName': 'Matías J. Magni',
'clientPicture': 'http://cdn.appicar.com/users/TGTbYMoThGpZWveKa.png',
'date': datetime.datetime(2018, 3, 14, 15, 46, 50, 497000),
'points': 4.0,
'text': {'en': 'Excellent place, the drinks are amazing and they '
'serve squid rings!',
'es': 'Excelente lugar, los tragos son muy ricos y '
'¡sirven rabas!',
'it': 'Posto eccellente, le bevande sono molto gustose '
'e servono anelli di calamari!'}},
{'_id': ObjectId('5aaacf349304c432ef2bbe4b'),
'clientId': 'GnLdeKy6kmDhAkAkF',
'clientName': 'Edgardo Marti',
'clientPicture': 'http://cdn.appicar.com/users/GnLdeKy6kmDhAkAkF.jpg',
'date': datetime.datetime(2018, 3, 15, 19, 53, 24, 633000),
'points': 3.0,
'text': {'en': 'Very nice place to spend a quiet time with your '
'mate. Very affordable prices and good attention '
'from the waiters.',
'es': 'Lindo lugar para pasar un rato tranquilo con tu '
'pareja. Los precios muy asequibles y buena '
'atención por parte de los mozos.',
'it': 'Bello posto per trascorrere un periodo '
'tranquillo con il tuo compagno. Prezzi molto '
'convenienti e buona attenzione da parte dei '
'camerieri.'}}]}
{'URI': 'antares',
'address': 'Av. Arístides Villanueva 153',
'category': 'DB.bar',
'city': 'Mendoza',
'country': 'Argentina',
'date': datetime.datetime(2017, 8, 5, 1, 51, 35, 847000),
'description': {'en': 'Antares is an Argentine brewery that produces craft '
'beers in the city of Mar del Plata since 1998. It is '
'made in seven varieties, following European recipes, '
'not only found in the Argentine market, but it is '
'already marketed in Brazil, Canada, United States, '
'Uruguay and Sweden, exports involve a little more than '
'20,000 bottles per year. Some of the varieties are '
'aged in oak vases for a month, achieving unique '
'flavors and an alcoholic strength of 8%. Locations '
'have been installed in different Argentine provinces '
'and in Buenos Aires, where you can taste some '
'appetizers, dishes of German food and the seven '
'varieties of beer they make. The production is 50,000 '
'liters of beer per month. We assign tables by arrival. '
'Antares bottle loading every day from 18 a 21 hs. ',
'es': '',
'it': ''},
'featured': True,
'id': 28202411502443212115215594330,
'image': 'http://cdn.appicar.com/stores/5kPxqXmRw6yyjfRLb.jpg',
'lat': -32.892855,
'lng': -68.851691,
'menu': {'items': {'food': [{'_id': ObjectId('5aa06c7a84b554537f888855'),
'category': 'Pizza',
'date': datetime.datetime(2018, 3, 7, 22, 49, 30, 118000),
'name': {'en': 'Mozzarella Pizza',
'es': 'Pizza Mozzarella',
'it': 'Pizza alla Mozzarella'},
'paymentMethods': ['Cash', 'Credit Card'],
'picture': 'http://cdn.appicar.com/stores/menu/5kPxqXmRw6yyjfRLb/5aa06c7a84b554537f888855.jpg',
'price': [{'currency': 'ARS',
'value': 120.0}]}]}},
'name': 'Antares',
'reviews': [{'_id': ObjectId('5aaad09e9304c432ef2bbe4f'),
'clientId': 'TGTbYMoThGpZWveKa',
'clientName': 'Matías J. Magni',
'clientPicture': 'http://cdn.appicar.com/users/TGTbYMoThGpZWveKa.png',
'date': datetime.datetime(2018, 3, 14, 15, 46, 50, 497000),
'points': 2.0,
'text': {'en': 'Excellent place to spend a quiet time with your '
'mate. Very affordable prices and good attention '
'from the waiters.',
'es': 'Excelente lugar para pasar un rato tranquilo con '
'tu pareja. Los precios muy asequibles y buena '
'atención por parte de los mozos.',
'it': 'Posto eccellente per trascorrere un periodo '
'tranquillo con il tuo compagno. Prezzi molto '
'convenienti e buona attenzione da parte dei '
'camerieri.'}},
{'_id': ObjectId('5aaad09e9304c432ef2bbe50'),
'clientId': 'GnLdeKy6kmDhAkAkF',
'clientName': 'Edgardo Marti',
'clientPicture': 'http://cdn.appicar.com/users/TGTbYMoThGpZWveKa.png',
'date': datetime.datetime(2018, 3, 15, 19, 53, 24, 633000),
'points': 4.0,
'text': {'en': 'Excellent place to spend a quiet time with your '
'mate. Very affordable prices and good attention '
'from the waiters.',
'es': 'Excelente lugar para pasar un rato tranquilo con '
'tu pareja. Los precios muy asequibles y buena '
'atención por parte de los mozos.',
'it': 'Posto eccellente per trascorrere un periodo '
'tranquillo con il tuo compagno. Prezzi molto '
'convenienti e buona attenzione da parte dei '
'camerieri.'}}]}
Store.menu field is defined as JSON. How can I perform a query inside of their keys?
pyDAL can store JSON data in any database, but it does not have an API for querying within JSON data, even for databases that support such operations. If using a database that supports SQL (e.g., Postgres), you can query within a JSON field by passing a raw SQL string as the query to the
DALobject. However, this approach does not apply to MongoDB.For MongoDB, the best approach is to use the pymongo driver directly for querying. You can either create your own connection via pymongo, or re-use the connection that pyDAL has created, which can be accessed at
db._adapter.connection(the driver itself can be accessed atdb._adapter.driver).