Upgrade for compatibility with SQLAlchemy 2

[jesus-hergueta]

Hi.

I'm trying to use the tool to export a Dockerized PostgresDB to a SQLite DB in order to read it with Datasette.

But when I try to run the command get the following error:

python3 -m venv venv
source venv/bin/activate
pip install db-to-sqlite
pip install 'db-to-sqlite[postgresql]'
And then:

❯ db-to-sqlite "postgresql://user:password@localhost:8080/my_db" demo.db \
    --all \
    --postgres-schema public
Traceback (most recent call last):
  File "/root/datasette/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1410, in execute
    meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/root/datasette/venv/bin/db-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/root/datasette/venv/lib/python3.10/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/root/datasette/venv/lib/python3.10/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/root/datasette/venv/lib/python3.10/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/root/datasette/venv/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/root/datasette/venv/lib/python3.10/site-packages/db_to_sqlite/cli.py", line 112, in cli
    results = db_conn.execute("select * from {}".format(table_quoted))
  File "/root/datasette/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    raise exc.ObjectNotExecutableError(statement) from err
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'select * from "some_table"'

I'm using PG 13.9-1.pgdg110+1 in Docker Desktop 4.16.3 (96739).
Python 3.10.6
Ubuntu 22.04.1 LTS with WSL2

[simonw]

I'm seeing this error too: https://github.com/simonw/simonwillisonblog-backup/actions/runs/4108524950/jobs/7089316644

Traceback (most recent call last):
  File "/opt/hostedtoolcache/Python/3.10.9/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1410, in execute
    meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/hostedtoolcache/Python/3.10.9/x64/bin/db-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/opt/hostedtoolcache/Python/3.10.9/x64/lib/python3.10/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/opt/hostedtoolcache/Python/3.10.9/x64/lib/python3.10/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/opt/hostedtoolcache/Python/3.10.9/x64/lib/python3.10/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/opt/hostedtoolcache/Python/3.10.9/x64/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/opt/hostedtoolcache/Python/3.10.9/x64/lib/python3.10/site-packages/db_to_sqlite/cli.py", line 112, in cli
    results = db_conn.execute("select * from {}".format(table_quoted))
  File "/opt/hostedtoolcache/Python/3.10.9/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    raise exc.ObjectNotExecutableError(statement) from err
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'select * from "auth_permission"'

[simonw]

I bet this is related to SQLAlchemy 2: https://docs.sqlalchemy.org/en/14/changelog/migration_20.html

% pip freeze | grep SQ 
SQLAlchemy==2.0.1

[simonw]

Running this fixes the errors:

pip install 'SQLAlchemy<2.0' 

[simonw]

With SQLAlchemy 1.4 installed I can run this to see migration hints:

SQLALCHEMY_WARN_20=1 pytest

I get back 41 warnings:

tests/test_db_to_sqlite.py: 41 warnings
tests/test_redact.py: 12 warnings
  /Users/simon/Dropbox/Development/db-to-sqlite/db_to_sqlite/cli.py:112: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
    results = db_conn.execute("select * from {}".format(table_quoted))

tests/test_db_to_sqlite.py: 43 warnings
tests/test_redact.py: 12 warnings
  /Users/simon/Dropbox/Development/db-to-sqlite/db_to_sqlite/cli.py:186: RemovedIn20Warning: The Row.keys() method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. Use the namedtuple standard accessor Row._fields, or for full mapping behavior use  row._mapping.keys()  (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
    d = dict(row)

tests/test_db_to_sqlite.py: 134 warnings
tests/test_redact.py: 36 warnings
  /Users/simon/Dropbox/Development/db-to-sqlite/db_to_sqlite/cli.py:114: RemovedIn20Warning: Using non-integer/slice indices on Row is deprecated and will be removed in version 2.0; please use row._mapping[<key>], or the mappings() accessor on the Result object. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
    rows = (redacted_dict(r, redact_these) for r in results)

tests/test_db_to_sqlite.py::test_specific_tables[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_specific_tables[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_specific_tables[postgres://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_specific_tables[postgres://localhost/test_db_to_sqlite]
  /Users/simon/Dropbox/Development/db-to-sqlite/db_to_sqlite/cli.py:109: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
    count = db_conn.execute(

tests/test_db_to_sqlite.py::test_sql_query[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgres://localhost/test_db_to_sqlite]
  /Users/simon/Dropbox/Development/db-to-sqlite/db_to_sqlite/cli.py:170: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
    results = db_conn.execute(sql)

tests/test_db_to_sqlite.py::test_sql_query[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgres://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgres://localhost/test_db_to_sqlite]
  /Users/simon/Dropbox/Development/db-to-sqlite/db_to_sqlite/cli.py:171: RemovedIn20Warning: The Row.keys() method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. Use the namedtuple standard accessor Row._fields, or for full mapping behavior use  row._mapping.keys()  (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
    rows = (dict(r) for r in results)

tests/test_db_to_sqlite.py::test_sql_query[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgresql://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgres://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgres://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgres://localhost/test_db_to_sqlite]
tests/test_db_to_sqlite.py::test_sql_query[postgres://localhost/test_db_to_sqlite]
  /Users/simon/.local/share/virtualenvs/db-to-sqlite-Nk_u3PUs/lib/python3.10/site-packages/sqlite_utils/db.py:3604: RemovedIn20Warning: Using non-integer/slice indices on Row is deprecated and will be removed in version 2.0; please use row._mapping[<key>], or the mappings() accessor on the Result object. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
    for record in records:

[simonw]

Doing this fixes the warnings about .execute():

from sqlalchemy import text

# ...

results = db_conn.execute(text(sql))

[simonw]

Finally got the tests to pass in:

• Some of the tests are hanging in CI #47

[jesus-hergueta]

Thank you @simonw !