python3, virtualenv and django on mac os x homebrew

brew update
brew upgrade
brew install python3

pip3 install virtualenvwrapper

add the following to your ~/.bash_profile:

export WORKON_HOME=$HOME/.virtualenvs
export PROJECT_HOME=$HOME/Projects
export VIRTUALENVWRAPPER_PYTHON='/usr/local/bin/python3'
source /usr/local/bin/

Then create and activate the virtual environment:

git clone
# or just git pull
cd /projects/your_project
mkvirtualenv your_project --python=/usr/local/bin/python3
workon your_project

You can add this to ~/.virtualenvs/your-project/bin/postactivate  so the env vars get imported to the command line directly from .envs:

# This hook is sourced after this virtualenv is activated.

if [ -f ${PWD}/.env ]; then
    echo "activating .env..."
    set -a
    . ${PWD}/.env
    set +a

In some projects there might be an .env-example file. Copy it to .env and update the sample values inside.

pip install -r requirements.txt
pip install -r requirements-dev.txt

# migrate the db
./ migrate

Django Quick Deploy

The quickest way to deploy django from github

sudo su
ssh-keygen -t rsa -C ""
apt-get install git
git clone<org>/<project>.git
apt-get install python python-dev python-pip screen
pip install -r requirements.txt
./ runserver

Python Django Production Deploy with Apache

  1. Create github repo and clone locally
  2. Add .gitignore
  3. create virtualenv (mkvirtualenv envname)
  4. Set the correct virtualenv in PyCharm
  5. create database & users
  6. Update database settings (see below)
  7. First commit
  8. Add site and app
  9. Second commit

Some basic stuff

sudo su
apt-get install joe apache2 git python-setuptools python-dev python3 libapache2-mod-wsgi-py3

ssh-keygen -t rsa -C ""
# add the public key to your github account

Install Postgresql

apt-get install postgresql-server-dev-9.1
apt-get install postgresql postgresql-client
su - postgres
CREATE USER flashcards WITH PASSWORD 'xxxx';
GRANT ALL PRIVILEGES ON DATABASE flashcards to flashcards;
cd /var/www/your_project
git clone

Install virtualenv

pip install virtualenvwrapper
source /usr/local/bin/
mkvirtualenv -p /usr/bin/python3.4 venv
workon venv
pip install -r requirements.txt

 'default': {
 'ENGINE': 'django.db.backends.postgresql_psycopg2', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
 'NAME': 'mydb', # Or path to database file if using sqlite3.
 # The following settings are not used with sqlite3:
 'USER': 'myuser',
 'PASSWORD': 'password',
 'HOST': 'localhost', # Empty for localhost through domain sockets or '' for localhost through TCP.
 'PORT': '', # Set to empty string for default.

Add to site/

# Activate your virtual env
execfile(activate_env, dict(__file__=activate_env))

Make WSGI work

cat > /etc/apache2/conf.d/wsgipythonpath.conf
WSGIPythonPath /opt/virtual_env/flashcards/bin/python

a2enconf /etc/apache2/conf.d/wsgipythonpath.conf
service apache2 reload

Make cert

openssl genrsa -des3 -out server.key 2048
openssl req -new -key server.key -out server.csr
cp server.key
openssl rsa -in -out server.key
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt

Add new VirtualHost

Google Spreadsheet Advanced Formulas

Pivot Tables

=query('demo-source'!A:Z;"select A,sum(C) group by A pivot B")

Sum all available lines for arbitrary number of colums per line:


Solution Source

Query a source data range for values according to an arbitrary number of rows in the destination area:

=ARRAYFORMULA(MMULT(SIGN(<destination condition>=TRANSPOSE(<source condition>));<source data>)



This is a pretty sick little piece of code – it allows you to match any type of data columns to an arbitrary list of things based on a unique key (like a sql join).
Solution Source

More powerful JOIN implementation for multiple conditions:

=arrayformula(if(len(A2:A)>0,sumif(‘other_sheet’!A:A&’other_sheet’!L:L, A2:A&“token“,‘other_sheet’!J:J),))


  • =sumif can only accept one condition and =sumifs does not work with arrayformula
  • arrayformula is needed so that the JOIN works for dynamic lists (for example form another =query())
  • ‘other_sheet’!J:J is the column from which the values should be summed up
  • ‘other_sheet’!A:A is the column the first condition should be applied to
  • ‘other_sheet’!L:L is the column the second condition should be applied to
  • A2:A is the first condition (here it means that the cell value should equal to the corresponding value in A2:A)
  • “*token*” is the second condition (here it’s a kind of regular expression that finds the token in the cell content).
  • two or more columns as well as conditions can be concatenated according to!msg/docs/OrriYjusT6k/2XSeq1FhH3oJ

Use: This is great if you have a dynamic list (for example coming from a query() and you want to match each row with a value from another table (in this example from ‘other_sheet’) based on multiple conditions. The first condition normally makes comparisons based on a unique key so that the matched rows never exceed one row.

Use =query() to filter rows of a sheet that are empty:

=query(source, "select * where A = "&char(34)&char(34)

Calculate remaining days in the current year or the current month:




parse an european date:


How to Git for Beginners

A best-practice model for using git:

Merge a development branch with master:

git checkout master
git pull origin master
git merge test
git push origin master

Simple rebase with conflict resolution

git checkout experiment
git rebase master

If there is a conflict at this time git will show an error and mark conflicting code in your source code. Resolve it and then continue, make sure you delete all the markers as well.

git rebase --continue

Bring an experimental branch up-to-date in comparison to the root branch

# in a secondary branch:
# as if work in the secondary branch had started on top of the changes made to the root branch in the meantime
git pull --rebase
# or
git checkout <root-branch>
git pull
git checkout <experiment-branch>
git rebase <root-branch>
# for example git rebase stage
# If there are conflicts you can either resolve them manually or use 
git checkout --ours foo/bar.js
# to take the experiment version use --theirs, for the root-branch version use --ours
# see
# finally add any changes
git add .
# before you run
git rebase --continue

change to a previous commit

# change to a previous commit
# find the commit id
git log
# revert to that commit id
git reset --hard commit_sha

Apply hotfixes:

checkout master, then do

git cherry-pick <COMMIT_HASH>

if you merge the whole branch later on git will only apply the commits that weren’t already in master that’s a pretty cool way to apply urgent patches.

git merge origin/master

or create a pull request on github, have somebody review / accept it.

cherry pick files (or commits) from another branch

git fetch
git checkout someotherbranch
git pull
git checkout workingbranch
git checkout someotherbranch folder/

amend last comit that is already pushed

Watch out if others have referred to the previous commit they are in serious trouble afterwards

git commit --amend
git push -f


Watch out: In a git repo, only the commit id of a submodule is tracked.

Add a submodule:

git submodule add filepath
git submodule init

Updating all submodules in a repo

git pull && git submodule init && git submodule update && git submodule status
git commit -a

already pushed to remote and want to squash commits?

git rebase -i <commit-id-of-first-commit-you-want-to-squash>

make sure to replace pick by ‘squash’ for all but the first commit in the list that git will show you.

Colorful bash in Debian

# System-wide .bashrc file for interactive bash(1) shells.

# To enable the settings / commands in this file for login shells as well,
# this file has to be sourced in /etc/profile.

# If not running interactively, don't do anything
[ -z "$PS1" ] && return

# check the window size after each command and, if necessary,
# update the values of LINES and COLUMNS.
shopt -s checkwinsize

# set variable identifying the chroot you work in (used in the prompt below)
if [ -z "$debian_chroot" ] && [ -r /etc/debian_chroot ]; then
    debian_chroot=$(cat /etc/debian_chroot)


# Set colorful PS1 only on colorful terminals.
# dircolors --print-database uses its own built-in database
# instead of using /etc/DIR_COLORS.  Try to use the external file
# first to take advantage of user additions.  Use internal bash
# globbing instead of external grep binary.
safe_term=${TERM//[^[:alnum:]]/?}   # sanitize TERM
[[ -f ~/.dir_colors   ]] && match_lhs="${match_lhs}$(<~/.dir_colors)"
[[ -f /etc/DIR_COLORS ]] && match_lhs="${match_lhs}$(</etc/DIR_COLORS)"
[[ -z ${match_lhs}    ]] \
        && type -P dircolors >/dev/null \
        && match_lhs=$(dircolors --print-database)
[[ $'\n'${match_lhs} == *$'\n'"TERM "${safe_term}* ]] && use_color=true

if ${use_color} ; then
        # Enable colors for ls, etc.  Prefer ~/.dir_colors #64489
        if type -P dircolors >/dev/null ; then
                if [[ -f ~/.dir_colors ]] ; then
                        eval $(dircolors -b ~/.dir_colors)
                elif [[ -f /etc/DIR_COLORS ]] ; then
                        eval $(dircolors -b /etc/DIR_COLORS)

        if [[ ${EUID} == 0 ]] ; then
                PS1='${debian_chroot:+($debian_chroot)}\[&#92;&#48;33[01;31m\]\h\[&#92;&#48;33[01;34m\] \W \$\[&#92;&#48;33[00m\] '
                PS1='${debian_chroot:+($debian_chroot)}\[&#92;&#48;33[01;32m\]\u@\h\[&#92;&#48;33[01;34m\] \w \$\[&#92;&#48;33[00m\] '

        alias ls='ls --color=auto'
        alias grep='grep --colour=auto'
        if [[ ${EUID} == 0 ]] ; then
                # show root@ when we don't have colors
                PS1='\u@\h \W \$ '
                PS1='\u@\h \w \$ '

# Try to keep environment pollution down, EPA loves us.
unset use_color safe_term match_lhs

# Commented out, don't overwrite xterm -T "title" -n "icontitle" by default.
# If this is an xterm set the title to user@host:dir
#case "$TERM" in
#    PROMPT_COMMAND='echo -ne "&#92;&#48;33]0;${USER}@${HOSTNAME}: ${PWD}&#92;&#48;07"'
#    ;;
#    ;;

# enable bash completion in interactive shells
if [ -f /etc/bash_completion ] && ! shopt -oq posix; then
    . /etc/bash_completion

# if the command-not-found package is installed, use it
if [ -x /usr/lib/command-not-found -o -x /usr/share/command-not-found ]; then
    function command_not_found_handle {
            # check because c-n-f could've been removed in the meantime
                if [ -x /usr/lib/command-not-found ]; then
           /usr/bin/python /usr/lib/command-not-found -- $1
                   return $?
                elif [ -x /usr/share/command-not-found ]; then
           /usr/bin/python /usr/share/command-not-found -- $1
                   return $?
           return 127


Python: Beginner Learnings

This builds the requirements.txt. Should contain version of the package (=pinned)

pip freeze | grep paketname >> requirements.txt

in the requirements.txt git repos can be included like this:
git+git::// googletools


Ctrl+Click on Function leads directly to the function location
Ctrl+E shows the last open files for quick going back

Wrapper Class:

class SSWrapper(object):

    ss_instance = None

    def __init__(self, ss_instance):
        self.ss_instance = ss_instance

    def get_worksheet_by_id(id):
        for x in self.ss_instance.asdfasd

   ss = gspread.blah()
   wrapper = SSWrapper(ss)


Pass Code:

class Worksheet(gspread.Worksheet):