def next(self, key: str, doc: dict, replace_targets: dict = None) -> Tuple[str, QueryOptions]: statement, args, scan_consistency, ad_hoc = next(self.queries) if replace_targets: for bucket in replace_targets.keys(): bucket_substring = "`{}`".format(bucket) for i in range(statement.count(bucket_substring)): where = [ m.start() for m in re.finditer(bucket_substring, statement) ][i] before = statement[:where] after = statement[where:] scope, collection = replace_targets[bucket][i].split(":") replace_target = "default:`{}`.`{}`.`{}`".format( bucket, scope, collection) after = after.replace(bucket_substring, replace_target) statement = before + after if 'key' in args: args = [key] else: args = args.format(**doc) args = eval(args) query_opts = QueryOptions( adhoc=bool(ad_hoc), scan_consistency=self.scan_consistency(scan_consistency), positional_parameters=args) return statement, query_opts
def get_user_info(self, username): try: n1ql = """ SELECT c.custId, u.userId, u.username, u.`password` FROM `{0}` u JOIN `{0}` c ON c.username = u.username AND c.doc.type = 'customer' WHERE u.docType ='user' AND u.username = $1 ORDER BY u.userId DESC LIMIT 1; """.format(self.bucket_name) params = [username] opts = QueryOptions(positional_parameters=params) result = self.__cluster.query(n1ql, opts) user = next((u for u in result.rows() if u is not None), None) return {'user_info': user, 'error': None} except Exception as ex: output_message(ex, 'repository.py:get_user_info() - error:') return { 'user_info': None, 'error': { 'message': repr(ex), 'stackTrace': traceback.format_exc() } }
def test_scope_query_with_metrics(self): scope = self.bucket.scope(self.beer_sample_collections.scope) initial = datetime.datetime.now() result = scope.query("SELECT * FROM breweries LIMIT 1", QueryOptions(metrics=True)) self.assertRows(result, 1) taken = datetime.datetime.now() - initial metadata = result.metadata() # type: QueryMetaData metrics = metadata.metrics() self.assertIsInstance(metrics.elapsed_time(), datetime.timedelta) self.assertLess(metrics.elapsed_time(), taken) self.assertGreater(metrics.elapsed_time(), datetime.timedelta(milliseconds=0)) self.assertLess(metrics.elapsed_time(), taken) self.assertGreater(metrics.execution_time(), datetime.timedelta(milliseconds=0)) expected_counts = { metrics.mutation_count: 0, metrics.result_count: 1, metrics.sort_count: 0, metrics.warning_count: 0 } for method, expected in expected_counts.items(): count_result = method() fail_msg = "{} failed".format(method) self.assertIsInstance(count_result, UnsignedInt64, msg=fail_msg) self.assertEqual(UnsignedInt64(expected), count_result, msg=fail_msg) self.assertGreater(metrics.result_size(), UnsignedInt64(500)) self.assertEqual(UnsignedInt64(0), metrics.error_count()) self.assertIsNone(metadata.profile())
def test_simple_query_prepared(self): result = self.cluster.query( "SELECT * FROM `beer-sample` LIMIT 2", QueryOptions(adhoc=False, metrics=True)) # type: QueryResult self.assertRows(result, 2) self.assertIsNone(result.metadata().profile()) self.assertFalse(result._params._adhoc)
def lookup_by_callsign(cs): print("\nLookup Result: ") try: sql_query = 'SELECT VALUE name FROM `travel-sample` WHERE type = "airline" AND callsign = $1' row_iter = cluster.query(sql_query, QueryOptions(positional_parameters=[cs])) for row in row_iter: print(row) except Exception as e: print(e)
def test_cluster_query_context(self): q_context = '{}.{}'.format(self.bucket_name, self.beer_sample_collections.scope) # test with QueryOptions q_opts = QueryOptions(query_context=q_context, adhoc=True) result = self.cluster.query("SELECT * FROM beers LIMIT 2", q_opts) self.assertRows(result, 2) # test with kwargs result = self.cluster.query("SELECT * FROM beers LIMIT 2", query_context=q_context) self.assertRows(result, 2)
def test_bad_query_context(self): # test w/ no context result = self.cluster.query("SELECT * FROM beers LIMIT 2") with self.assertRaises(KeyspaceNotFoundException): result.rows() # test w/ bad scope q_context = '{}.{}'.format(self.bucket_name, 'fake-scope') result = self.cluster.query("SELECT * FROM beers LIMIT 2", QueryOptions(query_context=q_context)) with self.assertRaises(ScopeNotFoundException): result.rows()
async def n1ql_query(cluster): try: result = cluster.query( "SELECT h.* FROM `travel-sample`.inventory.hotel h WHERE h.country=$country LIMIT 10", QueryOptions(named_parameters={"country": "United Kingdom"})) async for row in result: print("Found row: {}".format(row)) except ParsingFailedException as ex: print(ex) except CouchbaseException as ex: print(ex)
def test_bad_scope_query(self): scope = self.bucket.scope(self.beer_sample_collections.scope) q_context = '{}.{}'.format(self.bucket_name, 'fake-scope') result = scope.query("SELECT * FROM beers LIMIT 2", QueryOptions(query_context=q_context)) with self.assertRaises(ScopeNotFoundException): result.rows() q_context = '{}.{}'.format('fake-bucket', self.beer_sample_collections.scope) result = scope.query("SELECT * FROM beers LIMIT 2", query_context=q_context) with self.assertRaises(KeyspaceNotFoundException): result.rows()
def update_status(self): for build in self.weekly.builds: logger.info('Updating status of build {}'.format(build)) for status in self.cluster.query( self.STATUS_QUERY, QueryOptions(positional_parameters=build)): status = { 'build': build, 'component': status['component'], 'metric_status': { 'collected': status['total'], }, } self.weekly.update_status(status)
def get_orders(self, customer_id): ''' Lab 4: N1QL operations 1. Get orders for customerId - WHERE order.orderStatus != 'created' - Document properties needed (more can be provided): id, orderStatus, shippingInfo.name aliased as shippedTo, grandTotal, lineItems, orderDate (hint use MILLIS_TO_STR()) ''' try: n1ql = """ SELECT META(o).id, o.orderStatus, o.shippingInfo.name AS shippedTo, o.grandTotal, o.lineItems, MILLIS_TO_STR(o.orderDate) AS orderDate FROM `{0}` o WHERE o.doc.type = 'order' AND o.custId=$1 ORDER BY o.orderDate DESC NULLS FIRST """.format(self.bucket_name) params = [customer_id] # TODO: adhoc, add adhoc=True to QueryOptions opts = QueryOptions(positional_parameters=params) result = self.__cluster.query(n1ql, opts) return {'orders': result.rows(), 'error': None} except Exception as ex: output_message(ex, 'repository.py:get_orders() - error:') return { 'order': False, 'error': { 'message': repr(ex), 'stackTrace': traceback.format_exc() } }
def get_new_order(self, customer_id): ''' Lab 4: N1QL operations 1. Get latest order for customerId - WHERE order.orderStatus = 'created' - Document properties needed (more can be provided): doc, custId, orderStatus, billingInfo, shippingInfo, shippingTotal, tax, lineItems, grandTotal, orderId, _id ''' try: n1ql = """ SELECT o.doc, o.custId, o.orderStatus, o.billingInfo, o.shippingInfo, o.shippingTotal, o.tax, o.lineItems, o.grandTotal, o.orderId, o._id FROM `{0}` o WHERE o.doc.type = 'order' AND o.custId=$1 AND o.orderStatus = 'created' ORDER BY o.orderDate DESC NULLS FIRST LIMIT 1; """.format(self.bucket_name) params = [customer_id] # TODO: adhoc, add adhoc=True to QueryOptions opts = QueryOptions(positional_parameters=params) result = self.__cluster.query(n1ql, opts) return {'order': result.rows(), 'error': None} except Exception as ex: output_message(ex, 'repository.py:get_new_order() - error:') return { 'order': False, 'error': { 'message': repr(ex), 'stackTrace': traceback.format_exc() } }
def test_scope_query_with_named_params_in_options(self): scope = self.bucket.scope(self.beer_sample_collections.scope) result = scope.query( "SELECT * FROM breweries WHERE META().id LIKE $brewery LIMIT 1", QueryOptions(named_parameters={'brewery': '21st_amendment%'})) self.assertRows(result, 1)
def test_mixed_named_parameters(self): result = self.cluster.query( "SELECT * FROM `beer-sample` WHERE brewery_id LIKE $brewery LIMIT 1", QueryOptions(named_parameters={'brewery': 'xxffqqlx'}), brewery='21st_am%') self.assertRows(result, 1)
def test_mixed_positional_parameters(self): # we assume that positional overrides one in the Options result = self.cluster.query( "SELECT * FROM `beer-sample` WHERE brewery_id LIKE $1 LIMIT 1", QueryOptions(positional_parameters=['xgfflq']), '21st_am%') self.assertRows(result, 1)
# Initialize the Connection cluster = Cluster('couchbases://' + endpoint + '?ssl=no_verify', ClusterOptions(PasswordAuthenticator(username, password))) cb = cluster.bucket(bucket_name) cb_coll = cb.default_collection() # Create a N1QL Primary Index (but ignore if it exists) cluster.query_indexes().create_primary_index(bucket_name, ignore_if_exists=True) # Store a Document cb_coll.upsert( 'u:king_arthur', { 'name': 'Arthur', 'email': '*****@*****.**', 'interests': ['Holy Grail', 'African Swallows'] }) # Load the Document and print it print(cb_coll.get('u:king_arthur').content_as[str]) # Perform a N1QL Query row_iter = cluster.query( 'SELECT name FROM %s WHERE $1 IN interests' % (bucket_name), QueryOptions(positional_parameters=['African Swallows'])) # Print each found Row for row in row_iter: print(row)
from couchbase.cluster import QueryScanConsistency # tag::n1ql_basic_example[] from couchbase.cluster import Cluster, ClusterOptions, QueryOptions from couchbase.auth import PasswordAuthenticator from couchbase.exceptions import CouchbaseException cluster = Cluster.connect( "couchbase://localhost", ClusterOptions(PasswordAuthenticator("Administrator", "password"))) bucket = cluster.bucket("travel-sample") collection = bucket.default_collection() try: result = cluster.query( "SELECT * FROM `travel-sample`.inventory.airport LIMIT 10", QueryOptions(metrics=True)) for row in result.rows(): print("Found row: {}".format(row)) print("Report execution time: {}".format( result.metadata().metrics().execution_time())) except CouchbaseException as ex: import traceback traceback.print_exc() # end::n1ql_basic_example[] # tag::positional[] result = cluster.query(
def test_simple_query_with_positional_params_in_options(self): result = self.cluster.query( "SELECT * FROM `beer-sample` WHERE brewery_id LIKE $1 LIMIT 1", QueryOptions(positional_parameters=['21st_amendment%'])) self.assertRows(result, 1)
# tag::simple_query[] from twisted.internet import reactor from txcouchbase.cluster import TxCluster from couchbase.cluster import ClusterOptions, QueryOptions from couchbase.auth import PasswordAuthenticator def handle_query_results(result): for r in result.rows(): print("query row: {}".format(r)) reactor.stop() cluster = TxCluster("couchbase://localhost", ClusterOptions(PasswordAuthenticator("Administrator", "password"))) # create a bucket object bucket = cluster.bucket("travel-sample") # create a collection object cb = bucket.default_collection() d = cluster.query("SELECT ts.* FROM `travel-sample` ts WHERE ts.`type`=$type LIMIT 10", QueryOptions(named_parameters={"type": "hotel"})) d.addCallback(handle_query_results) reactor.run() # end::simple_query[]
def find_builds(self, version: str) -> Iterator[dict]: for build in self.cluster.query( self.BUILD_QUERY, QueryOptions(positional_parameters=version)): yield build
def find_snapshots(self, url: str): for snapshots in self.cluster.query( self.SNAPSHOT_QUERY, QueryOptions(positional_parameters=url)): for snapshot in snapshots: yield snapshot
print (rv) #insert multiple documents user0002 = { "firstName" : "Major", "lastName" : "Tom", "email" : "[email protected](opens in new tab)", "tagLine" : "Send me up a drink", "type" : "user" } user0003 = { "firstName" : "Jerry", "lastName" : "Wasaracecardriver", "email" : "[email protected](opens in new tab)", "tagLine" : "el sob number one", "type" : "user" } rv = bucket.upsert_multi({'newDoc2':user0002, 'newDoc3':user0003}) print (rv) # first lookup using the key rv = bucket.get('newDoc2') print(rv.value) #first secondary lookup to find all users with email ending in @acme.com query_result = cluster.query('SELECT * FROM `default` WHERE email LIKE $email', QueryOptions(named_parameters={'email': "*****@*****.**"})) for row in query_result: print(row)
def test_simple_query_with_named_params_in_options(self): result = self.cluster.query( "SELECT * FROM `beer-sample` WHERE brewery_id LIKE $brewery LIMIT 1", QueryOptions(named_parameters={'brewery': '21st_amendment%'})) self.assertRows(result, 1)
"select * from bucket where type = $type", type="airport") # SDK 2 positional parameters bucket.query( "select * from bucket where type = $1", "airport") # end::queryparameterized_sdk2[] del bucket # tag::queryparameterized[] # SDK 3 named parameters from couchbase.cluster import QueryOptions cluster.query( "select * from bucket where type = $type", QueryOptions(named_parameters={"type": "airport"})) # SDK 3 positional parameters cluster.query( "select * from bucket where type = $1", QueryOptions(positional_parameters=["airport"])) #end::queryparameterized[] #tag::analyticssimple[] # SDK 3 simple analytics query analytics_result = cluster.analytics_query("select * from dataset") for value in analytics_result: #... pass #end::analyticssimple[]
def test_query_with_profile(self): result = self.cluster.query("SELECT * FROM `beer-sample` LIMIT 1", QueryOptions(profile=QueryProfile.TIMINGS)) self.assertRows(result, 1) self.assertIsNotNone(result.metadata().profile())
"couchbases://{}".format(endpoint), ClusterOptions( PasswordAuthenticator(username, password, cert_path=cert_path))) cb = cluster.bucket(bucket_name) cb_coll = cb.default_collection() # Create a N1QL Primary Index (but ignore if it exists) cluster.query_indexes().create_primary_index(bucket_name, ignore_if_exists=True) # Store a Document cb_coll.upsert( "u:king_arthur", { "name": "Arthur", "email": "*****@*****.**", "interests": ["Holy Grail", "African Swallows"] }) # Load the Document and print it print(cb_coll.get("u:king_arthur").content_as[str]) # Perform a N1QL Query row_iter = cluster.query( "SELECT cbc.* FROM {} cbc WHERE $1 IN cbc.interests".format(bucket_name), QueryOptions(positional_parameters=["African Swallows"])) # Print each found Row for row in row_iter.rows(): print("Found row: {}".format(row)) # end::cloud_connect[]