{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "from cartoframes.auth import Credentials\n", "from cartoframes.data.clients import SQLClient" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "credentials = Credentials(username='cartovl', api_key='default_public')\n", "\n", "sql = SQLClient(credentials)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Query" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[{'adm0name': 'Belize',\n", " 'pop_max': 15220,\n", " 'the_geom': '0101000020E610000048F259B9173156C091EB964485403140'}]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql.query('SELECT adm0name, pop_max, the_geom FROM populated_places LIMIT 1')" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'rows': [{'adm0name': 'Belize',\n", " 'pop_max': 15220,\n", " 'the_geom': '0101000020E610000048F259B9173156C091EB964485403140'}],\n", " 'time': 0.004,\n", " 'fields': {'adm0name': {'type': 'string', 'pgtype': 'text'},\n", " 'pop_max': {'type': 'number', 'pgtype': 'int4'},\n", " 'the_geom': {'type': 'geometry',\n", " 'wkbtype': 'Unknown',\n", " 'dims': 2,\n", " 'srid': 4326}},\n", " 'total_rows': 1}" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql.query('SELECT adm0name, pop_max, the_geom FROM populated_places LIMIT 1', verbose=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Execute" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "scrolled": true }, "outputs": [], "source": [ "# sql.execute('SELECT * FROM populated_places')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Helpers" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('City', 7122), ('Capital', 200)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql.distinct('populated_places', 'adm_0_cap_name')" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "7322" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql.count('populated_places')" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[[-16.2500006525, 28.0999760122], [2.65424597028, 43.530016092]]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql.bounds('SELECT * FROM populated_places WHERE adm0name = \\'Spain\\'')" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Column name Column type \n", "-------------------------------------\n", "cartodb_id number \n", "the_geom geometry \n", "the_geom_webmercator geometry \n", "adm0name string \n", "adm1name string \n", "scalerank number \n", "natscale number \n", "labelrank number \n", "featurecla string \n", "name string \n", "namepar string \n", "namealt string \n", "diffascii number \n", "nameascii string \n", "adm0cap number \n", "capalt number \n", "capin string \n", "worldcity number \n", "megacity number \n", "sov0name string \n", "sov_a3 string \n", "adm0_a3 string \n", "iso_a2 string \n", "note string \n", "latitude number \n", "longitude number \n", "changed number \n", "namediff number \n", "diffnote string \n", "pop_max number \n", "pop_min number \n", "pop_other number \n", "rank_max number \n", "rank_min number \n", "geonameid number \n", "meganame string \n", "ls_name string \n", "ls_match number \n", "checkme number \n", "created_at date \n", "updated_at date \n", "adm_0_cap string \n", "adm_0_cap_name string \n" ] } ], "source": [ "sql.schema('populated_places')" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'cartodb_id': 'number',\n", " 'the_geom': 'geometry',\n", " 'the_geom_webmercator': 'geometry',\n", " 'adm0name': 'string',\n", " 'adm1name': 'string',\n", " 'scalerank': 'number',\n", " 'natscale': 'number',\n", " 'labelrank': 'number',\n", " 'featurecla': 'string',\n", " 'name': 'string',\n", " 'namepar': 'string',\n", " 'namealt': 'string',\n", " 'diffascii': 'number',\n", " 'nameascii': 'string',\n", " 'adm0cap': 'number',\n", " 'capalt': 'number',\n", " 'capin': 'string',\n", " 'worldcity': 'number',\n", " 'megacity': 'number',\n", " 'sov0name': 'string',\n", " 'sov_a3': 'string',\n", " 'adm0_a3': 'string',\n", " 'iso_a2': 'string',\n", " 'note': 'string',\n", " 'latitude': 'number',\n", " 'longitude': 'number',\n", " 'changed': 'number',\n", " 'namediff': 'number',\n", " 'diffnote': 'string',\n", " 'pop_max': 'number',\n", " 'pop_min': 'number',\n", " 'pop_other': 'number',\n", " 'rank_max': 'number',\n", " 'rank_min': 'number',\n", " 'geonameid': 'number',\n", " 'meganame': 'string',\n", " 'ls_name': 'string',\n", " 'ls_match': 'number',\n", " 'checkme': 'number',\n", " 'created_at': 'date',\n", " 'updated_at': 'date',\n", " 'adm_0_cap': 'string',\n", " 'adm_0_cap_name': 'string'}" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql.schema('populated_places', raw=True)" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count 7.32e+03 \n", "type: string\n" ] } ], "source": [ "sql.describe('populated_places', 'adm0name')" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count 7.32e+03 \n", "avg 3.23e+05 \n", "min -9.90e+01 \n", "max 3.57e+07 \n", "type: number\n" ] } ], "source": [ "sql.describe('populated_places', 'pop_max')" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "# sql.create_table('test', [('id', 'INT'), ('name', 'TEXT')]) # cartodbfy=False\n", "# sql.insert_table('test', ['id', 'name'], [0, 'a'])\n", "# sql.update_table('test', 'name', 'b', 'id = 0')\n", "# sql.rename_table('test', 'new_test')\n", "# sql.drop_table('new_test')" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.7" } }, "nbformat": 4, "nbformat_minor": 2 }