exp for https://github.com/optuna/optuna/pull/1076

error_msg.log

[I 2020-04-16 15:04:56,418] Using an existing study with name 'parallel' instead of creating a new one.
feature_fraction, val_score: inf:   0%|                                                                                                                                                                                                                                                                  | 0/7 [00:00<?, ?it/s][W 2020-04-16 15:04:56,643] Setting status of trial#15 as TrialState.FAIL because of the following error: AttributeError("'numpy.float64' object has no attribute 'translate'")
Traceback (most recent call last):
  File "/home/smly/gitws/optuna/optuna/study.py", line 682, in _run_trial
    result = func(trial)
  File "/home/smly/gitws/optuna/optuna/integration/lightgbm_tuner/optimize.py", line 229, in __call__
    param_value = min(trial.suggest_uniform("feature_fraction", 0.4, 1.0 + EPS), 1.0)
  File "/home/smly/gitws/optuna/optuna/trial.py", line 550, in suggest_uniform
    return self._suggest(name, distribution)
  File "/home/smly/gitws/optuna/optuna/trial.py", line 980, in _suggest
    return self._set_new_param_or_get_existing(name, param_value, distribution)
  File "/home/smly/gitws/optuna/optuna/trial.py", line 987, in _set_new_param_or_get_existing
    self._trial_id, name, param_value_in_internal_repr, distribution
  File "/home/smly/gitws/optuna/optuna/storages/rdb/storage.py", line 516, in set_trial_param
    commit_success = self._commit_with_integrity_check(session)
  File "/home/smly/gitws/optuna/optuna/storages/rdb/storage.py", line 876, in _commit_with_integrity_check
    session.commit()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1026, in commit
    self.transaction.commit()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 493, in commit
    self._prepare_impl()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 472, in _prepare_impl
    self.session.flush()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2451, in flush
    self._flush(objects)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2589, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2549, in _flush
    flush_context.execute()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1120, in _emit_insert_statements
    statement, params
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1468, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/cursors.py", line 168, in execute
    query = self.mogrify(query, args)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/cursors.py", line 147, in mogrify
    query = query % self._escape_args(args, conn)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/cursors.py", line 127, in _escape_args
    return {key: conn.literal(val) for (key, val) in args.items()}
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/cursors.py", line 127, in <dictcomp>
    return {key: conn.literal(val) for (key, val) in args.items()}
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/connections.py", line 467, in literal
    return self.escape(obj, self.encoders)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/connections.py", line 460, in escape
    return converters.escape_item(obj, self.charset, mapping=mapping)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/converters.py", line 27, in escape_item
    val = encoder(val, mapping)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/converters.py", line 118, in escape_unicode
    return u"'%s'" % _escape_unicode(value)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/converters.py", line 73, in _escape_unicode
    return value.translate(_escape_table)
AttributeError: 'numpy.float64' object has no attribute 'translate'
Traceback (most recent call last):
  File "/home/smly/gitws/optuna/optuna/study.py", line 682, in _run_trial
    result = func(trial)
  File "/home/smly/gitws/optuna/optuna/integration/lightgbm_tuner/optimize.py", line 229, in __call__
    param_value = min(trial.suggest_uniform("feature_fraction", 0.4, 1.0 + EPS), 1.0)
  File "/home/smly/gitws/optuna/optuna/trial.py", line 550, in suggest_uniform
    return self._suggest(name, distribution)
  File "/home/smly/gitws/optuna/optuna/trial.py", line 980, in _suggest
    return self._set_new_param_or_get_existing(name, param_value, distribution)
  File "/home/smly/gitws/optuna/optuna/trial.py", line 987, in _set_new_param_or_get_existing
    self._trial_id, name, param_value_in_internal_repr, distribution
  File "/home/smly/gitws/optuna/optuna/storages/rdb/storage.py", line 516, in set_trial_param
    commit_success = self._commit_with_integrity_check(session)
  File "/home/smly/gitws/optuna/optuna/storages/rdb/storage.py", line 876, in _commit_with_integrity_check
    session.commit()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1026, in commit
    self.transaction.commit()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 493, in commit
    self._prepare_impl()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 472, in _prepare_impl
    self.session.flush()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2451, in flush
    self._flush(objects)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2589, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2549, in _flush
    flush_context.execute()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1120, in _emit_insert_statements
    statement, params
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1468, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/cursors.py", line 168, in execute
    query = self.mogrify(query, args)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/cursors.py", line 147, in mogrify
    query = query % self._escape_args(args, conn)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/cursors.py", line 127, in _escape_args
    return {key: conn.literal(val) for (key, val) in args.items()}
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/cursors.py", line 127, in <dictcomp>
    return {key: conn.literal(val) for (key, val) in args.items()}
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/connections.py", line 467, in literal
    return self.escape(obj, self.encoders)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/connections.py", line 460, in escape
    return converters.escape_item(obj, self.charset, mapping=mapping)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/converters.py", line 27, in escape_item
    val = encoder(val, mapping)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/converters.py", line 118, in escape_unicode
    return u"'%s'" % _escape_unicode(value)
  File "/home_raw/smly/a/lib/python3.7/site-packages/pymysql/converters.py", line 73, in _escape_unicode
    return value.translate(_escape_table)
AttributeError: 'numpy.float64' object has no attribute 'translate'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "parallel_1.py", line 43, in <module>
    model = tuner.run()
  File "/home/smly/gitws/optuna/optuna/integration/lightgbm_tuner/optimize.py", line 541, in run
    self.tune_feature_fraction()
  File "/home/smly/gitws/optuna/optuna/integration/lightgbm_tuner/optimize.py", line 587, in tune_feature_fraction
    self.tune_params([param_name], len(param_values), sampler, "feature_fraction")
  File "/home/smly/gitws/optuna/optuna/integration/lightgbm_tuner/optimize.py", line 669, in tune_params
    study.optimize(objective, n_trials=_n_trials, catch=())
  File "/home/smly/gitws/optuna/optuna/study.py", line 334, in optimize
    func, n_trials, timeout, catch, callbacks, gc_after_trial, None
  File "/home/smly/gitws/optuna/optuna/study.py", line 631, in _optimize_sequential
    self._run_trial_and_callbacks(func, catch, callbacks, gc_after_trial)
  File "/home/smly/gitws/optuna/optuna/study.py", line 661, in _run_trial_and_callbacks
    trial = self._run_trial(func, catch, gc_after_trial)
  File "/home/smly/gitws/optuna/optuna/study.py", line 704, in _run_trial
    self._storage.set_trial_system_attr(trial_id, "fail_reason", message)
  File "/home/smly/gitws/optuna/optuna/storages/rdb/storage.py", line 643, in set_trial_system_attr
    self._set_trial_system_attr_without_commit(session, trial_id, key, value)
  File "/home/smly/gitws/optuna/optuna/storages/rdb/storage.py", line 650, in _set_trial_system_attr_without_commit
    trial = models.TrialModel.find_or_raise_by_id(trial_id, session)
  File "/home/smly/gitws/optuna/optuna/storages/rdb/models.py", line 220, in find_or_raise_by_id
    trial = cls.find_by_id(trial_id, session)
  File "/home/smly/gitws/optuna/optuna/storages/rdb/models.py", line 182, in find_by_id
    return query.one_or_none()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3244, in one_or_none
    ret = list(self)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3317, in __iter__
    return self._execute_and_instances(context)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3339, in _execute_and_instances
    querycontext, self._connection_from_session, close_with_result=True
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3354, in _get_bind_args
    mapper=self._bind_mapper(), clause=querycontext.statement, **kw
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3332, in _connection_from_session
    conn = self.session.connection(**kw)
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1123, in connection
    execution_options=execution_options,
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1129, in _connection_for_bind
    engine, execution_options
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 407, in _connection_for_bind
    self._assert_active()
  File "/home_raw/smly/a/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 294, in _assert_active
    % self._rollback_exception
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: 'numpy.float64' object has no attribute 'translate'

parallel_lightgbm_tuner.py

import lightgbm as lgb
import numpy as np
import sklearn.datasets
from sklearn.metrics import accuracy_score
from sklearn.model_selection import train_test_split

import optuna
from optuna.integration.lightgbm import LightGBMTuner


data, target = sklearn.datasets.load_breast_cancer(return_X_y=True)
train_x, val_x, train_y, val_y = train_test_split(data, target, test_size=0.25, random_state=1)
dtrain = lgb.Dataset(train_x, label=train_y)
dval = lgb.Dataset(val_x, label=val_y)

params = {
    "objective": "binary",
    "metric": "binary_logloss",
    "verbosity": -1,
    "boosting_type": "gbdt",
    "num_threads": 1,
}

# study = optuna.create_study(
#     storage="sqlite:///lgbtuner.db", study_name="parallel", load_if_exists=True
# )
study = optuna.create_study(
    storage=(
        "mysql+pymysql://optuna_user:"
        "p4rW%21w8%40AUfJ5TkA@localhost/optuna_db"),
    study_name="parallel",
    load_if_exists=True
)

tuner = LightGBMTuner(
    params,
    dtrain,
    valid_sets=[dtrain, dval],
    verbose_eval=100,
    early_stopping_rounds=100,
    study=study,
)
model = tuner.run()
prediction = np.rint(model.predict(val_x, num_iteration=model.best_iteration))
accuracy = accuracy_score(val_y, prediction)
best_params = model.params

print("Number of finished trials: {}".format(len(study.trials)))
print("Best params:", best_params)
print("  Accuracy = {}".format(accuracy))
print("  Params: ")
for key, value in best_params.items():
    print("    {}: {}".format(key, value))

seutp_rdb.sql

CREATE DATABASE optuna_db;
CREATE USER 'optuna_user'@'localhost' IDENTIFIED BY 'p4rW!w8@AUfJ5TkA';
GRANT CREATE,INSERT,UPDATE,SELECT ON optuna_db.* TO 'optuna_user'@'localhost';

error: AttributeError: 'numpy.float64' object has no attribute 'translate'

please see: https://stackoverflow.com/questions/46205532/numpy-float64-object-has-no-attribute-translate-inserting-value-to-mysql-in

fx-kirin 's answer: