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 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 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 _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 __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 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 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 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 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 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 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 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(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 _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 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 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 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 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 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 setUpClass(self):
        """Database setup before the CRUD tests."""
        print("Creating a temporary datatbsse...")
        engine = create_engine('sqlite:///:memory:')
        Base.metadata.create_all(engine)
        session = scoped_session(sessionmaker(bind=engine))
        self.API_NAME = "demoapi"
        self.HYDRUS_SERVER_URL = "http://hydrus.com/"
        self.session = session

        self.doc = doc_maker.create_doc(
            doc, self.HYDRUS_SERVER_URL, self.API_NAME)

        test_classes = doc_parse.get_classes(self.doc.generate())

        # Getting list of classes from APIDoc
        self.doc_collection_classes = [
            self.doc.collections[i]["collection"].class_.title for i in self.doc.collections]
        print(self.doc_collection_classes)
        print(random.choice(self.doc_collection_classes))
        test_properties = doc_parse.get_all_properties(test_classes)
        doc_parse.insert_classes(test_classes, self.session)
        doc_parse.insert_properties(test_properties, self.session)
        print("Classes and properties added successfully.")
        print("Setup done, running tests...") 

def connect_db(self):
        """Connect the state manager to the persistent DB."""
        self.db_engine = create_engine(
            config.config_mgr.conf.database.database_connect_string,
            pool_size=config.config_mgr.conf.database.pool_size,
            pool_pre_ping=config.config_mgr.conf.database.pool_pre_ping,
            max_overflow=config.config_mgr.conf.database.pool_overflow,
            pool_timeout=config.config_mgr.conf.database.pool_timeout,
            pool_recycle=config.config_mgr.conf.database.connection_recycle)
        self.db_metadata = MetaData(bind=self.db_engine)

        self.tasks_tbl = tables.Tasks(self.db_metadata)
        self.result_message_tbl = tables.ResultMessage(self.db_metadata)
        self.active_instance_tbl = tables.ActiveInstance(self.db_metadata)
        self.boot_action_tbl = tables.BootAction(self.db_metadata)
        self.ba_status_tbl = tables.BootActionStatus(self.db_metadata)
        self.build_data_tbl = tables.BuildData(self.db_metadata)
        return 

def create(self,database):
    """
    params:
        database: str. Database to be created. The path should be included
    """
    assert(database[-4:]=='.mdo')
    #initialize
    engine=create_engine('sqlite:///'+database)
    Base.metadata.create_all(engine)
    Session=o.sessionmaker(bind=engine)
    self.session=Session()
    
    #configurations
    config=Config()
    config.project_name='dev'
    config.author='HZJ'
    config.program_version='0.0.1'
    config.unit='N_m_C'
    config.create_time=datetime.now()                 
    self.session.add(config)
    self.session.commit()
    
    #default material and sections
    self.add_material('Q345',7849,'isotropic_elastic',E=2e11,mu=0.3)
    self.add_material('C35',2500,'isotropic_elastic',E=2e10,mu=0.2)
    self.add_frame_section('1-L-H400x200x14x20','Q345','I',[0.4,0.2,0.014,0.02])
    self.add_area_section('A-M120','C35','m',0.12)
    
    #default loadcase
    self.add_loadcase('S','static-linear',1.)
    self.session.commit()
    self.session.close() 

def test_from_results(self):
        """The from_results method creates a QueryResult."""
        engine = create_engine("sqlite://", strategy=ASYNCIO_STRATEGY)
        async with engine.connect() as conn:
            result = await conn.execute("SELECT 1 AS a, 2 AS b")
            query_results = await QueryResults.from_results(result)
        assert query_results.keys == ["a", "b"]
        assert query_results.rows == [(1, 2)]
        assert query_results.latency is None 

def test_from_results_with_latency(self):
        """The from_results method creates a QueryResult."""
        engine = create_engine("sqlite://", strategy=ASYNCIO_STRATEGY)
        async with engine.connect() as conn:
            result = await conn.execute("SELECT 1 AS a, 2 AS b")
            # simulate latency tracking
            conn.sync_connection.info["query_latency"] = 1.2
            query_results = await QueryResults.from_results(result)
        assert query_results.keys == ["a", "b"]
        assert query_results.rows == [(1, 2)]
        assert query_results.latency == 1.2 

def connect(path):
	global session
	global engine
	
	# TODO: think about check_same_thread=False
	engine = create_engine("sqlite:///%s/data.db"%path, connect_args={'check_same_thread':False}, echo = False) # echo = true aktiviert debug logging

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

def test_basic(self):
        try:
            # start postgresql server
            pgsql = testing.postgresql.Postgresql()
            self.assertIsNotNone(pgsql)
            params = pgsql.dsn()
            self.assertEqual('test', params['database'])
            self.assertEqual('127.0.0.1', params['host'])
            self.assertEqual(pgsql.settings['port'], params['port'])
            self.assertEqual('postgres', params['user'])

            # connect to postgresql (w/ psycopg2)
            conn = psycopg2.connect(**pgsql.dsn())
            self.assertIsNotNone(conn)
            self.assertRegexpMatches(pgsql.read_bootlog(), 'is ready to accept connections')
            conn.close()

            # connect to postgresql (w/ sqlalchemy)
            engine = sqlalchemy.create_engine(pgsql.url())
            self.assertIsNotNone(engine)

            # connect to postgresql (w/ pg8000)
            conn = pg8000.connect(**pgsql.dsn())
            self.assertIsNotNone(conn)
            self.assertRegexpMatches(pgsql.read_bootlog(), 'is ready to accept connections')
            conn.close()
        finally:
            # shutting down
            pid = pgsql.server_pid
            self.assertTrue(pgsql.is_alive())

            pgsql.stop()
            sleep(1)

            self.assertFalse(pgsql.is_alive())
            with self.assertRaises(OSError):
                os.kill(pid, 0)  # process is down 

def _check(self, src, value, event_loop):
        sa_engine = await aiopg.sa.create_engine(self.db_dsn, minsize=0,
                                                 loop=event_loop)
        try:
            engine = Engine(AsyncIOExecutor(event_loop))
            result = await engine.execute(self.graph, read(src),
                                          {SA_ENGINE_KEY: sa_engine})
            check_result(result, value)
        finally:
            sa_engine.close()
            await sa_engine.wait_closed() 

def check(self, src, value):
        sa_engine = create_engine(
            'sqlite://',
            connect_args={'check_same_thread': False},
            poolclass=StaticPool,
        )
        setup_db(sa_engine)

        engine = Engine(ThreadsExecutor(thread_pool))
        result = engine.execute(self.graph, read(src),
                                {SA_ENGINE_KEY: sa_engine})
        check_result(result, value)