henrykironde: I feel like this is a hacky way to solve this issue, but it is one of those edge cases.

As mention in the issue #1117, if users follow the format of the required opts for the table-name provided in the engine, we do not get this issue.

_For example_ 
**Mysql and Postgres:** `{db}.{table}`, `{db}.prefix{table}`
**Sqlite:** `{db}_{table}`, `{db}_prefix{table}`

When a user provides something like `retriever install csv portal -t march`, the engine will fail to maintain the format for the table name.

To avoid this, we are getting the default required opt for that engine `{db}_{table}.csv` and replacing the `{table}` value with `prefix{table}`.
The final format will look like `{db}_prefix{table}.csv`
henrykironde: I will add some multi table tests as I add the fetch function. #1069
diff --git a/retriever/engines/__init__.py b/retriever/engines/__init__.py
index 0f4de6b..4156f7b 100644
--- a/retriever/engines/__init__.py
+++ b/retriever/engines/__init__.py
@@ -1,4 +1,6 @@
 """Contains DBMS-specific Engine implementations."""
+import os
+
 from retriever.lib.engine import Engine
 
 engines = [
@@ -47,6 +49,11 @@ def choose_engine(opts, choice=True):
             if (enginename == thisengine.name.lower() or thisengine.abbreviation and
                     enginename == thisengine.abbreviation):
                 engine = thisengine
+    if 'table_name' in opts:
+        if opts['table_name'] and "{table}" not in opts['table_name'] or "{db}" not in opts['table_name']:
+            for opt in engine.required_opts:
+                if opt[0] == 'table_name':
+                    raise Exception('Accepted Table format {fom}'.format(fom=opt[2]))
 
     engine.opts = opts
     return engine
diff --git a/retriever/engines/csvengine.py b/retriever/engines/csvengine.py
index 18e3bf2..871de5e 100644
--- a/retriever/engines/csvengine.py
+++ b/retriever/engines/csvengine.py
@@ -88,8 +88,7 @@ class engine(Engine):
     def to_csv(self):
         """Export sorted version of CSV file"""
         for keys in self.script.tables:
-            table_name = self.opts['table_name'].format(db=self.db_name, table=keys)
-            sort_csv(table_name)
+            sort_csv(self.table_name())
 
     def get_connection(self):
         """Gets the db connection."""
diff --git a/retriever/engines/jsonengine.py b/retriever/engines/jsonengine.py
index 4b2a108..aee229d 100644
--- a/retriever/engines/jsonengine.py
+++ b/retriever/engines/jsonengine.py
@@ -114,7 +114,7 @@ class engine(Engine):
     def to_csv(self):
         """Export table from json engine to CSV file"""
         for keys in list(self.script.tables):
-            table_name = self.opts['table_name'].format(db=self.db_name, table=keys)
+            table_name = self.table_name()
             header = self.script.tables[keys].get_insert_columns(join=False, create=True)
             csv_outfile = json2csv(table_name, header_values=header)
             sort_csv(csv_outfile)
diff --git a/retriever/engines/xmlengine.py b/retriever/engines/xmlengine.py
index e03e43c..cc5816f 100644
--- a/retriever/engines/xmlengine.py
+++ b/retriever/engines/xmlengine.py
@@ -109,7 +109,7 @@ class engine(Engine):
     def to_csv(self):
         """Export table from xml engine to CSV file."""
         for keys in list(self.script.tables):
-            table_name = self.opts['table_name'].format(db=self.db_name, table=keys)
+            table_name = self.table_name()
             header = self.script.tables[keys].get_insert_columns(join=False, create=True)
             csv_outfile = xml2csv(table_name, header_values=header)
             sort_csv(csv_outfile)

Error on custom table name.
The current state of the retriever uses the required_opts  `"table_name"` to customize the installation of dataset into dataset. This works fine for single table datasets however, it over rides the tables when the dataset contains multi tables. The best way to use this option is by using this custom `table_name` as a prefix to the table name. 

For example:

"{db}.{prefix_name}_{table}") 


diff --git a/test/test_regression.py b/test/test_regression.py
index a551828..15081ec 100644
--- a/test/test_regression.py
+++ b/test/test_regression.py
@@ -64,7 +64,7 @@ def setup_module():
 def teardown_module():
     """Cleanup temporary output files and return to root directory."""
     os.chdir(retriever_root_dir)
-    os.system("rm -r output*")
+    os.system("rm -r *output*")
     shutil.rmtree(os.path.join(retriever_root_dir, "raw_data"))
     os.system("rm testdb.sqlite")
 
@@ -141,8 +141,8 @@ def test_xmlengine_regression(dataset, expected, tmpdir):
     """Check for xmlenginee regression."""
     xml_engine.opts = {
         'engine': 'xml',
-        'table_name': 'output_file_{table}.xml'}
-    interface_opts = {'table_name': 'output_file_{table}.xml'}
+        'table_name': '{db}_output_{table}.xml'}
+    interface_opts = {'table_name': '{db}_output_{table}.xml'}
     assert get_csv_md5(dataset, xml_engine, tmpdir, install_xml, interface_opts) == expected
 
 
@@ -151,8 +151,8 @@ def test_jsonengine_regression(dataset, expected, tmpdir):
     """Check for jsonenginee regression."""
     json_engine.opts = {
         'engine': 'json',
-        'table_name': 'output_file_{table}.json'}
-    interface_opts = {'table_name': 'output_file_{table}.json'}
+        'table_name': '{db}_output_{table}.json'}
+    interface_opts = {'table_name': '{db}_output_{table}.json'}
     assert get_csv_md5(dataset, json_engine, tmpdir, install_json, interface_opts) == expected
 
 
@@ -161,8 +161,8 @@ def test_csv_regression(dataset, expected, tmpdir):
     """Check csv regression."""
     csv_engine.opts = {
         'engine': 'csv',
-        'table_name': 'output_file_{table}.csv'}
-    interface_opts = {'table_name': 'output_file_{table}.csv'}
+        'table_name': '{db}_output_{table}.csv'}
+    interface_opts = {'table_name': '{db}_output_{table}.csv'}
     assert get_csv_md5(dataset, csv_engine, tmpdir, install_csv, interface_opts) == expected
 
 

pip install -e .
parse_log_pytest
requirements.txt
pytest pytest-cov pytest-xdist pytest-mock pytest-asyncio
apt-get update apt-get install -y gcc
3.6
requirements.txt
pytest --no-header -rA --tb=line --color=no -p no:cacheprovider -W ignore::DeprecationWarning
alabaster==0.7.13
argcomplete==3.1.2
attrs==22.2.0
Babel==2.11.0
certifi==2021.5.30
charset-normalizer==2.0.12
coverage==6.2
docutils==0.18.1
execnet==1.9.0
future==1.0.0
idna==3.10
imagesize==1.4.1
importlib-metadata==4.8.3
importlib-resources==5.4.0
iniconfig==1.1.1
Jinja2==3.0.3
MarkupSafe==2.0.1
numpydoc==1.1.0
packaging==21.3
pluggy==1.0.0
pockets==0.9.1
psycopg2==2.7.7
py==1.11.0
Pygments==2.14.0
PyMySQL==1.0.2
pyparsing==3.1.4
pytest==7.0.1
pytest-asyncio==0.16.0
pytest-cov==4.0.0
pytest-mock==3.6.1
pytest-xdist==3.0.2
pytz==2025.2
requests==2.27.1
-e git+https://github.com/weecology/retriever.git@e55d4d3637c62df8a14f349208759cbe133986eb#egg=retriever
six==1.17.0
snowballstemmer==2.2.0
Sphinx==5.3.0
sphinx-py3doc-enhanced-theme==2.4.0
sphinx-rtd-theme==2.0.0
sphinxcontrib-applehelp==1.0.2
sphinxcontrib-devhelp==1.0.2
sphinxcontrib-htmlhelp==2.0.0
sphinxcontrib-jquery==4.1
sphinxcontrib-jsmath==1.0.1
sphinxcontrib-napoleon==0.7
sphinxcontrib-qthelp==1.0.3
sphinxcontrib-serializinghtml==1.1.5
tomli==1.2.3
tqdm==4.64.1
typing_extensions==4.1.1
urllib3==1.26.20
xlrd==2.0.1
zipp==3.6.0

name: retriever
channels:
  - defaults
  - https://repo.anaconda.com/pkgs/main
  - https://repo.anaconda.com/pkgs/r
  - conda-forge
dependencies:
  - _libgcc_mutex=0.1=main
  - _openmp_mutex=5.1=1_gnu
  - ca-certificates=2025.2.25=h06a4308_0
  - certifi=2021.5.30=py36h06a4308_0
  - ld_impl_linux-64=2.40=h12ee557_0
  - libffi=3.3=he6710b0_2
  - libgcc-ng=11.2.0=h1234567_1
  - libgomp=11.2.0=h1234567_1
  - libstdcxx-ng=11.2.0=h1234567_1
  - ncurses=6.4=h6a678d5_0
  - openssl=1.1.1w=h7f8727e_0
  - pip=21.2.2=py36h06a4308_0
  - python=3.6.13=h12debd9_1
  - readline=8.2=h5eee18b_0
  - setuptools=58.0.4=py36h06a4308_0
  - sqlite=3.45.3=h5eee18b_0
  - tk=8.6.14=h39e8969_0
  - wheel=0.37.1=pyhd3eb1b0_0
  - xz=5.6.4=h5eee18b_1
  - zlib=1.2.13=h5eee18b_1
  - pip:
      - alabaster==0.7.13
      - argcomplete==3.1.2
      - attrs==22.2.0
      - babel==2.11.0
      - charset-normalizer==2.0.12
      - coverage==6.2
      - docutils==0.18.1
      - execnet==1.9.0
      - future==1.0.0
      - idna==3.10
      - imagesize==1.4.1
      - importlib-metadata==4.8.3
      - importlib-resources==5.4.0
      - iniconfig==1.1.1
      - jinja2==3.0.3
      - markupsafe==2.0.1
      - numpydoc==1.1.0
      - packaging==21.3
      - pluggy==1.0.0
      - pockets==0.9.1
      - psycopg2==2.7.7
      - py==1.11.0
      - pygments==2.14.0
      - pymysql==1.0.2
      - pyparsing==3.1.4
      - pytest==7.0.1
      - pytest-asyncio==0.16.0
      - pytest-cov==4.0.0
      - pytest-mock==3.6.1
      - pytest-xdist==3.0.2
      - pytz==2025.2
      - requests==2.27.1
      - six==1.17.0
      - snowballstemmer==2.2.0
      - sphinx==5.3.0
      - sphinx-py3doc-enhanced-theme==2.4.0
      - sphinx-rtd-theme==2.0.0
      - sphinxcontrib-applehelp==1.0.2
      - sphinxcontrib-devhelp==1.0.2
      - sphinxcontrib-htmlhelp==2.0.0
      - sphinxcontrib-jquery==4.1
      - sphinxcontrib-jsmath==1.0.1
      - sphinxcontrib-napoleon==0.7
      - sphinxcontrib-qthelp==1.0.3
      - sphinxcontrib-serializinghtml==1.1.5
      - tomli==1.2.3
      - tqdm==4.64.1
      - typing-extensions==4.1.1
      - urllib3==1.26.20
      - xlrd==2.0.1
      - zipp==3.6.0
prefix: /opt/conda/envs/retriever

test/test_regression.py::test_xmlengine_regression[bird_size-98dcfdca19d729c90ee1c6db5221b775] test/test_regression.py::test_xmlengine_regression[mammal_masses-6fec0fc63007a4040d9bbc5cfcd9953e] test/test_regression.py::test_jsonengine_regression[bird_size-98dcfdca19d729c90ee1c6db5221b775] test/test_regression.py::test_jsonengine_regression[mammal_masses-6fec0fc63007a4040d9bbc5cfcd9953e] test/test_regression.py::test_csv_regression[bird_size-98dcfdca19d729c90ee1c6db5221b775] test/test_regression.py::test_csv_regression[mammal_masses-6fec0fc63007a4040d9bbc5cfcd9953e]
test/test_regression.py::test_postgres_regression[bird_size-98dcfdca19d729c90ee1c6db5221b775] test/test_regression.py::test_postgres_regression[mammal_masses-6fec0fc63007a4040d9bbc5cfcd9953e] test/test_regression.py::test_mysql_regression[bird_size-98dcfdca19d729c90ee1c6db5221b775] test/test_regression.py::test_mysql_regression[mammal_masses-6fec0fc63007a4040d9bbc5cfcd9953e]
test/test_regression.py::test_sqlite_regression[bird_size-98dcfdca19d729c90ee1c6db5221b775] test/test_regression.py::test_sqlite_regression[mammal_masses-6fec0fc63007a4040d9bbc5cfcd9953e] test/test_regression.py::test_download_regression[mt-st-helens-veg-d5782e07241cb3fe9f5b2e1bb804a794] test/test_regression.py::test_download_regression[bird-size-45c7507ae945868c71b5179f7682ea9c] test/test_regression.py::test_download_regression[mammal-masses-b54b80d0d1959bdea0bb8a59b70fa871]
retriever/engines/__init__.py retriever/engines/jsonengine.py retriever/engines/xmlengine.py retriever/engines/csvengine.py
retriever/engines/__init__.py retriever/engines/jsonengine.py retriever/engines/xmlengine.py retriever/engines/csvengine.py