Python sqlalchemy.engine.create_engine() Examples

def __init__(self, url, engine_kwargs=None, skip_compatibility_check=False):
        # type: (str, Optional[Dict[str, Any]], bool) -> None

        self.engine_kwargs = engine_kwargs or {}
        self.url = self._fill_storage_url_template(url)
        self.skip_compatibility_check = skip_compatibility_check

        self._set_default_engine_kwargs_for_mysql(url, self.engine_kwargs)

        try:
            self.engine = create_engine(self.url, **self.engine_kwargs)
        except ImportError as e:
            raise ImportError(
                "Failed to import DB access module for the specified storage URL. "
                "Please install appropriate one. (The actual import error is: " + str(e) + ".)"
            )

        self.scoped_session = orm.scoped_session(orm.sessionmaker(bind=self.engine))
        models.BaseModel.metadata.create_all(self.engine)

        self._version_manager = _VersionManager(self.url, self.engine, self.scoped_session)
        if not skip_compatibility_check:
            self._version_manager.check_table_schema_compatibility()

        weakref.finalize(self, self._finalize) 

def initdb():
    if not settings.IS_IN_DEV:
        raise RuntimeError('Should never use this in production environment')

    engine = get_engine()
    database_name = quote(engine.dialect, engine.url.database)
    schema_ddl = resource_string(*SCHEMA_FILE)

    anonymous_url = deepcopy(engine.url)
    anonymous_url.database = None
    anonymous_url.query = {}
    anonymous_engine = create_engine(anonymous_url)

    with anonymous_engine.connect() as connection:
        connection.execute(
            'drop database if exists {0}'.format(database_name))
        connection.execute(
            'create database {0} character set utf8mb4 '
            'collate utf8mb4_bin'.format(database_name))

    with anonymous_engine.connect() as connection:
        connection.execute('use {0}'.format(database_name))
        connection.execute(schema_ddl) 

def codegen(args):

    # Use reflection to fill in the metadata
    engine = create_engine(args.url)

    metadata = MetaData(engine)
    tables = args.tables.split(",") if args.tables else None
    metadata.reflect(engine, args.schema, not args.noviews, tables)
    if db.session.bind.dialect.name == "sqlite":
        # dirty hack for sqlite
        engine.execute("""PRAGMA journal_mode = OFF""")

    # Write the generated model code to the specified file or standard output

    capture = StringIO()
    # outfile = io.open(args.outfile, 'w', encoding='utf-8') if args.outfile else capture # sys.stdout
    generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined, args.noinflect, args.noclasses)
    generator.render(capture)
    generated = capture.getvalue()
    generated = fix_generated(generated)
    if args.outfile:
        outfile = io.open(args.outfile, "w", encoding="utf-8")
        outfile.write(generated)
    return generated 

def __setstate__(self, state):
        # type: (Dict[Any, Any]) -> None

        self.__dict__.update(state)
        try:
            self.engine = create_engine(self.url, **self.engine_kwargs)
        except ImportError as e:
            raise ImportError(
                "Failed to import DB access module for the specified storage URL. "
                "Please install appropriate one. (The actual import error is: " + str(e) + ".)"
            )

        self.scoped_session = orm.scoped_session(orm.sessionmaker(bind=self.engine))
        models.BaseModel.metadata.create_all(self.engine)
        self._version_manager = _VersionManager(self.url, self.engine, self.scoped_session)
        if not self.skip_compatibility_check:
            self._version_manager.check_table_schema_compatibility() 

def test_sqlalchemy_compilation():
    engine = create_engine('impala://localhost')
    metadata = MetaData(engine)
    # TODO: add other types to this table (e.g., functional.all_types)
    mytable = Table("mytable",
                    metadata,
                    Column('col1', STRING),
                    Column('col2', TINYINT),
                    Column('col3', INT),
                    Column('col4', DOUBLE),
                    impala_partition_by='HASH PARTITIONS 16',
                    impala_stored_as='KUDU',
                    impala_table_properties={
                        'kudu.table_name': 'my_kudu_table',
                        'kudu.master_addresses': 'kudu-master.example.com:7051'
                    })
    observed = str(CreateTable(mytable, bind=engine))
    expected = ('\nCREATE TABLE mytable (\n\tcol1 STRING, \n\tcol2 TINYINT, '
                '\n\tcol3 INT, \n\tcol4 DOUBLE\n)'
                '\nPARTITION BY HASH PARTITIONS 16\nSTORED AS KUDU\n'
                "TBLPROPERTIES ('kudu.table_name' = 'my_kudu_table', "
                "'kudu.master_addresses' = 'kudu-master.example.com:7051')\n\n")
    assert expected == observed 

def test_connection_sniff(self, mock_elasticsearch):
        """
            SQLAlchemy: test Elasticsearch is called for multiple hosts
        """
        mock_elasticsearch.return_value = None
        self.engine = create_engine(
            "elasticsearch+http://localhost:9200/"
            "?sniff_on_start=True"
            "&sniff_on_connection_fail=True"
            "&sniffer_timeout=3"
            "&sniff_timeout=4"
            "&max_retries=10"
            "&retry_on_timeout=True"
        )
        self.connection = self.engine.connect()
        mock_elasticsearch.assert_called_once_with(
            "http://localhost:9200/",
            sniff_on_start=True,
            sniff_on_connection_fail=True,
            sniffer_timeout=3,
            sniff_timeout=4,
            max_retries=10,
            retry_on_timeout=True,
        ) 

def create_sa_proxies(self):

        # create the table and mapper
        metadata = schema.MetaData()
        user_table = schema.Table(
            'user',
            metadata,
            schema.Column('id', types.Integer, primary_key=True),
            schema.Column('first_name', types.Unicode(25)),
            schema.Column('last_name', types.Unicode(25))
        )

        class User(object):
            pass
        orm.mapper(User, user_table)

        # create the session
        engine = create_engine('sqlite:///:memory:')
        metadata.bind = engine
        metadata.create_all()
        session = orm.sessionmaker(bind=engine)()

        # add some dummy data
        user_table.insert().execute([
            {'first_name': 'Jonathan', 'last_name': 'LaCour'},
            {'first_name': 'Yoann', 'last_name': 'Roman'}
        ])

        # get the SA objects
        self.sa_object = session.query(User).first()
        select = user_table.select()
        self.result_proxy = select.execute()
        self.row_proxy = select.execute().fetchone() 

def example_driver_for_sqlalchemy():
    from sqlalchemy.engine import create_engine
    import boto3
    client = boto3.client('rds-data')
    engine = create_engine(
        'mysql+pydataapi://',  # or 'postgresql+pydataapi://',
        connect_args={
            'resource_arn': 'arn:aws:rds:us-east-1:123456789012:cluster:dummy',
            'secret_arn': 'arn:aws:secretsmanager:us-east-1:123456789012:secret:dummy',
            'database': 'test',
            'client': client},
    )

    result: ResultProxy = engine.execute("select * from pets")
    print(result.fetchall()) 

def setUp(self):
        super(TestJsonifySQLAlchemyGenericEncoder, self).setUp()
        if not create_engine:
            self.create_fake_proxies()
        else:
            self.create_sa_proxies() 

def test_GSheetsCompiler(self, m):
        header_payload = {
            'status': 'ok',
            'table': {
                'cols': [
                    {'id': 'A', 'label': 'country', 'type': 'string'},
                    {
                        'id': 'B',
                        'label': 'cnt',
                        'type': 'number',
                        'pattern': 'General',
                    },
                ],
                'rows': [],
            },
        }
        m.get(
            'http://docs.google.com/gviz/tq?gid=0&tq=SELECT%20%2A%20LIMIT%200',
            json=header_payload,
        )
        m.get(
            'http://docs.google.com/gviz/tq?gid=0&tq=SELECT%20%2A',
            json=header_payload,
        )
        engine = create_engine('gsheets://')
        table = Table(
            'http://docs.google.com/', MetaData(bind=engine), autoload=True)
        query = select([table.columns.country], from_obj=table)
        result = str(query)
        expected = 'SELECT country \nFROM "http://docs.google.com/"'
        self.assertEqual(result, expected) 

def fx_engine(request):
    url = request.param
    engine = create_engine(url, poolclass=NullPool)
    request.addfinalizer(engine.dispose)
    return engine 

def _load_engines(squealy, config):
    datasources = config.get('datasources', [])
    if not datasources:
        raise SquealyConfigException("datasources not defined!")
    if not isinstance(datasources, list):
        raise SquealyConfigException("datasources must be a list of objects!")
    
    for datasource in datasources:
        _id = datasource['id']
        url = datasource['url']
        try:
            engine = create_engine(url)
        except SQLAlchemyError as e:
            raise SquealyConfigException("Could not load SQLAlchemy Engine for datasource " + _id + ", url = " + url) from e
        squealy.add_engine(_id, SqlAlchemyEngine(engine)) 

def get_engine(self):
        """Get the engine from the DBCore

        Returns
        -------
        :class:`sqlalchemy.engine.base.Engine`
            Engine with the database dialect
        """
        return create_engine(self.dburl) 

def __init__(self, db_url, instance_id=0,
                 stale_in_flight_threshold=15*60,
                 log_level=logging.WARNING):
        """
        Initialize the Peekaboo database handler.

        @param db_url: An RFC 1738 URL that points to the database.
        @param instance_id: A positive, unique ID differentiating this Peekaboo
                            instance from any other instance using the same
                            database for concurrency coordination. Value of 0
                            means that we're alone and have no other instances
                            to worry about.
        @param stale_in_flight_threshold: Number of seconds after which a in
        flight marker is considered stale and deleted or ignored.
        """
        logging.getLogger('sqlalchemy.engine').setLevel(log_level)

        self.__engine = create_engine(db_url, pool_recycle=1)
        session_factory = sessionmaker(bind=self.__engine)
        self.__session = scoped_session(session_factory)
        self.__lock = threading.RLock()
        self.instance_id = instance_id
        self.stale_in_flight_threshold = stale_in_flight_threshold
        if not self._db_schema_exists():
            self._init_db()
            logger.debug('Database schema created.') 

def metadata():
    metadata = MetaData()
    metadata.bind = create_engine(URL)
    return metadata 

def create_engine(self):
        conn_str = (
            "awsathena+jdbc://athena.{region_name}.amazonaws.com:443/"
            + "{schema_name}?s3_staging_dir={s3_staging_dir}&s3_dir={s3_dir}"
            + "&compression=snappy"
        )
        return create_engine(
            conn_str.format(
                region_name=ENV.region_name,
                schema_name=SCHEMA,
                s3_staging_dir=quote_plus(ENV.s3_staging_dir),
                s3_dir=quote_plus(ENV.s3_staging_dir),
            )
        ) 

def get_engine():
    return create_engine(settings.PRESTO_URL) 

def fx_session():
    engine = create_engine(TEST_DATABASE_URL)
    try:
        metadata = Base.metadata
        metadata.drop_all(bind=engine)
        metadata.create_all(bind=engine)
        session = Session(bind=engine)
        yield session
        session.rollback()
        metadata.drop_all(bind=engine)
    finally:
        engine.dispose() 

def sqlalchemy_metadata(host, port, database, username, password):
    url = URL(drivername='postgresql+psycopg2', username=username,
              password=password, host=host, port=port, database=database)
    engine = create_engine(url, server_side_cursors=True, connect_args={'connect_timeout': 4})
    # ensure that we can connect
    with engine.begin():
        pass  # this will throw OperationalError if it fails
    return MetaData(engine) 

def sqlalchemy_metadata(host, port, database, username, password):
    url = URL(drivername='postgresql+psycopg2', username=username,
              password=password, host=host, port=port, database=database)
    engine = create_engine(url, server_side_cursors=True, connect_args={'connect_timeout': 4})
    # ensure that we can connect
    with engine.begin():
        pass  # this will throw OperationalError if it fails
    return MetaData(engine) 

def broken_db(db):
    broken_engine = create_engine(
        'mysql+pymysql://root@127.0.0.1:1/dotdotdot?charset=utf8')
    broken_engines = {'master': broken_engine, 'slave': broken_engine}
    healthy_engines = dict(db.engines)
    try:
        DBSession.registry.registry.clear()
        DBSession.configure(engines=broken_engines)
        yield DBSession()
    finally:
        DBSession.registry.registry.clear()
        DBSession.configure(engines=healthy_engines) 

def connect(json_conf, logger=None, **kwargs):

    """
    Function to create an engine to connect to a DB with, using the
    configuration inside the provided json_conf.
    :param json_conf:
    :param logger:
    :return: sqlalchemy.engine.base.Engine
    """

    @event.listens_for(Engine, "connect")
    def set_sqlite_pragma(dbapi_connection, connection_record):
        cursor = dbapi_connection.cursor()
        try:
            cursor.execute("PRAGMA foreign_keys=ON")
            cursor.execute("PRAGMA synchronous=OFF")
            cursor.execute("PRAGMA temp_store=MEMORY")
            cursor.execute("PRAGMA journal_mode=MEMORY")
            cursor.execute("PRAGMA count_changes=OFF")
        except sqlite3.OperationalError:
            pass
        finally:
            cursor.close()

    if json_conf is None:
        return create_engine("sqlite:///:memory:", **kwargs)

    db_connection = functools.partial(create_connector, json_conf, logger=logger)
    engine = create_engine("{0}://".format(json_conf["db_settings"]["dbtype"]),
                           creator=db_connection, **kwargs)
    DBBASE.metadata.create_all(engine, checkfirst=True)

    return engine 

def setUp(self):
        from gengine.app.cache import clear_all_caches
        clear_all_caches()
        self.db = db.db()
        dsn = self.db.dsn()
        self.engine =  create_engine(
            "postgresql://%(user)s@%(host)s:%(port)s/%(database)s" % {
                "user": dsn["user"],
                "host": dsn["host"],
                "port": dsn["port"],
                "database": dsn["database"],
            }
        )
        init_session(override_session=scoped_session(get_sessionmaker(bind=self.engine)), replace=True)
        from gengine.metadata import Base
        Base.metadata.bind = self.engine

        Base.metadata.drop_all(self.engine)
        self.engine.execute("DROP SCHEMA IF EXISTS public CASCADE")
        self.engine.execute("CREATE SCHEMA IF NOT EXISTS public")

        from alembic.config import Config
        from alembic import command

        alembic_cfg = Config(attributes={
            'engine': self.engine,
            'schema': 'public'
        })
        script_location = os.path.join(
            os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))),
            'app/alembic'
        )
        alembic_cfg.set_main_option("script_location", script_location)

        from gengine.app import model

        tables = [t for name, t in model.__dict__.items() if isinstance(t, Table)]
        Base.metadata.create_all(self.engine, tables=tables)

        command.stamp(alembic_cfg, "head") 

def startDatabase(self):
        self.engine = create_engine(

            # sqlite in-memory
            #"sqlite://", reactor=reactor, strategy=TWISTED_STRATEGY

            # sqlite on filesystem
            "sqlite:////tmp/kotori.sqlite", reactor=reactor, strategy=TWISTED_STRATEGY

            # mysql... todo
        )

        # Create the table
        yield self.engine.execute(CreateTable(self.telemetry))
        #yield self.engine 

def setUp(self):
        self.engine = create_engine("elasticsearch+http://localhost:9200/")
        self.connection = self.engine.connect()
        self.table_flights = Table("flights", MetaData(bind=self.engine), autoload=True) 

def test_auth(self, mock_elasticsearch):
        """
            SQLAlchemy: test Elasticsearch is called with user password
        """
        mock_elasticsearch.return_value = None
        self.engine = create_engine(
            "elasticsearch+http://user:password@localhost:9200/"
        )
        self.connection = self.engine.connect()
        mock_elasticsearch.assert_called_once_with(
            "http://localhost:9200/", http_auth=("user", "password")
        ) 

def test_connection_https_and_auth(self, mock_elasticsearch):
        """
            SQLAlchemy: test Elasticsearch is called with https and param
        """
        mock_elasticsearch.return_value = None
        self.engine = create_engine(
            "elasticsearch+https://user:password@localhost:9200/"
        )
        self.connection = self.engine.connect()
        mock_elasticsearch.assert_called_once_with(
            "https://localhost:9200/", http_auth=("user", "password")
        ) 

def test_connection_https_and_params(self, mock_elasticsearch):
        """
            SQLAlchemy: test Elasticsearch is called with https and param
        """
        mock_elasticsearch.return_value = None
        self.engine = create_engine(
            "elasticsearch+https://localhost:9200/"
            "?verify_certs=False"
            "&use_ssl=False"
        )
        self.connection = self.engine.connect()
        mock_elasticsearch.assert_called_once_with(
            "https://localhost:9200/", verify_certs=False, use_ssl=False
        ) 

def test_connection_params(self, mock_elasticsearch):
        """
            SQLAlchemy: test Elasticsearch is called with advanced config params
        """
        mock_elasticsearch.return_value = None
        self.engine = create_engine(
            "elasticsearch+http://localhost:9200/"
            "?http_compress=True&maxsize=100&timeout=3"
        )
        self.connection = self.engine.connect()
        mock_elasticsearch.assert_called_once_with(
            "http://localhost:9200/", http_compress=True, maxsize=100, timeout=3
        ) 

def engine():
    engine = create_engine('bigquery://', echo=True)
    return engine