def test_batch_multi_sql(api_key_auth_client_usr):
sql = BatchSQLClient(api_key_auth_client_usr)
# Create query
data = sql.create(BATCH_SQL_MULTI_QUERY)
# Update status
job_id = data['job_id']
# Cancel if not finished
try:
confirmation = sql.cancel(job_id)
except CartoException:
pass
else:
assert confirmation == 'cancelled'
class CARTOUser(object):
def __init__(self,
user_name=None,
org_name=None,
api_url=None,
api_key=None,
check_ssl=True):
self.user_name = user_name
self.org_name = org_name
self.api_url = api_url
self.api_key = api_key
if not check_ssl:
old_request = requests.Session.request
requests.Session.request = partialmethod(old_request, verify=False)
warnings.filterwarnings('ignore', 'Unverified HTTPS request')
def initialize(self):
if not self.api_url and self.user_name:
self.api_url = "https://{}.carto.com/api/".format(self.user_name)
elif not self.api_url and not self.user_name:
raise Exception(
'Not enough data provided to initialize the client')
if self.org_name:
self.client = APIKeyAuthClient(self.api_url, self.api_key,
self.org_name)
else:
self.client = APIKeyAuthClient(self.api_url, self.api_key)
self.sql_client = SQLClient(self.client)
self.batch_client = BatchSQLClient(self.client)
def execute_sql(self, query, parse_json=True, format=None, do_post=False):
try:
try:
self.client
except AttributeError:
self.initialize()
return self.sql_client.send(query,
parse_json=parse_json,
format=format,
do_post=do_post)
except CartoException as e:
raise Exception(e.args[0].args[0][0])
def batch_check(self, job_id):
try:
self.batch_client
except AttributeError:
self.initialize()
return self.batch_client.read(job_id)
def batch_create(self, query):
try:
self.batch_client
except AttributeError:
self.initialize()
return self.batch_client.create(query)
def batch_cancel(self, job_id):
try:
self.batch_client
except AttributeError:
self.initialize()
return self.batch_client.cancel(job_id)
def get_dataset_manager(self):
try:
self.sql_client
except AttributeError:
self.initialize()
return DatasetManager(self.client)
def get_sync_manager(self):
try:
self.sql_client
except AttributeError:
self.initialize()
return SyncTableJobManager(self.client)
def upload(self, uri, sync_time=None):
try:
self.sql_client
except AttributeError:
self.initialize()
dataset_manager = DatasetManager(self.client)
if sync_time:
return dataset_manager.create(uri, sync_time)
else:
return dataset_manager.create(uri)
class CARTOUser(object):
def __init__(self,
user_name=None,
org_name=None,
api_url=None,
api_key=None,
check_ssl=True):
self.user_name = user_name
self.org_name = org_name
self.api_url = api_url
self.api_key = api_key
if not check_ssl:
old_request = requests.Session.request
requests.Session.request = partialmethod(old_request, verify=False)
warnings.filterwarnings('ignore', 'Unverified HTTPS request')
def initialize(self):
if not self.api_url and self.user_name:
self.api_url = "https://{}.carto.com/api/".format(self.user_name)
elif not self.api_url and not self.user_name:
raise Exception(
'Not enough data provided to initialize the client')
if self.org_name:
self.client = APIKeyAuthClient(self.api_url, self.api_key,
self.org_name)
else:
self.client = APIKeyAuthClient(self.api_url, self.api_key)
self.sql_client = SQLClient(self.client)
self.batch_client = BatchSQLClient(self.client)
self.copy_client = CopySQLClient(self.client)
def execute_sql(self, query, parse_json=True, format=None, do_post=False):
try:
try:
self.client
except AttributeError:
self.initialize()
return self.sql_client.send(query,
parse_json=parse_json,
format=format,
do_post=do_post)
except CartoException as e:
raise Exception(e.args[0].args[0][0])
def batch_check(self, job_id):
try:
self.batch_client
except AttributeError:
self.initialize()
return self.batch_client.read(job_id)
def batch_create(self, query):
try:
self.batch_client
except AttributeError:
self.initialize()
return self.batch_client.create(query)
def batch_cancel(self, job_id):
try:
self.batch_client
except AttributeError:
self.initialize()
return self.batch_client.cancel(job_id)
def get_dataset_manager(self):
try:
self.sql_client
except AttributeError:
self.initialize()
return DatasetManager(self.client)
def get_sync_manager(self):
try:
self.sql_client
except AttributeError:
self.initialize()
return SyncTableJobManager(self.client)
def upload(self, uri, sync_time=None):
try:
self.sql_client
except AttributeError:
self.initialize()
dataset_manager = DatasetManager(self.client)
if sync_time:
return dataset_manager.create(uri, sync_time)
else:
return dataset_manager.create(uri)
def copy_from(self, path, query, tablename=None, delimiter=','):
try:
self.copy_client
except AttributeError:
self.initialize()
if tablename is None:
tablename = Path(path).stem
if query is None:
with open(path, 'rb') as myfile:
headers = next(myfile).strip().decode('utf8')
query = f"""COPY {tablename} ({headers}) FROM stdin
(FORMAT CSV, DELIMITER '{delimiter}', HEADER false, QUOTE '"')"""
return self.copy_client.copyfrom_file_object(query, myfile)
return self.copy_client.copyfrom_file_path(query, path)
def copy_to(self, query, output, delimiter=','):
try:
self.copy_client
except AttributeError:
self.initialize()
copy_query = f"""COPY ({query}) TO stdout WITH
(FORMAT CSV, DELIMITER '{delimiter}', HEADER true, QUOTE '"')"""
return self.copy_client.copyto_file_path(copy_query, output)
' (defaults to env variable CARTO_API_KEY)')
args = parser.parse_args()
# Set authentification to CARTO
auth_client = APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY,
args.organization)
batchSQLClient = BatchSQLClient(auth_client)
# Batch SQL API operations
if args.operation == 'create':
# create a batch api job
createJob = batchSQLClient.create(args.query)
for a, b in createJob.items():
logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'read':
readJob = batchSQLClient.read(args.job_id)
for a, b in readJob.items():
logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'update':
updateJob = batchSQLClient.update(args.job_id, args.query)
for a, b in updateJob.items():
logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'cancel':
cancelJob = batchSQLClient.cancel(args.job_id)
for a, b in cancelJob.items():
logger.info('{key}: {value}'.format(key=a, value=b))
else:
logger.info("You have not written a correct operation option")
# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
auth_client = APIKeyAuthClient(
args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
batchSQLClient = BatchSQLClient(auth_client)
else:
logger.error('You need to provide valid credentials, run with -h parameter for details')
import sys
sys.exit(1)
# Batch SQL API operations
if args.operation == 'create':
# create a batch api job
createJob = batchSQLClient.create(args.query)
for a, b in createJob.items():
logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'read':
readJob = batchSQLClient.read(args.job_id)
for a, b in readJob.items():
logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'update':
updateJob = batchSQLClient.update(args.job_id, args.query)
for a, b in updateJob.items():
logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'cancel':
cancelJob = batchSQLClient.cancel(args.job_id)
for a, b in cancelJob.items():
logger.info('{key}: {value}'.format(key=a, value=b))
else:
logger.info("You have not written a correct operation option")