{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Python More Examples"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
""
],
"text/plain": [
""
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import addutils.toc ; addutils.toc.js(ipy_notebook=True)"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n"
],
"text/plain": [
""
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import os\n",
"import numpy as np\n",
"import pandas as pd\n",
"from addutils import css_notebook\n",
"css_notebook()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 1 Working with Excel "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"XlsxWriter is a Python module for creating Excel XLSX files."
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import xlsxwriter"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let’s start by creating a simple spreadsheet using Python and the XlsxWriter module."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"current_dir = os.path.abspath(os.path.curdir)\n",
"file_path = os.path.join(current_dir, 'tmp', 'my_excel_file.xlsx')\n",
"workbook = xlsxwriter.Workbook(file_path)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now we add a spreadsheet. If we pass a string we can specify the name of the sheet."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"worksheet = workbook.add_worksheet('My First Try')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Cells can be written with any value, and can be referenced either with row and column or with an identifier like 'D3'. Remember that positional reference is zero based."
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"0"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"worksheet.write(0, 0, 1024)\n",
"worksheet.write('C2', 'Some Text')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"How to assign numerical values and formulas to cells (notice that formulas are assigned as strings):"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"0"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"worksheet.write(5, 4, 12)\n",
"worksheet.write(6, 4, 15)\n",
"worksheet.write(7, 4, '=E6+E7')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It is also possible to format cells:"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"# Add a bold format to use to highlight cells.\n",
"bold = workbook.add_format({'bold': True})"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"and use it to format a cell:"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"0"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"worksheet.write(7, 3, 'Total:', bold)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It is also possible to format the size of the cell:"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"0"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Adjust the column width.\n",
"worksheet.set_column('D:E', 25)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Closing the workbook, saves the .xslx file."
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"workbook.close()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The file can be read with pandas:"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"a = pd.read_excel(file_path)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"as you can see the first column is interpreted as a column name in pandas. It is possible to write data to excel directly from pandas using the same engine (xlsxwriter)"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"# Specify a writer\n",
"writer = pd.ExcelWriter(file_path, engine='xlsxwriter')\n",
"# Create some data\n",
"expenses = {key: np.random.randint(100, size=10) for key in ['Rent', 'Gas', 'Food', 'Gym']}\n",
"expenses = pd.DataFrame(expenses)\n",
"# Write your DataFrame to a file \n",
"expenses.to_excel(writer, 'Sheet1', index=False)\n",
"# Save the result \n",
"writer.save()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 2 Sending an e-mail from a gmail account"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This example is about sending mails using Python smtplib through an SMTP server."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"sender = 'ACCOUNT E-MAIL'\n",
"password = 'PASSWORD'\n",
"recipient = 'RECIPIENT'\n",
"subject = 'A mail using Python'\n",
"body = 'This is the body of the message'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**NOTE**: If you want to use Gmail, please consider that it has strong security mechanism and it may block your mail. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The smtplib module defines an SMTP client session object that can be used to send mail to any Internet machine with an SMTP or ESMTP listener daemon.\n",
"The first step is to create a SMTP connection to the server. The `smtplib.SMTP` class encapsulates an SMTP connection. If the optional host and port parameters are given, the SMTP `connect()` method is called with those parameters during initialization. An `SMTPConnectError` is raised if the specified host doesn’t respond correctly. The optional timeout parameter specifies a timeout in seconds for blocking operations like the connection attempt (if not specified, the global default timeout setting will be used).\n",
"\n",
"Next we will identify ourself to an ESMTP server using HELO. The SMTP HELO clause is the stage of the SMTP protocol where SMTP servers get introduced each other. EHLO is just like HELO except that the server’s response text provides computer-readable information about the server’s abilities.\n",
"\n",
"Remember Google’s SMTP server is ‘smtp.gmail.com’ and the port is 587."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import smtplib\n",
"session = smtplib.SMTP('smtp.gmail.com', 587)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Next we call `SMTP.starttls` function to put the SMTP connection in TLS (Transport Layer Security) mode. All SMTP commands that follow will be encrypted. You should then call `ehlo()` again. If keyfile and certfile are provided, these are passed to the socket module’s `ssl()` function.If there has been no previous EHLO or HELO command this session, this method tries ESMTP EHLO first."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"session.starttls()\n",
"session.ehlo()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"OK, now we are safe to login to the server using SMTP.login(user, password). After successful login we use SMTP.sendmail(from_addr, to_addrs, msg[, mail_options, rcpt_options]) to send mails via Gmail."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"session.login(sender, password)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"headers = [\"from: \" + sender,\n",
" \"subject: \" + subject,\n",
" \"to: \" + recipient,\n",
" \"mime-version: 1.0\",\n",
" \"content-type: text/html\"]\n",
"headers = \"\\r\\n\".join(headers)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"session.sendmail(sender, recipient, headers + \"\\r\\n\\r\\n\" + body)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 3 Automating DuckDuckGo search"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Using duckduckgo API is possible to query duckduckgo search engine. Say we are going to search \"barack obama\""
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import json\n",
"import urllib\n",
"\n",
"searchfor = 'barack obama'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To do this we need to create a specially crafted url to pass to the google api. `urllib` is a standard `python` library that allows to create and retrive URL(s).\n"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"query = urllib.parse.urlencode({'q': searchfor})\n",
"url = 'http://api.duckduckgo.com/?format=json&{}'.format(query)\n",
"search_response = urllib.request.urlopen(url)\n",
"search_results = search_response.read()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now we have a json encoded string containing our results, we are going to use `json.loads` in order to get what we are interested to (say Total results and top hits)."
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Related Topics: 34\n",
"Total results: 0\n",
"Top 0 hits:\n"
]
}
],
"source": [
"results = json.loads(search_results.decode(encoding='UTF-8'))\n",
"related_topics = results['RelatedTopics']\n",
"hits = results['Results']\n",
"print('Related Topics: {}'.format(len(related_topics)))\n",
"print('Total results: {}'.format(len(hits)))\n",
"print('Top {} hits:'.format(len(hits)))\n",
"for h in hits:\n",
" print(' ', h['FirstURL'])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"---\n",
"\n",
"Visit [www.add-for.com]() for more tutorials and updates.\n",
"\n",
"This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License."
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python [conda env:addfor_tutorials]",
"language": "python",
"name": "conda-env-addfor_tutorials-py"
},
"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.4"
}
},
"nbformat": 4,
"nbformat_minor": 1
}