2012-03-05 12:11:53 -06:00
|
|
|
# This script allows easy access to Galaxy's database layer via the
|
2012-10-11 16:43:37 -04:00
|
|
|
# Galaxy models. For example:
|
2018-01-11 10:42:38 +01:00
|
|
|
# % python -i scripts/db_shell.py -c config/galaxy.ini
|
2012-03-05 12:11:53 -06:00
|
|
|
# >>> new_user = User("admin@gmail.com")
|
|
|
|
|
# >>> new_user.set_password
|
|
|
|
|
# >>> sa_session.add(new_user)
|
|
|
|
|
# >>> sa_session.commit()
|
|
|
|
|
# >>> sa_session.query(User).all()
|
2013-12-05 00:20:54 -06:00
|
|
|
#
|
2018-01-11 10:42:38 +01:00
|
|
|
# If you use ipython use:
|
|
|
|
|
# % ipython -i scripts/db_shell.py -- -c config/galaxy.ini
|
|
|
|
|
#
|
2012-10-11 16:43:37 -04:00
|
|
|
# You can also use this script as a library, for instance see https://gist.github.com/1979583
|
2012-03-05 12:11:53 -06:00
|
|
|
# these should maybe be refactored to remove duplication.
|
2016-06-26 02:43:57 -04:00
|
|
|
|
2015-07-13 20:13:28 +01:00
|
|
|
import datetime
|
|
|
|
|
import decimal
|
2023-07-15 16:06:07 +10:00
|
|
|
import logging
|
2013-12-05 00:20:54 -06:00
|
|
|
import os.path
|
2015-07-13 20:13:28 +01:00
|
|
|
import sys
|
2016-06-25 21:46:52 -04:00
|
|
|
|
2013-12-05 00:20:54 -06:00
|
|
|
# Setup DB scripting environment
|
2015-07-13 20:13:28 +01:00
|
|
|
from sqlalchemy import * # noqa
|
|
|
|
|
from sqlalchemy.exc import * # noqa
|
|
|
|
|
from sqlalchemy.orm import * # noqa
|
2017-11-15 08:56:10 -05:00
|
|
|
from sqlalchemy.sql import label # noqa
|
2012-03-05 12:11:53 -06:00
|
|
|
|
2016-02-18 13:38:07 +00:00
|
|
|
sys.path.insert(1, os.path.abspath(os.path.join(os.path.dirname(__file__), os.pardir, "lib")))
|
|
|
|
|
|
2018-01-19 15:09:38 +01:00
|
|
|
from galaxy.datatypes.registry import Registry
|
2015-07-13 20:13:28 +01:00
|
|
|
from galaxy.model import * # noqa
|
2018-03-23 09:41:05 -04:00
|
|
|
from galaxy.model import set_datatypes_registry # More explicit than `*` import
|
2016-02-18 13:38:07 +00:00
|
|
|
from galaxy.model.mapping import init
|
|
|
|
|
from galaxy.model.orm.scripts import get_config
|
|
|
|
|
|
2023-07-15 16:06:07 +10:00
|
|
|
WARNING_MODULES = ["parso", "asyncio", "galaxy.datatypes"]
|
|
|
|
|
for mod in WARNING_MODULES:
|
|
|
|
|
logger = logging.getLogger(mod)
|
|
|
|
|
logger.setLevel("WARNING")
|
|
|
|
|
|
2018-01-19 15:09:38 +01:00
|
|
|
registry = Registry()
|
|
|
|
|
registry.load_datatypes()
|
|
|
|
|
set_datatypes_registry(registry)
|
2021-11-29 10:23:47 +01:00
|
|
|
config = get_config(sys.argv)
|
|
|
|
|
db_url = config["db_url"]
|
2017-08-17 00:33:02 +01:00
|
|
|
sa_session = init("/tmp/", db_url).context
|
2015-07-13 20:13:28 +01:00
|
|
|
|
2014-06-10 15:01:38 -05:00
|
|
|
|
|
|
|
|
# Helper function for debugging sqlalchemy queries...
|
|
|
|
|
# http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query
|
|
|
|
|
def printquery(statement, bind=None):
|
|
|
|
|
"""
|
2017-10-12 17:10:12 +01:00
|
|
|
Print a query, with values filled in
|
2014-06-10 15:01:38 -05:00
|
|
|
for debugging purposes *only*
|
|
|
|
|
for security, you should always separate queries from their values
|
|
|
|
|
please also note that this function is quite slow
|
|
|
|
|
"""
|
|
|
|
|
import sqlalchemy.orm
|
2022-02-02 15:26:21 -05:00
|
|
|
|
2014-06-10 15:01:38 -05:00
|
|
|
if isinstance(statement, sqlalchemy.orm.Query):
|
|
|
|
|
if bind is None:
|
2019-06-11 11:31:35 +01:00
|
|
|
bind = statement.session.get_bind()
|
2014-06-10 15:01:38 -05:00
|
|
|
statement = statement.statement
|
|
|
|
|
elif bind is None:
|
|
|
|
|
bind = statement.bind
|
|
|
|
|
|
|
|
|
|
dialect = bind.dialect
|
|
|
|
|
compiler = statement._compiler(dialect)
|
|
|
|
|
|
|
|
|
|
class LiteralCompiler(compiler.__class__):
|
|
|
|
|
def visit_bindparam(self, bindparam, within_columns_clause=False, literal_binds=False, **kwargs):
|
2020-10-06 18:49:19 +01:00
|
|
|
return super().render_literal_bindparam(
|
2014-06-10 15:01:38 -05:00
|
|
|
bindparam, within_columns_clause=within_columns_clause, literal_binds=literal_binds, **kwargs
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
def render_literal_value(self, value, type_):
|
|
|
|
|
"""Render the value of a bind parameter as a quoted literal.
|
|
|
|
|
|
|
|
|
|
This is used for statement sections that do not accept bind paramters
|
|
|
|
|
on the target driver/database.
|
|
|
|
|
|
|
|
|
|
This should be implemented by subclasses using the quoting services
|
|
|
|
|
of the DBAPI.
|
|
|
|
|
|
|
|
|
|
"""
|
2020-10-06 18:49:19 +01:00
|
|
|
if isinstance(value, str):
|
2014-06-10 15:01:38 -05:00
|
|
|
value = value.replace("'", "''")
|
2024-04-27 16:03:22 +01:00
|
|
|
return f"'{value}'"
|
2014-06-10 15:01:38 -05:00
|
|
|
elif value is None:
|
|
|
|
|
return "NULL"
|
2020-10-06 18:49:19 +01:00
|
|
|
elif isinstance(value, (float, int)):
|
2014-06-10 15:01:38 -05:00
|
|
|
return repr(value)
|
|
|
|
|
elif isinstance(value, decimal.Decimal):
|
|
|
|
|
return str(value)
|
|
|
|
|
elif isinstance(value, datetime.datetime):
|
2024-04-27 16:03:22 +01:00
|
|
|
return f"TO_DATE('{value.strftime('%Y-%m-%d %H:%M:%S')}','YYYY-MM-DD HH24:MI:SS')"
|
2014-06-10 15:01:38 -05:00
|
|
|
|
|
|
|
|
else:
|
2024-04-27 16:03:22 +01:00
|
|
|
raise NotImplementedError(f"Don't know how to literal-quote value {value!r}")
|
2014-06-10 15:01:38 -05:00
|
|
|
|
|
|
|
|
compiler = LiteralCompiler(dialect, statement)
|
2016-06-25 21:46:52 -04:00
|
|
|
print(compiler.process(statement))
|