def create_db(): """ This will create the database, table, and schema. return -- The database engine connection from sqlalchemy """ if database_exists(DB_URI): engine = create_engine(DB_URI) conn = engine.connect() for schema in DB_SCHEMAS: if not conn.dialect.has_schema(conn, schema=schema): engine.execute(CreateSchema(schema)) conn.close() return engine else: create_database(DB_URI) engine = create_engine(DB_URI) conn = engine.connect() for schema in DB_SCHEMAS: if not conn.dialect.has_schema(conn, schema=schema): engine.execute(CreateSchema(schema)) conn.close() return engine
def mssql_setup_and_teardown(request): # create database with catch_warnings(): simplefilter("ignore", category=SAWarning) master_engine = engine_from(request.config) with master_engine.connect() as connection: connection.execution_options(isolation_level="AUTOCOMMIT") connection.execute(f'CREATE DATABASE {TEST_DB_NAME}') # create schema and tables engine = engine_from(request.config, database=TEST_DB_NAME) with engine.connect() as connection: connection.execute(CreateSchema('store')) connection.execute(CreateSchema('report')) Base.metadata.create_all(engine) with database_cwd(): # create index and procedure create_index = open('index.sql', 'r', encoding='utf-8').read() connection.execute(text(create_index)) create_procedure = open('procedure.sql', 'r', encoding='utf-8').read() connection.execute(text(create_procedure)) # add extended properties with ahjo ahjo.update_db_object_properties(engine, ['store', 'report']) engine.dispose() yield # drop database with master_engine.connect() as connection: connection.execution_options(isolation_level="AUTOCOMMIT") result = connection.execute( 'SELECT session_id FROM sys.dm_exec_sessions WHERE database_id = DB_ID(?)', (TEST_DB_NAME, )) for row in result.fetchall(): connection.execute(f'KILL {row.session_id}') connection.execute(f'DROP DATABASE {TEST_DB_NAME}')
def attach_schema(obj, schema): if schema is not None: ddl = CreateSchema(schema, quote=True) event.listen( obj, 'before_create', ddl.execute_if(callable_=should_create_schema, dialect='postgresql')) return obj
def attach_schema(obj, schema): if schema is not None: ddl = CreateSchema(schema, quote=True) event.listen(obj, 'before_create', ddl.execute_if(callable_=should_create_schema, dialect='postgresql')) return obj
def setup_schema(engine, schema): with engine.connect() as conn: has_schema = conn.execute( text( f"SELECT schema_name FROM information_schema.schemata WHERE schema_name = '{schema}';" )) if not has_schema.scalar(): conn.execute(CreateSchema(schema)) conn.execute(DropSchema(schema, None, True)) conn.execute(CreateSchema(schema)) pretty_print(f"Created Schema {schema}", True)
def init_store(self): """ Runs on server with super user privileges """ if self.schema: # User try: self._execute("CREATE USER %s WITH PASSWORD '%s'" % (self.schema, self.password)) except Exception, e: self.session.rollback() if "already exists" in repr(e): logger.info("Could not create user '%s', already exists." % self.schema) logger.info("Update password anyway for user '%s'" % self.schema) self._execute("ALTER USER %s WITH PASSWORD '%s'" % (self.schema, self.password)) else: logger.exception("Could not create user '%s'." % self.schema) # Schema try: self.engine.execute(CreateSchema(self.schema)) logger.info("Schema created") except Exception, e: self.session.rollback() if "already exists" in repr(e): logger.info("Could not create schema '%s', already exists." % self.schema) else: logger.exception("Could not create schema '%s'." % self.schema)
def __init__(self, dbconn, table_def=None, DbSchema_overide=None): assert isinstance(dbconn, DbConn) if table_def is None: self.table = CoinTbl else: self.table = table_def if DbSchema_overide is not None: # pprint.pprint((self.table.__dict__)) self.table.DbSchema = DbSchema_overide self.table.__table_args__['schema'] = self.table.DbSchema self.engine = create_engine(dbconn.get_conn_url()) try: self.engine.execute(CreateSchema(self.table.DbSchema)) logging.debug("Creating Database Schema: {}".format( self.table.DbSchema)) except: # logging.debug("Schema Already Exists No need to create:") pass # create tables assert isinstance(MetaBase, sqlalchemy.ext.declarative.api.DeclarativeMeta) MetaBase.metadata.create_all(bind=self.engine) # create session Session = sessionmaker() Session.configure(bind=self.engine) self.session = Session() # reflecting whole schema self.metadata = MetaData() self.metadata.reflect(bind=self.engine)
def __init__( self, db, store, read=True, write=True, read_through_write=True, delete=True, create_db=False, schema=None, create_schema=True, persistent_connections=True, ): upgrade_db = False super(PostgresRepo, self).__init__(read=read, write=write, read_through_write=read_through_write, delete=delete) if not isinstance(db, str) and schema is not None: raise ValueError('You can only provide a schema with a DB url.') init_db = False if create_db and isinstance(db, str): _create_db_if_needed(db) init_db = True upgrade_db = False self._run = None if isinstance(db, str): if create_db: init_db = True self._db_engine = _db_engine(db, schema, persistent_connections) self._sessionmaker = sqlalchemy.orm.sessionmaker( bind=self._db_engine) else: self._session = db if create_schema and schema is not None: with self.session() as session: q = sa.exists( sa.select([sa.text('schema_name')]).select_from( sa.text('information_schema.schemata')).where( sa.text('schema_name = :schema').bindparams( schema=schema))) if not session.query(q).scalar(): session.execute(CreateSchema(schema)) session.commit() init_db = True upgrade_db = False if init_db: self._db_init() if upgrade_db: self._db_upgrade() self.blobstore = store
def temporary_testing_schema(engine_with_types): schema = TEST_SCHEMA with engine_with_types.begin() as conn: conn.execute(CreateSchema(schema)) yield engine_with_types, schema with engine_with_types.begin() as conn: conn.execute(DropSchema(schema, cascade=True, if_exists=True))
def create_schema_with_engine(engine, schema_name): """ Creates a schema via the ENGINE rather than cursor. This is NOT dialect-dependent, it uses sqlalchemy and an engine First checks if the schema_name is None: if so, it skips everything and returns True Then checks if schema exists already: if it does, it won't try to recreate If it does not already exist, it attempts to create it Returns True if the schema exists at the end Returns False if errors encountered """ result = False try: if (schema_name == None): print("Skipping schema creation, as schema_name set to {}".format( schema_name)) result = True return (result) schema_exists = check_if_schema_exists_with_engine(engine, schema_name) if (schema_exists == True): print( "Schema name {} already exists, do not need to create".format( schema_name)) result = True else: print("Attempting to create schema: {}".format(schema_name)) engine.execute(CreateSchema(schema_name)) schema_exists = check_if_schema_exists_with_engine( engine, schema_name) result = schema_exists return (result) except: traceback.print_exc() return (result)
def postgres_test_db(): """ Fixture to yield a PostgreSQL database to be used in the unit tests. The database will only be created once per test session, but the etl_records table will be cleared for each test. """ postgres_test_db = testing.postgresql.Postgresql() engine = create_engine(postgres_test_db.url()) from sqlalchemy.schema import CreateSchema from sqlalchemy_utils import database_exists, create_database if not database_exists(engine.url): create_database(engine.url) engine.execute(CreateSchema("etl")) Base.metadata.create_all(bind=engine, tables=[ETLRecord.__table__]) engine.execute(""" CREATE TABLE IF NOT EXISTS mds_raw_data_dump ( imei TEXT, msisdn TEXT, event_time TIMESTAMPTZ, cell_id TEXT ); """) yield postgres_test_db postgres_test_db.stop()
def app(base_app): """Flask application fixture. Scope: function This fixture offers a Flask application with already a database connection and all the models created. When finished it will delete all models. .. code-block:: python def create_ninja_turtle() with app.test_client() as client: somedata = 'ninja turtle' res = client.post(url_for('api.create_object'), content_type='application/json', data=json.dumps(somedata)) assert res.status_code == 200 """ from reana_db.database import Session from reana_db.models import Base, Resource engine = create_engine(base_app.config["SQLALCHEMY_DATABASE_URI"]) base_app.session.bind = engine with base_app.app_context(): if not engine.dialect.has_schema(engine, "__reana"): engine.execute(CreateSchema("__reana")) if not database_exists(engine.url): create_database(engine.url) Base.metadata.create_all(bind=engine) Resource.initialise_default_resources() yield base_app Session.close() # close hanging connections Base.metadata.drop_all(bind=engine)
def create(): if not session.query( exists( select([("schema_name") ]).select_from("information_schema.schemata").where( "schema_name = SCHEMA"))).scalar(): engine.execute(CreateSchema(SCHEMA)) if not session.query( exists( select([("schema_name") ]).select_from("information_schema.schemata").where( "schema_name = 'raw'"))).scalar(): engine.execute(CreateSchema('raw')) Base.metadata.create_all(engine)
def main(): from vdv2pg.parser import Parser args = get_argument_parser(__doc__).parse_args() engine, metadata = configure(args) parser = Parser(metadata, args.schema) conn = engine.connect() with conn.begin(): if not args.append: try: conn.execute(CreateSchema(args.schema)) except ProgrammingError as e: logger.warning("Could not create schema '%s': %s", args.schema, e) exit(1) for filename in args.input_file: try: parser.parse(conn, filename) except Exception: logger.exception("Failed to ingest %s", filename) exit(1) if args.post_ingest_script: try: execute_sql_script(conn, args.post_ingest_script, args.schema) except Exception: logger.exception("Failed to execute %s", args.post_ingest_script) exit(1)
async def create_dataset( *, dataset: str = Depends(dataset_dependency), request: DatasetCreateIn, is_authorized: bool = Depends(is_admin), response: Response, ) -> DatasetResponse: """Create or update a dataset.""" input_data: Dict = request.dict(exclude_none=True, by_alias=True) try: new_dataset: ORMDataset = await datasets.create_dataset( dataset, **input_data) except RecordAlreadyExistsError as e: raise HTTPException(status_code=400, detail=str(e)) await db.status(CreateSchema(dataset)) await db.status(f"GRANT USAGE ON SCHEMA {dataset} TO {READER_USERNAME};") await db.status( f"ALTER DEFAULT PRIVILEGES IN SCHEMA {dataset} GRANT SELECT ON TABLES TO {READER_USERNAME};" ) response.headers["Location"] = f"/{dataset}" return await _dataset_response(dataset, new_dataset)
def init_database(engine): """Initializes a the database.""" if not database_exists(str(config.SQLALCHEMY_DATABASE_URI)): create_database(str(config.SQLALCHEMY_DATABASE_URI)) schema_name = "dispatch_core" if not engine.dialect.has_schema(engine, schema_name): with engine.connect() as connection: connection.execute(CreateSchema(schema_name)) tables = get_core_tables() Base.metadata.create_all(engine, tables=tables) version_schema(script_location=config.ALEMBIC_CORE_REVISION_PATH) setup_fulltext_search(engine, tables) # setup an required database functions session = sessionmaker(bind=engine) db_session = session() # default organization organization = Organization( name="default", slug="default", default=True, description="Default dispatch organization.", ) db_session.add(organization) db_session.commit() init_schema(engine=engine, organization=organization)
def create_schema(schema, engine): """ This method creates a Postgres schema. """ if schema not in get_all_schemas(engine): with engine.begin() as connection: connection.execute(CreateSchema(schema))
def run(self): engine = create_engine( 'postgresql://%s:%s@%s/%s' % (self.user, self.password, self.host, self.database)) try: engine.execute(CreateSchema(parameters().exp_name)) except: # should catch psycopg2.ProgrammingError, but doesnt work pass pandas_files = [ pd.read_table(self.input()[name].path, skiprows=2, index_col=0, names=[ 'Gene', 'Chr', 'Start', 'End', 'Strand', 'Length', name ], usecols=['Gene', name], header=None) for name in self.input() ] count_table = pd.concat(pandas_files, axis=1).sort_index(axis=1) count_table.to_csv("%s/%s.csv" % (parameters().exp_dir, self.table)) count_table.to_sql(self.table, con=engine, schema=parameters().exp_name) # Taken from luigi source code, makes marker table and adds entry self.output().create_marker_table() connection = self.output().connect() self.output().touch(connection) connection.commit() connection.close()
def __init__(self, import_base, revision_date, has_sql_context=False, force_fetch=True): self.amplicon_code_names = {} # mapping from dirname to amplicon ontology self._engine = make_engine() self._create_extensions() self._session = sessionmaker(bind=self._engine)() self._import_base = import_base self._methodology = 'v1' self._analysis_url = '' self._revision_date = revision_date self._has_sql_context = has_sql_context self._force_fetch = force_fetch # these are used exclusively for reporting back to CSIRO on the state of the ingest self.sample_metadata_incomplete = set() self.sample_non_integer = set() self.sample_not_in_metadata = set() self.otu_invalid = set() try: self._session.execute(DropSchema(SCHEMA, cascade=True)) except sqlalchemy.exc.ProgrammingError: self._session.invalidate() self._session.execute(CreateSchema(SCHEMA)) self._session.commit() Base.metadata.create_all(self._engine) self.ontology_init()
def test_handle_events_no_triggers(self, app, db): with create_pgevents(app) as pg: class Widget(db.Model): __tablename__ = "widget" id = db.Column(db.Integer, primary_key=True) db.session.execute(CreateSchema("private")) db.session.commit() class Gadget(db.Model): __tablename__ = "gadget" __table_args__ = {"schema": "private"} id = db.Column(db.Integer, primary_key=True) create_all(db) widget_callback_called = 0 def widget_callback(event_id, row_id, identifier): nonlocal widget_callback_called widget_callback_called += 1 db.session.add(Gadget()) db.session.commit() pg.handle_events() assert widget_callback_called == 0
def create_schema(engine, schema): sql = select([('schema_name')]).\ select_from('information_schema.schemata').\ where("schema_name = '%s'" % schema) q = engine.execute(sql) if not q.fetchone(): engine.execute(CreateSchema(schema))
def create_schema(schema): """ This method creates a Postgres schema corresponding to the application. """ if not schema_exists(schema): with engine.begin() as connection: connection.execute(CreateSchema(schema))
def test_listen_mixed(self, app, db): class Widget(db.Model): __tablename__ = "widget" id = db.Column(db.Integer, primary_key=True) db.session.execute(CreateSchema("private")) db.session.commit() class Gadget(db.Model): __tablename__ = "gadget" __table_args__ = {"schema": "private"} id = db.Column(db.Integer, primary_key=True) create_all(db) def widget_callback(row_id, identifier): pass def gadget_callback(row_id, identifier): pass with create_pgevents() as pg: pg.listen(Widget, {"insert"}, widget_callback) assert "public.widget" in pg._triggers assert len(pg._triggers["public.widget"]) == 1 widget_trigger = pg._triggers["public.widget"][0] assert not widget_trigger.installed assert widget_trigger.target == Widget assert widget_trigger.events == {"insert"} assert widget_trigger.callback == widget_callback with create_connection(db, raw=True) as conn: trigger_installed_ = trigger_installed(conn, "widget") assert not trigger_installed_ pg.init_app(app) assert widget_trigger.installed with create_connection(db, raw=True) as conn: trigger_installed_ = trigger_installed(conn, "widget") assert trigger_installed_ pg.listen(Gadget, {"delete"}, gadget_callback) assert "private.gadget" in pg._triggers assert len(pg._triggers["private.gadget"]) == 1 trigger = pg._triggers["private.gadget"][0] assert trigger.installed assert trigger.target == Gadget assert trigger.events == {"delete"} assert trigger.callback == gadget_callback with create_connection(db, raw=True) as conn: trigger_installed_ = trigger_installed(conn, "gadget", schema="private") assert trigger_installed_
def test_listens_for_mixed(self, app, db): with create_pgevents() as pg: class Widget(db.Model): __tablename__ = 'widget' id = db.Column(db.Integer, primary_key=True) db.session.execute(CreateSchema('private')) db.session.commit() class Gadget(db.Model): __tablename__ = 'gadget' __table_args__ = {'schema': 'private'} id = db.Column(db.Integer, primary_key=True) create_all(db) @pg.listens_for(Widget, {'insert'}) def widget_callback(row_id, identifier): pass assert ('public.widget' in pg._triggers) assert (len(pg._triggers['public.widget']) == 1) trigger = pg._triggers['public.widget'][0] assert (trigger.installed == False) assert (trigger.target == Widget) assert (trigger.events == {'insert'}) assert (trigger.callback == widget_callback) with create_connection(db, raw=True) as conn: trigger_installed_ = trigger_installed(conn, 'widget') assert (trigger_installed_ == False) pg.init_app(app) assert (trigger.installed == True) with create_connection(db, raw=True) as conn: trigger_installed_ = trigger_installed(conn, 'widget') assert (trigger_installed_ == True) @pg.listens_for(Gadget, {'delete'}) def gadget_callback(row_id, identifier): pass assert ('private.gadget' in pg._triggers) assert (len(pg._triggers['private.gadget']) == 1) trigger = pg._triggers['private.gadget'][0] assert (trigger.installed == True) assert (trigger.target == Gadget) assert (trigger.events == {'delete'}) assert (trigger.callback == gadget_callback) with create_connection(db, raw=True) as conn: trigger_installed_ = trigger_installed(conn, 'gadget', schema='private') assert (trigger_installed_ == True)
def _setup_schemas(self): """ Create new schema, identify previous and drop older ones. It also verifies if all the data from the previous schema has been processed. """ # Schema name for current file self.last_modification_date = datetime.utcfromtimestamp( os.stat(self.input_refids_filename).st_mtime) self.schema_name = self.schema_prefix + self.last_modification_date.strftime( "%Y%m%d_%H%M%S") # Create schema if needed existing_schema_names = Inspector.from_engine( self.engine).get_schema_names() existing_schema_names = filter( lambda x: x.startswith(self.schema_prefix), existing_schema_names) if self.schema_name not in existing_schema_names: self.connection.execute(CreateSchema(self.schema_name)) filtered_existing_schema_names = existing_schema_names else: filtered_existing_schema_names = filter( lambda x: x != self.schema_name, existing_schema_names) # Determine previous schema name if any if len(filtered_existing_schema_names) > 0: filtered_existing_schema_names.sort(reverse=True) self.previous_schema_name = filtered_existing_schema_names[0] # Verify the data that is going to be imported is newer than the data already imported schema_date_fingerprint = int( self.schema_name.replace(self.schema_prefix, "").replace("_", "")) previous_schema_date_fingerprint = int( self.previous_schema_name.replace(self.schema_prefix, "").replace("_", "")) if previous_schema_date_fingerprint >= schema_date_fingerprint: raise Exception( "The data to be imported has a date fingerprint '{0}' equal or older than the data already in the DB '{1}'" .format(self.schema_name, self.previous_schema_name)) # Verify if all the data from the previous schema has been processed. self._reconstruct_previous_expanded_raw_data() missing = self._find_not_processed_records_from_previous_run() if missing: missing_str = ",\n".join([ "citing: '{}', content: '{}'".format(m[0], m[1]) for m in missing ]) #self.logger.error("Some previous records were not processed ({} in total) and will be re-processed: {}".format(len(missing), missing_str)) self.logger.error( "Some previous records were not processed ({} in total) and will be re-processed" .format(len(missing))) # Drop old schemas (just keep last 3) if len(filtered_existing_schema_names) > 2: for old_schema_name in filtered_existing_schema_names[2:]: drop_schema = "drop schema {0} cascade;" self._execute_sql(drop_schema, old_schema_name)
def createSchema(name, bind=None): try: engine = sqla.get_engine(app, bind) engine.execute(CreateSchema(name)) except sqlalchemy.exc.ProgrammingError as e: # schema probably already exists... do nothing # but just in case it's another error, print it print("Error creating schema {}, ignoring: {}".format(name, e))
def create_schema_if_not_exists(self): """ Creates the schema defined in given instance of PandasSpecialEngine if it does not exist. """ if (self.schema is not None and not self.engine.dialect.has_schema(self.engine, self.schema)): self.engine.execute(CreateSchema(self.schema))
def CreateSchemaByName(engine,schema_name,call_back=None): """创建schema,schema_name:架构名,call_back:回调函数""" obj = CreateSchema(schema_name) # print(link) # engine = create_engine(link,encoding = 'utf-8') engine.execute(obj) if call_back: call_back()
def create_schemas(engine, schemas): # Create schemas for schema in schemas: try: engine.execute(CreateSchema(schema)) except ProgrammingError as error: if not 'schema "%s" already exists' % schema in str(error): raise
def __init__(self, db, table_def): # type: (dbutils.conn, str, str) -> object """ :rtype: """ self.host = db.host self.dbschema = 'logging' self.database = db.dbname self.engine = None # instance #assert isinstance(db, db_utils.DB) key = str(table_def.DbSchema + table_def.__tablename__) self.table = self.table_dict.get(key, None) if self.table is None: self.table_dict[key] = table_def self.table = self.table_dict[key] # call class method to make sure url attribute is set if self.engine_dict.get('only1', None) is None: #db.connect_SqlAlchemy() sql_alchemy_uri_connected = db.sql_alchemy_uri.format( userid=db.userid, pwd=db.pwd, host=db.host, port=db.port, db=db.dbname) self.engine_dict['only1'] = sqlalchemy.create_engine( sql_alchemy_uri_connected) self.engine = self.engine_dict['only1'] try: self.engine.execute(CreateSchema(self.table.DbSchema)) logging.debug("Creating Database Schema: {}".format( self.table.DbSchema)) except: # logging.debug("Schema Already Exists No need to create:") pass # create tables "" MetaBase.metadata.create_all(bind=self.engine) else: self.engine = self.engine_dict['only1'] # create session Session = sqlalchemy.orm.sessionmaker() Session.configure(bind=self.engine) self.session = Session() # reflecting whole schema self.metadata = MetaData() self.metadata.reflect(bind=self.engine)
def init_db(): import dqm.models # create database schema # see http://stackoverflow.com/questions/13677781/getting-sqlalchemy-to-issue-create-schema-on-create-all event.listen(Base.metadata, 'before_create', CreateSchema('dqm')) # create database tables db.create_all()