PYSPARK_DRIVER_PYTHON=jupyter PYSPARK_DRIVER_PYTHON_OPTS='notebook' ~/Spark/default/bin/pyspark

$ diff -u /mnt/c/Users/dobachi/.hyper.js{.2018010101,}
--- /mnt/c/Users/dobachi/.hyper.js.2018010101   2019-01-01 23:26:47.691869000 +0900
+++ /mnt/c/Users/dobachi/.hyper.js      2019-01-01 23:28:25.273185100 +0900
@@ -9,7 +9,7 @@
     updateChannel: 'stable',

     // default font size in pixels for all tabs
-    fontSize: 12,
+    fontSize: 15,

     // font family with optional fallbacks
     fontFamily: 'Menlo, "DejaVu Sans Mono", Consolas, "Lucida Console", monospace',
@@ -107,7 +107,7 @@

     // for setting shell arguments (i.e. for using interactive shellArgs: `['-i']`)
     // by default `['--login']` will be used
-    shellArgs: ['--login'],
+    shellArgs: ['/C', 'wsl'],

     // for environment variables
     env: {},

set ambiwidth=double

$ sudo apt install git build-essential libssl-dev libreadline-dev zlib1g-dev x11-apps x11-utils x11-xserver-utils libsqlite3-dev nodejs fonts-ipafont libxml2-dev libxslt1-dev

--- /home/dobachi/.bashrc.2018123001    2018-12-30 23:21:48.626055900 +0900
+++ /home/dobachi/.bashrc       2018-12-30 23:20:26.333894000 +0900
@@ -115,3 +115,5 @@
     . /etc/bash_completion
   fi
 fi
+
+export DISPLAY=localhost:0.0

$ sudo apt install language-pack-ja
$ sudo update-locale LANG=ja_JP.UTF-8
$ sudo apt install -y uim uim-xim uim-fep uim-anthy dbus-x11
$ cat << EOF > ~/.uim
  (define default-im-name 'anthy)
  (define-key generic-on-key? '("<Control> "))
  (define-key generic-off-key? '("<Control> "))
  EOF
$ cat << EOF >> ~/.profile
  export GTK_IM_MODULE=uim
  EOF

set -as terminal-overrides ',*:indn@'

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
)

addresses = Table('addresses', metadata,
  Column('id', Integer, primary_key=True),
  Column('user_id', None, ForeignKey('users.id')),
  Column('email_address', String, nullable=False)
 )

metadata.create_all(engine)

ins = users.insert()

str(ins)
--
'INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)'

ins = users.insert().values(name='jack', fullname='Jack Jones')
str(ins)
--
'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'

conn = engine.connect()

result = conn.execute(ins)
--
2018-12-24 14:08:34,031 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname) VALUES (?, ?)
2018-12-24 14:08:34,031 INFO sqlalchemy.engine.base.Engine ('jack', 'Jack Jones')
2018-12-24 14:08:34,032 INFO sqlalchemy.engine.base.Engine COMMIT

>>> result.inserted_primary_key
--
[1]

ins = users.insert()
conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams')
--
2018-12-24 14:12:55,688 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
2018-12-24 14:12:55,688 INFO sqlalchemy.engine.base.Engine (2, 'wendy', 'Wendy Williams')
2018-12-24 14:12:55,688 INFO sqlalchemy.engine.base.Engine COMMIT
<sqlalchemy.engine.result.ResultProxy object at 0x7f63c8ca7940>

conn.execute(addresses.insert(), [
   {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
   {'user_id': 1, 'email_address' : 'jack@msn.com'},
   {'user_id': 2, 'email_address' : 'www@www.org'},
   {'user_id': 2, 'email_address' : 'wendy@aol.com'},
])
--
2018-12-24 14:13:42,077 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
2018-12-24 14:13:42,078 INFO sqlalchemy.engine.base.Engine ((1, 'jack@yahoo.com'), (1, 'jack@msn.com'), (2, 'www@www.org'), (2, 'wendy@aol.com'))
2018-12-24 14:13:42,078 INFO sqlalchemy.engine.base.Engine COMMIT
<sqlalchemy.engine.result.ResultProxy object at 0x7f63c8ca7c18>

from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)
--
2018-12-24 14:16:11,363 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname 
FROM users
2018-12-24 14:16:11,363 INFO sqlalchemy.engine.base.Engine ()

for row in result:
    print(row)
--
(1, 'jack', 'Jack Jones')
(2, 'wendy', 'Wendy Williams')

result = conn.execute(s)
row = result.fetchone()
print("name:", row['name'], "; fullname:", row['fullname'])
--
name: jack ; fullname: Jack Jones

for row in conn.execute(select([users, addresses])):
    print(row)
--
2018-12-24 14:20:14,164 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses
2018-12-24 14:20:14,164 INFO sqlalchemy.engine.base.Engine ()
(1, 'jack', 'Jack Jones', 1, 1, 'jack@yahoo.com')
(1, 'jack', 'Jack Jones', 2, 1, 'jack@msn.com')
(1, 'jack', 'Jack Jones', 3, 2, 'www@www.org')
(1, 'jack', 'Jack Jones', 4, 2, 'wendy@aol.com')
(2, 'wendy', 'Wendy Williams', 1, 1, 'jack@yahoo.com')
(2, 'wendy', 'Wendy Williams', 2, 1, 'jack@msn.com')
(2, 'wendy', 'Wendy Williams', 3, 2, 'www@www.org')
(2, 'wendy', 'Wendy Williams', 4, 2, 'wendy@aol.com')

for row in conn.execute(select([users, addresses]).where(users.c.id == addresses.c.user_id)):
    print(row)
--
2018-12-24 14:22:46,769 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses 
WHERE users.id = addresses.user_id
2018-12-24 14:22:46,769 INFO sqlalchemy.engine.base.Engine ()
(1, 'jack', 'Jack Jones', 1, 1, 'jack@yahoo.com')
(1, 'jack', 'Jack Jones', 2, 1, 'jack@msn.com')
(2, 'wendy', 'Wendy Williams', 3, 2, 'www@www.org')
(2, 'wendy', 'Wendy Williams', 4, 2, 'wendy@aol.com')

from sqlalchemy.sql import and_, or_, not_
s = select([(users.c.fullname +
              ", " + addresses.c.email_address).
               label('title')]).\
       where(
          and_(
              users.c.id == addresses.c.user_id,
              users.c.name.between('m', 'z'),
              or_(
                 addresses.c.email_address.like('%@aol.com'),
                 addresses.c.email_address.like('%@msn.com')
              )
          )
       )
conn.execute(s).fetchall()

s = select([(users.c.fullname +
              ", " + addresses.c.email_address).
               label('title')]).\
       where(
          (users.c.id == addresses.c.user_id) &
          (users.c.name.between('m', 'z')) &
          (
          addresses.c.email_address.like('%@aol.com') | \
          addresses.c.email_address.like('%@msn.com')
          )
       )
conn.execute(s).fetchall()

s = select([(users.c.fullname +
              ", " + addresses.c.email_address).
               label('title')]).\
       where(users.c.id == addresses.c.user_id).\
       where(users.c.name.between('m', 'z')).\
       where(
              or_(
                 addresses.c.email_address.like('%@aol.com'),
                 addresses.c.email_address.like('%@msn.com')
              )
       )
conn.execute(s).fetchall()

from sqlalchemy.sql import text
s = text(
    "SELECT users.fullname || ', ' || addresses.email_address AS title "
        "FROM users, addresses "
        "WHERE users.id = addresses.user_id "
        "AND users.name BETWEEN :x AND :y "
        "AND (addresses.email_address LIKE :e1 "
            "OR addresses.email_address LIKE :e2)")
conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall()

stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")
stmt = stmt.bindparams(x="m", y="z")
# stmt = stmt.bindparams(bindparam("x", type_=String), bindparam("y", type_=String))
result = conn.execute(stmt, {"x": "m", "y": "z"})
result.fetchall()
--
[(2, 'wendy', 'Wendy Williams')]

s = select([
       text("users.fullname || ', ' || addresses.email_address AS title")
    ]).\
        where(
            and_(
                text("users.id = addresses.user_id"),
                text("users.name BETWEEN 'm' AND 'z'"),
                text(
                    "(addresses.email_address LIKE :x "
                    "OR addresses.email_address LIKE :y)")
            )
        ).select_from(text('users, addresses'))
conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall()

from sqlalchemy import func
stmt = select([
        addresses.c.user_id,
        func.count(addresses.c.id).label('num_addresses')]).\
        group_by("user_id").order_by("user_id", "num_addresses")

conn.execute(stmt).fetchall()

print(users.join(addresses))

print(users.join(addresses,
                addresses.c.email_address.like(users.c.name + '%')
            )
)

s = select([users.c.fullname]).select_from(
   users.join(addresses,
            addresses.c.email_address.like(users.c.name + '%'))
   )
conn.execute(s).fetchall()
--
2018-12-25 12:45:29,919 INFO sqlalchemy.engine.base.Engine SELECT users.fullname 
FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
2018-12-25 12:45:29,919 INFO sqlalchemy.engine.base.Engine ('%',)
[('Jack Jones',), ('Jack Jones',), ('Wendy Williams',)]

print(select([users.c.fullname]).select_from(users.outerjoin(addresses)))
--
SELECT users.fullname 
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id

from sqlalchemy.sql import bindparam
s = users.select(users.c.name.like(bindparam('username', type_=String) + text("'%'")))
conn.execute(s, username='wendy').fetchall()
--
2018-12-25 13:34:19,392 INFO sqlalchemy.engine.base.Engine SELECT users.fullname 
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
2018-12-25 13:34:19,392 INFO sqlalchemy.engine.base.Engine ()
[('Jack Jones',), ('Jack Jones',), ('Wendy Williams',), ('Wendy Williams',)]

s = select([users, addresses]).\
    where(
       or_(
         users.c.name.like(
                bindparam('name', type_=String) + text("'%'")),
         addresses.c.email_address.like(
                bindparam('name', type_=String) + text("'@%'"))
       )
    ).\
    select_from(users.outerjoin(addresses)).\
    order_by(addresses.c.id)
conn.execute(s, name='jack').fetchall()
--
2018-12-25 13:43:43,880 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id 
WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%' ORDER BY addresses.id
2018-12-25 13:43:43,880 INFO sqlalchemy.engine.base.Engine ('jack', 'jack')
[(1, 'jack', 'Jack Jones', 1, 1, 'jack@yahoo.com'), (1, 'jack', 'Jack Jones', 2, 1, 'jack@msn.com')]

stmt = users.update().\
            where(users.c.name == 'jack').\
            values(name='ed')

conn.execute(stmt)

stmt = users.update().\
            where(users.c.name == bindparam('oldname')).\
            values(name=bindparam('newname'))
conn.execute(stmt, [
    {'oldname':'jack', 'newname':'ed'},
    {'oldname':'wendy', 'newname':'mary'},
    {'oldname':'jim', 'newname':'jake'},
    ])
conn.execute(select([users])).fetchall()
--
2018-12-25 15:11:08,628 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname 
FROM users
2018-12-25 15:11:08,628 INFO sqlalchemy.engine.base.Engine ()
[(1, 'ed', 'Jack Jones'), (2, 'mary', 'Wendy Williams')]

$ mkdir -p  ~/Sources/docker_flask/sqlalchemy
$ cd ~/Sources/docker_flask/sqlalchemy
$ cat << EOF > Dockerfile
  FROM ubuntu:latest
  
  RUN apt-get update
  RUN apt-get install python3 python3-pip -y
  
  RUN pip3 install flask flask_sqlalchemy
  EOF

$ sudo docker build . -t dobachi/flask_sqlalchemy:1.0

$ mkdir apps
$ cat << EOF > apps/app.py
  from flask import Flask
  from flask_sqlalchemy import SQLAlchemy
  
  app = Flask(__name__)
  app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
  db = SQLAlchemy(app)

  class User(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      username = db.Column(db.String(80), unique=True, nullable=False)
      email = db.Column(db.String(120), unique=True, nullable=False)
  
      def __repr__(self):
          return '<User %r>' % self.username
  EOF

$ sudo docker run -it --rm -p 5000:5000 -v $(pwd)/apps:/usr/local/apps -e "FLASK_APP=app.py" -w /usr/local/apps -e "LC_ALL=C.UTF-8" -e "LANG=UTF-8" dobachi/flask_sqlalchemy:1.0 /bin/bash

>>> from app import db
/usr/local/lib/python3.6/dist-packages/flask_sqlalchemy/__init__.py:794: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
>>> db.create_all()
>>> from app import User
>>> admin = User(username='admin', email='admin@example.com')
>>> guest = User(username='guest', email='guest@example.com')
>>> db.session.add(admin)
>>> db.session.add(guest)
>>> db.session.commit()
>>> User.query.all()
[<User 'admin'>, <User 'guest'>]
>>> User.query.filter_by(username='admin').first()
<User 'admin'>

root@a5d5ee81df78:/usr/local/apps# apt install sqlite3
root@a5d5ee81df78:/usr/local/apps# sqlite3 /tmp/test.db
sqlite> .databases
main: /tmp/test.db
sqlite> .tables
user
sqlite> select * from user;
1|admin|admin@example.com
2|guest|guest@example.com

$ mkdir -p  ~/Sources/docker_flask/sqlalchemy_app
$ cd ~/Sources/docker_flask/sqlalchemy_app
$ mkdir apps
$ cat << EOF > apps/app.py
  from flask import Flask, url_for, request, render_template, redirect
  from flask_sqlalchemy import SQLAlchemy
  
  app = Flask(__name__)
  app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
  db = SQLAlchemy(app)
  
  class User(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      username = db.Column(db.String(80), unique=True, nullable=False)
      email = db.Column(db.String(120), unique=True, nullable=False)
  
      def __repr__(self):
          return '<User %r>' % self.username
  
  db.create_all()
  
  @app.route('/regist', methods=['POST', 'GET'])
  def regist():
      if request.method == 'POST':
          user = User(username=request.form['username'], email=request.form['email'])
          db.session.add(user)
          db.session.commit()
          return redirect(url_for('list'))
      # the code below is executed if the request method
      # was GET or the credentials were invalid
      return render_template('regist.html')
  
  @app.route('/list')
  def list():
      users = User.query.order_by(User.username).all()
      return render_template('list.html', users=users)
  EOF
$ mkdir -p apps/templates
$ cat << EOF > apps/templates/regist.html
  <!doctype html>
  <title>Registration</title>
  <form action="/regist" method="post">
    <div>username: <input type="text" name="username"></div>
    <div>email: <input type="text" name="email"></div>
    <input type="submit" value="send">
    <input type="reset" value="reset">
  </form>
  
  <a href="/list">list</a>
  EOF
$ cat << EOF > apps/templates/list.html
  <!doctype html>
  <title>List ordered by username</title>
  <table>
      <thead>
          <tr>
              <th>id</th>
              <th>username</th>
              <th>email</th>
          </tr>
        </thead>
      <tbody>
          {% for item in users %}
          <tr>
              <td>
                  {{ item.id }}
              </td>
              <td>
                  {{ item.username }}
              </td>
              <td>
                  {{ item.email }}
              </td>
          </tr>
          {% endfor %}
      </tbody>
  </table>
  <a href="/regist">registration</a>
  EOF

$ sudo docker run -it --rm -p 5000:5000 -v $(pwd)/apps:/usr/local/apps -e "FLASK_APP=app.py" -w /usr/local/apps -e "LC_ALL=C.UTF-8" -e "LANG=UTF-8" dobachi/flask_sqlalchemy:1.0 flask run --host 0.0.0.0