def test_binding_timestamps(conn_cnx, db_parameters): """ Binding datetime object with TIMESTAMP_LTZ. The value is bound as TIMESTAMP_NTZ, but since it is converted to UTC in the backend, the returned value must be """ with conn_cnx() as cnx: cnx.cursor().execute(""" create or replace table {name} ( c1 integer, c2 timestamp_ltz) """.format(name=db_parameters['name'])) try: with conn_cnx(paramstyle=u'numeric', timezone=PST_TZ) as cnx: current_localtime = datetime.now() cnx.cursor().execute( """ insert into {name}(c1, c2) values(:1, :2) """.format(name=db_parameters['name']), (123, ("TIMESTAMP_LTZ", current_localtime))) rec = cnx.cursor().execute( """ select * from {name} where c1=? """.format(name=db_parameters['name']), (123, )).fetchall() assert len(rec) == 1 assert rec[0][0] == 123 assert convert_datetime_to_epoch(rec[0][1]) == \ convert_datetime_to_epoch(current_localtime) finally: with conn_cnx() as cnx: cnx.cursor().execute(""" drop table if exists {name} """.format(name=db_parameters['name']))
def test_pendulum_binding(conn_cnx, db_parameters): pendulum_test = pendulum.now() try: with conn_cnx() as cnx: cnx.cursor().execute(""" create or replace table {name} ( c1 timestamp ) """.format(name=db_parameters['name'])) c = cnx.cursor() fmt = "insert into {name}(c1) values(%(v1)s)".format( name=db_parameters['name'] ) c.execute(fmt, {'v1': pendulum_test}) assert len(cnx.cursor().execute( "select count(*) from {name}".format( name=db_parameters['name'])).fetchall()) == 1 with conn_cnx(paramstyle='qmark') as cnx: cnx.cursor().execute(""" create or replace table {name} (c1 timestamp, c2 timestamp) """.format(name=db_parameters['name'])) with conn_cnx(paramstyle='qmark') as cnx: cnx.cursor().execute(""" insert into {name} values(?, ?) """.format(name=db_parameters['name']), (pendulum_test, pendulum_test)) ret = cnx.cursor().execute(""" select * from {name} """.format(name=db_parameters['name'])).fetchone() assert convert_datetime_to_epoch( ret[0]) == convert_datetime_to_epoch(pendulum_test) finally: with conn_cnx() as cnx: cnx.cursor().execute(""" drop table if exists {name} """.format(name=db_parameters['name']))
def test_binding(conn_cnx, db_parameters): """ Paramstyle qmark basic tests """ with conn_cnx(paramstyle=u'qmark') as cnx: cnx.cursor().execute(""" create or replace table {name} ( c1 BOOLEAN, c2 INTEGER, c3 NUMBER(38,2), c4 VARCHAR(1234), c5 FLOAT, c6 BINARY, c7 BINARY, c8 TIMESTAMP_NTZ, c9 TIMESTAMP_NTZ, c10 TIMESTAMP_NTZ, c11 TIMESTAMP_NTZ, c12 TIMESTAMP_LTZ, c13 TIMESTAMP_LTZ, c14 TIMESTAMP_LTZ, c15 TIMESTAMP_LTZ, c16 TIMESTAMP_TZ, c17 TIMESTAMP_TZ, c18 TIMESTAMP_TZ, c19 TIMESTAMP_TZ, c20 DATE, c21 TIME, c22 TIMESTAMP_NTZ, c23 TIME, c24 STRING ) """.format(name=db_parameters['name'])) current_utctime = datetime.utcnow() current_localtime = pytz.utc.localize(current_utctime, is_dst=False).astimezone( pytz.timezone(PST_TZ)) current_localtime_without_tz = datetime.now() current_localtime_with_other_tz = pytz.utc.localize( current_localtime_without_tz, is_dst=False).astimezone(pytz.timezone(JST_TZ)) dt = date(2017, 12, 30) tm = datetime_time(hour=1, minute=2, second=3, microsecond=456) struct_time_v = time.strptime("30 Sep 01 11:20:30", "%d %b %y %H:%M:%S") tdelta = timedelta(seconds=tm.hour * 3600 + tm.minute * 60 + tm.second, microseconds=tm.microsecond) try: with conn_cnx(paramstyle=u'qmark', timezone=PST_TZ) as cnx: cnx.cursor().execute( """ insert into {name} values( ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?) """.format(name=db_parameters['name']), ( True, 1, Decimal("1.2"), 'str1', 1.2, # Py2 has bytes in str type, so Python Connector bytes(b'abc') if not PY2 else bytearray(b'abc'), bytearray(b'def'), current_utctime, current_localtime, current_localtime_without_tz, current_localtime_with_other_tz, (u"TIMESTAMP_LTZ", current_utctime), (u"TIMESTAMP_LTZ", current_localtime), (u"TIMESTAMP_LTZ", current_localtime_without_tz), (u"TIMESTAMP_LTZ", current_localtime_with_other_tz), (u"TIMESTAMP_TZ", current_utctime), (u"TIMESTAMP_TZ", current_localtime), (u"TIMESTAMP_TZ", current_localtime_without_tz), (u"TIMESTAMP_TZ", current_localtime_with_other_tz), dt, tm, (u"TIMESTAMP_NTZ", struct_time_v), (u"TIME", tdelta), (u"TEXT", None))) ret = cnx.cursor().execute( """ select * from {name} where c1=? and c2=? """.format(name=db_parameters['name']), (True, 1)).fetchone() assert ret[0], "BOOLEAN" assert ret[2] == Decimal("1.2"), "NUMBER" assert ret[4] == 1.2, "FLOAT" assert ret[5] == b'abc' assert ret[6] == b'def' assert ret[7] == current_utctime assert convert_datetime_to_epoch( ret[8]) == convert_datetime_to_epoch(current_localtime) assert convert_datetime_to_epoch( ret[9]) == convert_datetime_to_epoch( current_localtime_without_tz) assert convert_datetime_to_epoch( ret[10]) == convert_datetime_to_epoch( current_localtime_with_other_tz) assert convert_datetime_to_epoch( ret[11]) == convert_datetime_to_epoch(current_utctime) assert convert_datetime_to_epoch( ret[12]) == convert_datetime_to_epoch(current_localtime) assert convert_datetime_to_epoch( ret[13]) == convert_datetime_to_epoch( current_localtime_without_tz) assert convert_datetime_to_epoch( ret[14]) == convert_datetime_to_epoch( current_localtime_with_other_tz) assert convert_datetime_to_epoch( ret[15]) == convert_datetime_to_epoch(current_utctime) assert convert_datetime_to_epoch( ret[16]) == convert_datetime_to_epoch(current_localtime) assert convert_datetime_to_epoch( ret[17]) == convert_datetime_to_epoch( current_localtime_without_tz) assert convert_datetime_to_epoch( ret[18]) == convert_datetime_to_epoch( current_localtime_with_other_tz) assert ret[19] == dt assert ret[20] == tm assert convert_datetime_to_epoch( ret[21]) == calendar.timegm(struct_time_v) assert timedelta(seconds=ret[22].hour * 3600 + ret[22].minute * 60 + ret[22].second, microseconds=ret[22].microsecond) == tdelta assert ret[23] is None finally: with conn_cnx() as cnx: cnx.cursor().execute(""" drop table if exists {name} """.format(name=db_parameters['name']))
def test_binding(conn_cnx, db_parameters, bulk_array_optimization): """Paramstyle qmark binding tests to cover basic data types.""" CREATE_TABLE = """create or replace table {name} ( c1 BOOLEAN, c2 INTEGER, c3 NUMBER(38,2), c4 VARCHAR(1234), c5 FLOAT, c6 BINARY, c7 BINARY, c8 TIMESTAMP_NTZ, c9 TIMESTAMP_NTZ, c10 TIMESTAMP_NTZ, c11 TIMESTAMP_NTZ, c12 TIMESTAMP_LTZ, c13 TIMESTAMP_LTZ, c14 TIMESTAMP_LTZ, c15 TIMESTAMP_LTZ, c16 TIMESTAMP_TZ, c17 TIMESTAMP_TZ, c18 TIMESTAMP_TZ, c19 TIMESTAMP_TZ, c20 DATE, c21 TIME, c22 TIMESTAMP_NTZ, c23 TIME, c24 STRING, c25 STRING, c26 STRING ) """ INSERT = """ insert into {name} values( ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?,?,?) """ with conn_cnx(paramstyle="qmark") as cnx: cnx.cursor().execute(CREATE_TABLE.format(name=db_parameters["name"])) current_utctime = datetime.utcnow() current_localtime = pytz.utc.localize(current_utctime, is_dst=False).astimezone( pytz.timezone(PST_TZ) ) current_localtime_without_tz = datetime.now() current_localtime_with_other_tz = pytz.utc.localize( current_localtime_without_tz, is_dst=False ).astimezone(pytz.timezone(JST_TZ)) dt = date(2017, 12, 30) tm = datetime_time(hour=1, minute=2, second=3, microsecond=456) struct_time_v = time.strptime("30 Sep 01 11:20:30", "%d %b %y %H:%M:%S") tdelta = timedelta( seconds=tm.hour * 3600 + tm.minute * 60 + tm.second, microseconds=tm.microsecond ) data = ( True, 1, Decimal("1.2"), "str1", 1.2, # Py2 has bytes in str type, so Python Connector bytes(b"abc"), bytearray(b"def"), current_utctime, current_localtime, current_localtime_without_tz, current_localtime_with_other_tz, ("TIMESTAMP_LTZ", current_utctime), ("TIMESTAMP_LTZ", current_localtime), ("TIMESTAMP_LTZ", current_localtime_without_tz), ("TIMESTAMP_LTZ", current_localtime_with_other_tz), ("TIMESTAMP_TZ", current_utctime), ("TIMESTAMP_TZ", current_localtime), ("TIMESTAMP_TZ", current_localtime_without_tz), ("TIMESTAMP_TZ", current_localtime_with_other_tz), dt, tm, ("TIMESTAMP_NTZ", struct_time_v), ("TIME", tdelta), ("TEXT", None), "", ',an\\\\escaped"line\n', ) try: with conn_cnx(paramstyle="qmark", timezone=PST_TZ) as cnx: csr = cnx.cursor() if bulk_array_optimization: cnx._session_parameters[CLIENT_STAGE_ARRAY_BINDING_THRESHOLD] = 1 csr.executemany(INSERT.format(name=db_parameters["name"]), [data]) else: csr.execute(INSERT.format(name=db_parameters["name"]), data) ret = ( cnx.cursor() .execute( """ select * from {name} where c1=? and c2=? """.format( name=db_parameters["name"] ), (True, 1), ) .fetchone() ) assert len(ret) == 26 assert ret[0], "BOOLEAN" assert ret[2] == Decimal("1.2"), "NUMBER" assert ret[4] == 1.2, "FLOAT" assert ret[5] == b"abc" assert ret[6] == b"def" assert ret[7] == current_utctime assert convert_datetime_to_epoch(ret[8]) == convert_datetime_to_epoch( current_localtime ) assert convert_datetime_to_epoch(ret[9]) == convert_datetime_to_epoch( current_localtime_without_tz ) assert convert_datetime_to_epoch(ret[10]) == convert_datetime_to_epoch( current_localtime_with_other_tz ) assert convert_datetime_to_epoch(ret[11]) == convert_datetime_to_epoch( current_utctime ) assert convert_datetime_to_epoch(ret[12]) == convert_datetime_to_epoch( current_localtime ) assert convert_datetime_to_epoch(ret[13]) == convert_datetime_to_epoch( current_localtime_without_tz ) assert convert_datetime_to_epoch(ret[14]) == convert_datetime_to_epoch( current_localtime_with_other_tz ) assert convert_datetime_to_epoch(ret[15]) == convert_datetime_to_epoch( current_utctime ) assert convert_datetime_to_epoch(ret[16]) == convert_datetime_to_epoch( current_localtime ) assert convert_datetime_to_epoch(ret[17]) == convert_datetime_to_epoch( current_localtime_without_tz ) assert convert_datetime_to_epoch(ret[18]) == convert_datetime_to_epoch( current_localtime_with_other_tz ) assert ret[19] == dt assert ret[20] == tm assert convert_datetime_to_epoch(ret[21]) == calendar.timegm(struct_time_v) assert ( timedelta( seconds=ret[22].hour * 3600 + ret[22].minute * 60 + ret[22].second, microseconds=ret[22].microsecond, ) == tdelta ) assert ret[23] is None assert ret[24] == "" assert ret[25] == ',an\\\\escaped"line\n' finally: with conn_cnx() as cnx: cnx.cursor().execute( """ drop table if exists {name} """.format( name=db_parameters["name"] ) )