Can't pass database URL to imdbpy2sql.py script

Question

Whether I'm running imdbpy2sql.py or s32cinemagoer.py the same error occurs:

python3 s32cinemagoer.py /home/username/frozendata 'mysql://imdb:imdb@localhost/imdb'

Traceback (most recent call last):
  File "s32cinemagoer.py", line 197, in <module>
    engine = sqlalchemy.create_engine(db_uri, encoding='utf-8', echo=False)

  File "<string>", line 2, in create_engine
  File "/home/username/.local/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
  File "/home/username/.local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 680, in create_engine
    raise TypeError(
TypeError: Invalid argument(s) 'encoding' sent to create_engine(), using configuration MySQLDialect_mysqldb/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of component

I've checked SQLAlchemy documentation on create_engine() function, but still don't get what's the issue with the argumetns I am passing in. create_engine function takes several arguments (e.g.engine = create_engine("mysql://scott:tiger@hostname/dbname", encoding='latin1', echo=True) including "encoding" argument.

Here is what SQLAlchemy documentation says about legacy utf-8 encoding in MySQL:

The encoding used for Unicode has traditionally been 'utf8'. However, for MySQL versions 5.5.3 and MariaDB 5.5 on forward, a new MySQL-specific encoding 'utf8mb4' has been introduced, and as of MySQL 8.0 a warning is emitted by the server if plain utf8 is specified within any server-side directives, replaced with utf8mb3. The rationale for this new encoding is due to the fact that MySQL’s legacy utf-8 encoding only supports codepoints up to three bytes instead of four. Therefore, when communicating with a MySQL or MariaDB database that includes codepoints more than three bytes in size, this new charset is preferred, if supported by both the database as well as the client DBAPI.

Here is the set_connection() function from cinemagoer package that probably passes wrong encoding:

def setConnection(uri, tables, encoding='utf8', debug=False):
    """Set connection for every table."""
    params = {'encoding': encoding}
    # FIXME: why on earth MySQL requires an additional parameter,
    #        is well beyond my understanding...
    if uri.startswith('mysql'):
        if '?' in uri:
            uri += '&'
        else:
            uri += '?'
        uri += 'charset=%s' % encoding

I am now trying to run script with SQLAlchemy 1.3, as suggested here and PyMySQL adapter (otherwise I get No module named 'MySQLdb' error), but it doesn`t seem to work either, same issue occurs.

Ubuntu 20.04.6 LTS
Python 3.8.10
cinemagoer 2023.5.1
SQLAlchemy 1.3/2.0

score 0 · Answer 1 · answered Jun 22 '23 at 11:22

0

Do you need to wrap the -u argument in quotes?

answered Jun 22 '23 at 11:22

Austin

39
3

Won't work. If I pass python3 imdbpy2sql.py -d `/home/username/frozendata -u 'mysql://imdb:imdb@host/imdb'`nothing changes. If I pass `python3 imdbpy2sql.py -d /home/username/frozendata -u \`mysql://imdb:imdb@localhost/imdb\`` I get `-bash: mysql://imdb:imdb@host/imdb`: No such file or directory Troubles with arguments. – Васисулий Пупкин Jun 22 '23 at 12:00
maybe it's this answer with triple slashes https://stackoverflow.com/questions/16910847/cant-access-the-sql-database – Austin Jun 22 '23 at 13:34
Still the same issue occurs. Seems like the problem is with the "encoding" argument that script passes to SQLAlchemy function. I will try to hardcode [new MySQL-specific encoding 'utf8mb4'](https://docs.sqlalchemy.org/en/20/dialects/mysql.html#charset-selection) into the script and see what happens. – Васисулий Пупкин Jun 23 '23 at 05:12

score 0 · Accepted Answer · answered Jun 23 '23 at 08:58

0

can you try this? just an example, passing encoding directly to db uri

def setConnection(uri, tables, encoding='utf8', debug=False):
    """Set connection for every table."""
    # FIXME: why on earth MySQL requires an additional parameter,
    #        is well beyond my understanding...
    if uri.startswith('mysql'):
        if '?' in uri:
            uri += '&'
        else:
            uri += '?'
        uri += 'charset=%s' % encoding

    engine = sqlalchemy.create_engine(uri, echo=debug)

    ......

answered Jun 23 '23 at 08:58

Baktybek Baiserkeev

577
4
4

Thanks for reply! I've hardcoded 'latin1' encoding like this: `def setConnection(uri, tables, encoding='latin1', debug=False): """Set connection for every table.""" params = {'encoding': encoding} # FIXME: why on earth MySQL requires an additional parameter, # is well beyond my understanding... if uri.startswith('mysql'): if '?' in uri: uri += '&' else: uri += '?'`And it seems to be working now! I had to downgrade [SQLAlchemy to version 1.4](https://github.com/cinemagoer/cinemagoer/issues/454) too. – Васисулий Пупкин Jun 23 '23 at 13:48

Can't pass database URL to imdbpy2sql.py script

2 Answers2