Monthly Archives: December 2018

Python and Excel

- - Python, Tutorials

I intend to host a set of examples on using python to interact and work with excel files. This article in particular will use openpyxl module in python throughout the examples.

Installing openpyxl

I am using python3 throughout the examples, however it should work similarly with python2. As a good development practice, we should segregate dependencies between various projects and therefore is advised to use virtual environment or some sort.

python3 -m venv excelenv
source excelenv/bin/activate
pip install openpyxl

Open an existing document in python
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> wb
<openpyxl.workbook.workbook.Workbook object at 0x7f010dce4240>
>>>
Selecting sheets from the workbook/document
>>> wb.get_sheet_names()
['Sheet1']
>>>
>>> sheet = wb.get_sheet_by_name("Sheet1")
>>> sheet
<Worksheet "Sheet1">
>>>
>>> sheet.max_column
3
>>> sheet.max_row
3
>>> sheet.min_row
1
>>> sheet.min_column
2
>>>
>>> sheet['A1'].value
'thetaranights.com'
>>> sheet['B1'].value
102345
>>> sheet['A2'].value
'thetaranights.com'
>>> sheet['B2'].value
123443
>>>
When the cell being accessed doesn’t have a value
>>> a = sheet['A4'].value
>>> a
>>> type(a)
<class 'NoneType'>
>>>

OpenPyXL will automatically interpret the types of the values in the cells of the sheet and return them as an object of that type. string , int, dates, etc.

>>> type(sheet['A3'].value)
<class 'str'>
>>> type(sheet['B3'].value)
<class 'int'>
Accessing cell values using row, column directive

Although we can access the values of the cell using alphabetic letters directive, we can also access them using row number and column numbers. An excel row and column starts at 1, not 0.

>>> sheet.cell(row=0, column=0)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/bhishan-1504/excelinpython/excelenv/lib/python3.6/site-packages/openpyxl/worksheet/worksheet.py", line 296, in cell
    raise ValueError("Row or column values must be at least 1")
ValueError: Row or column values must be at least 1
>>>

>>> sheet.cell(row=1, column=1)
<Cell 'Sheet1'.A1>
>>> sheet.cell(row=1, column=1).value
'thetaranights.com'
>>>
Reading values from excel sheet
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_sheet_by_name("Sheet1")
>>> for each_row in range(sheet.min_row, sheet.max_row + 1):
...     for each_col in range(sheet.min_column, sheet.max_column + 1):
...         print(sheet.cell(row=each_row, column=each_col).value)
...
thetaranights.com
102345
thetaranights.com
123443
thetaranights.com
102234
>>>
Creating excel document
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.get_sheet_names()
['Sheet']
>>> sheet = wb.get_sheet_by_name('Sheet')
>>> sheet
<Worksheet "Sheet">
>>> sheet.title = 'Custom Created worksheet'
>>> wb.get_sheet_names()
['Custom Created worksheet']
>>> sheet
<Worksheet "Custom Created worksheet">
>>> sheet['A1'] = 'thetaranights.com'
>>> sheet['A1'].value
'thetaranights.com'
>>>
>>> sheet.append(['thetaranights.com', '102948']) # adding rows to sheet
>>> wb.save('createdbyopenpyxl.xlsx')
>>>

Going serverless with Chalice and AWS lambda

- - Python, Tutorials

The intentions of this post is to host a simple example of chalice from AWS that allows serverless API creation with the use of AWS lambda. You also get auto-generation of IAM policy making it faster to deploy web applications. Chalice expects to pick the AWS credentials from ~/.aws/config

Prerequisites for chalice[AWS Credentials]

If you’ve used AWS API or boto/boto3 for python, you’ve probably already added the credentials in place. Otherwise you could do it as below:

mkdir ~/.aws
touch ~/.aws/config

Contents of the config file

[default]
aws_access_key_id = YOUR_ACCESS_KEY_HERE
aws_secret_access_key = YOUR_SECRET_ACCESS_KEY
region = YOUR_REGION

Installing Chalice

As a good development practice, we should segregate dependencies between various projects and therefore is advised to use virtual environment or some sort.

$ python3 -m venv serverlessenv
$ source serverlessenv/bin/activate

(serverlessenv)$ pip install chalice

Creating simple API with chalice

Chalice comes with command line tool. See what commands are available

(serverlessenv)$ chalice

Usage: chalice [OPTIONS] COMMAND [ARGS]...

Options:
--version Show the version and exit.
--project-dir TEXT The project directory. Defaults to CWD
--debug / --no-debug Print debug logs to stderr.
--help Show this message and exit.

Commands:
delete
deploy
gen-policy
generate-pipeline Generate a cloudformation template for a...
generate-sdk
invoke Invoke the deployed lambda function NAME.
local
logs
new-project
package
url

(serverlessenv)$ chalice new-project

___ _ _ _ _ ___ ___ ___
/ __|| || | /_\ | | |_ _|/ __|| __|
| (__ | __ | / _ \ | |__ | || (__ | _|
\___||_||_|/_/ \_\|____||___|\___||___|

The python serverless microframework for AWS allows
you to quickly create and deploy applications using
Amazon API Gateway and AWS Lambda.

Please enter the project name: basichelloworld

(serverlessenv)$ cd basichelloworld/

(serverlessenv)~/basichelloworld$ ls -a

app.py .chalice .gitignore requirements.txt

Chalice is very simple, somewhat similar to Flask if you come from there.

Auto-generated app.py
from chalice import Chalice

app = Chalice(app_name='basichelloworld')


@app.route('/')
def index():
    return {'hello': 'world'}


# The view function above will return {"hello": "world"}
# whenever you make an HTTP GET request to '/'.
#
# Here are a few more examples:
#
# @app.route('/hello/{name}')
# def hello_name(name):
#    # '/hello/james' -> {"hello": "james"}
#    return {'hello': name}
#
# @app.route('/users', methods=['POST'])
# def create_user():
#     # This is the JSON body the user sent in their POST request.
#     user_as_json = app.current_request.json_body
#     # We'll echo the json body back to the user in a 'user' key.
#     return {'user': user_as_json}
#
# See the README documentation for more examples.
#
Running the API locally

(serverlessenv)~/basichelloworld$ chalice local
Serving on 127.0.0.1:8000

(serverlessenv)~/basichelloworld$ curl -X GET http://127.0.0.1:8000
{"hello": "world"}

Going serverless with chalice through AWS lambda

(serverlessenv)~/basichelloworld$ chalice deploy
Creating deployment package.
Updating policy for IAM role: basichelloworld
Creating lambda function: basichelloworld
Creating Rest API
Resources deployed:
- Lambda ARN: arn:aws:lambda:us-east-1:9582857991:function:basichelloworld
- Rest API URL: https://fxcdyzuitc.execute-api.us-east-1.amazonaws.com/api/

(serverlessenv)~/basichelloworld$ curl -X GET https://fxcdyzuitc.execute-api.us-east-1.amazonaws.com/api/
{"hello": "world"}