basic where clause sentax

[Stephen Jones]

Hello there - 

I am trying to get a where clause to work and haven't been very successful. I'm hoping someone here will be able to shed some light on the subject. Here is the example and source for my logic:

Source: https://cassandra.apache.org/doc/cql3/CQL.html#selectStmt

I have a column family with 5 columns: veggies, fruits, drinks, var1 and var2. I want to find all of the rows where veggies ='carrots', fruits = 'apples', drinks = 'coke', and return all of that row's columns? So far what I have is the following:

row = session.execute('SELECT veggies as veggies, fruits as fruits, drinks as drinks FROM orders_archive WHERE ********)

I've tried multiple things and continue to get the following error:

Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
 File "/net/homedirs/sjones/lib/python2.6/site-packages/cassandra_driver-2.1.2.post-py2.6-linux-x86_64.egg/cassandra/cluster.py", line 1295, in execute
   result = future.result(timeout)
 File "/net/homedirs/sjones/lib/python2.6/site-packages/cassandra_driver-2.1.2.post-py2.6-linux-x86_64.egg/cassandra/cluster.py", line 2799, in result
   raise self._final_exception
cassandra.protocol.SyntaxException: <ErrorMessage code=2000 [Syntax error in CQL query] message="line 1:117 no viable alternative at input 'drinks'">

My question is; can someone finish the line with the correct sentax needed to get the results I'm looking for? Here are just a few of the combinations I've tried using my source link as a guide:

• row = session.execute('SELECT veggies as veggies, fruits as fruits, drinks as drinks FROM orders_archive WHERE "carrots" in veggies, "apples in fruits, "coke" in drinks)
  
• row = session.execute('SELECT veggies as veggies, fruits as fruits, drinks as drinks FROM orders_archive WHERE (veggies,fruits,drinks) = ("carrots","apples","coke"))
  
• row = session.execute('SELECT veggies as veggies, fruits as fruits, drinks as drinks FROM orders_archive WHERE veggies = "carrots", fruits = "apples", drinks = "coke")
• row = session.execute('SELECT veggies as veggies, fruits as fruits, drinks as drinks FROM orders_archive WHERE veggies = "carrots" AND fruits = "apples" AND drinks = "coke")

I feel that I'm on the correct path, I just can't seem to figure out where my sentax is off. Thoughts? Thanks in advance for your help. 

[Russell Hatch]

Hi Stephen,

Seems like the first thing here is to figure out if your problem is actually with the python driver. I'd suggest trying your same query in cqlsh. If it doesn't work in cqlsh then it's probably not driver related, and the question might be better suited to the cassandra users mailing list here: http://planetcassandra.org/apache-cassandra-mailing-lists/

In either case you'll want to include your schema so people can help you debug. At first glance your last query syntax looks approximately correct, but note that "select as" is superfluous when requesting the same column name already in your schema.

Cheers,

Russ

To unsubscribe from this group and stop receiving emails from it, send an email to python-driver-u...@lists.datastax.com.

[Adam Holmberg]

I can add that the error you're seeing is definitely coming from the server. There is no CQL parsing on the driver side.

As Russ mentioned you might be able to sort this out quickly using cqlsh. I suggest first making sure the columns you're after actually exist in the table:

USE <your keyspace>
DESC TABLE orders_archive;

Also mentioned: the aliases are superfluous. To get all columns from that table, for a particular parition:

SELECT * FROM orders_archive WHERE <your primary key> = 'something';

If we knew your table structure we could be more specific about what needs to be in the WHERE clause. Definitely hit up the Cassandra users list if this doesn't clear things up.

Regards,

Adam Holmberg

[Stephen Jones]

Thank you for the information. I was able to test a few things out in cqlsh interactive shell. Turns out that ' and " don't represent the same thing, like in python. My solution was to simply escape and use single quotes for my query exampled below.  I do have one follow up question that I will actually post in the cassanda users forum as well. What is the easiest way to get column aliases with the returned data? Or how do I decrypt them? Metadata? I am still working on that, but if anyone has experience it'd be great to know. Thanks again for the help. 

• row = session.execute('SELECT * FROM "orders_archive" WHERE veggies = \'carrots\' AND fruits = \'apples\' AND drinks = \'coke\'')

Cheers!

[Adam Holmberg]

What is the easiest way to get column aliases with the returned data?

That depends on the row_factory being used. By default rows come back as namedtuples:

http://datastax.github.io/python-driver/api/cassandra/query.html#cassandra.query.named_tuple_factory

Adam

To unsubscribe from this group and stop receiving emails from it, send an email to python-driver-u...@lists.datastax.com.