Python sqlalchemy.exc.InternalError() Examples

def __init__(self, user, password, database, host, port):
        driver = 'mysql+pymysql'

        self.url = URL(driver, user, password, host, port, database)

        # Hack to establish SSL connection (see #231)
        try:
            self._engine = create_engine(self.url, echo=True,
                                         connect_args={'ssl': {'activate': True}})
            self._engine.connect().close()
        except InternalError:
            self._engine = create_engine(self.url, echo=True)

        self._Session = sessionmaker(bind=self._engine)

        # Create the schema on the database.
        # It won't replace any existing schema
        ModelBase.metadata.create_all(self._engine) 

def upgrade():
    try:
        op.create_table('eventhandlercondition',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('eventhandler_id', sa.Integer(), nullable=True),
        sa.Column('Key', sa.Unicode(length=255), nullable=False),
        sa.Column('Value', sa.Unicode(length=2000), nullable=True),
        sa.Column('comparator', sa.Unicode(length=255), nullable=True),
        sa.ForeignKeyConstraint(['eventhandler_id'], ['eventhandler.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('eventhandler_id', 'Key', name='ehcix_1')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table eventhandlercondition already exists.")
        else:
            print("Table already exists")
            print(exx)

    except Exception as exx:
        print("Could not add Table eventhandlercondition")
        print (exx) 

def _deadlock_error(operational_error, match, engine_name, is_disconnect):
    """Filter for MySQL or Postgresql deadlock error.

    NOTE(comstud): In current versions of DB backends, Deadlock violation
    messages follow the structure:

    mysql+mysqldb:
    (OperationalError) (1213, 'Deadlock found when trying to get lock; try '
                         'restarting transaction') <query_str> <query_args>

    mysql+mysqlconnector:
    (InternalError) 1213 (40001): Deadlock found when trying to get lock; try
                         restarting transaction

    postgresql:
    (TransactionRollbackError) deadlock detected <deadlock_details>


    ibm_db_sa:
    SQL0911N The current transaction has been rolled back because of a
    deadlock or timeout <deadlock details>

    """
    raise exception.DBDeadlock(operational_error) 

def upgrade():
    try:
        op.create_table('passwordreset',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('recoverycode', sa.Unicode(length=255), nullable=False),
        sa.Column('username', sa.Unicode(length=64), nullable=False),
        sa.Column('realm', sa.Unicode(length=64), nullable=False),
        sa.Column('resolver', sa.Unicode(length=64), nullable=True),
        sa.Column('email', sa.Unicode(length=255), nullable=True),
        sa.Column('timestamp', sa.DateTime(), nullable=True),
        sa.Column('expiration', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_index(op.f('ix_passwordreset_realm'), 'passwordreset',
                        ['realm'], unique=False)
        op.create_index(op.f('ix_passwordreset_username'), 'passwordreset',
                        ['username'], unique=False)
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table passwordreset already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'passwordreset'")
        print (exx) 

def upgrade():
    try:
        op.add_column('token', sa.Column('revoked', sa.Boolean(),
                                         default=False))
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column revoked already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add column 'revoked' to table 'token'")
        print (exx)

    try:
        op.add_column('token', sa.Column('locked', sa.Boolean(),
                                         default=False))
    except (OperationalError, ProgrammingError)  as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column locked already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add column 'locked' to table 'token'")
        print (exx) 

def upgrade():
    try:
        op.create_table('radiusserver',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('identifier', sa.Unicode(length=255), nullable=False),
        sa.Column('server', sa.Unicode(length=255), nullable=False),
        sa.Column('port', sa.Integer(), nullable=True),
        sa.Column('secret', sa.Unicode(length=255), nullable=True),
        sa.Column('description', sa.Unicode(length=2000), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('identifier')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table 'radiusserver' already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'radiusserver'")
        print (exx) 

def test_check_constraint(self, connection):
        assert_raises(
            (
                exc.IntegrityError,
                exc.ProgrammingError,
                exc.OperationalError,
                # PyMySQL raising InternalError until
                # https://github.com/PyMySQL/PyMySQL/issues/607 is resolved
                exc.InternalError,
            ),
            connection.exec_driver_sql,
            "insert into non_native_enum_table "
            "(id, someenum) values(1, 'four')",
        ) 

def execute(cls, engine, query):
        try:
            conn = engine.connect()
            conn.execute(query)
        except (OperationalError, ProgrammingError, InternalError) as e:
            code = e.orig.args[0]
            if isinstance(e, ProgrammingError) and code == 1007:
                # Query for creating database failed because it exists
                raise DatabaseExists(error=e.orig.args[1], code=code)
            else:
                raise DatabaseError(error=e.orig.args[1], code=code) 

def _assert_b_is_locked(self, should_be_locked):
        B = self.classes.B
        with testing.db.begin() as alt_trans:
            alt_trans.exec_driver_sql("set innodb_lock_wait_timeout=1")
            # set x/y > 10
            try:
                alt_trans.execute(update(B).values(x=15, y=19))
            except (exc.InternalError, exc.OperationalError) as err:
                assert "Lock wait timeout exceeded" in str(err)
                assert should_be_locked
            else:
                assert not should_be_locked 

def _assert_a_is_locked(self, should_be_locked):
        A = self.classes.A
        with testing.db.begin() as alt_trans:
            alt_trans.exec_driver_sql("set innodb_lock_wait_timeout=1")
            # set x/y > 10
            try:
                alt_trans.execute(update(A).values(x=15, y=19))
            except (exc.InternalError, exc.OperationalError) as err:
                assert "Lock wait timeout exceeded" in str(err)
                assert should_be_locked
            else:
                assert not should_be_locked 

def _raise_savepoints_as_dberrors(error, match, engine_name, is_disconnect):
    # NOTE(rpodolyaka): this is a special case of an OperationalError that used
    # to be an InternalError. It's expected to be wrapped into oslo.db error.
    raise exception.DBError(error) 

def session(connection, monkeypatch):
    """Return an sqlalchemy session, and after the test tear down everything properly."""
    # Begin the nested transaction
    transaction = connection.begin()
    # Use the connection with the already started transaction
    session = Session(bind=connection)

    def get_session():
        return session

    from pollbot import db

    monkeypatch.setattr(db, "get_session", get_session)
    assert session == db.get_session()

    yield session

    # Since we are not committing things to the database directly when
    # testing, initially deferred constraints are not checked. The
    # following statement makes the DB check these constraints. We are
    # executing this command AFTER the tests and NOT BEFORE, because
    # within a transaction the DB is allowed to take temporarily
    # invalid state. Read
    # https://www.postgresql.org/docs/current/static/sql-set-constraints.html
    # for details.
    try:
        connection.execute("SET CONSTRAINTS ALL IMMEDIATE")
    except InternalError:
        # This is the case when we are doing something in the tests
        # that we expect it to fail by executing the statement above.
        # In this case, the transaction will be in an already failed
        # state, executing further SQL statements are ignored and doing
        # so raises an exception.
        pass

    session.close()
    # Roll back the broader transaction
    transaction.rollback()
    # Put back the connection to the connection pool
    connection.close() 

def test_create_lost_audit(faker, mocker, user):
    hook = mocker.patch('huskar_api.models.audit.audit._publish_new_action')
    session = mocker.patch('huskar_api.models.audit.audit.DBSession')
    session.side_effect = [InternalError(None, None, None, None)]
    action = (action_types.UPDATE_CONFIG, 'data', [(TYPE_SITE, 0)])
    with raises(AuditLogLostError):
        AuditLog.create(user.id, faker.ipv4(), action)

    hook.assert_called_once() 

def session(connection, monkeypatch):
    """Return an sqlalchemy session, and after the test tear down everything properly."""
    # Begin the nested transaction
    transaction = connection.begin()
    # Use the connection with the already started transaction
    session = Session(bind=connection)

    def get_session():
        return session

    from stickerfinder import db

    monkeypatch.setattr(db, "get_session", get_session)
    assert session == db.get_session()

    yield session

    # Since we are not committing things to the database directly when
    # testing, initially deferred constraints are not checked. The
    # following statement makes the DB check these constraints. We are
    # executing this command AFTER the tests and NOT BEFORE, because
    # within a transaction the DB is allowed to take temporarily
    # invalid state. Read
    # https://www.postgresql.org/docs/current/static/sql-set-constraints.html
    # for details.
    try:
        connection.execute("SET CONSTRAINTS ALL IMMEDIATE")
    except InternalError:
        # This is the case when we are doing something in the tests
        # that we expect it to fail by executing the statement above.
        # In this case, the transaction will be in an already failed
        # state, executing further SQL statements are ignored and doing
        # so raises an exception.
        pass

    session.close()
    # Roll back the broader transaction
    transaction.rollback()
    # Put back the connection to the connection pool
    connection.close() 

def test_savepoint_without_rollback(self, registry):
        registry.migration.savepoint('test')
        registry.migration.release_savepoint('test')
        with pytest.raises((InternalError, OperationalError)):
            registry.migration.rollback_savepoint('test') 

def upgrade():
    try:
        op.create_table('eventhandler',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('ordering', sa.Integer(), nullable=False),
        sa.Column('event', sa.Unicode(length=255), nullable=False),
        sa.Column('handlermodule', sa.Unicode(length=255), nullable=False),
        sa.Column('condition', sa.Unicode(length=1024), nullable=True),
        sa.Column('action', sa.Unicode(length=1024), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_table('eventhandleroption',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('eventhandler_id', sa.Integer(), nullable=True),
        sa.Column('Key', sa.Unicode(length=255), nullable=False),
        sa.Column('Value', sa.Unicode(length=2000), nullable=True),
        sa.Column('Type', sa.Unicode(length=2000), nullable=True),
        sa.Column('Description', sa.Unicode(length=2000), nullable=True),
        sa.ForeignKeyConstraint(['eventhandler_id'], ['eventhandler.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('eventhandler_id', 'Key', name='ehoix_1')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table 'eventhandler' already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'eventhandler'")
        print (exx) 

def upgrade():
    try:
        op.add_column('radiusserver', sa.Column('dictionary',
                                                sa.Unicode(length=255),
                                                nullable=True))
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table 'radiusserver' already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'radiusserver'")
        print (exx) 

def upgrade():
    try:
        op.create_table('subscription',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('application', sa.Unicode(length=30), nullable=True),
        sa.Column('for_name', sa.Unicode(length=50), nullable=False),
        sa.Column('for_address', sa.Unicode(length=128), nullable=True),
        sa.Column('for_email', sa.Unicode(length=128), nullable=False),
        sa.Column('for_phone', sa.Unicode(length=50), nullable=False),
        sa.Column('for_url', sa.Unicode(length=80), nullable=True),
        sa.Column('for_comment', sa.Unicode(length=255), nullable=True),
        sa.Column('by_name', sa.Unicode(length=50), nullable=False),
        sa.Column('by_email', sa.Unicode(length=128), nullable=False),
        sa.Column('by_address', sa.Unicode(length=128), nullable=True),
        sa.Column('by_phone', sa.Unicode(length=50), nullable=True),
        sa.Column('by_url', sa.Unicode(length=80), nullable=True),
        sa.Column('date_from', sa.DateTime(), nullable=True),
        sa.Column('date_till', sa.DateTime(), nullable=True),
        sa.Column('num_users', sa.Integer(), nullable=True),
        sa.Column('num_tokens', sa.Integer(), nullable=True),
        sa.Column('num_clients', sa.Integer(), nullable=True),
        sa.Column('level', sa.Unicode(length=30), nullable=True),
        sa.Column('signature', sa.Unicode(length=640), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_index(op.f('ix_subscription_application'), 'subscription', ['application'], unique=False)
        op.create_index(op.f('ix_subscription_id'), 'subscription', ['id'], unique=False)
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table subscription already exists.")
        else:
            print("Table subscription exists")
            print(exx)

    except Exception as exx:
        print("Could not add Table subscription")
        print (exx) 

def upgrade():
    try:
        op.add_column('eventhandler', sa.Column('name', sa.Unicode(
            length=64), default=u""))
        op.add_column('eventhandler', sa.Column('active', sa.Boolean(), nullable=True))
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Columns name and active already exist.")
        else:
            print("Columns name and active already exist.")
            print(exx)

    except Exception as exx:
        print("Could not add columns name and active.")
        print (exx) 

def upgrade():
    try:
        op.create_table('smsgateway',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('identifier', sa.Unicode(length=255), nullable=False),
        sa.Column('description', sa.Unicode(length=1024), nullable=True),
        sa.Column('providermodule', sa.Unicode(length=1024), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('identifier')
        )
        op.create_table('smsgatewayoption',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('Key', sa.Unicode(length=255), nullable=False),
        sa.Column('Value', sa.UnicodeText(), nullable=True),
        sa.Column('Type', sa.Unicode(length=100), nullable=True),
        sa.Column('gateway_id', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['gateway_id'], ['smsgateway.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('gateway_id', 'Key', name='sgix_1')
        )
        op.create_index(op.f('ix_smsgatewayoption_gateway_id'), 'smsgatewayoption', ['gateway_id'], unique=False)
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table smsgateway already exists.")
        else:
            print("Table already exists")
            print(exx)

    except Exception as exx:
        print("Could not add Table smsgateway")
        print (exx)
    ### end Alembic commands ### 

def upgrade():
    try:
        op.add_column('resolverrealm', sa.Column('priority', sa.Integer(),
                                                 nullable=True))
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column priority already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add column 'priority' to table 'resolverrealm'")
        print (exx) 

def upgrade():
    try:
        op.add_column('policy', sa.Column('condition', sa.Integer(), nullable=False))
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column condition already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add column 'condition' to table 'policy'")
        print (exx) 

def upgrade():
    try:
        op.add_column('policy', sa.Column('adminrealm',
                                          sa.Unicode(length=256),
                                          nullable=True))
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column adminrealm already exists.")
        else:
            print(exx)
    except Exception as exx:
        print("Could not add the column 'adminrealm' to table policy")
        print(exx) 

def setup_table(self, table: Any) -> None:
        if self._engine is None:
            raise Exception("Engine not set when required: {}".format(self))
        try:
            table.__table__.create(self._engine, checkfirst=True)
        except InternalError as e:
            if "Table '{}' already exists".format(table.__tablename__) in str(e):
                # This is a race condition from checkfirst=True. Can happen
                # if two threads call this method at the same time.
                pass
            else:
                raise 

def create_database_engine(user, password, database, host, port):
    """Create a database engine"""

    driver = 'mysql+pymysql'
    url = URL(driver, user, password, host, port, database,
              query={'charset': 'utf8mb4'})

    # Generic parameters for the engine.
    #
    # SSL param needs a non-empty dict to be activated in pymsql.
    # That is why a fake parameter 'activate' is given but not
    # used by the library.
    #
    engine_params = {
        'poolclass': QueuePool,
        'pool_size': 25,
        'pool_pre_ping': True,
        'echo': False,
        'connect_args': {
            'ssl': {
                'activate': True
            }
        }
    }

    engine = create_engine(url, **engine_params)

    try:
        engine.connect().close()
    except InternalError:
        # Try non-SSL connection
        engine_params['connect_args'].pop('ssl')
        engine = create_engine(url, **engine_params)
        engine.connect().close()

    return engine