def testPeriodDataTypes(self):
# REST-304 - REST Does not support for period data types.
if self.dsn == "ODBC":
with udaExec.connect(self.dsn,
username=self.username,
password=self.password) as conn:
self.assertIsNotNone(conn)
conn.execute("""CREATE TABLE testPeriodDataTypes (id INTEGER,
a PERIOD(DATE),
b PERIOD(DATE) FORMAT 'YYYY-MM-DD',
c PERIOD(DATE) FORMAT 'YYYYMMDD',
d PERIOD(TIMESTAMP),
e PERIOD(TIMESTAMP WITH TIME ZONE),
f PERIOD(TIME),
g PERIOD(TIME WITH TIME ZONE))""")
period = datatypes.Period(datetime.date(1980, 4, 10),
datetime.date(2015, 7, 2))
conn.execute(
"INSERT INTO testPeriodDataTypes (id, a, b, c) VALUES "
"(1, PERIOD(DATE '1980-04-10', DATE '2015-07-02'), "
"'(1980-04-10, 2015-07-02)',"
"'(1980-04-10, 2015-07-02))')")
conn.execute(
"INSERT INTO testPeriodDataTypes (id, a, b, c) VALUES "
"(2, ?, ?, ?)",
("('1980-04-10', '2015-07-02')",
'(1980-04-10, 2015-07-02)', '(1980-04-10, 2015-07-02)'))
conn.execute(
"INSERT INTO testPeriodDataTypes (id, a, b, c) VALUES "
"(3, ?, ?, ?)", (period, period, period))
for row in conn.execute("SELECT * FROM testPeriodDataTypes "
"WHERE id IN (1,2,3) ORDER BY id"):
self.assertEqual(row.a, period)
self.assertEqual(row.b, period)
self.assertEqual(row.c, period)
periodWithZone = datatypes.Period(
datetime.datetime(1980, 4, 10, 23, 45, 15, 0,
datatypes.TimeZone("+", 0, 0)),
datetime.datetime(2015, 7, 2, 17, 36, 33, 0,
datatypes.TimeZone("+", 0, 0)))
periodWithoutZone = datatypes.Period(
datetime.datetime(1980, 4, 10, 23, 45, 15),
datetime.datetime(2015, 7, 2, 17, 36, 33))
conn.execute(
"INSERT INTO testPeriodDataTypes (id, d, e) VALUES "
"(4, PERIOD(TIMESTAMP '1980-04-10 23:45:15', "
"TIMESTAMP '2015-07-02 17:36:33'), "
"'(1980-04-10 23:45:15+00:00, "
"2015-07-02 17:36:33+00:00)')")
conn.execute(
"INSERT INTO testPeriodDataTypes (id, d, e) VALUES "
"(5, ?, ?)", (periodWithoutZone, periodWithZone))
for row in conn.execute("SELECT * FROM testPeriodDataTypes "
"WHERE id IN (4,5) ORDER BY id"):
self.assertEqual(
row.d, periodWithoutZone,
str(row.d) + "!=" + str(periodWithoutZone))
self.assertEqual(row.e, periodWithZone,
str(row.e) + "!=" + str(periodWithZone))
timeWithZone = datatypes.Period(
datetime.time(17, 36, 33, 0, datatypes.TimeZone("+", 0,
0)),
datetime.time(23, 45, 15, 0, datatypes.TimeZone("+", 0,
0)))
timeWithoutZone = datatypes.Period(datetime.time(17, 36, 33),
datetime.time(23, 45, 15))
conn.execute(
"INSERT INTO testPeriodDataTypes (id, f, g) VALUES "
"(6, PERIOD(TIME '17:36:33', TIME '23:45:15'), "
"'(17:36:33+00:00, 23:45:15+00:00)')")
conn.execute(
"INSERT INTO testPeriodDataTypes (id, f, g) VALUES "
"(7, ?, ?)", (timeWithoutZone, timeWithZone))
for row in conn.execute("SELECT * FROM testPeriodDataTypes "
"WHERE id IN (6,7) ORDER BY id"):
self.assertEqual(row.f, timeWithoutZone,
str(row.f) + "!=" + str(timeWithoutZone))
self.assertEqual(row.g, timeWithZone,
str(row.g) + "!=" + str(timeWithZone))
periodUntilChange = datatypes.Period(
datetime.date(1980, 4, 10), datetime.date(9999, 12, 31))
conn.execute(
"INSERT INTO testPeriodDataTypes (id, a, b, c) VALUES "
"(8, PERIOD(DATE '1980-04-10', UNTIL_CHANGED), "
"PERIOD(DATE '1980-04-10', UNTIL_CHANGED), NULL)")
for row in conn.execute("SELECT * FROM testPeriodDataTypes "
"WHERE id IN (8) ORDER BY id"):
self.assertEqual(row.a, periodUntilChange)
self.assertEqual(row.b, periodUntilChange)
self.assertIsNone(row.c)
def testDateAndTimeDataTypes(self):
with udaExec.connect(self.dsn,
username=self.username,
password=self.password) as conn:
self.assertIsNotNone(conn)
with conn.cursor() as cursor:
cursor.execute("""CREATE TABLE testDateAndTimeDataTypes (
id INT,
name VARCHAR(128),
"timestamp" TIMESTAMP,
timestampWithZone TIMESTAMP WITH TIME ZONE,
"time" TIME,
"timeWithZone" TIME WITH TIME ZONE,
"date" DATE,
timestamp3 TIMESTAMP(3))""")
timestamp = datetime.datetime(2015, 5, 18, 12, 34, 56, 789000)
timestampWithZone = datetime.datetime(
2015, 5, 18, 12, 34, 56, 789000,
datatypes.TimeZone("-", 5, 0))
time = datetime.time(12, 34, 56, 789000)
timeWithZone = datetime.time(12, 34, 56, 789000,
datatypes.TimeZone("+", 10, 30))
date = datetime.date(2015, 5, 18)
timestamp3 = datetime.datetime(2015, 5, 18, 12, 34, 56, 789000)
cursor.execute(
"INSERT INTO testDateAndTimeDataTypes "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
("1", "TEST1", "2015-05-18 12:34:56.789",
"2015-05-18 12:34:56.789-05:00", "12:34:56.789",
"12:34:56.789+10:30", "2015-05-18",
"2015-05-18 12:34:56.789"))
cursor.execute(
"INSERT INTO testDateAndTimeDataTypes "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
(2, "TEST2", timestamp, timestampWithZone, time,
timeWithZone, date, str(timestamp3)[:-3]))
cursor.execute(
"INSERT INTO testDateAndTimeDataTypes VALUES "
"(3, 'TEST3', '2015-05-18 12:34:56.789', "
"'2015-05-18 12:34:56.789-05:00', '12:34:56.789', "
"'12:34:56.789+10:30', '2015-05-18', "
"'2015-05-18 12:34:56.789')")
rowId = 0
for row in cursor.execute("SELECT * FROM "
"testDateAndTimeDataTypes "
"ORDER BY id"):
rowId += 1
self.assertEqual(row.id, rowId)
self.assertEqual(row.name, "TEST" + str(rowId))
count = 0
for t in (row.timestamp, row.timestampWithZone, row.time,
row.timeWithZone, row.date, row.timestamp3):
if count not in (2, 3):
self.assertEqual(t.year, 2015)
self.assertEqual(t.month, 5)
self.assertEqual(t.day, 18)
if count != 4:
self.assertEqual(t.hour, 12)
self.assertEqual(t.minute, 34)
self.assertEqual(t.second, 56)
self.assertEqual(t.microsecond, 789000)
count += 1
# Time zone information is not coming back for REST per
# REST-302.
if self.dsn == "ODBC":
self.assertEqual(
row.timestampWithZone.tzinfo.utcoffset(None),
datetime.timedelta(hours=-5))
self.assertEqual(
row.timeWithZone.tzinfo.utcoffset(None),
datetime.timedelta(hours=10, minutes=30))
self.assertEqual(row.timestampWithZone,
timestampWithZone)
self.assertEqual(row.timeWithZone, timeWithZone)
self.assertEqual(row.timestamp, timestamp)
self.assertEqual(row.time, time)
self.assertEqual(row.date, date)
self.assertEqual(rowId, 3)