def create_playground_schemas():
dialect = helpers.read_config('db_dialect')['dialect']
if dialect != 'snowflake':
pass
else:
connection_params = helpers.read_config('metadata')
engine = create_engine(
'snowflake://{user}:{password}@{account}/{database}?warehouse={warehouse}'
.format(user=connection_params['user'],
password=connection_params['password'],
account=connection_params['account'],
database=connection_params['database'],
warehouse=connection_params['warehouse']))
metadata_schema = connection_params['schema']
rawvault_schema = helpers.read_config('rawvault')['schema']
staging_schema = helpers.read_config('staging')['schema']
for playground_schema in [
metadata_schema, rawvault_schema, staging_schema
]:
try:
engine.execute(schema.CreateSchema(playground_schema))
except exc.ProgrammingError:
pass
engine.dispose()
def init_db(conn: Connection) -> None:
"""
Configures the target schema in which the tweets data will be stored,
creates the schema and the table if not existing yet
:param conn: SQLAlchemy connection object
"""
logger.info(f"{Fore.YELLOW}Initializing database ...{Style.RESET_ALL}")
# Create specified schema if not exists
if not conn.dialect.has_schema(conn, schema_name):
logger.info(
f"{Fore.YELLOW}Schema {schema_name} does not exist, creating it ...{Style.RESET_ALL}"
)
conn.execute(schema.CreateSchema(schema_name))
logger.info(
f"{Fore.GREEN}Schema {schema_name} successfully created !{Style.RESET_ALL}"
)
else:
logger.info(
f"{Fore.GREEN}Schema {schema_name} was found, continuing database initialization "
f"...{Style.RESET_ALL}")
# Create tables
Base.metadata.create_all(bind=conn)
logger.info(
f"{Fore.GREEN}Schema {schema_name} successfully configured !{Style.RESET_ALL}"
)
def dbe():
url = str(conf.DATABASE_URL)
engine = create_engine(url)
assert not database_exists(
url), 'Test database already exists. Aborting tests.'
create_database(url)
engine.execute(schema.CreateSchema(
conf.TOKEN_SCHEMA_NAME)) # create schema
metadata.create_all(engine) # Create tables.
yield engine
drop_database(url)
def schema_exists(self, schema_name, create=False):
"""[check if schema already exist]
Args:
schema_name ([string]): [schema name]
create (bool, optional): [create table if not exist]. Defaults to False.
Returns:
[bool]: [True if schema exists]
"""
ret = self.engine.dialect.has_schema(self.engine, schema_name)
if not ret:
if create is True:
self.engine.execute(schema.CreateSchema(schema_name))
return ret
def pg_schema_generator(cls, engine_copy, schema_name):
try:
if not engine_copy.dialect.has_schema(engine_copy, schema_name):
engine_copy.execute(schema.CreateSchema(schema_name))
cls.connection_manager_logger.info(
'[connection_manager]: Successfully generated schema: {} in respective database of '
'postgresql'.format(schema_name))
return True
cls.connection_manager_logger.info(
'[connection_manager]: Schema: {} already exists in respective database of '
'postgresql'.format(schema_name))
return True
except Exception as e:
cls.connection_manager_logger.exception(
'[connection_manager]: Error generating schema {} in Postgres. Stack trace to follow.'
.format(schema_name))
cls.connection_manager_logger.error(str(e))
def ensure_schema_exists(database: Database) -> None:
"""Ensure database schema configured to be used with database exists and create it if missing."""
if database.is_disabled():
logger.error(
f"Cannot setup schema, because {database} connection is not available"
)
return
with database.transaction_context() as session:
connection = session.connection()
schema_name = database._database_schema
existing_schemas = connection.dialect.get_schema_names(connection)
if schema_name in existing_schemas:
logger.info(f"Found schema: {schema_name} in {database}")
return
logger.info(f"Creating schema: {schema_name} in {database}")
connection.execute(schema.CreateSchema(schema_name))
def save_to_postgres(dataframe, tablename):
logging.info(f"Ukládám [{tablename}].")
engine = create_engine(f"{postgre_cnn}/{database}")
if not engine.dialect.has_schema(engine, dbschema):
engine.execute(schema.CreateSchema(dbschema))
# nastavit malé názvy sloupců, protože postgre je nemá rádo
dataframe.columns = [c.lower() for c in dataframe.columns]
indLabel = dataframe.columns[0]
dataframe.set_index(dataframe.columns[0], inplace=True)
dataframe.to_sql(tablename.lower(),
engine,
schema=dbschema,
if_exists='replace',
chunksize=100, # definuje, kolik kusů naráz se zapisuje
index=True,
index_label=indLabel,
# dtype=types.String,
method='multi') # spojuje inserty do větších kup, takže by to mělo být rychlejší
lambda r: json.loads(r)['psc'])
logging.info('Odstranuji neuzitecne sloupce')
df20.drop(['zadatel_adresa', 'nazeva'], axis='columns', inplace=True)
# prejmenovat vsude kod projektu
df06.rename(columns={'cislo_projektu': 'kod_projektu'}, inplace=True)
df13.rename(columns={'cislo_projektu': 'kod_projektu'}, inplace=True)
df20.rename(columns={'kod': 'kod_projektu'}, inplace=True)
# db import
dbschema = 'eufondy'
database = '/import'
engine = create_engine(postgre_cnn + database)
if not engine.dialect.has_schema(engine, dbschema):
engine.execute(schema.CreateSchema(dbschema))
indLabel = 'kod_projektu'
logging.info('Nahravam do db soubor 06')
df06.set_index(indLabel, inplace=True)
df06.to_sql(
'dotace2006',
engine,
schema=dbschema,
if_exists='replace',
chunksize=100, # definuje, kolik kusů naráz se zapisuje
index=True,
index_label=indLabel,
method='multi')
logging.info('Nahravam do db soubor 13')
api = Api(app)
db = SQLAlchemy(app)
ma = Marshmallow(app)
Swagger(app)
# Modules
from simple_project.models import Something
try:
Something.all()
except Exception as e:
logger.info('Creating DB')
logger.info(e)
# Get schemas
ins = inspect(db.engine)
if 'public' not in ins.get_schema_names():
# Create public schema if none
db.engine.execute(schema.CreateSchema('public'))
# Build tables
db.create_all()
db.session.commit()
# Resources
from simple_project.resources import SomethingResource
api.add_resource(SomethingResource, '/main_resource')
def check_schema_exists():
engine = create_engine(
'postgresql://*****:*****@172.18.0.2:5432/airflow')
if not engine.dialect.has_schema(engine, 'nyc_taxi'):
engine.execute(schema.CreateSchema('nyc_taxi'))
from db import settings, DB
from sqlalchemy import DateTime, Column, String, schema, Float
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Triggers(Base):
__tablename__ = 'Triggers'
__table_args__ = {'schema': settings.env}
id = Column('Id', String, primary_key=True, nullable=False)
trigged_at = Column('trigged_at', DateTime)
symbol = Column('stock_symbol', String)
price = Column('price', Float)
transaction_type = Column('transaction_type', String)
engine = DB.create_engine()
# create schema if doesn't exist
if not engine.dialect.has_schema(engine, settings.env):
engine.execute(schema.CreateSchema(settings.env))
Base.metadata.create_all(bind=engine)
