def test_determine_output_date_order_style_datetime(self):
unhandled_hints = set()
# Records Mover only supports Postgres in ISO format at this
# point (YYYY-MM-DD) - see comments in types.py and in
# date_output_style.py for more detail.
expected_failures = {
'MM-DD-YYYY',
'DD-MM-YYYY',
'MM/DD/YY',
'DD/MM/YY',
'DD-MM-YY',
}
for dateformat in DATE_CASES:
records_format = DelimitedRecordsFormat(
hints={
'dateformat': dateformat,
'timeonlyformat': 'HH24:MI:SS',
'datetimeformattz': f'{dateformat} HH:MI:SSOF',
'datetimeformat': f'{dateformat} HH24:MI:SS'
})
fail_if_cant_handle_hint = True
validated_hints =\
records_format.validate(fail_if_cant_handle_hint=fail_if_cant_handle_hint)
try:
out = determine_date_output_style(unhandled_hints,
validated_hints,
fail_if_cant_handle_hint)
except NotImplementedError:
if dateformat in expected_failures:
pass
else:
raise
self.assertEqual(out, ('ISO', None))
def test_determine_output_date_order_style_datetimeformat(self):
unhandled_hints = set()
# Records Mover only supports Postgres in ISO format at this
# point (YYYY-MM-DD) - see comments in types.py and in
# date_output_style.py for more detail.
expected_failures = {
# no timezone, even though otherwise in ISO format
'YYYY-MM-DD HH:MI:SS',
# not in ISO format
'MM/DD/YY HH24:MI',
# not in ISO format
'YYYY-MM-DD HH12:MI AM',
}
natural_dateformat = {
'YYYY-MM-DD HH:MI:SS': 'YYYY-MM-DD',
'MM/DD/YY HH24:MI': 'MM/DD/YY',
'YYYY-MM-DD HH24:MI:SS': 'YYYY-MM-DD',
'YYYY-MM-DD HH12:MI AM': 'YYYY-MM-DD',
}
natural_timeonlyformat = {
'YYYY-MM-DD HH:MI:SS': 'HH:MI:SS',
'MM/DD/YY HH24:MI': 'HH24:MI',
'YYYY-MM-DD HH24:MI:SS': 'HH24:MI:SS',
'YYYY-MM-DD HH12:MI AM': 'HH12:MI AM',
}
natural_datetimeformattz = {
'YYYY-MM-DD HH:MI:SS': 'YYYY-MM-DD HH:MI:SSOF',
'MM/DD/YY HH24:MI': 'MM/DD/YY HH24:MIOF',
'YYYY-MM-DD HH24:MI:SS': 'YYYY-MM-DD HH24:MI:SSOF',
'YYYY-MM-DD HH12:MI AM': 'YYYY-MM-DD HH12:MI AM'
}
for datetimeformat in DATETIME_CASES:
records_format = DelimitedRecordsFormat(
hints={
'dateformat': natural_dateformat[datetimeformat],
'timeonlyformat': natural_timeonlyformat[datetimeformat],
'datetimeformattz':
natural_datetimeformattz[datetimeformat],
'datetimeformat': datetimeformat,
})
fail_if_cant_handle_hint = True
validated_hints =\
records_format.validate(fail_if_cant_handle_hint=fail_if_cant_handle_hint)
try:
out = determine_date_output_style(unhandled_hints,
validated_hints,
fail_if_cant_handle_hint)
except NotImplementedError:
if datetimeformat in expected_failures:
pass
else:
raise
self.assertEqual(out, ('ISO', None))
def test_postgres_copy_options_csv_no_quoting(self):
records_format = DelimitedRecordsFormat(variant='csv',
hints={
'quoting': None,
'compression': None,
})
unhandled_hints = set(records_format.hints)
fail_if_cant_handle_hint = True
with self.assertRaises(NotImplementedError):
postgres_copy_options_csv(
unhandled_hints,
records_format.validate(fail_if_cant_handle_hint=True),
fail_if_cant_handle_hint, CopyOptionsMode.UNLOADING)
def test_determine_output_date_order_style_iso(self):
unhandled_hints = set()
records_format = DelimitedRecordsFormat(hints={
'dateformat': 'YYYY-MM-DD',
'timeonlyformat': 'HH24:MI:SS',
'datetimeformattz': 'YYYY-MM-DD HH:MI:SSOF',
'datetimeformat': 'YYYY-MM-DD HH24:MI:SS'
})
fail_if_cant_handle_hint = True
validated_hints = records_format.validate(fail_if_cant_handle_hint=fail_if_cant_handle_hint)
out = determine_date_output_style(unhandled_hints,
validated_hints,
fail_if_cant_handle_hint)
self.assertEqual(out, ('ISO', None))
def test_redshift_copy_options_encodings(self):
tests = {
'UTF16': Encoding.utf16,
'UTF16LE': Encoding.utf16le,
'UTF16BE': Encoding.utf16be
}
for hint_spelling, redshift_sqlalchemy_spelling in tests.items():
records_format =\
DelimitedRecordsFormat(variant='bluelabs',
hints={
'encoding': hint_spelling
})
unhandled_hints = set(records_format.hints.keys())
out = redshift_copy_options(
unhandled_hints,
records_format.validate(fail_if_cant_handle_hint=True),
fail_if_cant_handle_hint=True,
fail_if_row_invalid=True,
max_failure_rows=0)
self.assertIs(out['encoding'], redshift_sqlalchemy_spelling)
def test_postgres_copy_options_csv_minimal_quoting(self):
records_format = DelimitedRecordsFormat(variant='csv',
hints={
'quoting': 'minimal',
'compression': None,
})
unhandled_hints = set(records_format.hints)
fail_if_cant_handle_hint = True
mode = CopyOptionsMode.UNLOADING
hints = records_format.validate(fail_if_cant_handle_hint=True)
out = postgres_copy_options_csv(unhandled_hints, hints,
fail_if_cant_handle_hint, mode)
self.assertEqual(
out, {
'format': 'csv',
'quote': '"',
'delimiter': ',',
'encoding': 'UTF8',
'format': 'csv',
'header': True,
})
def test_determine_output_date_order_style_timeonlyformat(self):
unhandled_hints = set()
# Records Mover only supports Postgres in ISO format at this
# point (HH24:MI:SS aka HH:MI:SS) - see comments in types.py and in
# date_output_style.py for more detail.
expected_failures = {
# only HH:MM:SS/HH24MM:SS are supported via the 'ISO'
# format output
'HH12:MI AM'
}
for timeonlyformat in TIMEONLY_CASES:
if 'AM' in timeonlyformat:
datetimeformattz = f"YYYY-MM-DD {timeonlyformat}"
else:
datetimeformattz = f"YYYY-MM-DD {timeonlyformat}OF"
records_format = DelimitedRecordsFormat(
hints={
'dateformat': "YYYY-MM-DD",
'timeonlyformat': timeonlyformat,
'datetimeformattz': datetimeformattz,
'datetimeformat': f"YYYY-MM-DD {timeonlyformat}",
})
fail_if_cant_handle_hint = True
validated_hints =\
records_format.validate(fail_if_cant_handle_hint=fail_if_cant_handle_hint)
out = None
try:
out = determine_date_output_style(unhandled_hints,
validated_hints,
fail_if_cant_handle_hint)
except NotImplementedError:
if timeonlyformat in expected_failures:
pass
else:
raise
if out is not None:
self.assertEqual(out, ('ISO', None))
def mysql_load_options(unhandled_hints: Set[str],
records_format: DelimitedRecordsFormat,
fail_if_cant_handle_hint: bool) -> MySqlLoadOptions:
hints = records_format.validate(fail_if_cant_handle_hint=fail_if_cant_handle_hint)
#
# The server uses the character set indicated by the
# character_set_database system variable to interpret the
# information in the file. SET NAMES and the setting of
# character_set_client do not affect interpretation of input. If
# the contents of the input file use a character set that differs
# from the default, it is usually preferable to specify the
# character set of the file by using the CHARACTER SET clause. A
# character set of binary specifies “no conversion.”
#
hint_encoding: HintEncoding = hints.encoding
character_set = MYSQL_CHARACTER_SETS_FOR_LOAD.get(hint_encoding)
if character_set is not None:
mysql_character_set = character_set
else:
cant_handle_hint(fail_if_cant_handle_hint, 'encoding', records_format.hints)
mysql_character_set = 'utf8'
quiet_remove(unhandled_hints, 'encoding')
field_terminator: HintFieldDelimiter = hints.field_delimiter
mysql_fields_terminator = field_terminator
quiet_remove(unhandled_hints, 'field-delimiter')
# https://dev.mysql.com/doc/refman/8.0/en/load-data.html
#
#
# LOAD DATA can be used to read files obtained from external
# sources. For example, many programs can export data in
# comma-separated values (CSV) format, such that lines have fields
# separated by commas and enclosed within double quotation marks,
# with an initial line of column names. If the lines in such a
# file are terminated by carriage return/newline pairs, the
# statement shown here illustrates the field- and line-handling
# options you would use to load the file:
#
# LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
# FIELDS TERMINATED BY ',' ENCLOSED BY '"'
# LINES TERMINATED BY '\r\n'
# IGNORE 1 LINES;
#
#
mysql_fields_enclosed_by = None
mysql_fields_optionally_enclosed_by = None
hint_quotechar: HintQuoteChar = hints.quotechar
hint_quoting: HintQuoting = hints.quoting
if hint_quoting == 'all':
mysql_fields_enclosed_by = hint_quotechar
elif hint_quoting == 'minimal':
# "If the input values are not necessarily enclosed within
# quotation marks, use OPTIONALLY before the ENCLOSED BY option."
#
# This implies to me that parsing here is permissive -
# otherwise unambiguous strings without double quotes around
# them will be understood as a string, not rejected.
mysql_fields_optionally_enclosed_by = hint_quotechar
elif hint_quoting == 'nonnumeric':
mysql_fields_optionally_enclosed_by = hint_quotechar
elif hint_quoting is None:
pass
else:
_assert_never(hint_quotechar)
quiet_remove(unhandled_hints, 'quoting')
quiet_remove(unhandled_hints, 'quotechar')
# If the field begins with the ENCLOSED BY character, instances of
# that character are recognized as terminating a field value only
# if followed by the field or line TERMINATED BY sequence. To
# avoid ambiguity, occurrences of the ENCLOSED BY character within
# a field value can be doubled and are interpreted as a single
# instance of the character. For example, if ENCLOSED BY '"' is
# specified, quotation marks are handled as shown here:
hint_doublequote: HintDoublequote = hints.doublequote
if hint_quoting is not None:
# We need to ignore flake8's "is vs ==" check because 'is'
# doesn't work currently with MyPy's Literal[] case checking
if hint_doublequote == True: # noqa: E712
pass
elif hint_doublequote == False: # noqa: E712
cant_handle_hint(fail_if_cant_handle_hint, 'doublequote', records_format.hints)
else:
_assert_never(hint_doublequote)
quiet_remove(unhandled_hints, 'doublequote')
# FIELDS ESCAPED BY controls how to read or write special characters:
#
# * For input, if the FIELDS ESCAPED BY character is not empty,
# occurrences of that character are stripped and the following
# character is taken literally as part of a field value. Some
# two-character sequences that are exceptions, where the first
# character is the escape character.
#
# [...]
#
# If the FIELDS ESCAPED BY character is empty, escape-sequence
# interpretation does not occur.
hint_escape: HintEscape = hints.escape
if hint_escape is None:
mysql_fields_escaped_by = None
elif hint_escape == '\\':
mysql_fields_escaped_by = '\\'
else:
_assert_never(hint_quoting)
quiet_remove(unhandled_hints, 'escape')
mysql_lines_starting_by = ''
hint_record_terminator: HintRecordTerminator = hints.record_terminator
mysql_lines_terminated_by = hint_record_terminator
quiet_remove(unhandled_hints, 'record-terminator')
hint_header_row: HintHeaderRow = hints.header_row
# We need to ignore flake8's "is vs ==" check because 'is'
# doesn't work currently with MyPy's Literal[] case checking
if hint_header_row == True: # noqa: E712
mysql_ignore_n_lines = 1
elif hint_header_row == False: # noqa: E712
mysql_ignore_n_lines = 0
else:
_assert_never(hint_header_row)
quiet_remove(unhandled_hints, 'header-row')
hint_compression: HintCompression = hints.compression
if hint_compression is not None:
cant_handle_hint(fail_if_cant_handle_hint, 'compression', records_format.hints)
quiet_remove(unhandled_hints, 'compression')
#
# Date/time parsing in MySQL seems to be permissive enough to
# accept all formats we've sent at it in integration tests. That
# said, DD/MM support is unlikely to work if the server isn't set
# to a compatible locale. This is true for a number of the
# database drivers; the backlog item to address is here:
#
# https://github.com/bluelabsio/records-mover/issues/75
#
# To address, we'll want to look into "set trade_date" per
# https://stackoverflow.com/questions/44171283/load-data-local-infile-with-sqlalchemy-and-pymysql
#
quiet_remove(unhandled_hints, 'dateformat')
quiet_remove(unhandled_hints, 'timeonlyformat')
quiet_remove(unhandled_hints, 'datetimeformat')
quiet_remove(unhandled_hints, 'datetimeformattz')
return MySqlLoadOptions(character_set=mysql_character_set,
fields_terminated_by=mysql_fields_terminator,
fields_enclosed_by=mysql_fields_enclosed_by,
fields_optionally_enclosed_by=mysql_fields_optionally_enclosed_by,
fields_escaped_by=mysql_fields_escaped_by,
lines_starting_by=mysql_lines_starting_by,
lines_terminated_by=mysql_lines_terminated_by,
ignore_n_lines=mysql_ignore_n_lines)
def test_valid(self):
variants = list(get_args(DelimitedVariant)) # type: ignore
for variant in variants:
records_format = DelimitedRecordsFormat(variant=variant)
records_format.validate(fail_if_cant_handle_hint=True)
def test_invalid_dateformattz(self):
records_format = DelimitedRecordsFormat(
variant='bluelabs', hints={'datetimeformattz': 'invalid'})
with self.assertRaises(NotImplementedError):
records_format.validate(fail_if_cant_handle_hint=True)