Can't connect to a local, existing MySQL DB

[bolinocroustibat]

First Check

• [X] I added a very descriptive title to this issue.
• [X] I used the GitHub search to find a similar issue and didn't find it.
• [X] I searched the SQLModel documentation, with the integrated search.
• [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
• [X] I already read and followed all the tutorial in the docs and didn't find an answer.
• [X] I already checked if it is not related to SQLModel but to Pydantic.
• [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class RealWordEN(SQLModel, table=True):
    id: int = Field(primary_key=True)
    string: str
    type: Optional[str]
    number: Optional[str]
    tense: Optional[str]

    __tablename__ = "real_words_EN"


engine = create_engine("mysql+mysql://root:root@localhost:8889/words", echo=True)
# engine = create_engine("mysql://root:root@localhost:8889/words", echo=True)


with Session(engine) as session:
    statement = select(RealWordEN)
    words = session.exec(statement)

Description

I've been trying to interact with an existing MySQL DB. When I try the run this code I get the following error message:

raise exc.NoSuchModuleError(
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:mysql.mysql

I also tried with th DB URL format: engine = create_engine("mysql://root:root@localhost:8889/words", echo=True). In that case, I get a different error message:

ModuleNotFoundError: No module named 'MySQLdb'

Is there some MySQL connection Python module that needs to be installed as well along sqlmodel? I've been trying with installing pymysql, aiomysql, with similar errors.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.9

Additional Context

No response

[mikkothegeeko]

Hello,

I just tried to recreate your issue. It seems like mysqlclient is the only connector that I can get to work using:

engine = create_engine("mysql://root:root@localhost:8889/words", echo=True)

Unfortunately, I couldn't find any recommended connector in the documentation.

[bolinocroustibat]

Thank you. Same, I don't know if a connector is needed and which. I think this should be in the documentation.

[invokermain]

https://docs.sqlalchemy.org/en/14/dialects/mysql.html#dialect-mysql ? This library is pretty clear that it wraps SQLAlchemy

[johnatr]

Hello,

I just tried to recreate your issue. It seems like mysqlclient is the only connector that I can get to work using:

engine = create_engine("mysql://root:root@localhost:8889/words", echo=True)

Unfortunately, I couldn't find any recommended connector in the documentation.

dang really wish the documentation would mention this. no way of me knowing, I gave up on the original client because it did not support Python3

[johnatr]

https://docs.sqlalchemy.org/en/14/dialects/mysql.html#dialect-mysql ? This library is pretty clear that it wraps SQLAlchemy

yeah but was not obvious to someone who is looking at the SQLModel page and easily overlooked. would appreciate for future developer friendliness to include notes to use pip3 install mysqlclient for me I had no idea a fork existed for python 3. just saw the original worked for python2 and not 3 which got me trying out pymysql

[invokermain]

I feel your frustration it is hard in a library such as this where do you draw the line. Obviously no point tiangolo redocumenting the whole of Sqlalchemy the maintenance burden would be insane. He doesn't really seem to maintain this library as it is. For future reference check if the error is a sql model or sql alchemy error. Your error actually says sql alchemy. Therefore you can guess its actually related to sql alchemy.

[johnatr]

I feel your frustration it is hard in a library such as this where do you draw the line. Obviously no point tiangolo redocumenting the whole of Sqlalchemy the maintenance burden would be insane. He doesn't really seem to maintain this library as it is. For future reference check if the error is a sql model or sql alchemy error. Your error actually says sql alchemy. Therefore you can guess its actually related to sql alchemy.

appreciate the reply but that is concerning to hear it is not being actively maintained. seem like it Is in a good spot, I'm not sure whether I should just stick to SQLAlchemy or incorporate SQLModel bits but so far not exactly confidence inspiring to find gaps and obviously falls outside SQLModel.

[invokermain]

For the record, I didn't mean it's not being actively maintained as such, but its only at ver 0.0.6, with 64 open pull requests. Some fix simple bugs and have been sat there for 6 months without anyone looking at them. And there hasn't been a release in 4 months. And you are right that it does seem to be in a pretty good spot.

For what its worth SQLModel does make the whole FastAPI + SQLAlchemy thing a lot nicer, but you can't side step really knowing SQLAlchemy. The alternative is to define separate Pydantic and SQLAlchemy models for basically the same thing. Also the type hinting in this library is really nice.

I'd say stick with it

[emmeowzing]

I think I just stumbled over this issue on v0.0.20.

"""
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/concurrent/futures/process.py", line 246, in _process_worker
    r = call_item.fn(*call_item.args, **call_item.kwargs)
  File "/opt/premiscale/src/premiscale/metrics/__init__.py", line 120, in __call__
    self._initialize_host()
  File "/opt/premiscale/src/premiscale/metrics/__init__.py", line 136, in _initialize_host
    stateConnection.open()
  File "/opt/premiscale/src/premiscale/metrics/state/mysql.py", line 62, in open
    connection = create_engine(self._connection_string)
  File "<string>", line 2, in create_engine
  File "/opt/premiscale/poetry-cache/virtualenvs/premiscale-s1FYViBm-py3.10/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
  File "/opt/premiscale/poetry-cache/virtualenvs/premiscale-s1FYViBm-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 599, in create_engine
    dbapi = dbapi_meth(**dbapi_args)
  File "/opt/premiscale/poetry-cache/virtualenvs/premiscale-s1FYViBm-py3.10/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 147, in import_dbapi
    return __import__("MySQLdb")
ModuleNotFoundError: No module named 'MySQLdb'
"""

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

Traceback (most recent call last):
  File "/opt/premiscale/src/premiscale/daemon.py", line 169, in start
    process.result()
  File "/usr/local/lib/python3.10/concurrent/futures/_base.py", line 451, in result
    return self.__get_result()
  File "/usr/local/lib/python3.10/concurrent/futures/_base.py", line 403, in __get_result
    raise self._exception
ModuleNotFoundError: No module named 'MySQLdb'

[emmeowzing]

I think I just stumbled over this issue on v0.0.20.

"""
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/concurrent/futures/process.py", line 246, in _process_worker
    r = call_item.fn(*call_item.args, **call_item.kwargs)
  File "/opt/premiscale/src/premiscale/metrics/__init__.py", line 120, in __call__
    self._initialize_host()
  File "/opt/premiscale/src/premiscale/metrics/__init__.py", line 136, in _initialize_host
    stateConnection.open()
  File "/opt/premiscale/src/premiscale/metrics/state/mysql.py", line 62, in open
    connection = create_engine(self._connection_string)
  File "<string>", line 2, in create_engine
  File "/opt/premiscale/poetry-cache/virtualenvs/premiscale-s1FYViBm-py3.10/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
  File "/opt/premiscale/poetry-cache/virtualenvs/premiscale-s1FYViBm-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 599, in create_engine
    dbapi = dbapi_meth(**dbapi_args)
  File "/opt/premiscale/poetry-cache/virtualenvs/premiscale-s1FYViBm-py3.10/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 147, in import_dbapi
    return __import__("MySQLdb")
ModuleNotFoundError: No module named 'MySQLdb'
"""

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

Traceback (most recent call last):
  File "/opt/premiscale/src/premiscale/daemon.py", line 169, in start
    process.result()
  File "/usr/local/lib/python3.10/concurrent/futures/_base.py", line 451, in result
    return self.__get_result()
  File "/usr/local/lib/python3.10/concurrent/futures/_base.py", line 403, in __get_result
    raise self._exception
ModuleNotFoundError: No module named 'MySQLdb'

Installing mysqlclient fixed this. Probably unrelated to this thread.

[C-L-STARK]

(kbe) ➜  knowledge-base-embedding git:(main) ✗ pip install mysqlclient                                                                                                  
Collecting mysqlclient
  Using cached mysqlclient-2.2.6.tar.gz (91 kB)
  Installing build dependencies ... done
  Getting requirements to build wheel ... error
  error: subprocess-exited-with-error
  
  × Getting requirements to build wheel did not run successfully.
  │ exit code: 1
  ╰─> [29 lines of output]
      Trying pkg-config --exists mysqlclient
      Command 'pkg-config --exists mysqlclient' returned non-zero exit status 1.
      Trying pkg-config --exists mariadb
      Command 'pkg-config --exists mariadb' returned non-zero exit status 1.
      Trying pkg-config --exists libmariadb
      Command 'pkg-config --exists libmariadb' returned non-zero exit status 1.
      Trying pkg-config --exists perconaserverclient
      Command 'pkg-config --exists perconaserverclient' returned non-zero exit status 1.
      Traceback (most recent call last):
        File "/opt/anaconda3/envs/kbe/lib/python3.12/site-packages/pip/_vendor/pyproject_hooks/_in_process/_in_process.py", line 353, in <module>
          main()
        File "/opt/anaconda3/envs/kbe/lib/python3.12/site-packages/pip/_vendor/pyproject_hooks/_in_process/_in_process.py", line 335, in main
          json_out['return_val'] = hook(**hook_input['kwargs'])
                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/opt/anaconda3/envs/kbe/lib/python3.12/site-packages/pip/_vendor/pyproject_hooks/_in_process/_in_process.py", line 118, in get_requires_for_build_wheel
          return hook(config_settings)
                 ^^^^^^^^^^^^^^^^^^^^^
        File "/private/var/folders/vf/gnd4xmld20s9gph6l1vx740w0000gn/T/pip-build-env-60w38cop/overlay/lib/python3.12/site-packages/setuptools/build_meta.py", line 334, in get_requires_for_build_wheel
          return self._get_build_requires(config_settings, requirements=[])
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/private/var/folders/vf/gnd4xmld20s9gph6l1vx740w0000gn/T/pip-build-env-60w38cop/overlay/lib/python3.12/site-packages/setuptools/build_meta.py", line 304, in _get_build_requires
          self.run_setup()
        File "/private/var/folders/vf/gnd4xmld20s9gph6l1vx740w0000gn/T/pip-build-env-60w38cop/overlay/lib/python3.12/site-packages/setuptools/build_meta.py", line 320, in run_setup
          exec(code, locals())
        File "<string>", line 155, in <module>
        File "<string>", line 49, in get_config_posix
        File "<string>", line 28, in find_package_name
      Exception: Can not find valid pkg-config name.
      Specify MYSQLCLIENT_CFLAGS and MYSQLCLIENT_LDFLAGS env vars manually
      [end of output]
  
  note: This error originates from a subprocess, and is likely not a problem with pip.
error: subprocess-exited-with-error

× Getting requirements to build wheel did not run successfully.
│ exit code: 1
╰─> See above for output.

note: This error originates from a subprocess, and is likely not a problem with pip.
(kbe) ➜  knowledge-base-embedding git:(main) ✗ 

[C-L-STARK]

$ # Assume you are activating Python 3 venv
$ brew install mysql-client pkg-config
$ export PKG_CONFIG_PATH="$(brew --prefix)/opt/mysql-client/lib/pkgconfig"
$ pip install mysqlclient

use this fixed this bug!

[YuriiMotov]

This is not SQLModel issue.

Please refer to the documentation of the driver you are using and follow installation instructions: https://docs.sqlalchemy.org/en/20/dialects/mysql.html