def init_db(env): if env == 'docker': database.initialize( PostgresqlExtDatabase('rowboat', host='db', user='******', register_hstore=False, port=int(os.getenv('PG_PORT', 5432)), autorollback=True)) else: database.initialize( PostgresqlExtDatabase('rowboat', user='******', port=int(os.getenv('PG_PORT', 5432)), register_hstore=False, autorollback=True)) database.execute_sql('CREATE EXTENSION IF NOT EXISTS pg_trgm;') database.execute_sql('CREATE EXTENSION IF NOT EXISTS hstore;') for model in REGISTERED_MODELS: model.create_table(True) if hasattr(model, 'SQL'): database.execute_sql(model.SQL)
def __init__(self, manager: BlueprintManager, config): super().__init__(manager, config) self.config = config self.db = PostgresqlExtDatabase(**config['db']) self._initialize() self._migrations() self.receipthandle_by_instructionstateid = dict()
class Database(object): def __init__(self): self.database_config = dict(settings.DATABASE_CONFIG) self.database_config['register_hstore'] = False self.database_name = self.database_config.pop('name') self.database = PostgresqlExtDatabase(self.database_name, **self.database_config) self.app = None self.pid = os.getpid() def init_app(self, app): self.app = app self.register_handlers() def connect_db(self): self._check_pid() self.database.connect() def close_db(self, exc): self._check_pid() if not self.database.is_closed(): self.database.close() def _check_pid(self): current_pid = os.getpid() if self.pid != current_pid: logging.info("New pid detected (%d!=%d); resetting database lock.", self.pid, current_pid) self.pid = os.getpid() self.database._conn_lock = threading.Lock() def register_handlers(self): self.app.before_request(self.connect_db) self.app.teardown_request(self.close_db)
class Database(object): def __init__(self): self.database_config = dict(settings.DATABASE_CONFIG) self.database_config['register_hstore'] = False self.database_name = self.database_config.pop('name') self.database = PostgresqlExtDatabase(self.database_name, **self.database_config) self.app = None self.pid = os.getpid() def init_app(self, app): self.app = app self.register_handlers() def connect_db(self): self._check_pid() self.database.connect() def close_db(self, exc): self._check_pid() if not self.database.is_closed(): self.database.close() def _check_pid(self): current_pid = os.getpid() if self.pid != current_pid: logging.info("New pid detected (%d!=%d); resetting database lock.", self.pid, current_pid) self.pid = os.getpid() self.database._conn_lock = threading.Lock() def register_handlers(self): self.app.before_request(self.connect_db) self.app.teardown_request(self.close_db)
def open_connection(self) -> None: if not jh.is_jesse_project() or jh.is_unit_testing(): return # if it's not None, then we already have a connection if self.db is not None: return options = { "keepalives": 1, "keepalives_idle": 60, "keepalives_interval": 10, "keepalives_count": 5 } self.db = PostgresqlExtDatabase( ENV_VALUES['POSTGRES_NAME'], user=ENV_VALUES['POSTGRES_USERNAME'], password=ENV_VALUES['POSTGRES_PASSWORD'], host=ENV_VALUES['POSTGRES_HOST'], port=int(ENV_VALUES['POSTGRES_PORT']), sslmode=ENV_VALUES.get('POSTGRES_SSLMODE', 'disable'), **options) # connect to the database self.db.connect()
def test_get_columns_using_enumerator(self, database: PostgresqlExtDatabase, database_table: Tuple[str, str]): enum_name = TEST_ENUM_NAME enumerators.create_enumerator(enum_name) columns = enumerators.get_columns_used_by_enumerator(enum_name) assert len(columns) == 0 schema_name = database_table[0] table_name = database_table[1] column_name = "test_column" sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}" database.execute_sql(sql_add_column) columns = enumerators.get_columns_used_by_enumerator(enum_name) assert len(columns) == 1 expected_data = { "schema_name": schema_name, "table_name": table_name, "column_name": column_name } assert columns[0] == expected_data assert enumerators.is_enumerator_used_by_any_column(enum_name) is True
class PersistentEventBus(EventBus): def __init__(self, config): super().__init__(config) self.db = PostgresqlExtDatabase(**config['db']) database_proxy.initialize(self.db) self._migrations() def _migrations(self): self.db.create_tables([EventModel], safe=True) def publish(self, event: Event): exec_id = event.metadata['blueprint_execution_id'] existing_event = self.get_event(event.topic, exec_id) if not existing_event: newevent = EventModel(topic=event.topic, body=event.body, metadata=event.metadata) newevent.save() else: existing_event.body = event.body existing_event.metadata = event.metadata existing_event.save() def get_event(self, topic: str, blueprint_execution_id: str): try: eventmodel = EventModel.get((EventModel.topic == topic) & (EventModel.metadata['blueprint_execution_id'] == blueprint_execution_id)) return eventmodel except DoesNotExist as e: return
def __init__(self): self.database_config = dict(settings.DATABASE_CONFIG) self.database_config['register_hstore'] = False self.database_name = self.database_config.pop('name') self.database = PostgresqlExtDatabase(self.database_name, **self.database_config) self.app = None self.pid = os.getpid()
def test_connect_postgres(): logger.debug("health: testing postgres connection to %s", settings.POSTGRES_HOST) client = PostgresqlExtDatabase( settings.POSTGRES_DB, user=settings.POSTGRES_USER, password=settings.POSTGRES_PASSWORD, host=settings.POSTGRES_HOST, port=5432, ) client.connect() return True, "Postgres db connection success !"
def createDbObject(self): if conf.sqldb == 'heroku': DATABASE_URL = os.environ.get('DATABASE_URL') db = urlparse(DATABASE_URL) user = db.username password = db.password path = db.path[1:] host = db.hostname port = db.port return PostgresqlExtDatabase(path, user=user, password=password, host=host, port=port) return PostgresqlExtDatabase(conf.sqldb, user='******', password='******')
def __init__(self): self.database_config = dict(settings.DATABASE_CONFIG) self.database_config['register_hstore'] = False self.database_name = self.database_config.pop('name') self.database = PostgresqlExtDatabase(self.database_name, **self.database_config) self.app = None self.pid = os.getpid()
def test_drop_enumerator_which_is_used_by_columns( self, database: PostgresqlExtDatabase, database_table: Tuple[str, str]): enum_name = TEST_ENUM_NAME enumerators.create_enumerator(enum_name) schema_name = database_table[0] table_name = database_table[1] column_name = "test_column" sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}" database.execute_sql(sql_add_column) with pytest.raises(peewee.InternalError): enumerators.drop_enumerator(enum_name)
def get_cfg(): ''' Get the configure value. ''' cfg_var = dir(cfg) if 'DB_CFG' in cfg_var: db_cfg = cfg.DB_CFG else: db_cfg = ConfigDefault.DB_CFG if 'user' not in db_cfg: db_cfg['user'] = db_cfg['db'] if 'SMTP_CFG' in cfg_var: smtp_cfg = cfg.SMTP_CFG else: smtp_cfg = ConfigDefault.SMTP_CFG if 'SITE_CFG' in cfg_var: site_cfg = cfg.SITE_CFG else: site_cfg = ConfigDefault.SITE_CFG if 'ROLE_CFG' in cfg_var: role_cfg = cfg.ROLE_CFG else: role_cfg = ConfigDefault.ROLE_CFG role_cfg['view'] = role_cfg.get('view', '') role_cfg['add'] = role_cfg.get('add', '1000') role_cfg['edit'] = role_cfg.get('edit', '2000') role_cfg['delete'] = role_cfg.get('delete', '3000') role_cfg['admin'] = role_cfg.get('admin', '0300') ################################################################### site_url = site_cfg['site_url'].strip('/') site_cfg['site_url'] = site_url infor = site_url.split(':') if len(infor) == 1: site_cfg['PORT'] = 8888 else: site_cfg['PORT'] = infor[-1] if 'DEBUG' in site_cfg: pass else: site_cfg['DEBUG'] = False db_con = PostgresqlExtDatabase( db_cfg['db'], user=db_cfg['user'], password=db_cfg['pass'], host='127.0.0.1', autocommit=True, autorollback=True) return (db_con, smtp_cfg, site_cfg, role_cfg)
def get_db(self): self.db = PostgresqlExtDatabase(self.db_name, user=self.user, password=self.password, host=self.host, register_hstore=False) return self.db
def return_db(): db_config = parse_db_url(os.environ['DATABASE_URL']) if os.getenv('MIGRATION','0') =='1': from playhouse.postgres_ext import PostgresqlExtDatabase return PostgresqlExtDatabase( db_config['database'], user=db_config.get('user', None), password=db_config.get('password', None), host=db_config.get('host', 'localhost'), port=db_config.get('port', '5432') ) else: from playhouse.pool import PooledPostgresqlExtDatabase return PooledPostgresqlExtDatabase( db_config['database'], max_connections=os.getenv('DB_POOL', 5), stale_timeout=os.getenv('DB_TIMEOUT', 300), # 5 minutes. user=db_config.get('user', None), password=db_config.get('password', None), host=db_config.get('host', 'localhost'), port=db_config.get('port', '5432'))
def test_is_enumerator_used_by_any_column(self, database: PostgresqlExtDatabase, database_table: Tuple[str, str]): enum_name = TEST_ENUM_NAME enumerators.create_enumerator(enum_name) assert enumerators.is_enumerator_used_by_any_column(enum_name) is False schema_name = database_table[0] table_name = database_table[1] column_name = "test_column" sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}" database.execute_sql(sql_add_column) assert enumerators.is_enumerator_used_by_any_column(enum_name) is True
class LagouPipeline(object): def __init__(self): self.db = db def process_item(self, item, spider): print(item) sql = "INSERT INTO position(position_name,city,business_zones," \ "company_full_name,company_short_name,company_lable_list," \ "company_size,education,finance_stage,first_type,industry_field," \ "job_nature,position_lables,salary,salary_max,salary_min," \ "salary_avg,second_type,work_year) VALUES (" \ "'{item[position_name]}'," \ "'{item[city]}'," \ "ARRAY{item[business_zones]}," \ "'{item[company_full_name]}'," \ "'{item[company_short_name]}'," \ "ARRAY{item[company_lable_list]}," \ "'{item[company_size]}','{item[education]}'," \ "'{item[finance_stage]}'," \ "'{item[first_type]}'," \ "'{item[industry_field]}'," \ "'{item[job_nature]}'," \ "ARRAY{item[position_lables]}," \ "'{item[salary]}'," \ "{item[salary_max]}," \ "{item[salary_min]}," \ "{item[salary_avg]}," \ "'{item[second_type]}','{item[work_year]}')".format(item=item) print(sql) try: self.db.execute_sql(sql) self.db.commit() except peewee.IntegrityError: self.db.rollback() except (peewee.OperationalError, peewee.InterfaceError): self.db.close() self.db = PostgresqlExtDatabase(database=DB_DATABASE, host=DB_HOST, user=DB_USER, password=DB_PASSWORD, autocommit=True, autorollback=True) self.db.execute_sql(sql) self.db.commit() return item
def get_db(): db = None if env == 'test': db = PostgresqlExtDatabase(TEST_DB_NAME, user=DB_UN, password=DB_PW, host=DB_HOST, port=DB_PORT) else: db = PostgresqlExtDatabase(DB_NAME, user=DB_UN, password=DB_PW, host=DB_HOST, port=DB_PORT) return db
def get_enum_values(database: PostgresqlExtDatabase, name: str): sql = f"""SELECT t2.enumlabel FROM pg_type t1 JOIN pg_enum t2 ON t1.oid = t2.enumtypid WHERE t1.typname='{name}'""" cursor = database.execute_sql(sql) result = tuple(row[0] for row in cursor.fetchall()) return result
def set_db(cls, cfg_path): with open(cfg_path, 'r') as infile: config = json.load(infile) Database.__DB__ = PostgresqlExtDatabase(database=config['db_name'], user=config['user'], password=config['password'], host=config['host'], port=config['port'], register_hstore=False)
def generate_db_object(dbname): db_obj = PostgresqlExtDatabase( dbname, host=app_config.CONFIG.POSTGRES_HOST, port=app_config.CONFIG.POSTGRES_PORT, user=app_config.CONFIG.POSTGRES_USER, password=app_config.CONFIG.POSTGRES_PASSWORD, thread_safe=True ) return db_obj
def init_db(env): if env == 'docker': database.initialize( PostgresqlExtDatabase('referee', host='db', user='******', port=int(os.getenv('PG_PORT', 5432)), autorollback=True)) else: database.initialize( PostgresqlExtDatabase('referee', user='******', port=int(os.getenv('PG_PORT', 5432)), autorollback=True)) for model in REGISTERED_MODELS: model.create_table(True) if hasattr(model, 'SQL'): database.execute_sql(model.SQL)
def createTable(Model, db_name, user=None, password=None, host='127.0.0.1'): ''' create by bigzhu at 15/04/04 01:08:30 建立数据库表; peewee 要在定义时候指定 db 相当不人性化,修正 modify by bigzhu at 15/04/04 01:32:46 没有这个数据库的时候,直接返回建立数据的语句 modify by bigzhu at 15/04/04 01:45:43 如果表已经存在,不能往下继续了 ''' if user is None: user = db_name if password is None: password = db_name if host is None: host = '127.0.0.1' #db = PostgresqlExtDatabase(db_name, user=user, password=password, host='127.0.0.1', register_hstore=False) db = PostgresqlExtDatabase(db_name, user=user, password=password, host=host, register_hstore=False) Model._meta.database = db try: if Model.table_exists(): print 'table %s already exists' % Model.__name__ return createBaseTable(db) Model.create_table() print 'create table ' + Model.__name__ except peewee.OperationalError: print public_bz.getExpInfo() showDBCreate(db_name) exit(1) table_name = Model.__name__ if table_name != 'base': sql = ''' alter table %s inherit base; ''' % table_name db.execute_sql(sql) resetBaseDefault(db) # add table comment comment = Model.__doc__ sql = ''' COMMENT ON TABLE %s IS '%s'; ''' % (table_name, comment) db.execute_sql(sql)
def __getattr__(self, attr): if not self.obj: db = PostgresqlExtDatabase( self.prefix + config.DB_NAME, user=config.get('DB_USER'), password=config.get('DB_PASSWORD'), host=config.get('DB_HOST'), port=config.get('DB_PORT'), autorollback=True, ) self.initialize(db) return getattr(self.obj, attr)
def test_create_and_drop_enumerator(self, database: PostgresqlExtDatabase): enum_name = TEST_ENUM_NAME enumerators.create_enumerator(enum_name) enumerators.drop_enumerator(enum_name) sql_select = self.build_select_enum_sql(enum_name) cursor = database.execute_sql(sql_select) rows = cursor.fetchall() assert len(rows) == 0
def init(): print('INITIALIZING DATABASE AND LOADING MODELS...') database.initialize( PostgresqlExtDatabase('seriousbot_dev', user='******', port=5432, autorollback=True)) for model in MODELS: print(f"Looping {model}") model.create_table(True) if hasattr(model, 'SQL'): database.execute_sql(model.SQL)
def test_create_enumerator_duplicate(self, database: PostgresqlExtDatabase): enum_name = TEST_ENUM_NAME enumerators.create_enumerator(enum_name) with pytest.raises(peewee.ProgrammingError): enumerators.create_enumerator(enum_name) sql_select = self.build_select_enum_sql(enum_name) cursor = database.execute_sql(sql_select) rows = cursor.fetchall() assert len(rows) == 1
def init_db(env): if env == 'docker': database.initialize(PostgresqlExtDatabase( 'rowboat', host=db_host, password=db_password, user='******', port=int(5432), autorollback=True)) else: database.initialize(PostgresqlExtDatabase( 'rowboat', host=db_host, password=db_password, user='******', port=int(5432), autorollback=True)) for model in REGISTERED_MODELS: model.create_table(True) if hasattr(model, 'SQL'): database.execute_sql(model.SQL)
def connect(): """ Function that connects to the database given the credentials in DB/db.json Credentials are not kept in memory this way :return: """ try: DBManager.__DB = PostgresqlExtDatabase(database=os.environ['DB'], user=os.environ['USERNAME'], password=os.environ['PASSWORD'], host=os.environ['HOST'], port=os.environ['PORT']) print('Connected.') return True, None except: return False, Errors.DB_CONN_FAIL.value
def init_db(): database.initialize( PostgresqlExtDatabase(databaseconfig['database'], host=databaseconfig['host'], user=databaseconfig['username'], password=databaseconfig['password'], port=int(databaseconfig['port']), autorollback=True)) for model in REGISTERED_MODELS: model.create_table(True) if hasattr(model, 'SQL'): database.execute_sql(model.SQL)
def __init__(self): """ Virtually private constructor. """ if Database.__instance != None: raise Exception("This class is a Database!") else: try: Database.__instance = PostgresqlExtDatabase( os.getenv('DATABASE_DB'), user=os.getenv('DATABASE_USER'), password=os.getenv('DATABASE_PASS'), host=os.getenv('DATABASE_URL'), port=5432 ) except Exception as e: print(e)
def get_cfg(): ''' Get the configure value. :return: ''' import cfg cfg_var = dir(cfg) if 'DB_CFG' in cfg_var: db_cfg = cfg.DB_CFG else: db_cfg = ConfigDefault.DB_CFG if 'user' not in db_cfg: db_cfg['user'] = db_cfg['db'] if 'SMTP_CFG' in cfg_var: smtp_cfg = cfg.SMTP_CFG else: smtp_cfg = ConfigDefault.SMTP_CFG if 'SITE_CFG' in cfg_var: site_cfg = cfg.SITE_CFG else: site_cfg = ConfigDefault.SITE_CFG site_url = site_cfg['site_url'].strip('/') site_cfg['site_url'] = site_url infor = site_url.split(':') if len(infor) == 1: site_cfg['PORT'] = 8888 else: site_cfg['PORT'] = infor[-1] if 'DEBUG' in site_cfg: pass else: site_cfg['DEBUG'] = False DB_CON = PostgresqlExtDatabase(db_cfg['db'], user=db_cfg['user'], password=db_cfg['pass'], host='127.0.0.1', autocommit=True, autorollback=True) return (DB_CON, smtp_cfg, site_cfg)
def test_create_enumerator_string_values(self, database: PostgresqlExtDatabase): enum_name = TEST_ENUM_NAME enum_values = ("v1", "v2", "v3") enumerators.create_enumerator(enum_name, enum_values) sql_select = self.build_select_enum_sql(enum_name) cursor = database.execute_sql(sql_select) rows = cursor.fetchall() assert len(rows) == 1 actual_values = self.get_enum_values(database, enum_name) assert actual_values == enum_values
DATABASE_NAME = 'analytics' DOMAIN = 'http://analytics.yourdomain.com' # TODO: change me. JAVASCRIPT = """(function(id){ var d=document,i=new Image,e=encodeURIComponent; i.src='%s/a.gif?id='+id+'&url='+e(d.location.href)+'&ref='+e(d.referrer)+'&t='+e(d.title); })(%s)""".replace('\n', '') # Flask settings. DEBUG = bool(os.environ.get('DEBUG')) SECRET_KEY = 'secret - change me' # TODO: change me. app = Flask(__name__) app.config.from_object(__name__) database = PostgresqlExtDatabase( DATABASE_NAME, register_hstore=True, user='******') class BaseModel(Model): class Meta: database = database class Account(BaseModel): domain = CharField() def verify_url(self, url): netloc = urlparse(url).netloc url_domain = '.'.join(netloc.split('.')[-2:]) # Ignore subdomains. return self.domain == url_domain class PageView(BaseModel):
__author__ = 'Ken' import unittest import requests from playhouse.test_utils import test_database from playhouse.postgres_ext import PostgresqlExtDatabase from models import Student TEST_DB = PostgresqlExtDatabase(database='test', user='******') TEST_DB.connect() TEST_DB.drop_tables([Student]) TEST_DB.create_tables([Student], safe=True) STUDENT_DATA = { 'th_username': '******', 'email': '*****@*****.**', 'first_name': 'Ken', 'last_name': 'Alger', 'password': '******', 'github_username': '******', 'city': 'Keizer', 'state': 'OR', 'country': 'USA' } STUDENT_LIST = ['kenalger', 'craigsdennis', 'kennethlove'] class StudentModelTestCase(unittest.TestCase):
)) UNACCEPTABLE_HTML_TAGS = frozenset(( "script", "noscript", "style" )) ACCEPTABLE_DATA_TYPES = frozenset(( "asp", "aspx", "html", "htm", "php", "" )) db = PostgresqlExtDatabase("nosenew", user="******") db.connect() class HashPassedURL(Model): url_hash = IntegerField(unique=True) class Meta(object): database = db db_table = "search_hashpassedurl" class RobotHandler(request.HTTPHandler): """ Inherited HTTPHandler (A class to handle opening of HTTP URLs), that check robots.txt