Python flask_sqlalchemy.SQLAlchemy() Examples

def init_app(self, app, db, **kwargs):
        """
        app: The Flask application instance.
        """
        if not isinstance(app, Flask):
            raise TypeError(
                'hobbit_core.HobbitManager.init_app(): '
                'Parameter "app" is an instance of class "{}" '
                'instead of a subclass of class "flask.Flask".'.format(
                    app.__class__.__name__))

        if not isinstance(db, SQLAlchemy):
            raise TypeError('hobbit-core be dependent on SQLAlchemy.')
        self.db = db

        app.config.setdefault('HOBBIT_UPPER_SEQUENCE_NAME', False)

        # Bind hobbit-core to app
        app.hobbit_manager = self 

def DbClient(app, user, password, host, port, name):
    """
    Orion does not need to provide any additional abstractions over the client object created by
    SQLAlchemy. This function directly returns the object instantiated by creating a SQLAlchemy
    object from the current Flask app.

    :param app: Flask application object.
    :param user: The username of the MySQL user.
    :param password: The password of the MySQL user.
    :param host: The host of the MySQL instance.
    :param port: The port of the MySQL instance.
    :param name: The name of the MySQL database.
    :return: A SQLAlchemy object for interacting with the database.
    """
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://{user}:{password}@{host}:{port}/{name}'.format(
        user=user,
        password=password,
        host=host,
        port=port,
        name=name,
    )
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

    return flask_sqlalchemy.SQLAlchemy(app, session_options=session_opts) 

def setUp(self):
        app = Flask(__name__)
        app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
        app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
        db = SQLAlchemy(app)

        class DummyModel(db.Model):
            """ SQLAlchemy Dummy Object """

            id = db.Column(db.Integer, primary_key=True)
            integer = db.Column(db.Integer)

        db.create_all()

        self.DummyModel = DummyModel
        self.define_validators()

        self.dummy = DummyModel()

        self.app = app
        self.db = db 

def from_model(cls, cmd: commands.Command):
        """This method turns a subclass of commands.Command into an SQLAlchemy model.
        The parameters of the command are encoded as a JSON dictionary inside the parameters column.

        Args:
              cmd (commands.Command): The command to be turned into a database model.
        Returns:
              Command: The database model, ready to be committed.
        """
        c = cls()
        assert cmd.request_type is not None
        c.request_type = cmd.request_type
        c.uuid = cmd.uuid
        c.parameters = cmd.parameters

        return c 

def app_with_scout():
    with flask_app_with_scout() as app:
        app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///:memory:"
        app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
        db = SQLAlchemy(app)
        # Setup according to https://docs.scoutapm.com/#flask-sqlalchemy
        instrument_sqlalchemy(db)
        conn = db.engine.connect()

        @app.route("/sqlalchemy/")
        def sqlalchemy():
            result = conn.execute("SELECT 'Hello from the DB!'")
            return list(result)[0][0]

        try:
            yield app
        finally:
            conn.close() 

def generate_context(config):
    """Create the Flask app context and initializes any extensions such as Celery, Redis, SQLAlchemy, etc.

    :param dict config: Partial Flask config dict from generate_config().

    :return: The Flask app instance.
    """
    flask_app = Flask(__name__)
    flask_app.config.update(config)
    flask_app.config['TESTING'] = True
    flask_app.config['CELERY_ACCEPT_CONTENT'] = ['pickle']

    if 'SQLALCHEMY_DATABASE_URI' in flask_app.config:
        db = SQLAlchemy(flask_app)
        db.engine.execute('DROP TABLE IF EXISTS celery_tasksetmeta;')
    elif 'REDIS_URL' in flask_app.config:
        redis = Redis(flask_app)
        redis.flushdb()

    Celery(flask_app)
    return flask_app 

def init_flask():
    global db
    global UserMixin
    import docassemble.webapp.database
    if docassemble.webapp.database.pool_pre_ping:
        from flask_sqlalchemy import SQLAlchemy as _BaseSQLAlchemy
        class SQLAlchemy(_BaseSQLAlchemy):
            def apply_pool_defaults(self, app, options):
                super().apply_pool_defaults(app, options)
                options["pool_pre_ping"] = True
    else:
        from flask_sqlalchemy import SQLAlchemy
    db = SQLAlchemy()
    import flask_user
    UserMixin = flask_user.UserMixin
    return db 

def create_docker_init(project_address):
    import time
    t = time.strftime('%Y/%m/%d %H:%M:%S')
    project_name = os.path.basename(project_address)
    address = project_address + os.sep + project_name
    text = f"""#!/usr/bin/python3.7
# -*- coding: utf-8 -*-
# @Time  : {t}
# @Author: Jtyoui@qq.com
import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from config import CONFIG

env = os.getenv("FLASK_ENV")
db = SQLAlchemy()
app = Flask(__name__)
app.config.from_object(CONFIG.get(env, CONFIG['development']))
db.init_app(app)
"""
    if not os.path.exists(address):
        os.mkdir(address)
    if not os.path.exists(address + os.sep + '__init__.py'):
        with open(address + os.sep + '__init__.py', 'w', encoding='utf-8')as wf:
            wf.write(text)
    else:
        warnings.warn('路径：' + address + os.sep + '__init__.py已存在。默认不覆盖，请删除后在执行！') 

def db():
    return SQLAlchemy() 

def db():
    return SQLAlchemy() 

def __repr__(self):
        return '<id is %s, creditType is %s, queryType is %s, creditStatus is %s, monitorTime is %s, elapsedTime is %s>' % (
            self.id, self.credit_type, self.query_type, self.credit_status, self.monitor_time, self.elapsed_time)


# SQLAlchemy core 

def get_monitor_with_orm():
    s = monitor_db.get_connection_session(url)
    print(s.query(Monitor).limit(2).all())
    print(s.query(Monitor).first())
    print(type(s.query(Monitor)))
    print(s.query(Monitor).count())


# SQLAlchemy core 

def test_replace_send_code(app, get_message):
    from flask_sqlalchemy import SQLAlchemy
    from flask_security.models import fsqla_v2 as fsqla
    from flask_security import Security, us_send_security_token

    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///:memory:"
    db = SQLAlchemy(app)

    fsqla.FsModels.set_db_info(db)

    class Role(db.Model, fsqla.FsRoleMixin):
        pass

    class User(db.Model, fsqla.FsUserMixin):
        def us_send_security_token(self, method, **kwargs):
            assert method == "sms"
            us_send_security_token(self, method, **kwargs)

    with app.app_context():
        db.create_all()

    ds = SQLAlchemyUserDatastore(db, User, Role)
    app.security = Security(app, datastore=ds)

    with app.app_context():
        client = app.test_client()

        # since we don't use client fixture - have to add user
        data = dict(email="trp@lp.com", password="password")
        response = client.post("/register", data=data, follow_redirects=True)
        assert b"Welcome trp@lp.com" in response.data
        logout(client)

        set_phone(app, email="trp@lp.com")
        data = dict(identity="trp@lp.com", chosen_method="sms")
        response = client.post("/us-signin/send-code", data=data, follow_redirects=True)
        assert b"Code has been sent" in response.data 

def sqlalchemy_setup(request, app, tmpdir, realdburl):
    from flask_sqlalchemy import SQLAlchemy
    from flask_security.models import fsqla_v2 as fsqla

    if realdburl:
        db_url, db_info = _setup_realdb(realdburl)
        app.config["SQLALCHEMY_DATABASE_URI"] = db_url
    else:
        app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///:memory:"

    db = SQLAlchemy(app)

    fsqla.FsModels.set_db_info(db)

    class Role(db.Model, fsqla.FsRoleMixin):
        pass

    class User(db.Model, fsqla.FsUserMixin):
        security_number = db.Column(db.Integer, unique=True)
        # For testing allow null passwords.
        password = db.Column(db.String(255), nullable=True)

        def get_security_payload(self):
            # Make sure we still properly hook up to flask JSONEncoder
            return {"email": str(self.email), "last_update": self.update_datetime}

    with app.app_context():
        db.create_all()

    def tear_down():
        if realdburl:
            db.drop_all()
            _teardown_realdb(db_info)

    request.addfinalizer(tear_down)

    return SQLAlchemyUserDatastore(db, User, Role) 

def setUp(self):
        class TestConfig(object):
            SQLALCHEMY_TRACK_MODIFICATIONS = True
            SQLALCHEMY_DATABASE_URI = 'sqlite://'
            DEBUG = True
            TESTING = True
            MSEARCH_INDEX_NAME = mkdtemp()
            MSEARCH_BACKEND = 'whoosh'

        self.app = Flask(__name__)
        self.app.config.from_object(TestConfig())
        self.db = SQLAlchemy(self.app)
        self.search = Search(self.app, db=self.db)

        db = self.db

        class Post(db.Model, ModelSaveMixin):
            __tablename__ = 'basic_posts'
            __searchable__ = ['title', 'content']

            id = db.Column(db.Integer, primary_key=True)
            title = db.Column(db.String(49))
            content = db.Column(db.Text)

            def __repr__(self):
                return '<Post:{}>'.format(self.title)

        self.Post = Post 

def get_args():

    parser = argparse.ArgumentParser(description="Generates SQLAlchemy model code from an existing database.")
    parser.add_argument("url", nargs="?", help="SQLAlchemy url to the database")
    parser.add_argument("--version", action="store_true", help="print the version number and exit")
    parser.add_argument("--host", default="0.0.0.0", help="host (interface ip) to run")
    parser.add_argument("--port", default=5000, type=int, help="host (interface ip) to run")
    parser.add_argument("--models", default=None, help="Load models from file instead of generating them dynamically")
    parser.add_argument("--schema", help="load tables from an alternate schema")
    parser.add_argument("--tables", help="tables to process (comma-separated, default: all)")
    parser.add_argument("--noviews", action="store_true", help="ignore views")
    parser.add_argument("--noindexes", action="store_true", help="ignore indexes")
    parser.add_argument("--noconstraints", action="store_true", help="ignore constraints")
    parser.add_argument("--nojoined", action="store_true", help="don't autodetect joined table inheritance")
    parser.add_argument("--noinflect", action="store_true", help="don't try to convert tables names to singular form")
    parser.add_argument("--noclasses", action="store_true", help="don't generate classes, only tables")
    parser.add_argument("--outfile", help="file to write output to (default: stdout)")
    args = parser.parse_args()

    if args.version:
        version = pkg_resources.get_distribution("sqlacodegen").parsed_version # noqa: F821
        print(version.public)
        exit()
    if not args.url:
        print("You must supply a url\n", file=sys.stderr)
        parser.print_help()
        exit(1)

    return args 

def setUp(self):
        FlaskBloggingTestCase.setUp(self)

        temp_dir = tempfile.gettempdir()
        self._dbfile = os.path.join(temp_dir, "temp.db")
        conn_string = self._conn_string(self._dbfile)
        self.app.config["SQLALCHEMY_BINDS"] = {
            'blog': conn_string
        }
        self._db = SQLAlchemy(self.app)
        self.storage = SQLAStorage(db=self._db, bind="blog")
        self._engine = self._db.get_engine(self.app, bind="blog")
        self._meta = self._db.metadata
        self._db.create_all(bind=["blog"]) 

def init_app(app):
    _config_requests_session(app)
    _config_url_converters(app)
    _config_authmethods(app)
    _config_killmails(app)


# SQLAlchemy performance logging 

def _get_extension_type(desc):
    """Return the extension_type of a SQLAlchemy descriptors.

    This also handles proxy descriptors, looking up the extension type on
    the proxied-to descriptor.
    """
    extension_type = desc.extension_type
    if extension_type is NOT_EXTENSION:
        proxied_desc = getattr(desc, 'descriptor', None)
        if proxied_desc is not None:
            extension_type = proxied_desc.extension_type
    return extension_type 

def check_database_connected(db):
    """
    A built-in check to see if connecting to the configured default
    database backend succeeds.

    It's automatically added to the list of Dockerflow checks if a
    :class:`~flask_sqlalchemy.SQLAlchemy` object is passed
    to the :class:`~dockerflow.flask.app.Dockerflow` class during
    instantiation, e.g.::

        from flask import Flask
        from flask_sqlalchemy import SQLAlchemy
        from dockerflow.flask import Dockerflow

        app = Flask(__name__)
        app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
        db = SQLAlchemy(app)

        dockerflow = Dockerflow(app, db=db)
    """
    from sqlalchemy.exc import DBAPIError, SQLAlchemyError

    errors = []
    try:
        with db.engine.connect() as connection:
            connection.execute("SELECT 1;")
    except DBAPIError as e:
        msg = "DB-API error: {!s}".format(e)
        errors.append(Error(msg, id=health.ERROR_DB_API_EXCEPTION))
    except SQLAlchemyError as e:
        msg = 'Database misconfigured: "{!s}"'.format(e)
        errors.append(Error(msg, id=health.ERROR_SQLALCHEMY_EXCEPTION))
    return errors 

def db(app):
    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite://"
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    return SQLAlchemy(app) 

def test_full_db_check(mocker):
    app = Flask("db-check")
    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite://"
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db = SQLAlchemy(app)
    dockerflow = Dockerflow(app, db=db)
    assert "check_database_connected" in dockerflow.checks

    response = app.test_client().get("/__heartbeat__")
    assert response.status_code == 200
    assert json.loads(response.data.decode())["status"] == "ok" 

def test_full_db_check_error(mocker):
    app = Flask("db-check")
    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite://"
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db = SQLAlchemy(app)

    engine_connect = mocker.patch.object(db.engine, "connect")
    engine_connect.side_effect = SQLAlchemyError
    dockerflow = Dockerflow(app, db=db)
    assert "check_database_connected" in dockerflow.checks

    with app.test_client() as test_client:
        response = test_client.get("/__heartbeat__")
        assert response.status_code == 500
        assert json.loads(response.data.decode())["status"] == "error" 

def db(app: Flask) -> Generator[SQLAlchemy, None, None]:
    """Flask-SQLAlchemy Fixture"""
    _db.app = app
    #_db.create_all()
    yield _db
    # _db.drop_all() 

def session(db: SQLAlchemy) -> Generator[scoped_session, None, None]:
    """SQLAlchemy session Fixture"""
    connection: sqlalchemy.engine.base.Connection = db.engine.connect()

    with db.app.app_context():
        config = Config(ALEMBIC_CONFIG)

        # Issues with running upgrade() in a fixture with SQLite in-memory db:
        # https://github.com/miguelgrinberg/Flask-Migrate/issues/153
        #
        # Basically: Alembic always spawns a new connection from upgrade() unless you specify a connection
        # in config.attributes['connection']
        config.attributes['connection'] = connection
        upgrade(config, 'head')
        connection.execute("SELECT * FROM devices")

    # transaction = connection.begin()

    options = dict(bind=connection)
    session = db.create_scoped_session(options=options)

    db.session = session

    yield session

    # transaction.rollback()
    session.remove() 

def db(app):
    app.config["SQLALCHEMY_DATABASE_URI"] = os.environ.get(
        "DATABASE_URL", "sqlite://"
    )

    # TODO: Remove once this is the default.
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    return fsa.SQLAlchemy(app) 

def init_admin_views(flask_app, database):
    from psi.app.views import init_admin_views
    try:
        return init_admin_views(flask_app, database)
    except sqlalchemy.exc.SQLAlchemyError as e:
        # If we're running the flask utility script and Postgres
        # isn't available, `init_admin_views` will raise an OperationalError,
        # blocking the utility script from running. Instead, we catch the
        # exception and warn the user so the user can invoke some of the
        # commands that don't require database connections.
        # TODO: don't require Postgres on app object initialization
        log.exception(e)
        log.warn('Cannot register admin views because of a SQLAlchemy error. '
                 'Skipping...') 

def init_db(flask_app):
    from flask_sqlalchemy import SQLAlchemy
    sqlalchemy = SQLAlchemy(flask_app, session_options={'autoflush': False})
    sqlalchemy.init_app(flask_app)
    Info.set_db(sqlalchemy)
    return sqlalchemy 

def from_sql(row):
  """Translates an SQLAlchemy model instance into a dictionary."""
  if not row:
    return None
  data = row.__dict__.copy()
  data['id'] = row.id
  data.pop('_sa_instance_state')
  return data 

def init():
        # Do init once
        app = Flask(__name__)

        # Read and override configs
        app.config.from_pyfile('config_main.py')
        app.config.from_pyfile('../instance/config_site.py')
        app.config.from_envvar('GHC_SETTINGS', silent=True)

        # Global Logging config
        logging.basicConfig(level=int(app.config['GHC_LOG_LEVEL']),
                            format=app.config['GHC_LOG_FORMAT'])

        app.config['GHC_SITE_URL'] = \
            app.config['GHC_SITE_URL'].rstrip('/')

        app.secret_key = app.config['SECRET_KEY']

        App.db_instance = SQLAlchemy(app)
        App.babel_instance = Babel(app)

        # Plugins (via Docker ENV) must be list, but may have been
        # specified as comma-separated string, or older set notation
        app.config['GHC_PLUGINS'] = to_list(app.config['GHC_PLUGINS'])
        app.config['GHC_USER_PLUGINS'] = \
            to_list(app.config['GHC_USER_PLUGINS'])

        # Concatenate core- and user-Plugins
        App.plugins_instance = \
            app.config['GHC_PLUGINS'] + app.config['GHC_USER_PLUGINS']

        # Needed to find Plugins
        home_dir = os.path.dirname(os.path.abspath(__file__))
        App.home_dir = sys.path.append('%s/..' % home_dir)

        # Finally assign app-instance
        App.app_instance = app
        App.count += 1
        LOGGER.info("created GHC App instance #%d" % App.count)