def __init__(
self,
domain: Optional[Domain] = None,
dialect: Text = "sqlite",
url: Text = None,
db: Text = "rasa.db",
username: Text = None,
password: Text = None,
event_broker: Optional[EventChannel] = None,
) -> None:
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine.url import URL
from sqlalchemy import create_engine
engine_url = URL(dialect, username, password, url, database=db)
logger.debug("Attempting to connect to database "
'via "{}"'.format(engine_url.__to_string__()))
self.engine = create_engine(engine_url)
self.session = sessionmaker(bind=self.engine)()
self.Base.metadata.create_all(self.engine)
logger.debug("Connection to SQL database '{}' successful".format(db))
super(SQLTrackerStore, self).__init__(domain, event_broker)
def check_connection(db_uri: URL,
*,
retry_attempts: int = 10,
retry_interval: int = 2) -> None:
db_uri_str = db_uri.__to_string__()
logging.info('Establishing connection ' f'with "{db_uri_str}".')
with get_engine(db_uri) as engine:
for attempt_num in range(retry_attempts):
try:
with engine.connect():
pass
break
except OperationalError:
err_msg = ('Connection attempt ' f'#{attempt_num + 1} failed.')
logging.error(err_msg)
time.sleep(retry_interval)
else:
err_message = ('Failed to establish connection '
f'with "{db_uri_str}" '
f'after {retry_attempts} attempts '
f'with {retry_interval} s. interval.')
raise ConnectionError(err_message)
logging.info(f'Connection established with "{db_uri_str}".')
def __init__(
self,
domain: Optional[Domain] = None,
dialect: Text = "sqlite",
host: Optional[Text] = None,
port: Optional[int] = None,
db: Text = "rasa.db",
username: Text = None,
password: Text = None,
event_broker: Optional[EventChannel] = None,
login_db: Optional[Text] = None,
) -> None:
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine.url import URL
from sqlalchemy import create_engine
engine_url = URL(
dialect,
username,
password,
host,
port,
database=login_db if login_db else db,
)
logger.debug("Attempting to connect to database "
'via "{}"'.format(engine_url.__to_string__()))
# Database might take a while to come up
while True:
try:
self.engine = create_engine(engine_url)
# if `login_db` has been provided, use current connection with
# that database to create working database `db`
if login_db:
self._create_database_and_update_engine(db, engine_url)
try:
self.Base.metadata.create_all(self.engine)
except (
sqlalchemy.exc.OperationalError,
sqlalchemy.exc.ProgrammingError,
) as e:
# Several Rasa services started in parallel may attempt to
# create tables at the same time. That is okay so long as
# the first services finishes the table creation.
logger.error("Could not create tables: {}".format(e))
self.session = sessionmaker(bind=self.engine)()
break
except (
sqlalchemy.exc.OperationalError,
sqlalchemy.exc.IntegrityError,
) as e:
logger.warning(e)
sleep(5)
logger.debug("Connection to SQL database '{}' successful".format(db))
super(SQLTrackerStore, self).__init__(domain, event_broker)
def do_run(self):
# R0914: Too many local variables.
# pylint: disable=R0914
import sqlalchemy
from sqlalchemy import exc
from sqlalchemy.event import listen
from sqlalchemy.engine.url import URL
def start_query(conn, *dummy):
''' Save time the query starts. '''
conn.info['wh_time'] = timeit.default_timer()
def end_query(conn, *dummy):
''' Save time the query's finished. '''
conn.info['wh_time'] = timeit.default_timer() - conn.info['wh_time']
config = {
k: v for k, v in self.config.iteritems()
if k in self.config_raw['properties'].keys()
}
config['drivername'] = config.pop('dbtype')
if config['drivername'] == 'sqlite':
config['database'] = config.pop('host')
query = config.pop('query')
url = URL(**config)
try:
engine = sqlalchemy.create_engine(url)
except ImportError as err:
return ((
'error',
'{}. Sensor checking sql requires it. Please install it.'
.format(err)
),)
error_msg = (
'(database {})\nError: {{}}\n Message from database: "{{}}"'
.format(url.__to_string__())
)
try:
connection = engine.connect()
except exc.TimeoutError:
return (
('error', error_msg.format('Timeout getting connection', None)),
)
except exc.SQLAlchemyError as err:
return (
('error', error_msg.format('Could not connect to database', err)),
)
listen(connection, 'before_cursor_execute', start_query)
listen(connection, 'after_cursor_execute', end_query)
try:
result = connection.execute(query).fetchall()
time = connection.info['wh_time']
except exc.StatementError as err:
return ((
'error', error_msg.format(
'Error executing statement {}'.format(err.statement), err)
),)
except exc.SQLAlchemyError as err:
return ((
'error', error_msg.format(
'Error executing statement, your query: {}'.format(
self.config['query']),
err.message)
),)
finally:
connection.close()
try:
if len(result) == 1 and len(result[0]) == 1:
result = ('result_num', float(result[0][0]))
else:
raise ValueError
except ValueError:
result = ('result', str(result)[1:-1])
return (result, ('query_time', time))
def do_run(self):
# R0914: Too many local variables.
# pylint: disable=R0914
import sqlalchemy
from sqlalchemy import exc
from sqlalchemy.event import listen
from sqlalchemy.engine.url import URL
def start_query(conn, *dummy):
''' Save time the query starts. '''
conn.info['wh_time'] = timeit.default_timer()
def end_query(conn, *dummy):
''' Save time the query's finished. '''
conn.info['wh_time'] = timeit.default_timer(
) - conn.info['wh_time']
config = {
k: v
for k, v in self.config.iteritems()
if k in self.config_raw['properties'].keys()
}
config['drivername'] = config.pop('dbtype')
if config['drivername'] == 'sqlite':
config['database'] = config.pop('host')
query = config.pop('query')
url = URL(**config)
try:
engine = sqlalchemy.create_engine(url)
except ImportError as err:
return (('error',
'{}. Sensor checking sql requires it. Please install it.'.
format(err)), )
error_msg = (
'(database {})\nError: {{}}\n Message from database: "{{}}"'.
format(url.__to_string__()))
try:
connection = engine.connect()
except exc.TimeoutError:
return (('error',
error_msg.format('Timeout getting connection', None)), )
except exc.SQLAlchemyError as err:
return (('error',
error_msg.format('Could not connect to database', err)), )
listen(connection, 'before_cursor_execute', start_query)
listen(connection, 'after_cursor_execute', end_query)
try:
result = connection.execute(query).fetchall()
time = connection.info['wh_time']
except exc.StatementError as err:
return (('error',
error_msg.format(
'Error executing statement {}'.format(err.statement),
err)), )
except exc.SQLAlchemyError as err:
return (('error',
error_msg.format(
'Error executing statement, your query: {}'.format(
self.config['query']), err.message)), )
finally:
connection.close()
try:
if len(result) == 1 and len(result[0]) == 1:
result = ('result_num', float(result[0][0]))
else:
raise ValueError
except ValueError:
result = ('result', str(result)[1:-1])
return (result, ('query_time', time))
