# -*- coding: utf-8 -*-

#from app.migrator import Migrator
import os, sys
import random
import psycopg2


with psycopg2.connect(dbname='test', host='localhost', port='5432', user='test', password='test') as conn:
    with conn.cursor() as cur:

        #cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
        #m = Migrator(conn)
        r = cur.execute("""
        CREATE TABLE IF NOT EXISTS "public"."persons" (
            "id" serial,
            "name" text,
            "age" int,
            PRIMARY KEY ("id")
        );
        """)

    with conn.cursor() as cur:
        cur.execute("INSERT INTO persons (name, age) VALUES (%(name)s, %(age)s)", {
            'name': 'Bill_%s' % random.randint(0, 10),
            'age': 100,
        })


    with conn.cursor() as cur:
        rows = []
        for i in range(0,10):
            rows.append({
                'name': 'Bill_%s' % random.randint(0, 10),
                'age': random.randint(20,80),
            })
        cur.executemany("INSERT INTO persons (name, age) VALUES (%(name)s, %(age)s)", rows)


    with conn.cursor() as cur:
        cur.execute("SELECT * FROM persons order by age")
        row = cur.fetchone()
        print row

    with conn.cursor() as cur:
        cur.execute("SELECT * FROM persons")
        rows = cur.fetchall()
        print rows[0:3], '...', cur.rowcount

    with conn.cursor() as cur:
        for i in range(0, 10):
            cur.execute("UPDATE persons SET NAME=%(new_name)s WHERE name=%(name)s",{
                'name': 'Bill_%s' % random.randint(0, 10),
                'new_name': 'Marry_%s' % random.randint(0, 10),
            })

    with conn.cursor() as cur:
        cur.execute("SELECT * FROM persons WHERE name like 'Marry%'")
        for row in cur:
            print row
        #print rows


    print "====================================="
    # test on duplicate key update / upsert
    with conn.cursor() as cur:
        #cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
        #m = Migrator(conn)
        r = cur.execute("""
        CREATE TABLE IF NOT EXISTS "public"."persons2" (
            "name" text,
            "age" int,
            PRIMARY KEY ("name")
        );
        """)

    with conn.cursor() as cur:
        rows = []
        for i in range(0,100):
            rows.append({
                'name': 'Bill_%s' % random.randint(0, 3),
                'age': random.randint(20,80),
            })
        # INSERT INTO tablename (a, b, c) values (1, 2, 10)
        # ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;
        cur.executemany("""
        INSERT INTO persons2 (name, age) VALUES (%(name)s, %(age)s)
        ON CONFLICT (name) DO UPDATE SET age=%(age)s
        """, rows)

        cur.execute("SELECT * FROM persons2")
        for row in cur:
            print row
        #result = cur.fetchone()
        #print result.rowcount
        #print cur.rowcount
        #print dir(cur)
        #print result
        #print dir(result)
        #print result.count()