ysql_simple_txn_test.py

# Dependencies:
# On CentOS you can install psycopg2 thus:
#
#  sudo yum install postgresql-libs
#  sudo yum install python-psycopg2

import psycopg2;
import time;

def create_table():
  conn = psycopg2.connect("host=172.151.23.120 dbname=postgres user=postgres port=5433")
  conn.set_session(autocommit=True)
  cur = conn.cursor()

  cur.execute("""DROP TABLE IF EXISTS users""");
  cur.execute("""CREATE TABLE IF NOT EXISTS users(
                      id     text,
                      ename  text,
                      age    int,
                      PRIMARY KEY(id))
              """)
  print("Created users table")
  print("====================")


def load_table():
  conn = psycopg2.connect("host=172.151.23.120 dbname=postgres user=postgres port=5433")
  conn.set_session(autocommit=True)
  cur = conn.cursor()

  cur.execute("""PREPARE insert_stmt (int, text, int) AS
                 INSERT INTO users(id, ename, age) VALUES ($1, $2, $3)""")
  start_time = time.time()
  for idx in range(50):
     now_time = time.time()
     cur.execute("""begin transaction""");
     delta_begin = (time.time() - now_time)*1000

     now_time = time.time()
     cur.execute("""EXECUTE insert_stmt(%s, %s, %s)""",
                        (idx, "msg--"+str(idx), idx));
     delta_insert = (time.time() - now_time)*1000

     now_time = time.time()
     cur.execute("""commit""");
     delta_commit = (time.time() - now_time)*1000

     print("begintx: %s ms, insert: %s ms, commit: %s ms" % (delta_begin, delta_insert, delta_commit))


# Main
create_table()
load_table()