tabulate: Error loading data into PostgreSQL

[duncandewhurst]

tabulate returns the following error when trying to load data into a PostgreSQL database:

sqlalchemy.exc.IdentifierError: Identifier 'contracts_implementation_transactions_payer_additionalIdentifiers' exceeds maximum length of 63 characters

It is possible to change the maximum identifier length in PostgreSQL, but only by editing and recompiling the source code, so it would be better for tabulate to truncate identifiers when the target database is PostgreSQL.

Full traceback:

Traceback (most recent call last):
  File "/usr/local/bin/ocdskit", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.7/dist-packages/ocdskit/cli/__main__.py", line 63, in main
    command.handle()
  File "/usr/local/lib/python3.7/dist-packages/ocdskit/cli/commands/tabulate.py", line 21, in handle
    metadata, engine = self.create_db(self.args.database_url, deref_schema, drop=self.args.drop)
  File "/usr/local/lib/python3.7/dist-packages/ocdskit/cli/commands/tabulate.py", line 103, in create_db
    metadata.create_all(engine)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/schema.py", line 4745, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", line 3079, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", line 2081, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/visitors.py", line 485, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/ddl.py", line 823, in visit_metadata
    [t for t in tables if self._can_create_table(t)]
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/ddl.py", line 823, in <listcomp>
    [t for t in tables if self._can_create_table(t)]
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/ddl.py", line 784, in _can_create_table
    self.dialect.validate_identifier(table.name)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py", line 579, in validate_identifier
    % (ident, self.max_identifier_length)
sqlalchemy.exc.IdentifierError: Identifier 'contracts_implementation_transactions_payer_additionalIdentifiers' exceeds maximum length of 63 characters

---------------------------------------------------------------------------

CalledProcessError                        Traceback (most recent call last)

<ipython-input-14-85ec13ec1d89> in <module>()
----> 1 get_ipython().run_cell_magic('shell', '', '\ncat compiled_releases.json | ocdskit tabulate postgresql://postgres:postgres@localhost:5432/ocds_demo')

2 frames

/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
   2115             magic_arg_s = self.var_expand(line, stack_depth)
   2116             with self.builtin_trap:
-> 2117                 result = fn(magic_arg_s, cell)
   2118             return result
   2119 

/usr/local/lib/python3.7/dist-packages/google/colab/_system_commands.py in _shell_cell_magic(args, cmd)
    111   result = _run_command(cmd, clear_streamed_output=False)
    112   if not parsed_args.ignore_errors:
--> 113     result.check_returncode()
    114   return result
    115 

/usr/local/lib/python3.7/dist-packages/google/colab/_system_commands.py in check_returncode(self)
    137     if self.returncode:
    138       raise subprocess.CalledProcessError(
--> 139           returncode=self.returncode, cmd=self.args, output=self.output)
    140 
    141   def _repr_pretty_(self, p, cycle):  # pylint:disable=unused-argument

CalledProcessError: Command '
cat compiled_releases.json | ocdskit tabulate postgresql://postgres:postgres@localhost:5432/ocds_demo' returned non-zero exit status 1.

[jpmckinney]

This command will be removed per #75.