SELECT ... WHERE ... query in CouchDB + Python

Question

I'm new to CouchDb. When I installed it, I thought it would be something like MongoDb, but mongo now seems to be more transparent than couch. At least, in mongo I could insert and get data almost right away, using find(), in couch I do not see such a simple way of doing a query. So, imagine, that I saved a document

{'type':'post','theme':'blabla'}

And now I need to query all posts. How would I do that in Python (using couchdb module)?

Queries are done via "Views" in CouchDB, a completely different concept. In the end you do not traverse the data, you traverse the B-Tree created by your View. A explanation from SQL to CouchDB can be found here: guide.couchdb.org/draft/cookbook.html — h4cc
– h4cc, Commented Mar 2, 2015 at 21:38

Chris Snow · Accepted Answer · 2015-03-02 12:56:19Z

4

First, try creating a view.

function(doc) {
  if(doc.type) {
    emit(doc.type);
  }
}

See here for more information on views: http://guide.couchdb.org/draft/views.html

Next, write some python. I only have experience with the cloudant-python library which would look something like this:

import cloudant

account = cloudant.Account('http://localhost:5984')
db = account.database('yourdb')

view = db.view('theview')
options = {
    'key': 'post',
    'include_docs': True
}
for row in view.iter(params=options):
    # emits only rows with the key 'post'
    # with each row's emitting document

Note: this should also work for CouchDB

answered Mar 2, 2015 at 12:56

Chris Snow

24.9k38 gold badges158 silver badges332 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tomasz Gandor Over a year ago

How to create the view programmatically? (I think it's different in python-cloudant than in python-couchdb)...

Tomasz Gandor · Accepted Answer · 2021-01-02 01:44:39Z

The easiest thing you can use is a Mango Query:

First, let's make the database and save your document (using save, create is deprecated):

import couchdb
s = couchdb.Server()  # this is http://localhost:5984/ by default
db = s.create('posts')
db.save({'type': 'post', 'theme': 'blabla'})

We should have the setup OP described.

Querying with `find`

for doc in db.find({'selector': {'type': 'post'}}):
    # process your doc
    print(doc)

That's it! Read more about the possibilities here: http://localhost:5984/_utils/docs/api/database/find.html

Note, that the JSON query is passed as a normal Python dict. The selector can have multiple conditions, not only on equality:

db.find({'selector': {'type': 'post', 'theme': {'$regex': '^bla'}})

Don't forget the index

If you were using the API directly, you would get a warning in the result. The query didn't use an index! For larger DBs this would be slow.

Creating the index programmatically was a challenge, but I learned how to do it via source diving (you don't need to do it often - it's a DB administration task, and you can do it with Fauxton UI).

Here's how to access and see indexes:

idx = db.index()

To display:

>>> list(idx)
[{'ddoc': None,
  'name': '_all_docs',
  'type': 'special',
  'def': {'fields': [{'_id': 'asc'}]}}]

And now create a new one, on type:

idx[None, None] = ['type']

(passing None will generate a random Design Document, and a random name for the index (UUIDs)):

>>> list(idx)
[{'ddoc': None,
  'name': '_all_docs',
  'type': 'special',
  'def': {'fields': [{'_id': 'asc'}]}},
 {'ddoc': '_design/3298cb694b9b0e42b2a70030ece92eca87d3552d',
  'name': '3298cb694b9b0e42b2a70030ece92eca87d3552d',
  'type': 'json',
  'def': {'fields': [{'type': 'asc'}]}}]

If you will be filtering on theme, you can add that, too:

idx['ddoc_theme_idx', 'theme_idx'] = [{'theme': 'asc'}]

Which gives:

>>> list(idx)
[{'ddoc': None,
  'name': '_all_docs',
  'type': 'special',
  'def': {'fields': [{'_id': 'asc'}]}},
 {'ddoc': '_design/3298cb694b9b0e42b2a70030ece92eca87d3552d',
  'name': '3298cb694b9b0e42b2a70030ece92eca87d3552d',
  'type': 'json',
  'def': {'fields': [{'type': 'asc'}]}},
 {'ddoc': '_design/ddoc_theme_idx',
  'name': 'theme_idx',
  'type': 'json',
  'def': {'fields': [{'theme': 'asc'}]}}]

The index can be on multiple fields - just add more fields on the list, e.g.:

idx[None, None] = ['type', 'theme']

Collectives™ on Stack Overflow

SELECT ... WHERE ... query in CouchDB + Python

2 Answers 2

1 Comment

Querying with `find`

Don't forget the index

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Querying with find

Don't forget the index

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Querying with `find`