Compatible with MySQL?

[kelle]

@jgagneastro asked:

I'm wondering if is something my group can use to communicate with moca through MySQL, in the docs I see only SQLite examples

[dr-rodriguez]

Astrodbkit2 at its core uses SQLAlchemy, so any database architecture supported there should work, unless there are specific extensions or alterations.
All that should be needed is to write a connection string to your MySQL database with the appropriate driver and package installed: https://docs.sqlalchemy.org/en/20/core/engines.html#mysql

[jgagneastro]

Nice thank you ! I’ll try it out then. Jonathan Gagné, Ph.D. Conseiller Scientifique / Scientific Advisor Planétarium de Montréal | Espace pour la vie Professeur associé / Adjunct professor, Université de Montréal 4801 av. Pierre-de Coubertin, Montréal, Québec H1V 3V4 ***@***.******@***.***> From: David Rodriguez ***@***.***> Date: Thursday, June 27, 2024 at 1:00 PM To: astrodbtoolkit/AstrodbKit2 ***@***.***> Cc: Jonathan Gagné ***@***.***>, Mention ***@***.***> Subject: Re: [astrodbtoolkit/AstrodbKit2] Compatible with MySQL? (Issue #76) Astrodbkit2 at its core uses SQLAlchemy, so any database architecture supported there should work, unless there are specific extensions or alterations. All that should be needed is to write a connection string to your MySQL database with the appropriate driver and package installed: https://docs.sqlalchemy.org/en/20/core/engines.html#mysql — Reply to this email directly, view it on GitHub<#76 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACQFUAIWT7USQ2NQPONM6KTZJRVMDAVCNFSM6AAAAABKAON2K6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCOJVGU3TCOJUHE>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[jgagneastro]

Hey David,

I just tried using the connection string I usually use in mocapy, and got an error message. I tried using mysql+pymysql, or just mysql, and both failed (see below):

(astrodbkit2) Mizar jonathan jonathan $ python
Python 3.12.4 | packaged by Anaconda, Inc. | (main, Jun 18 2024, 10:07:17) [Clang 14.0.6 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from astrodbkit2.astrodb import Database
>>> from urllib.parse import quote_plus as urlquote
>>> import os
>>> env_username = os.environ.get('MOCA_USERNAME')
>>> env_password = os.environ.get('MOCA_PASSWORD')
>>> env_dbname = os.environ.get('MOCA_DBNAME')
>>> env_host = os.environ.get('MOCA_HOST')
>>> connection_string='mysql+pymysql://'+env_username+':'+urlquote(env_password)+'@'+env_host+'/'+env_dbname
>>> Database
<class 'astrodbkit2.astrodb.Database'>
>>> db = Database(connection_string)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/astrodbkit2/astrodb.py", line 307, in __init__
    self.session, self.base, self.engine = load_connection(
                                           ^^^^^^^^^^^^^^^^
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/astrodbkit2/astrodb.py", line 159, in load_connection
    engine = create_engine(connection_string, connect_args=connection_arguments)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in create_engine
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 599, in create_engine
    dbapi = dbapi_meth(**dbapi_args)
            ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/sqlalchemy/dialects/mysql/pymysql.py", line 75, in import_dbapi
    return __import__("pymysql")
           ^^^^^^^^^^^^^^^^^^^^^
ModuleNotFoundError: No module named 'pymysql'
>>> connection_string='mysql://'+env_username+':'+urlquote(env_password)+'@'+env_host+'/'+env_dbname
>>> db = Database(connection_string)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/astrodbkit2/astrodb.py", line 307, in __init__
    self.session, self.base, self.engine = load_connection(
                                           ^^^^^^^^^^^^^^^^
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/astrodbkit2/astrodb.py", line 159, in load_connection
    engine = create_engine(connection_string, connect_args=connection_arguments)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in create_engine
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 599, in create_engine
    dbapi = dbapi_meth(**dbapi_args)
            ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/anaconda3-native/anaconda3/envs/astrodbkit2/lib/python3.12/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 147, in import_dbapi
    return __import__("MySQLdb")
           ^^^^^^^^^^^^^^^^^^^^^
ModuleNotFoundError: No module named 'MySQLdb'

It sounds like there may currently be something incompatible with MySQL in the way astrodbkit2 is set up.

[jgagneastro]

Oh nevermind, I just needed to pip install pymysql (I did not recall having done this with mocapy).