#!/usr/bin/env python
# coding: utf-8

# # An Introduction to python-fmrest (dotfmp demo)

# python-fmrest is a wrapper around the FileMaker Data API.
# 
# No need to worry about manually requesting access tokens, setting the right http headers, parsing responses, ...

# ## Use cases

# Some things you may use the python-fmrest library for:
# 
# - Build a backend for a web app that works with FileMaker data
# - Use python-fmrest together with a rest framework to build your own data API as middleware  
#  (so that you don't expose the whole FM data API to a third party, but only allowed endpoints/actions)
# - Explore your FileMaker data with data analysis tools from the Python ecosystem
# - Anything else you could do in the past with the CWP/XML API

# ## Installation (get you up and running quickly)

# If you haven't worked with Python and Virtualenvs before:
# 
# - `brew install python3`
#  - No brew? `/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"`)
# - `pip3 install virtualenv`
# 
# If you have worked with Python and Virtualenvs before, or after executing the steps above:
# 
# - ```virtualenv venv --python=`which python3` ```
# - `source venv/bin/activate`
# - `pip install python-fmrest`

# Link to this notebook: https://github.com/davidhamann/python-fmrest/tree/master/examples/conf_dotfmp_2018.ipynb

# ## The demo setup

# - FileMaker Server 17, with Data API enabled, in a VM
# - Hosted Database called "Planets"
#  - incl. account with `fmrest` extended privilege
# - Example code running in a Jupyter Notebook with a Python 3.6 kernel
#  - Not necessarily needed, but nice for exploration and presentation (mixing code and annotations)
# - Installed python-fmrest library

# ## Import module

# In[ ]:


import fmrest


# In[ ]:


fmrest.__version__


# ## Create server instance

# In[ ]:


fms = fmrest.Server(
    'https://dotfmp-demo.davidhamann.de',
    user='admin',
    password='admin',
    database='planets',
    layout='Planets',
    # if you are testing without cert/domain you may need the parameter verify_ssl=False here.
)


# This gives you a server instance which provides all further methods to interact with the Data API.

# In[ ]:


fms


# ## Login

# Obtain a token from FMS:

# In[ ]:


fms.login()


# ## Get records and access field and portal data

# List all records from the Planets table

# In[ ]:


planets = fms.get_records()
for planet in planets:
    print(f'{planet.id}, {planet.record_id}, {planet.name}')


# In[ ]:


type(planets), planets


# Look at (some of) the moons of Jupiter (list records of a portal)

# In[ ]:


record = fms.get_record(5, portals=[{'name': 'moons', 'limit': 5}])

portal = record['portal_moons']
record, portal


# Fetching a record always gives you a Record instance. The portal rows, however, are returned as a Foundset.

# In[ ]:


for row in portal:
    print(row['Moons::name'])


# You can inspect what fields are available:

# In[ ]:


record.keys()


# In[ ]:


record.values()


# In[ ]:


record.to_dict()


# And access the value by attribute or key:

# In[ ]:


record.name, record['atmosphere']


# So far we have seen Server, Foundset, Record. These are the main classes you need to be aware of when working with the library.

# ## Find records

# In[ ]:


find_request = [{'name': 'Earth'}, {'name': 'Jupiter'}]
foundset = fms.find(query=find_request)

earth = foundset[0]
earth


# ## Edit a record

# In[ ]:


earth.name = 'Blue Dot'
earth


# In[ ]:


fms.edit(earth)


# Handle outdated record values:

# In[ ]:


# change back
earth.name = 'Earth'
fms.edit(earth, validate_mod_id=False)


# ## Create a record

# In[ ]:


pluto = fms.create_record({'name': 'Pluto', 'id': 9})
pluto


# ## Delete a record

# In[ ]:


fms.delete_record(pluto)


# ## Performing scripts (new in v17)

# In[ ]:


fms.get_record(
    1,
    scripts={
        'after': ['say_hello', 'dotfmp']
    }
)
fms.last_script_result


# In[ ]:


fms.last_script_result['after'][1]


# ## Uploading container data (new in v17)

# In[ ]:


with open('../scratch/dotfmp_logo.png', 'rb') as image:
    result = fms.upload_container(3, 'image', image) # upload dotfmp logo into field with name "image" of record 3
result


# Now retrieve the image again:

# In[ ]:


earth = fms.get_record(3)
earth.image


# In[ ]:


name, type_, length, response = fms.fetch_file(earth.image)
name, type_, length


# In[ ]:


from IPython.display import Image
Image(response.content)


# ## Exceptions

# In[ ]:


find_request = [{'name': 'something that doesn\'t exist'}]
foundset = fms.find(query=find_request)


# ## Foundset into DataFrame

# Turn Foundset into a Pandas DataFrame to do statistical analyses on your dataset, work with missing data, reshape/pivot, perform joins/merges, plot with matplotlib, export, etc.

# In[ ]:


foundset = fms.get_records()
df = foundset.to_df()
df.loc[:, df.columns != 'image']


# In[ ]:


df[['name', 'atmosphere', 'rings', 'confirmed_moons', 'mass']].set_index('name').T


# In[ ]:


df.describe()


# ... or plot some data with matplotlib

# In[ ]:


get_ipython().run_line_magic('matplotlib', 'notebook')
df.plot(x='name', y='confirmed_moons')


# ... or export the data in a different format

# In[ ]:


path = 'data.csv'
df.to_csv(path, sep=";", index=False)
from IPython.display import FileLink
FileLink(path)


# Read about Pandas here: https://pandas.pydata.org

# ## More on python-fmrest

# - **More examples**: https://github.com/davidhamann/python-fmrest/tree/master/examples
# - GitHub: https://github.com/davidhamann/python-fmrest (MIT License)
# - v16 to v17 switch: https://davidhamann.de/2018/05/15/python-fmrest-and-filemaker-17-data-api/
# - Support and issues: https://github.com/davidhamann/python-fmrest/issues

# ## More on me

# - https://davidhamann.de/conference/
# - https://twitter.com/d_hamann

# # Any questions? Ask now or later.