Flask app, sqlalchemy and daemon - how right connect to db?

[Евгений Рымарев]

Hello everyone!

I have Flask app with using flask_sqlalchemy:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config.from_pyfile(filename='settings.py', silent=True)
db = SQLAlchemy(app=app)

I want connect to same database from daemon. In daemon i just import db and use db.engine.execute for SQLAlchemy queries.  

But when daemon starts main app can't connect to database.

In log i see that:

fdb.fbcore.DatabaseError: ('Error while connecting to database:\n- SQLCODE: -902\n- I/O error during "lock" operation for file "main.fdb"\n- Database already opened with engine instance, incompatible with current', -902, 335544344)

I trying use isolation level:

from fdb.fbcore import ISOLATION_LEVEL_READ_COMMITED_LEGACY
class TPBAlchemy(SQLAlchemy):
    def apply_driver_hacks(self, app_, info, options):
        if 'isolation_level' not in options:
            options['isolation_level'] = ISOLATION_LEVEL_READ_COMMITED_LEGACY
        return super(TPBAlchemy, self).apply_driver_hacks(app_, info, options)

And replace this:

db = SQLAlchemy()

To:

db = TPBAlchemy()

But this only make another error:

TypeError: Invalid argument(s) 'isolation_level' sent to create_engine(), using configuration FBDialect_fdb/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of components.

I would appreciate the full example to address my issue.  

Thanks!

[mike bayer]

On 03/03/2017 12:08 PM, Евгений Рымарев wrote:
> Hello everyone!
> I have Flask app with using flask_sqlalchemy:
>
> |
> fromflask importFlask
> fromflask_sqlalchemy importSQLAlchemy
> app =Flask(__name__)
> app.config.from_pyfile(filename='settings.py',silent=True)
> db =SQLAlchemy(app=app)
> |
>
> I want connect to same database from daemon. In daemon i just import db
> and use db.engine.execute for SQLAlchemy queries.
> But when daemon starts main app can't connect to database.
> In log i see that:
> |
> fdb.fbcore.DatabaseError:('Error while connecting to database:\n-
> SQLCODE: -902\n- I/O error during "lock" operation for file
> "main.fdb"\n- Database already opened with engine instance, incompatible
> with current',-902,335544344)
> |
>
> I trying use isolation level:
> |
> fromfdb.fbcore importISOLATION_LEVEL_READ_COMMITED_LEGACY
> classTPBAlchemy(SQLAlchemy):
> defapply_driver_hacks(self,app_,info,options):
> if'isolation_level'notinoptions:
> options['isolation_level']=ISOLATION_LEVEL_READ_COMMITED_LEGACY
> returnsuper(TPBAlchemy,self).apply_driver_hacks(app_,info,options)

So the Firebird dialects in SQLAlchemy have no "isolation level"
support. But also, the isolation level feature in SQLAlchemy dialects
set this value *after* the connection is made. The error you are
getting indicates that you aren't connecting in the first place; so
hopefully this is resolved by setting a certain isolation level on all
connections.

In any case, to call firebird-specific APIs like isolation levels when
you connect, use the pool connect event:

http://docs.sqlalchemy.org/en/latest/core/events.html#sqlalchemy.events.PoolEvents.connect


so on your Engine (I'm not sure how to do this with Flask, wherever they
do create_engine or you can do it on the Engine class globally)


engine = create_engine("firebird+fdb://...")

@event.listens_for(engine, 'connect')
def receive_connect(dbapi_connection, connection_record):
dbapi_connection.call_special_firebird_method(
ISOLATION_LEVEL_READ_COMMITTED_LEGACY)


above you'd replace "call_special_firebird_method" with whatever you
need to make this setting on the DBAPI connection.





> |
>
> And replace this:
> |
> db =SQLAlchemy()
> |
>
> To:
> |
> db =TPBAlchemy()

> |
>
> But this only make another error:
> |
> TypeError:Invalidargument(s)'isolation_level'sent to
> create_engine(),usingconfiguration

> FBDialect_fdb/QueuePool/Engine. Pleasecheck that the keyword arguments
> are appropriate forthiscombination of components.

> |
>
> I would appreciate the full example to address my issue.
> Thanks!
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Евгений Рымарев]

>>call_special_firebird_method

Not understand. What method? You mean fdb method?

I found method _Connection__set_default_tpb and use it.

app = Flask(__name__)
app.config.from_pyfile(filename='settings.py', silent=True)
db = SQLAlchemy(app=app)

engine = create_engine(settings.SQLALCHEMY_DATABASE_URI)

@event.listens_for(engine, 'connect')
def receive_connect(dbapi_connection, connection_record):

    dbapi_connection._Connection__set_default_tpb(ISOLATION_LEVEL_READ_COMMITED_LEGACY)

This function is performed, but the same result - error in logs about "lock".

пятница, 3 марта 2017 г., 20:08:45 UTC+3 пользователь Евгений Рымарев написал:

[Евгений Рымарев]

full error

Traceback (most recent call last):

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1122, in _do_get

    return self._pool.get(wait, self._timeout)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/queue.py", line 145, in get

    raise Empty

sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2138, in _wrap_pool_connect

    return fn()

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 387, in connect

    return _ConnectionFairy._checkout(self)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 766, in _checkout

    fairy = _ConnectionRecord.checkout(pool)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 516, in checkout

    rec = pool._do_get()

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1138, in _do_get

    self._dec_overflow()

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise

    raise value

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1135, in _do_get

    return self._create_connection()

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 333, in _create_connection

    return _ConnectionRecord(self)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 461, in __init__

    self.__connect(first_connect_check=True)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 651, in __connect

    connection = pool._invoke_creator(self)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect

    return dialect.connect(*cargs, **cparams)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 393, in connect

    return self.dbapi.connect(*cargs, **cparams)

  File "/usr/local/lib/python3.5/site-packages/fdb/fbcore.py", line 728, in connect

    "Error while connecting to database:")

fdb.fbcore.DatabaseError: ('Error while connecting to database:\n- SQLCODE: -902\n- I/O error during "lock" operation for file "/var/db/main.fdb"\n- Database already opened with engine instance, incompatible with current', -902, 335544344)

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

Traceback (most recent call last):

  File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1994, in __call__

    return self.wsgi_app(environ, start_response)

  File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1985, in wsgi_app

    response = self.handle_exception(e)

  File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1540, in handle_exception

    reraise(exc_type, exc_value, tb)

  File "/usr/local/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise

    raise value

  File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1982, in wsgi_app

    response = self.full_dispatch_request()

  File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1614, in full_dispatch_request

    rv = self.handle_user_exception(e)

  File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1517, in handle_user_exception

    reraise(exc_type, exc_value, tb)

  File "/usr/local/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise

    raise value

  File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1612, in full_dispatch_request

    rv = self.dispatch_request()

  File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1598, in dispatch_request

    return self.view_functions[rule.endpoint](**req.view_args)

  File "./mon.py", line 184, in mon

    'result': engine.execute(result),

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2054, in execute

    connection = self.contextual_connect(close_with_result=True)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2103, in contextual_connect

    self._wrap_pool_connect(self.pool.connect, None),

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2142, in _wrap_pool_connect

    e, dialect, self)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1456, in _handle_dbapi_exception_noconnection

    exc_info

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise

    raise value.with_traceback(tb)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2138, in _wrap_pool_connect

    return fn()

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 387, in connect

    return _ConnectionFairy._checkout(self)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 766, in _checkout

    fairy = _ConnectionRecord.checkout(pool)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 516, in checkout

    rec = pool._do_get()

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1138, in _do_get

    self._dec_overflow()

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise

    raise value

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1135, in _do_get

    return self._create_connection()

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 333, in _create_connection

    return _ConnectionRecord(self)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 461, in __init__

    self.__connect(first_connect_check=True)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 651, in __connect

    connection = pool._invoke_creator(self)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect

    return dialect.connect(*cargs, **cparams)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 393, in connect

    return self.dbapi.connect(*cargs, **cparams)

  File "/usr/local/lib/python3.5/site-packages/fdb/fbcore.py", line 728, in connect

    "Error while connecting to database:")

sqlalchemy.exc.DatabaseError: (fdb.fbcore.DatabaseError) ('Error while connecting to database:\n- SQLCODE: -902\n- I/O error during "lock" operation for file "/var/db/main.fdb"\n- Database already opened with engine instance, incompatible with current', -902, 335544344)

суббота, 4 марта 2017 г., 2:31:47 UTC+3 пользователь Евгений Рымарев написал:

[mike bayer]

On 03/03/2017 06:31 PM, Евгений Рымарев wrote:
>>>call_special_firebird_method
> Not understand. What method? You mean fdb method?

yes, whatever workaround fdb gives you for this issue.

>
> I found method _Connection__set_default_tpb and use it.
> |

> app =Flask(__name__)
> app.config.from_pyfile(filename='settings.py',silent=True)
> db =SQLAlchemy(app=app)

> engine =create_engine(settings.SQLALCHEMY_DATABASE_URI)
>
> @event.listens_for(engine,'connect')
> defreceive_connect(dbapi_connection,connection_record):

>
> dbapi_connection._Connection__set_default_tpb(ISOLATION_LEVEL_READ_COMMITED_LEGACY)
> |
> This function is performed, but the same result - error in logs about
> "lock".

I know nothing about this issue on Firebird you'd need to ask on their
mailing list for how to deal with this situation.

>
> пятница, 3 марта 2017 г., 20:08:45 UTC+3 пользователь Евгений Рымарев
> написал:
>
> Hello everyone!
> I have Flask app with using flask_sqlalchemy:
>
> |

> fromflask importFlask
> fromflask_sqlalchemy importSQLAlchemy
> app =Flask(__name__)
> app.config.from_pyfile(filename='settings.py',silent=True)
> db =SQLAlchemy(app=app)
> |
>

> I want connect to same database from daemon. In daemon i just import
> db and use db.engine.execute for SQLAlchemy queries.
> But when daemon starts main app can't connect to database.
> In log i see that:
> |
> fdb.fbcore.DatabaseError:('Error while connecting to database:\n-
> SQLCODE: -902\n- I/O error during "lock" operation for file
> "main.fdb"\n- Database already opened with engine instance,
> incompatible with current',-902,335544344)
> |
>
> I trying use isolation level:
> |

> fromfdb.fbcore importISOLATION_LEVEL_READ_COMMITED_LEGACY
> classTPBAlchemy(SQLAlchemy):
> defapply_driver_hacks(self,app_,info,options):
> if'isolation_level'notinoptions:
>
> options['isolation_level']=ISOLATION_LEVEL_READ_COMMITED_LEGACY
>
> returnsuper(TPBAlchemy,self).apply_driver_hacks(app_,info,options)
> |
>

> And replace this:
> |
> db =SQLAlchemy()
> |
>
> To:
> |
> db =TPBAlchemy()
> |
>

> But this only make another error:
> |

> TypeError:Invalidargument(s)'isolation_level'sent to
> create_engine(),usingconfiguration

> FBDialect_fdb/QueuePool/Engine. Pleasecheck that the keyword
> arguments are appropriate forthiscombination of components.

> |
>
> I would appreciate the full example to address my issue.
> Thanks!
>