3f9EVxES

1# -*- coding: utf-8 -*-
2
3#from app.migrator import Migrator
4import os, sys
5import random
6import psycopg2
7
8
9with psycopg2.connect(dbname='test', host='localhost', port='5432', user='test', password='test') as conn:
10    with conn.cursor() as cur:
11
12        #cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
13        #m = Migrator(conn)
14        r = cur.execute("""
15        CREATE TABLE IF NOT EXISTS "public"."persons" (
16            "id" serial,
17            "name" text,
18            "age" int,
19            PRIMARY KEY ("id")
20        );
21        """)
22
23    with conn.cursor() as cur:
24        cur.execute("INSERT INTO persons (name, age) VALUES (%(name)s, %(age)s)", {
25            'name': 'Bill_%s' % random.randint(0, 10),
26            'age': 100,
27        })
28
29
30    with conn.cursor() as cur:
31        rows = []
32        for i in range(0,10):
33            rows.append({
34                'name': 'Bill_%s' % random.randint(0, 10),
35                'age': random.randint(20,80),
36            })
37        cur.executemany("INSERT INTO persons (name, age) VALUES (%(name)s, %(age)s)", rows)
38
39
40    with conn.cursor() as cur:
41        cur.execute("SELECT * FROM persons order by age")
42        row = cur.fetchone()
43        print row
44
45    with conn.cursor() as cur:
46        cur.execute("SELECT * FROM persons")
47        rows = cur.fetchall()
48        print rows[0:3], '...', cur.rowcount
49
50    with conn.cursor() as cur:
51        for i in range(0, 10):
52            cur.execute("UPDATE persons SET NAME=%(new_name)s WHERE name=%(name)s",{
53                'name': 'Bill_%s' % random.randint(0, 10),
54                'new_name': 'Marry_%s' % random.randint(0, 10),
55            })
56
57    with conn.cursor() as cur:
58        cur.execute("SELECT * FROM persons WHERE name like 'Marry%'")
59        for row in cur:
60            print row
61        #print rows
62
63
64    print "====================================="
65    # test on duplicate key update / upsert
66    with conn.cursor() as cur:
67        #cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
68        #m = Migrator(conn)
69        r = cur.execute("""
70        CREATE TABLE IF NOT EXISTS "public"."persons2" (
71            "name" text,
72            "age" int,
73            PRIMARY KEY ("name")
74        );
75        """)
76
77    with conn.cursor() as cur:
78        rows = []
79        for i in range(0,100):
80            rows.append({
81                'name': 'Bill_%s' % random.randint(0, 3),
82                'age': random.randint(20,80),
83            })
84        # INSERT INTO tablename (a, b, c) values (1, 2, 10)
85        # ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;
86        cur.executemany("""
87        INSERT INTO persons2 (name, age) VALUES (%(name)s, %(age)s)
88        ON CONFLICT (name) DO UPDATE SET age=%(age)s
89        """, rows)
90
91        cur.execute("SELECT * FROM persons2")
92        for row in cur:
93            print row
94        #result = cur.fetchone()
95        #print result.rowcount
96        #print cur.rowcount
97        #print dir(cur)
98        #print result
99        #print dir(result)
100        #print result.count()