Python sqlalchemy.engine.create_engine() Examples

The following are 30 code examples of sqlalchemy.engine.create_engine(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy.engine , or try the search function .
Example #1
Source File: storage.py    From optuna with MIT License 6 votes vote down vote up
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) 
Example #2
Source File: db.py    From huskar with MIT License 6 votes vote down vote up
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) 
Example #3
Source File: expose_existing.py    From safrs with GNU General Public License v3.0 6 votes vote down vote up
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 
Example #4
Source File: storage.py    From optuna with MIT License 6 votes vote down vote up
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() 
Example #5
Source File: test_sqlalchemy.py    From impyla with Apache License 2.0 6 votes vote down vote up
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 
Example #6
Source File: test_sqlalchemy.py    From elasticsearch-dbapi with Apache License 2.0 6 votes vote down vote up
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,
        ) 
Example #7
Source File: test_jsonify.py    From pecan with BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
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() 
Example #8
Source File: example.py    From py-data-api with MIT License 5 votes vote down vote up
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()) 
Example #9
Source File: test_jsonify.py    From pecan with BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
def setUp(self):
        super(TestJsonifySQLAlchemyGenericEncoder, self).setUp()
        if not create_engine:
            self.create_fake_proxies()
        else:
            self.create_sa_proxies() 
Example #10
Source File: test_dialect.py    From gsheets-db-api with MIT License 5 votes vote down vote up
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) 
Example #11
Source File: conftest.py    From sqlalchemy-utc with MIT License 5 votes vote down vote up
def fx_engine(request):
    url = request.param
    engine = create_engine(url, poolclass=NullPool)
    request.addfinalizer(engine.dispose)
    return engine 
Example #12
Source File: __init__.py    From squealy with MIT License 5 votes vote down vote up
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)) 
Example #13
Source File: base.py    From geomancer with MIT License 5 votes vote down vote up
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) 
Example #14
Source File: db.py    From PeekabooAV with GNU General Public License v3.0 5 votes vote down vote up
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.') 
Example #15
Source File: test_querying.py    From asyncpgsa with Apache License 2.0 5 votes vote down vote up
def metadata():
    metadata = MetaData()
    metadata.bind = create_engine(URL)
    return metadata 
Example #16
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up
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),
            )
        ) 
Example #17
Source File: presto.py    From missioncontrol with Mozilla Public License 2.0 5 votes vote down vote up
def get_engine():
    return create_engine(settings.PRESTO_URL) 
Example #18
Source File: conftest.py    From dodotable with MIT License 5 votes vote down vote up
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() 
Example #19
Source File: persist.py    From lang2program with Apache License 2.0 5 votes vote down vote up
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) 
Example #20
Source File: persist.py    From lang2program with Apache License 2.0 5 votes vote down vote up
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) 
Example #21
Source File: conftest.py    From huskar with MIT License 5 votes vote down vote up
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) 
Example #22
Source File: dbutils.py    From mikado with GNU Lesser General Public License v3.0 5 votes vote down vote up
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 
Example #23
Source File: base.py    From gamification-engine with MIT License 5 votes vote down vote up
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") 
Example #24
Source File: sql.py    From kotori with GNU Affero General Public License v3.0 5 votes vote down vote up
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 
Example #25
Source File: test_sqlalchemy.py    From elasticsearch-dbapi with Apache License 2.0 5 votes vote down vote up
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) 
Example #26
Source File: test_sqlalchemy.py    From elasticsearch-dbapi with Apache License 2.0 5 votes vote down vote up
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")
        ) 
Example #27
Source File: test_sqlalchemy.py    From elasticsearch-dbapi with Apache License 2.0 5 votes vote down vote up
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")
        ) 
Example #28
Source File: test_sqlalchemy.py    From elasticsearch-dbapi with Apache License 2.0 5 votes vote down vote up
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
        ) 
Example #29
Source File: test_sqlalchemy.py    From elasticsearch-dbapi with Apache License 2.0 5 votes vote down vote up
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
        ) 
Example #30
Source File: test_sqlalchemy_bigquery.py    From pybigquery with MIT License 5 votes vote down vote up
def engine():
    engine = create_engine('bigquery://', echo=True)
    return engine