Python sqlalchemy.create_engine() 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 load(self) -> bool:
        if not self.loader:
            try:
                self.loader = PluginLoader.find(self.type)
            except KeyError:
                self.log.error(f"Failed to find loader for type {self.type}")
                self.db_instance.enabled = False
                return False
        if not self.client:
            self.client = Client.get(self.primary_user)
            if not self.client:
                self.log.error(f"Failed to get client for user {self.primary_user}")
                self.db_instance.enabled = False
                return False
        if self.loader.meta.database:
            db_path = os.path.join(self.mb_config["plugin_directories.db"], self.id)
            self.inst_db = sql.create_engine(f"sqlite:///{db_path}.db")
        if self.loader.meta.webapp:
            self.inst_webapp, self.inst_webapp_url = self.webserver.get_instance_subapp(self.id)
        self.log.debug("Plugin instance dependencies loaded")
        self.loader.references.add(self)
        self.client.references.add(self)
        return True 

def create_new_ensemble():
    req_body = request.get_json()
    ensemble_name = req_body['ensemble_name']

    if os.path.exists(ensemble_name):
        return jsonify(message="File/folder already exists"), 400

    os.makedirs(ensemble_name)
    xcessiv_notebook_path = os.path.join(ensemble_name, app.config['XCESSIV_NOTEBOOK_NAME'])
    sqlite_url = 'sqlite:///{}'.format(xcessiv_notebook_path)
    engine = create_engine(sqlite_url)

    models.Base.metadata.create_all(engine)

    # Initialize
    extraction = models.Extraction()
    with functions.DBContextManager(ensemble_name) as session:
        session.add(extraction)
        session.commit()

    return jsonify(message="Xcessiv notebook created") 

def main(args, env):
    global Session

    if args.verbose >= 1:
        app.config['DEBUG'] = True
    sys.stderr.write("connecting to DB server {:s}\n".format(args.db))
    connection_succeeded = False
    while not connection_succeeded:
        try:
            engine = create_engine(args.db)
            Session = sessionmaker(bind = engine)
            Base.metadata.create_all(engine)
            sys.stderr.write("connection succeeded!\n")
            connection_succeeded = True
            app.run(debug = args.verbose >= 1, host = "0.0.0.0", port = 80)
        except OperationalError as err:
            if "Connection refused" in str(err):
                connection_succeeded = False
                time.sleep(10)
            else:
                raise 

def populate(cli_ctx, fixture_path):
    '''Populate fixtures.'''
    with cli_ctx.logger:
        log.info("populating fixture '{0}'", fixture_path)
        try:
            fixture = json.loads(fixture_path.read_text(encoding='utf8'))
        except AttributeError:
            log.error('No such fixture.')
            return

        engine = sa.create_engine(
            f"postgres://{cli_ctx.config['db']['user']}:{cli_ctx.config['db']['password']}"
            f"@{cli_ctx.config['db']['addr']}/{cli_ctx.config['db']['name']}")
        conn = engine.connect()
        populate_fixture(conn, fixture)
        conn.close() 

def open(self,database):
    """
    params:
        database: str. Database to be opered. The path should be included
    """
    assert(database[-4:]=='.mdo')
    if not os.path.exists(database):
        self._create(database)
    operate_db=database[:-4]+'.op'
    shutil.copy(database,operate_db)
#        engine=create_engine('sqlite:///:memory:')
    engine=create_engine('sqlite:///'+operate_db) #should be run in memory in the future
    Session=o.sessionmaker(bind=engine)
    self.session=Session()
    self.__operate_db=operate_db
    self.__storage_db=database 

def test_package_groups_save_to_sql():
    package = Package('data/datapackage-groups/datapackage.json')

    # Save to storage
    engine = sqlalchemy.create_engine('sqlite://')
    storage = Storage.connect('sql', engine=engine)
    package.save(storage=storage)

    # Check storage
    storage = Storage.connect('sql', engine=engine)
    assert storage.buckets == ['cars_2016', 'cars_2017', 'cars_2018']
    for year in [2016, 2017, 2018]:
        assert storage.describe('cars_%s' % year) == {
            'fields': [
                {'name': 'name', 'type': 'string'},
                {'name': 'value', 'type': 'integer'},
            ],
        }
        assert storage.read('cars_%s' % year) == [
            ['bmw', year],
            ['tesla', year],
            ['nissan', year],
        ] 

def setup_connection(self) -> None:
        assert isinstance(self.settings, SQLAlchemySettings), self.settings
        if self._engine is None:

            # Create SQLAlchemy engine.
            if self.is_sqlite():
                kwargs: Dict[str, Any] = {"connect_args": {"check_same_thread": False}}
            elif self.settings.pool_size == 1:
                kwargs = {"poolclass": StaticPool}
            else:
                kwargs = {"pool_size": self.settings.pool_size}

            self._engine = create_engine(
                self.settings.uri,
                strategy=self._connection_strategy,
                # echo=True,
                **kwargs
            )
            assert self._engine 

def get_dump_engine(echo=False):
    """ Creates a dump engine to a specific database.
        :returns: engine """

    statements = list()

    def dump(sql, *multiparams, **params):
        statement = str(sql.compile(dialect=engine.dialect))
        if statement in statements:
            return
        statements.append(statement)
        if statement.endswith(')\n\n'):
            if engine.dialect.name == 'oracle':
                print(statement.replace(')\n\n', ') PCTFREE 0;\n'))
            else:
                print(statement.replace(')\n\n', ');\n'))
        elif statement.endswith(')'):
            print(statement.replace(')', ');\n'))
        else:
            print(statement)
    sql_connection = config_get(DATABASE_SECTION, 'default')

    engine = create_engine(sql_connection, echo=echo, strategy='mock', executor=dump)
    return engine 

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = create_engine(DBURL, poolclass=pool.NullPool)

    with connectable.connect() as connection:

        context.configure(
            connection=connection,
            target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations() 

def _db_dsn(request):
    name = 'test_{}'.format(uuid.uuid4().hex)
    pg_dsn = 'postgresql://postgres:postgres@postgres:5432/postgres'
    db_dsn = 'postgresql://postgres:postgres@postgres:5432/{}'.format(name)

    pg_engine = sqlalchemy.create_engine(pg_dsn)
    pg_engine.raw_connection()\
        .set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    pg_engine.execute('CREATE DATABASE {0}'.format(name))
    pg_engine.dispose()

    db_engine = sqlalchemy.create_engine(db_dsn)
    setup_db(db_engine)
    db_engine.dispose()

    def fin():
        pg_engine = sqlalchemy.create_engine(pg_dsn)
        pg_engine.raw_connection() \
            .set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
        pg_engine.execute('DROP DATABASE {0}'.format(name))
        pg_engine.dispose()

    request.addfinalizer(fin)
    return db_dsn 

def test_query():
    sa_engine = create_engine(
        'sqlite://',
        connect_args={'check_same_thread': False},
        poolclass=StaticPool,
    )
    setup_db(sa_engine)

    app = ConsoleApplication(GRAPH, engine, {SA_ENGINE_KEY: sa_engine},
                             debug=True)
    query = b'[{:bar_list [:name :type {:foo_s [:name :count]}]}]'

    status, headers, content = request(app, 'POST', '/', payload=query)
    assert status == '200 OK'
    assert ('Content-Type', 'application/json') in headers
    result = json.loads(content.decode('utf-8'))
    assert 'bar_list' in result 

def oneshot(cli_ctx, alembic_config):
    '''
    Set up your database with one-shot schema migration instead of
    iterating over multiple revisions if there is no existing database.
    It uses alembic.ini to configure database connection.

    Reference: http://alembic.zzzcomputing.com/en/latest/cookbook.html
               #building-an-up-to-date-database-from-scratch
    '''
    with cli_ctx.logger:
        alembic_cfg = Config(alembic_config)
        sa_url = alembic_cfg.get_main_option('sqlalchemy.url')

        engine = sa.create_engine(sa_url)
        engine.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')

        with engine.begin() as connection:
            context = MigrationContext.configure(connection)
            current_rev = context.get_current_revision()

        if current_rev is None:
            # For a fresh clean database, create all from scratch.
            # (it will raise error if tables already exist.)
            log.info('Detected a fresh new database.')
            log.info('Creating tables...')
            with engine.begin() as connection:
                alembic_cfg.attributes['connection'] = connection
                metadata.create_all(engine, checkfirst=False)
                log.info('Stamping alembic version to head...')
                command.stamp(alembic_cfg, 'head')
        else:
            # If alembic version info is already available, perform incremental upgrade.
            log.info('Detected an existing database.')
            log.info('Performing schema upgrade to head...')
            with engine.begin() as connection:
                alembic_cfg.attributes['connection'] = connection
                command.upgrade(alembic_cfg, 'head')

        log.info("If you don't need old migrations, delete them and set "
                 "\"down_revision\" value in the earliest migration to \"None\".") 

def senddatanews(content,toparty=cf.get("wechat", "toparty"),agentid = cf.get("wechat", "agentid")):
    access_token = gettoken()
    send_url = 'https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=' + access_token
    send_values = {
        "toparty": "{0}".format(toparty),
        "msgtype": "news",
        "agentid": agentid,
        "news": {
            "articles": content
        }
    }
    logging.info(send_values)
    send_data = json.dumps(
        send_values,
        ensure_ascii=False).encode(
        encoding='UTF8')
    send_request = urllib.request.Request(send_url, send_data)
    response = urllib.request.urlopen(send_request)
    logging.info(response.read())
# def query_db(query_string):
#    logging.info(query_string.encode())
#    connection_string=cf.get("database","connection")
#    engine = sqlalchemy.create_engine(connection_string)
#    res = engine.execute(query_string)
#    return res 

def get_engine(self):
        with self._lock:
            uri = self.get_uri()
            echo = self._app.config['SQLALCHEMY_ECHO']
            if (uri, echo) == self._connected_for:
                return self._engine
            info = make_url(uri)
            options = {'convert_unicode': True}
            self._sa.apply_pool_defaults(self._app, options)
            self._sa.apply_driver_hacks(self._app, info, options)
            if echo:
                options['echo'] = True
            self._engine = rv = sqlalchemy.create_engine(info, **options)
            if _record_queries(self._app):
                _EngineDebuggingSignalEvents(self._engine,
                                             self._app.import_name).register()
            self._connected_for = (uri, echo)
            return rv 

def update_execution_options(self, **opt):
        """Update the default execution_options dictionary
        of this :class:`.Engine`.

        The given keys/values in \**opt are added to the
        default execution options that will be used for
        all connections.  The initial contents of this dictionary
        can be sent via the ``execution_options`` parameter
        to :func:`.create_engine`.

        .. seealso::

            :meth:`.Connection.execution_options`

            :meth:`.Engine.execution_options`

        """
        self._execution_options = \
            self._execution_options.union(opt)
        self.dispatch.set_engine_execution_options(self, opt)
        self.dialect.set_engine_execution_options(self, opt) 

def _bind_to(self, url, bind):
        """Bind to a Connectable in the caller's thread."""

        if isinstance(bind, util.string_types + (url.URL, )):
            try:
                self.context._engine = self.__engines[bind]
            except KeyError:
                e = sqlalchemy.create_engine(bind)
                self.__engines[bind] = e
                self.context._engine = e
        else:
            # TODO: this is squirrely.  we shouldn't have to hold onto engines
            # in a case like this
            if bind not in self.__engines:
                self.__engines[bind] = bind
            self.context._engine = bind 

def __init__(self, results_queue, thread_pool, use_file=True, use_database=True, filename="proxy-ip-list.csv"):
        self.use_file = use_file
        self.use_database = use_database
        self.filename = filename
        self.results_queue = results_queue
        self.thread_pool = thread_pool

        if use_database:
            try:
                cf = ConfigParser.ConfigParser()
                cf.read("config.ini")
                db_name = cf.get("Pansidong", "database")
                username = cf.get(db_name, "username")
                password = cf.get(db_name, "password")
                host = cf.get(db_name, "host")
                database = cf.get(db_name, "database")
            except AttributeError, e:
                logger.fatal(e.message)
                sys.exit(1)
            self.engine = create_engine("mysql://" + username + ":" + password + "@" +
                                        host + "/" + database + "?charset=utf8")
            self.db_session = sessionmaker(bind=self.engine)
            self.session = self.db_session() 

def init(config: Config) -> Engine:
    db = sql.create_engine(config["database"])
    Base.metadata.bind = db

    for table in (DBPlugin, DBClient):
        table.bind(db)

    if not db.has_table("alembic_version"):
        log = logging.getLogger("maubot.db")

        if db.has_table("client") and db.has_table("plugin"):
            log.warning("alembic_version table not found, but client and plugin tables found. "
                        "Assuming pre-Alembic database and inserting version.")
            db.execute("CREATE TABLE IF NOT EXISTS alembic_version ("
                       "    version_num VARCHAR(32) PRIMARY KEY"
                       ");")
            db.execute("INSERT INTO alembic_version VALUES ('d295f8dcfa64');")
        else:
            log.critical("alembic_version table not found. "
                         "Did you forget to `alembic upgrade head`?")
            sys.exit(10)

    return db 

def bind(self):
        """An :class:`.Engine` or :class:`.Connection` to which this
        :class:`.MetaData` is bound.

        Typically, a :class:`.Engine` is assigned to this attribute
        so that "implicit execution" may be used, or alternatively
        as a means of providing engine binding information to an
        ORM :class:`.Session` object::

            engine = create_engine("someurl://")
            metadata.bind = engine

        .. seealso::

           :ref:`dbengine_implicit` - background on "bound metadata"

        """
        return self._bind 

def build_mssql_engine_using_trusted_connections(server, database):
    """
    Given a server and database name, build a Trusted Connection MSSQL database engine. NOTE: Requires `pyodbc`
    
    Args:
        server (str): Server name 
        database (str): Database name

    Returns:
        sqlalchemy.engine.base.Engine: an sqlalchemy connection engine
    """
    hcai_db_library.validate_pyodbc_is_loaded()

    connection_string = build_mssql_trusted_connection_string(server, database)
    params = urllib.parse.quote_plus(connection_string)
    engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))

    return engine 

def init_db(app_db_path):
    # Open session for database connection
    global session
    global app_DB_path

    app_DB_path = app_db_path
    engine = create_engine(u'sqlite:///{0}'.format(app_db_path), echo=False)

    Session = sessionmaker()
    Session.configure(bind=engine)
    session = Session()

    if os.path.exists(app_db_path):
        Base.metadata.create_all(engine)
        migrate_Database(session)
        clean_database(session)
    else:
        Base.metadata.create_all(engine)
        create_admin_user(session)
        create_anonymous_user(session) 

def __init__(self, url, **kwargs):
        super(Database, self).__init__()

        # connect
        self.engine = sqlalchemy.create_engine(url, **kwargs)

        # create Session object
        self._Session = sqlalchemy.orm.sessionmaker(bind=self.engine) 

def init(config):
    global connect
    global DictCursor
    global IntegrityError

    engine = create_engine(config['conn']['str'] % config['conn']['kwargs'],
                           **config['kwargs'])
    dbapi = engine.dialect.dbapi
    IntegrityError = dbapi.IntegrityError

    DictCursor = dbapi.cursors.DictCursor
    connect = engine.raw_connection 

def test_invocation(self):

        dbapi_session = ReplayableSession()
        creator = config.db.pool._creator
        recorder = lambda: dbapi_session.recorder(creator())
        engine = create_engine(
            config.db.url, creator=recorder,
            use_native_hstore=False)
        self.metadata = MetaData(engine)
        self.engine = engine
        self.session = Session(engine)

        self.setup_engine()
        try:
            self._run_steps(ctx=self._dummy_ctx)
        finally:
            self.teardown_engine()
            engine.dispose()

        player = lambda: dbapi_session.player()
        engine = create_engine(
            config.db.url, creator=player,
            use_native_hstore=False)

        self.metadata = MetaData(engine)
        self.engine = engine
        self.session = Session(engine)

        self.setup_engine()
        try:
            self._run_steps(ctx=profiling.count_functions)
        finally:
            self.session.close()
            engine.dispose() 

def get_isolation_level(self):
        """Return the current isolation level assigned to this
        :class:`.Connection`.

        This will typically be the default isolation level as determined
        by the dialect, unless if the
        :paramref:`.Connection.execution_options.isolation_level`
        feature has been used to alter the isolation level on a
        per-:class:`.Connection` basis.

        This attribute will typically perform a live SQL operation in order
        to procure the current isolation level, so the value returned is the
        actual level on the underlying DBAPI connection regardless of how
        this state was set.  Compare to the
        :attr:`.Connection.default_isolation_level` accessor
        which returns the dialect-level setting without performing a SQL
        query.

        .. versionadded:: 0.9.9

        .. seealso::

            :attr:`.Connection.default_isolation_level` - view default level

            :paramref:`.create_engine.isolation_level`
            - set per :class:`.Engine` isolation level

            :paramref:`.Connection.execution_options.isolation_level`
            - set per :class:`.Connection` isolation level

        """
        try:
            return self.dialect.get_isolation_level(self.connection)
        except Exception as e:
            self._handle_dbapi_exception(e, None, None, None, None) 

def __create_schema() -> None:
    engine = create_engine(APP.config['SQLALCHEMY_DATABASE_URI'])
    if not database_exists(engine.url):
        create_database(engine.url)
        db.DB.create_all()
    engine.dispose() 

def setUpClass(cls):
        register_dialects()
        cls.db_name = os.environ.get("AURORA_DB_NAME", __name__)
        cls.engine = create_engine(cls.dialect + ':@/' + cls.db_name + "?charset=utf8mb4") 

def setUpClass(cls):
        register_dialects()
        cls.db_name = os.environ.get("AURORA_DB_NAME", __name__)
        cls.engine = create_engine(cls.dialect + ':@/' + cls.db_name) 

def default_isolation_level(self):
        """The default isolation level assigned to this :class:`.Connection`.

        This is the isolation level setting that the :class:`.Connection`
        has when first procured via the :meth:`.Engine.connect` method.
        This level stays in place until the
        :paramref:`.Connection.execution_options.isolation_level` is used
        to change the setting on a per-:class:`.Connection` basis.

        Unlike :meth:`.Connection.get_isolation_level`, this attribute is set
        ahead of time from the first connection procured by the dialect,
        so SQL query is not invoked when this accessor is called.

        .. versionadded:: 0.9.9

        .. seealso::

            :meth:`.Connection.get_isolation_level` - view current level

            :paramref:`.create_engine.isolation_level`
            - set per :class:`.Engine` isolation level

            :paramref:`.Connection.execution_options.isolation_level`
            - set per :class:`.Connection` isolation level

        """
        return self.dialect.default_isolation_level