sys.exit(1)
# Create and cartodbfy a table
sqlClient = SQLClient(auth_client)
sqlClient.send("""
CREATE TABLE IF NOT EXISTS copy_example (
the_geom geometry(Geometry,4326),
name text,
age integer
)
""")
sqlClient.send("SELECT CDB_CartodbfyTable(current_schema, 'copy_example')")
copyClient = CopySQLClient(auth_client)
# COPY FROM example
logger.info("COPY'ing FROM file...")
query = ('COPY copy_example (the_geom, name, age) '
'FROM stdin WITH (FORMAT csv, HEADER true)')
result = copyClient.copyfrom_file_path(query, 'files/copy_from.csv')
logger.info('result = %s' % result)
# COPY TO example
query = 'COPY copy_example TO stdout WITH (FORMAT csv, HEADER true)'
output_file = 'files/copy_export.csv'
copyClient.copyto_file_path(query, output_file)
logger.info('Table copied to %s' % output_file)
# Truncate the table to make this example repeatable
sqlClient.send('TRUNCATE TABLE copy_example RESTART IDENTITY')
# Create and cartodbfy a table
sqlClient = SQLClient(auth_client)
sqlClient.send("""
CREATE TABLE IF NOT EXISTS copy_example (
the_geom geometry(Geometry,4326),
name text,
age integer
)
""")
sqlClient.send("SELECT CDB_CartodbfyTable(current_schema, 'copy_example')")
copyClient = CopySQLClient(auth_client)
# COPY FROM example
logger.info("COPY'ing FROM file...")
query = ('COPY copy_example (the_geom, name, age) '
'FROM stdin WITH (FORMAT csv, HEADER true)')
result = copyClient.copyfrom_file_path(query, 'files/copy_from.csv')
logger.info('result = %s' % result)
# COPY TO example with pandas DataFrame
logger.info("COPY'ing TO pandas DataFrame...")
query = 'COPY copy_example TO stdout WITH (FORMAT csv, HEADER true)'
result = copyClient.copyto_stream(query)
df = pd.read_csv(result)
logger.info(df.head())
# Truncate the table to make this example repeatable
sqlClient.send('TRUNCATE TABLE copy_example RESTART IDENTITY')
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)