May 10, 2014
TechnologyIn fact this is a migration from sqlite3 to postgresql.
View the historical sqlite3
We will refer to our own design of database. First fetch the data file, this is a sqlite3 file, so we use sqlite3 to view its structure.
$ sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open ./weather.db
sqlite> .tables
foo
sqlite> .schema foo
CREATE TABLE foo (d_temper integer, d_humi integer, d_pm10 integer, d_pm25 real, d_time timestamp);
Although sqlite3 is supported on heroku, we’d better use heroku’s suggestion, to use postgre for storing out database.
Create Database In Postgres
####Datatime selection:
Postgres provides a very fantanstic way for handling the datatime, it supports the timezone, comparing to GAE’s database, this feature will let us get the current time based on timezone. So we did the following tests:
# CREATE TABLE my_tbl (
mylocaldb(# my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
mylocaldb(# CHECK(EXTRACT(TIMEZONE FROM my_timestamp) = '0')
mylocaldb(# );
CREATE TABLE
mylocaldb=# \dt my_tbl
List of relations
Schema | Name | Type | Owner
--------+--------+-------+-------
public | my_tbl | table | Trusty
(1 row)
When we want to insert the datatime into table ‘my_tbl’, simply do following:
mylocaldb=# SET timezone = 'UTC';
SET
mylocaldb=# INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
INSERT 0 1
And for querying out the inserted records, we do following:
mylocaldb=# select * from public.my_tbl
;
my_timestamp
-------------------------------
2014-05-10 01:39:52.87532+00
2014-05-10 01:42:44.130269+00
(2 rows)
mylocaldb=# SET timezone='Asia/Shanghai';
SET
mylocaldb=# select * from public.my_tbl
mylocaldb-# ;
my_timestamp
-------------------------------
2014-05-10 09:39:52.87532+08
2014-05-10 09:42:44.130269+08
(2 rows)
mylocaldb=# SET timezone='America/Los_Angeles';
SET
mylocaldb=# select * from public.my_tbl;
my_timestamp
-------------------------------
2014-05-09 18:39:52.87532-07
2014-05-09 18:42:44.130269-07
(2 rows)
We can see the output formats depends on our “timezone” value.
We listed following table for describing the Data we inserted:
Timestamp integer integer integer integer
Insert_Time Temperature Humidity PMTen PMTwoFive
Thus the sql sentense is as following:
mylocaldb=# CREATE TABLE weather (
mylocaldb(# my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
mylocaldb(# Temperature ^C
mylocaldb=# CREATE TABLE weather (
mylocaldb(# Insert_Time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
mylocaldb(# Temperature integer,
mylocaldb(# Humidity integer,
mylocaldb(# PMTen integer,
mylocaldb(# PMTwoFive integer,
mylocaldb(# CHECK(EXTRACT(TIMEZONE FROM Insert_Time) = '0'));
Check the tables:
mylocaldb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | my_tbl | table | Trusty
public | user | table | Trusty
public | weather | table | Trusty
(3 rows)
Insert one record:
mylocaldb=# INSERT INTO weather (Insert_Time, Temperature, Humidity, PMTen, PMTwoFive) VALUES(NOW(), 25, 80, 150, 75);
INSERT 0 1
Displaying the inserted record:
mylocaldb=# SET timezone='Asia/Shanghai';
SET
mylocaldb=# select * from public.weather;
insert_time | temperature | humidity | pmten | pmtwofive
-------------------------------+-------------+----------+-------+-----------
2014-05-10 10:38:27.276043+08 | 25 | 80 | 150 | 75
(1 row)
Database Operation
We need to create just once database, So this function should be Check_Or_Create().
We need to insert records, so Insert_Record() should be written.
Other Operation, modification or delete shouldn’t care at the very beginning.
We will use a new file for recording all of the function.
The code for Create and Insert record into weather table is listed as following:
from sqlalchemy import create_engine
from sqlalchemy import MetaData, Column, Table, ForeignKey
from sqlalchemy import Integer, String, DateTime
import datetime
# Create the engine for connecting the local database
# How to connect the remote engine, heroku postgres?
# Yes, it's possible. The DATABASE_URL environment variable provided by heroku fits perfectly as argument for create_engine. Behind the scene, it's a postgresql database, which is perfectly handled by sqlalchemy.
#
# The way to do it may vary depending on the framework you use, but there shouldn't be any difficulty.
engine = create_engine('postgresql+psycopg2://Trusty:@localhost:5432/mylocaldb',echo=True)
metadata=MetaData(bind=engine)
# Definition of the weather table
weather_table=Table('weather',metadata,
Column('Insert_Time', DateTime(timezone=True),primary_key=True),
Column('Temperature', Integer),
Column('Humidity', Integer),
Column('PmTen', Integer),
Column('PmTwoFive', Integer),
)
# Create the table in mylocaldb
metadata.create_all(checkfirst=True)
# The actual SQL Language
#CREATE TABLE weather (
# "Insert_Time" TIMESTAMP WITH TIME ZONE NOT NULL,
# "Temperature" INTEGER,
# "Humidity" INTEGER,
# "PmTen" INTEGER,
# "PmTwoFive" INTEGER,
# PRIMARY KEY ("Insert_Time")
#)
# mylocaldb=# select * from public.weather;
# Insert_Time | Temperature | Humidity | PmTen | PmTwoFive
# -------------+-------------+----------+-------+-----------
# (0 rows)
# Record Insertion
# First generate an insertion sentense:
ins = weather_table.insert()
#>>> str(ins)
#'INSERT INTO weather ("Insert_Time", "Temperature", "Humidity", "PmTen", "PmTwoFive") VALUES (%(Insert_Time)s, %(Temperature)s, %(Humidity)s, %(PmTen)s, %(PmTwoFive)s)'
ins = weather_table.insert().values(Insert_Time=datetime.datetime.utcnow(), Temperature=25, Humidity=75, PmTen=100, PmTwoFive=55)
#>>> str(ins)
#'INSERT INTO weather ("Insert_Time", "Temperature", "Humidity", "PmTen", "PmTwoFive") VALUES (%(Insert_Time)s, %(Temperature)s, %(Humidity)s, %(PmTen)s, %(PmTwoFive)s)'
#
#>>> ins.compile().params
#{'PmTen': 100, 'PmTwoFive': 55, 'Temperature': 25, 'Insert_Time': datetime.datetime(2014, 5, 10, 5, 58, 21, 677234), 'Humidity': 75}
# Connect to engine and execute.
conn = engine.connect()
# >>> conn
# <sqlalchemy.engine.base.Connection object at 0x2715890>
result = conn.execute(ins)
# View result in psql
# mylocaldb=# select * from public.weather;
# Insert_Time | Temperature | Humidity | PmTen | PmTwoFive
# -------------------------------+-------------+----------+-------+-----------
# 2014-05-10 05:58:21.677234+08 | 25 | 75 | 100 | 55
The critical functions has been pointed out in the above python file. Now we will consider how to run these functions at background. This will lead to next topic, “Run multiple process in a single Heroku dyno”.
May 10, 2014
TechnologyRisk
On Google App Engine it’s very convinient to setup a crontab task, while in heroku setting up a crontab task will occupy the material, thus will use another dyno, each dyno will cost $30/month. For avoiding this, we will re-design our Weather App.
Following is the detailed explanation on heroku’s dyno:
Heroku allows you to run one free dyno (or actually they give you 720 free dyno hours per month, which corresponds to one dyno constantly running). This means that if you choose to run one web dyno and one worker dyno (celery in this case), you’ll be charged for 720 dyno hours. However, if you have a very small project, or your’re working on a project that hasn’t been released yet, you can avoid this cost.
A heroku dyno is like a process, and in this process you can actually spawn new processes, as long as you stay within the limit of 512 mb ram (the process also only has one CPU core). Heroku suggests that you use foreman when you run your application on your local machine, but you can actually use foreman on heroku, in order to run multiple processes in a single dyno.
On Heroku, we don’t have physical machines; in fact there isn’t the concept of “machine” at all. Instead, Heroku has Dynos, which are described as “lightweight containers” for UNIX processes. From their documentation:
[A Dyno] can run any command available in its default environment combined with your app’s slug
Solution
Celerywww.celeryproject.org
or
Honchohttps://github.com/nickstenning/honcho
We will try Honcho first, because it’s based on python, so won’t affect our code format.
Celery Way
Install redis and celery:
pip install redis celery
Remember to use pip freeze to update the requirement.txt file.
We should also enable the RedisToGo plugin, install it via CLI:
$ heroku addons:add rediscloud
If you don’t have a credit card, then your installation of plugin will be fail. We will register an account on www.redislabs.com, then we will continue our setting. ]
heroku config:set REDISCLOUD_URL="http://Resouce_Name:Redis_Passwod@pub-redis-xxxx.xxx.xxx..garantiadata.com:1xxx3"
Your heroku app will restart, then we can test this redis database via following commands:
$ heroku run python
Running `python` attached to terminal... up, run.8246
Python 2.7.6 (default, Jan 16 2014, 02:39:37)
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import os
>>> import urlparse
>>> import redis
>>> url = urlparse.urlparse(os.environ.get('REDISCLOUD_URL'))
>>> r = redis.Redis(host=url.hostname, port=url.port, password=url.password)
>>> r.set('foo','bar')
True
>>> r.get('foo')
'bar'
An Article of using celery is right after this article, so we end this article and in next one we will re-design the web app to fit the celery way.
May 9, 2014
TechnologyAccounting Setting
First you should have heroku accounting, then create an app on heroku, write down its repository information, mine is listed as following:
Your app, python-weather-app, has been created.
App URL:
http://python-weather-app.herokuapp.com/
Git URL:
git@heroku.com:python-weather-app.git
Use the following code to set up your app for local development:
git clone git@heroku.com:python-weather-app.git -o heroku
Suggested next steps
Get started with Heroku.
Add some collaborators.
Check out some of our great add-ons.
Before you continue, make sure you have install heroku tools:
$ yaourt -S heroku-toolbelt
Create HelloWorld App
Use github for recording all of the source code.
Create a repository on github, mine is at “git@github.com:kkkttt/herokuWeatherApp.git”, then:
$ pwd
/home/Trusty/code/herokuWeatherApp
$ touch README.md
$ git init
$ git add README.md
$ git commit -m "first commit"
$ git remote add origin git@github.com:kkkttt/herokuWeatherApp.git
$ git push -u origin master
First login with heroku:
$ heroku login
Enter your Heroku credentials.
Email: xxxxxx@gmail.com
Password (typing will be hidden):
Authentication successful.
Now create the folder which holds WeatherApp and create the venv, later we will use virtual environment for working:
$ virtualenv2 venv
New python executable in venv/bin/python2
Also creating executable in venv/bin/python
Installing setuptools, pip...done.
$ source venv/bin/activate
(venv) $ pip install Flask gunicorn
Now we write a very simple python file, name it “hello.py”:
import os
from flask import Flask
app = Flask(__name__)
@app.route('/')
def hello():
return 'Hello World!'
Create a Procfile in the root directory which holds our own App:
$ cat Procfile
web: gunicorn hello:app
Use foreman for preview the web app:
$ foreman start
14:53:37 web.1 | started with pid 30946
14:53:37 web.1 | 2014-05-09 14:53:37 [30946] [INFO] Starting gunicorn 18.0
14:53:37 web.1 | 2014-05-09 14:53:37 [30946] [INFO] Listening at: http://0.0.0.0:5000 (30946)
....
Open your browser and visit “http://127.0.0.1:5000” and you can see “Hello World” is in browser.
Now make the requirement file in the root folder:
$ pip freeze>requirements.txt
(venv)$ cat requirements.txt
Flask==0.10.1
Jinja2==2.7.2
MarkupSafe==0.23
Werkzeug==0.9.4
gunicorn==18.0
itsdangerous==0.24
wsgiref==0.1.2
Deploy It To Heroku
We have to ignore the temp files, so we add following into our .gitignores file:
$ cat .gitignore
*~
*.pyc
venv/
We add the app into the heroku:
$ heroku git:remote -a python-weather-app
Git remote heroku added
(venv)$ git remote -v
heroku git@heroku.com:python-weather-app.git (fetch)
heroku git@heroku.com:python-weather-app.git (push)
origin git@github.com:kkkttt/herokuWeatherApp.git (fetch)
origin git@github.com:kkkttt/herokuWeatherApp.git (push)
Deploy:
$ git push heroku master
Now open your browser and visit “http://python-weather-app.herokuapp.com/" you will see the webpage displays “Hello World!".
May 9, 2014
TechnologyGet Current Weather Data
Now we begin to change our APP to a real funny staff. First we change the hello.py and begin to write our own “genhtml.py”
$ mv hello.py genhtml.py
$ cat Procfile
web: gunicorn genhtml:app
We know there is an python library which we could install from pip named “pywapi”, simply install it via:
(venv2) $ pip install pywapi
If your pip’s version is 1.5.1, then notice you have to use following command for installing the pywapi:
(venv2) $ pip install pywapi --allow-external pywapi --allow-unverified pywapi
Downloading/unpacking pywapi
pywapi is potentially insecure and unverifiable.
Downloading pywapi-0.3.8.tar.gz
Running setup.py (path:/home/Trusty/code/herokuWeatherApp/venv/build/pywapi/setup.py) egg_info for package pywapi
Installing collected packages: pywapi
Running setup.py install for pywapi
Successfully installed pywapi
Cleaning up...
Now change the genhtml.py to following content:
import os
from flask import Flask
import pywapi
import string
app = Flask(__name__)
@app.route('/')
# Generate the Nanjing Weather Data
def genhtml():
Yahoo_Result = pywapi.get_weather_from_yahoo('CHXX0099')
Current_Temp = string.lower(Yahoo_Result['condition']['temp'])
Current_Humi = string.lower(Yahoo_Result['atmosphere']['humidity'])
Tomorrow_Forecast = Yahoo_Result['forecasts'][0]
Twenty_Four_Hours = Yahoo_Result['forecasts'][1]
Fourty_Eight_Hours = Yahoo_Result['forecasts'][2]
Seventy_Two_Hours = Yahoo_Result['forecasts'][3]
return Current_Temp
After modification, we use “foreman start” for previewing the result, we can see the webpage returns the current temperature of Nanjing, its value is 25, as in following picture:
This shows the pywapi could be work together with other components, we will continue for next step.
###Deployment On Heroku
You need to change the requirement.txt file like following:
$ cat requirements.txt
--allow-all-external
--allow-unverified pywapi
Flask==0.10.1
Jinja2==2.7.2
MarkupSafe==0.23
Werkzeug==0.9.4
gunicorn==18.0
itsdangerous==0.24
pywapi==0.3.8
wsgiref==0.1.2
Then run “git push heroku master”, open your browser and visit http://python-weather-app.herokuapp.com/, the output the same as in local.
By now, we have created a very simple app on getting the current temperature of nanjing, next chapter we will use database for timely record the data.
May 9, 2014
TechnologyDatabase Introduction
Heroku Postgres Installation
Before using postgres, we have to install this add-ons, we call this step “attach Heroku POSTGRES to heroku application”:
$ heroku addons
python-weather-app has no add-ons.
$ heroku addons:add heroku-postgresql:dev
Adding heroku-postgresql:dev on python-weather-app... done, v7 (free)
Attached as HEROKU_POSTGRESQL_OLIVE_URL
Database has been created and is available
! This database is empty. If upgrading, you can transfer
! data from another database with pgbackups:restore.
Use `heroku addons:docs heroku-postgresql` to view documentation.
$ heroku addons | grep POSTGRES
heroku-postgresql:dev HEROKU_POSTGRESQL_OLIVE
If you want to refer the documentation of heroku postgres, simply use following command:
$ heroku addons:docs heroku-postgresql
Opening heroku-postgresql docs... done
A new browser window will be opened and you can view the help here.
View the configuration of heroku postgres via:
$ heroku pg:info
=== HEROKU_POSTGRESQL_OLIVE_URL (DATABASE_URL)
Plan: Dev
Status: Available
Connections: 0
PG Version: 9.3.4
Created: 2014-05-09 11:25 UTC
Data Size: 6.4 MB
Tables: 0
Rows: 0/10000 (In compliance)
Fork/Follow: Unsupported
Rollback: Unsupported
10000 rows means, if we use 24 rows per day, then around 1 year this database will be fulfilled. But anyway, at the very beginning developing, we won’t consider the latter problem.
After you installed the postgre for around 5 minutes, you can use following commands for displaying your database:
$ heroku pg:ps
pid | state | source | running_for | waiting | query
-----+-------+--------+-------------+---------+-------
(0 rows)
Now you can connect to pg via following command:
$ heroku pg:psql
---> Connecting to HEROKU_POSTGRESQL_OLIVE_URL (DATABASE_URL)
psql (9.3.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
d47ena4men35jn=>
What for next?
Local Postgres Installation
Install postgres via:
$ sudo pacamn -S postgre
$ sudo -i -u postgres
[postgres@DashArch ~]$ initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'
# In another terminal, enable and start the service
$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql
Now add current user into the postgres user:
[postgres@DashArch ~]$ createuser --interactive
Enter name of role to add: Trusty
Shall the new role be a superuser? (y/n) y
# Now 'Trusty' as postgres user, can create the weatherData database.
$ createdb weatherData
Basic user of postgres:
$ psql -d weatherData
psql (9.3.4)
Type "help" for help.
weatherData=# \help
......
weatherData=# \q
$
Connect Database In Python(Local Way)
Install the python packages:
$ pip install psycopg2
$ pip freeze
# Add the psycopg2 related line into the requirements.txt
A simple example on how to connect Database and view the content of the database:
[Trusty@~]$ sudo -u postgres createuser jim
[Trusty@~]$ sudo -u postgres createdb testdb -O jim
[Trusty@~/code/herokuWeatherApp]$ source venv/bin/activate
(venv)[Trusty@~/code/herokuWeatherApp]$ python
Python 2.7.6 (default, Feb 26 2014, 12:07:17)
[GCC 4.8.2 20140206 (prerelease)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> import sys
>>> con = None
>>> con = psycopg2.connect(database='testdb', user='jim')
>>> cur = con.cursor()
>>> cur.execute('SELECT version()')
>>> ver = cur.fetchone()
>>> print ver
('PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140206 (prerelease), 64-bit',)
>>> con.close()
>>> quit()
(venv)[Trusty@~/code/herokuWeatherApp]$
Connect Heroku Postgres
Now commit our modifications into heroku, and verify to see if we can really do some magic things with postgres:
$ git add .
$ git commit -m "commit for postgres"
$ git push heroku master
Install a new library:
$ pip install flask-sqlalchemy
$ pip freeze # grep the line and add it into the requirement.txt
Promoting the URL to DATABASE_URL:
$ heroku pg:promote HEROKU_POSTGRESQL_OLIVE_URL
Promoting HEROKU_POSTGRESQL_OLIVE_URL (DATABASE_URL) to DATABASE_URL... done
Then Add the following lines into genhtml.py:
from flask.ext.sqlalchemy import SQLAlchemy
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ['DATABASE_URL']
db = SQLAlchemy(app)
##############FenGe_Line####################
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
email = db.Column(db.String(120), unique=True)
def __init__(self, name, email):
self.name = name
self.email = email
def __repr__(self):
return '<Name %r>' % self.name
Commit again and begin for verifying the database via CLI:
Create a python interactive command window via:
>>> from genhtml import db
>>> db.create_all()
>>> from genhtml import User
>>> user = User('John Doe', 'john.doe@example.com')
>>> db.session.add(user)
>>> db.session.commit()
>>> all_users =User.query.all()
>>> print all_users
[<Name u'John Doe'>]
Now we can see 1 record has been inserted into the database:
[Trusty@~/code/herokuWeatherApp]$ heroku pg:info
=== HEROKU_POSTGRESQL_OLIVE_URL (DATABASE_URL)
Plan: Dev
Status: Available
Connections: 2
PG Version: 9.3.4
Created: 2014-05-09 11:25 UTC
Data Size: 6.5 MB
Tables: 1
Rows: 1/10000 (In compliance)
Fork/Follow: Unsupported
Rollback: Unsupported
How to see the inserted data?
$ heroku pg:psql
---> Connecting to HEROKU_POSTGRESQL_OLIVE_URL (DATABASE_URL)
psql (9.3.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
d47ena4men35jn=> select current_database();
current_database
------------------
d47ena4men35jn
(1 row)
d47ena4men35jn=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------------
public | user | table | yjusdsrpwpplxp
(1 row)
d47ena4men35jn=> \d user;
Table "public.user"
Column | Type | Modifiers
--------+------------------------+---------------------------------------------------
id | integer | not null default nextval('user_id_seq'::regclass)
name | character varying(80) |
email | character varying(120) |
Indexes:
"user_pkey" PRIMARY KEY, btree (id)
"user_email_key" UNIQUE CONSTRAINT, btree (email)
d47ena4men35jn=> SELECT * FROM public.user;
id | name | email
----+----------+----------------------
1 | John Doe | john.doe@example.com
(1 row)
We can use python’s interface for add/delete records, drop tables, etc.
