sqlalchemy.exc.ArgumentError: Textual SQL expression 'CREATE VIEW IF NOT EXISTS...' should be explicitly declared as text('CREATE VIEW IF NOT EXISTS...')

[rlyttle]

Device: Fenix 6s pro Host: OSX

Since Fenix 6s Pro only enables MTP for file transfer, I copied the files from the device to a local folder /Users/user/garmin/garmin/ and set the following in the configuration file:

"copy": {
        "mount_dir"                     : "/Users/user/garmin"
    },

When running import from this local directory using garmindb_cli.py --all --copy --import --analyze --latest -t 5 I get an error:

garmindb_cli.py --all --copy --import --analyze --latest -t 5

___Copying Data___
Copying files from /Users/user/garmin/garmin/settings to /Users/user/HealthData/FitFiles
100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 1805.55files/s]
Copying files from /Users/user/garmin/garmin/activity to /Users/user/HealthData/FitFiles/Activities
100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 3048.19files/s]
Copying files from /Users/user/garmin/garmin/monitor to /Users/user/HealthData/FitFiles/Monitoring/2023
100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 18/18 [00:00<00:00, 2868.01files/s]
Copying files from /Users/user/garmin/garmin/sleep to /Users/user/HealthData/FitFiles/Monitoring/2023
100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 1952.66files/s]
___Importing Latest Data___
Processing profile data
Traceback (most recent call last):
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/idbutils/db_object.py", line 200, in create_join_view
    cls.__create_view_if_not_exists(session, view_name, str(query))
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/idbutils/db_object.py", line 181, in __create_view_if_not_exists
    result = session.execute('CREATE VIEW IF NOT EXISTS ' + view_name + ' AS ' + query_str)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
    return self._execute_internal(
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2022, in _execute_internal
    statement = coercions.expect(roles.StatementRole, statement)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlalchemy/sql/coercions.py", line 413, in expect
    resolved = impl._literal_coercion(
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlalchemy/sql/coercions.py", line 639, in _literal_coercion
    return self._text_coercion(element, argname, **kw)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlalchemy/sql/coercions.py", line 632, in _text_coercion
    return _no_text_coercion(element, argname)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlalchemy/sql/coercions.py", line 602, in _no_text_coercion
    raise exc_cls(
sqlalchemy.exc.ArgumentError: Textual SQL expression 'CREATE VIEW IF NOT EXISTS...' should be explicitly declared as text('CREATE VIEW IF NOT EXISTS...')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/user/.pyenv/versions/3.9.0/bin/garmindb_cli.py", line 358, in <module>
    main(sys.argv[1:])
  File "/Users/user/.pyenv/versions/3.9.0/bin/garmindb_cli.py", line 342, in main
    import_data(args.trace, args.latest, args.stats)
  File "/Users/user/.pyenv/versions/3.9.0/bin/garmindb_cli.py", line 166, in import_data
    gp = GarminProfile(db_params_dict, fit_files_dir, debug)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/garmindb/import_monitoring.py", line 281, in __init__
    self.garmin_db = GarminDb(db_params)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/idbutils/db.py", line 66, in __init__
    self.init_table(table)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/idbutils/db.py", line 76, in init_table
    table.setup(self)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/idbutils/db_object.py", line 72, in setup
    cls.create_view(db)
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/garmindb/garmindb/garmin_db.py", line 120, in create_view
    cls.create_join_view(db, cls._get_default_view_name(), cols, Device, order_by=cls.timestamp.desc())
  File "/Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages/idbutils/db_object.py", line 202, in create_join_view
    raise DbViewException(f"Failed to create DB view {view_name} with table {join_table}", e)
idbutils.db_object.DbViewException: Failed to create DB view device_info_view with table <class 'garmindb.garmindb.garmin_db.Device'>:Textual SQL expression 'CREATE VIEW IF NOT EXISTS...' should be explicitly declared as text('CREATE VIEW IF NOT EXISTS...')

The import seems to be failing due to SQL error. I can confirm I have the required modules installed at the versions stated in https://github.com/tcgoetz/GarminDB/blob/master/requirements.in

GarminDB installed via pip on mac:

❯ pip show garmindb
Name: garmindb
Version: 3.2.6
Summary: Download data from Garmin Connect and store it in a SQLite db for analysis.
Home-page: https://github.com/tcgoetz/GarminDB
Author: Tom Goetz
Author-email:
License:
Location: /Users/user/.pyenv/versions/3.9.0/lib/python3.9/site-packages
Requires: cached-property, cloudscraper, fitfile, idbutils, ipykernel, ipyleaflet, matplotlib, python-dateutil, requests, sqlalchemy, tcxfile, tqdm
Required-by:

[Shamp0o]

I have the same issue on both Mac (M1) and Linux (Manjaro).

[tcgoetz]

The sqlalchemy version is not pinned in the requirements file. I'm guessing you have a newer version than I do. What version of sqlalchemy do you have? I have:

SQLAlchemy 1.4.42

[rlyttle]

Ah, I'm running Version: 2.0.0 from pip

[tcgoetz]

I'll look at putting out a version that works with 2.0 soon. In the meantime you can force sqlalchemy back to the earlier version as a workaround.

[Shamp0o]

Still facing the same error with 1.4.42

[mbdevpl]

@Shamp0o Strange. I had the same issue as you, but after pinning SQLAlchemy dependency like SQLAlchemy ~= 1.4 GarminDB download is working again.

For the record, here's my traceback (before pinning):

 Traceback (most recent call last):
   File "/usr/local/lib/python3.10/site-packages/idbutils/db_object.py", line 200, in create_join_view
     cls.__create_view_if_not_exists(session, view_name, str(query))
   File "/usr/local/lib/python3.10/site-packages/idbutils/db_object.py", line 181, in __create_view_if_not_exists
     result = session.execute('CREATE VIEW IF NOT EXISTS ' + view_name + ' AS ' + query_str)
   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
     return self._execute_internal(
   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2022, in _execute_internal
     statement = coercions.expect(roles.StatementRole, statement)
   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 413, in expect
     resolved = impl._literal_coercion(
   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 639, in _literal_coercion
     return self._text_coercion(element, argname, **kw)
   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 632, in _text_coercion
     return _no_text_coercion(element, argname)
   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 602, in _no_text_coercion
     raise exc_cls(
 sqlalchemy.exc.ArgumentError: Textual SQL expression 'CREATE VIEW IF NOT EXISTS...' should be explicitly declared as text('CREATE VIEW IF NOT EXISTS...')

 During handling of the above exception, another exception occurred:

 Traceback (most recent call last):
   File "/usr/local/bin/garmindb_cli.py", line 358, in <module>
     main(sys.argv[1:])
   File "/usr/local/bin/garmindb_cli.py", line 339, in main
     download_data(args.overwrite, args.latest, args.stats)
   File "/usr/local/bin/garmindb_cli.py", line 136, in download_data
     date, days = __get_date_and_days(GarminDb(db_params_dict), latest, Sleep, Sleep.total_sleep, 'sleep')
   File "/usr/local/lib/python3.10/site-packages/idbutils/db.py", line 66, in __init__
     self.init_table(table)
   File "/usr/local/lib/python3.10/site-packages/idbutils/db.py", line 76, in init_table
     table.setup(self)
   File "/usr/local/lib/python3.10/site-packages/idbutils/db_object.py", line 72, in setup
     cls.create_view(db)
   File "/usr/local/lib/python3.10/site-packages/garmindb/garmindb/garmin_db.py", line 120, in create_view
     cls.create_join_view(db, cls._get_default_view_name(), cols, Device, order_by=cls.timestamp.desc())
   File "/usr/local/lib/python3.10/site-packages/idbutils/db_object.py", line 202, in create_join_view
     raise DbViewException(f"Failed to create DB view {view_name} with table {join_table}", e)
 idbutils.db_object.DbViewException: Failed to create DB view device_info_view with table <class 'garmindb.garmindb.garmin_db.Device'>:Textual SQL expression 'CREATE VIEW IF NOT EXISTS...' should be explicitly declared as text('CREATE VIEW IF NOT EXISTS...')

Note: I also used a backup of the DB when retrying (just in case), and I also pinned the GarminDB version to 3.2.5 as I faced the following problem due to update of DB schema between 3.2.5 and 3.2.6. Which is unrelated to this issue, and not a real issue, I just need to figure out how to rebuild my DB.

 Traceback (most recent call last):
   File "/usr/local/bin/garmindb_cli.py", line 358, in <module>
     main(sys.argv[1:])
   File "/usr/local/bin/garmindb_cli.py", line 339, in main
     download_data(args.overwrite, args.latest, args.stats)
   File "/usr/local/bin/garmindb_cli.py", line 136, in download_data
     date, days = __get_date_and_days(GarminDb(db_params_dict), latest, Sleep, Sleep.total_sleep, 'sleep')
   File "/usr/local/lib/python3.10/site-packages/idbutils/db.py", line 66, in __init__
     self.init_table(table)
   File "/usr/local/lib/python3.10/site-packages/idbutils/db.py", line 77, in init_table
     self.attributes.table_version_check(self, table)
   File "/usr/local/lib/python3.10/site-packages/idbutils/db_attributes.py", line 34, in table_version_check
     raise RuntimeError("DB: %s table %s version mismatch. The DB schema has been updated. Please rebuild the %s DB. (%s vs %s)" %
 RuntimeError: DB: garmin table sleep version mismatch. The DB schema has been updated. Please rebuild the garmin DB. (2 vs 3)

[tcgoetz]

New release with SqlAlchemy pinned to 1.4: https://pypi.org/manage/project/garmindb/release/3.2.8/.

I have also started work on supporting sqlAlchemy 2.x in the develop branch.