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[]
