#!/usr/bin/env python3
from mysql import connector

CREATE_STMT = """
CREATE TABLE IF NOT EXISTS timedelta(
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    read_ TIME(6) NOT NULL,
    write_ TIME(6) NULL,
    PRIMARY KEY (id)
)
"""

TRUNCATE_STMT = """
TRUNCATE TABLE timedelta
"""

INSERT_STMT = """
INSERT INTO timedelta(read_) VALUES
    ('00:00:00.000001'),
    ('-00:00:00.000001'),
    ('48:00:00.000001'),
    ('-48:00:00.000001'),
    ('00:00:01'),
    ('-00:00:01'),
    ('00:01:01'),
    ('-00:01:01'),
    ('01:01:01'),
    ('-01:01:01')
"""

READ_STMT = """
SELECT id, read_ FROM timedelta
"""

UPDATE_STMT = """
UPDATE timedelta SET
    write_ = %s
WHERE id = %s
"""

VERIFY_STMT = """
SELECT *, IF(read_ = write_,'yes','no') AS `equal?` FROM timedelta
"""

db = connector.Connect(host='localhost', database='test', user='root', password='secure')

r_cursor = db.cursor()
w_cursor = db.cursor()

r_cursor.execute(CREATE_STMT)

r_cursor.execute(TRUNCATE_STMT)

r_cursor.execute(INSERT_STMT)


r_cursor.execute(READ_STMT)
print("Readtest:")
for row in r_cursor.fetchall():
    print(*row)
    # write back in 'write_' field
    w_cursor.execute(UPDATE_STMT, (row[1], row[0])) 

print()
print("Verification:")
r_cursor.execute(VERIFY_STMT)
for row in r_cursor.fetchall():
    print(*row)

db.commit()
r_cursor.close()
w_cursor.close()
db.close()