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
