class Database(object):
def __init__(self, app):
self.app = app
self.create_database()
self.register_connection_handlers()
self.Model = self.get_model_class()
def create_database(self):
self.database = PostgresqlDatabase(self.app.config['database_name'])
def get_model_class(self):
class BaseModel(Model):
class Meta:
db = self.database
return BaseModel
def _db_connect(self):
self.database.connect()
def _db_disconnect(self, exc):
if not self.database.is_closed():
self.database.close()
def register_connection_handlers(self):
self.app.before_request(self._db_connect)
self.app.teardown_request(self._db_disconnect)
def _conn(self, dbname, silent_on_fail=False, **params):
"""Connects to the DB and tests the connection."""
if 'password' not in params:
try:
params['password'] = pgpasslib.getpass(dbname=dbname, **params)
except pgpasslib.FileNotFound:
params['password'] = None
PostgresqlDatabase.init(self, dbname, **params)
self._metadata = {}
try:
PostgresqlDatabase.connect(self)
self.dbname = dbname
except OperationalError as ee:
if not silent_on_fail:
log.warning(f'failed to connect to database {self.database!r}: {ee}')
PostgresqlDatabase.init(self, None)
if self.is_connection_usable() and self.auto_reflect:
with self.atomic():
for model in self.models.values():
if getattr(model._meta, 'use_reflection', False):
if hasattr(model, 'reflect'):
model.reflect()
if self.connected:
self.post_connect()
return self.connected
def connect_to_db():
global psql_db
psql_db = PostgresqlDatabase(
database=getenv('DB_NAME'),
host=getenv('HOST'),
port=getenv('DB_PORT'),
user=getenv('DB_USER'),
password=getenv('DB_PASSWORD'),
)
psql_db.connect()
def check_postgres_up():
try:
db_handle = PostgresqlDatabase(database=POSTGRES_DATABASE,
host=POSTGRES_HOST,
port=POSTGRES_PORT,
user=POSTGRES_USER,
password=POSTGRES_PASSWORD)
db_handle.connect()
return True
except OperationalError as e:
return False
async def handle_request(request):
global psql_db
psql_db = PostgresqlDatabase(database='assp',
host='localhost',
user='******',
password='')
psql_db.connect()
# Create tables.
try:
psql_db.create_tables([User])
except OperationalError as e:
print(e)
class Database:
def __init__(self, app=None):
if app:
self.init_app(app)
def init_app(self, app):
self.app = app
self.load_db({
'user': app.config.get('DATABASE_USERNAME'),
'password': app.config.get('DATABASE_PASSWORD'),
'host': app.config.get('DATABASE_HOST', 'localhost'),
'port': app.config.get('DATABASE_PORT', '5432')
})
self.register_handlers()
self.Model = self.get_model_class()
def load_db(self, config):
self.db = PostgresqlDatabase(self.app.config['DATABASE'], **config)
def get_model_class(self):
class BaseModel(Model):
@classmethod
def get_or_404(cls, *args, **kwargs):
try:
return cls.get(*args, **kwargs)
except cls.DoesNotExist:
abort(404)
def __str__(self):
try:
return self.name
except AttributeError:
return str(self._get_pk_value())
class Meta:
database = self.db
return BaseModel
def connect_db(self):
self.db.connect()
def close_db(self, exc):
if not self.db.is_closed():
self.db.close()
def register_handlers(self):
self.app.before_request(self.connect_db)
self.app.teardown_request(self.close_db)
def get_connection():
global _connection
if not _connection:
db = PostgresqlDatabase(
# os.environ['POSTGRES_DB'],
'league_statistics_tracker',
user='******',
# host=os.environ['POSTGRES_HOST'],
host='localhost',
password='******'
# sslmode=os.environ['SSLMODE']
)
db.connect()
_connection = db
return _connection
async def handle_request(request):
global psql_db
psql_db = PostgresqlDatabase(
database='assp',
host='localhost',
user='******',
password=''
)
psql_db.connect()
# Create tables.
try:
psql_db.create_tables([User])
except OperationalError as e:
print(e)
def reset_db():
import os, psycopg2, urllib.parse as urlparse # CHECK
urlparse.uses_netloc.append('postgres')
url = urlparse.urlparse(os.environ["DATABASE_URL"])
db = PostgresqlDatabase(database=url.path[1:], user=url.username, password=url.password, host=url.hostname,
port=url.port)
db.connect(True)
print('CONNECTED')
db.drop_tables([AdminPage, TargetGroup, UserPage, SenderPage])
db.create_tables([AdminPage, TargetGroup, UserPage, SenderPage])
return 'DB is reseted!'
def main():
psql_db = PostgresqlDatabase('networking',
user=os.environ.get('DB_USERNAME'),
password=os.environ.get('DB_PASSWORD'))
try:
psql_db.connect()
except InternalError as px:
print(str(px))
# Create the EventHandler and pass it your bot's token.
token = os.environ.get('BOT_TOKEN')
updater = Updater(token)
logger.info("Initiating cron news sender...")
news_notify(updater.bot)
class PostgresDatabase(object):
def __init__(self, app, config):
host = config.get('host', DEFAULT_HOST)
port = config.get('port', DEFAULT_PORT)
user = config.get('user')
password = config.get('password')
name = config.get('name')
self.connection = PostgresqlDatabase(host=host,
port=port,
user=user,
password=password,
database=name)
self.connection.connect()
def get(self):
return self.connection
def connect(uri):
"""
Connects to the database at the given uri.
"""
global db_proxy
if uri:
logger.debug('Connected to db:%s.' % uri)
parsed = urlparse(uri)
db = PostgresqlDatabase(database=parsed.path[1:],
user=parsed.username,
password=parsed.password,
host=parsed.hostname,
autorollback=True)
db.connect()
db_proxy.initialize(db)
else:
logger.error('Could not connect to the database.')
class Database:
def __init__(self, databaseName, user, password, host, port):
self.database = PostgresqlDatabase(databaseName,
user=user,
password=password,
host=host,
port=port)
def getDatabase(self):
return self.database
def establishConnection(self):
self.database.connect()
def closeConnetion(self):
self.database.close()
def createTables(self, arrOfTables):
with self.database:
print(self.database)
self.database.create_tables(arrOfTables)
def get_user_by_authid(authid: str, db: PostgresqlDatabase) -> User:
attempts = 0
user = None
while attempts <= 2 and user is None:
try:
user = User.get(User.authid == authid)
except DoesNotExist:
cherrypy.response.cookie["authid"] = authid
cherrypy.response.cookie["authid"]["expires"] = 0
raise cherrypy.HTTPError(status=401, message="Bad authid")
except (InterfaceError, OperationalError) as e:
print(str(e))
attempts+= 1
db.close()
db.connect()
if user is None:
print("Couldnt connect and find user in thread", threading.get_ident())
print('after', attempts, 'attempts')
exit(1)
raise cherrypy.HTTPError("DB connection error")
else:
print("Got user", user.name, "after", attempts, "attempts")
return user
password='******',
host='127.0.0.1',
port=1234)
class Base(Model):
class Meta:
database = db
class Image(Base):
origin_image = CharField()
extract_image = CharField(unique=True, primary_key=True)
db.connect()
conn = psycopg2.connect(dbname="database_name",
user="******",
host='127.0.0.1',
password="******",
port=1234)
db.create_tables(Image)
def match(origin, extract):
try:
with db.atomic():
img = Image.create(origin_image=origin, extract_image=extract)
img.save()
except IntegrityError:
print("The extract image exists")
lat = DecimalField(max_digits=COORDINATES_LENGTH,
decimal_places=COORDINATES_DECIMAL, null=True)
long = DecimalField(max_digits=COORDINATES_LENGTH,
decimal_places=COORDINATES_DECIMAL, null=True)
flight_phase = CharField(null=True)
flight_nature = CharField(null=True)
dep_airfield = ForeignKeyField(Airfield, related_name='dep_accidents',
null=True)
asn_dep_airfield = CharField(20, null=True)
dep_weather = ForeignKeyField(Weather, related_name='department_accident',
null=True)
dest_airfield = ForeignKeyField(Airfield, related_name='dest_accidents',
null=True)
asn_dest_airfield = CharField(20, null=True)
dest_weather = ForeignKeyField(Weather, related_name='destination_accident',
null=True)
class Meta:
database = db
db.connect()
Airfield.create_table(True)
EngineType.create_table(True)
Weather.create_table(True)
AircraftType.create_table(True)
Airline.create_table(True)
Aircraft.create_table(True)
Accident.create_table(True)
from models import UserAccount, Complex
from peewee import (PostgresqlDatabase)
def create_users():
UserAccount.create(name='Tony')
UserAccount.create(name='Bruce')
def create_complex():
Complex.create(name='Prasads')
Complex.create(name='PVR')
pg_db = PostgresqlDatabase('movies',
user='******',
password='******',
host='0.0.0.0',
port=5444)
pg_db.connect()
create_users()
create_complex()
pg_db.commit()
from modules.settings import settings
from peewee import (PostgresqlDatabase, Model, CharField, PrimaryKeyField,
ForeignKeyField, IntegerField, TextField)
db_settings = settings['database']
database = PostgresqlDatabase(db_settings['name'], user=db_settings['user'])
database.connect()
class BaseModel(Model):
class Meta:
database = database
class Resource(BaseModel):
id = PrimaryKeyField()
uri = CharField(unique=True, max_length=4096)
class Endpoint(BaseModel):
id = PrimaryKeyField()
url = CharField(unique=True, max_length=4096)
class Backlink(BaseModel):
resource = ForeignKeyField(Resource, related_name='endpoints')
endpoint = ForeignKeyField(Endpoint, related_name='resources')
predicate = TextField()
count = IntegerField(default=0)
class DatabaseConfig:
"""
Instances of this class manage the configuration of tha database for the project. This includes the engine, host,
port, username, password and database name.
The typical work flow using this class is the following:
A new object is created from a config dict. Then the database is init-ed (meaning the object is created)
Then the database proxy (placeholder) for the models is connected to this database object at run time and then the
database is actually connected to its persistent counterpiece (file for sqlite, server for mysql):
EXAMPLE:
database_config = DatabaseConfig.from_dict(database_dict)
database_config.init()
database_proxy.initialize(database_config.get())
database_config.connect()
database_config.create_tables() # OPTIONAL
CHANGELOG
Added 09.06.2019
"""
# This dict will be used as the default base dict, when a new DatabaseConfig object is to be created from a dict,
# which may not contain all the keys. For the keys that are not present, the default ones will stay, but will
# otherwise be overwritten.
DEFAULT_DICT = {
'engine': 'sqlite',
'host': ':memory:',
'database': '',
'user': '',
'password': '',
'port': 0
}
# A list of all valid engine strings
SUPPORTED_ENGINES = ['sqlite', 'mysql', 'postgres']
# This is a list, that contains all the model classes of this project. This list is needed to be passed
# to the database to create all the tables
MODELS = [User, Reward, Pack]
# INSTANCE CONSTRUCTION
# ---------------------
def __init__(self, engine: str, host: str, database: str, user: str,
password: str, port: int) -> NoReturn:
# This will raise an error, if the engine string is not a valid one
self.check_engine(engine)
self.engine = engine
self.host = host
self.database = database
self.user = user
self.password = password
self.port = port
# Later on this variable will contain the actual database instance
self.instance = None
def check_engine(self, engine: str):
"""
This will raise a value error if the engine string is not one of the supported engines.
CHANGELOG
Added 09.06.2019
:param engine:
:return:
"""
if engine not in self.SUPPORTED_ENGINES:
raise ValueError('The engine "{}" is not supported'.format(engine))
# CREATING DATABASE INSTANCE
# --------------------------
def init(self):
"""
This method will create a new database object and put it into the "self.instance" variable. The type of
database object created depends on the value of the engine string
CHANGELOG
Added 09.06.2019
:return:
"""
# Basically I am using this dict as a "switch" statement to reduce "if"-clutter
engine_create_methods = {
'sqlite': self.init_sqlite,
'mysql': self.init_mysql,
'postgres': self.init_postgres
}
engine_create_methods[self.engine]()
def init_sqlite(self):
"""
Creates a new sqlite database object and puts it into "self.instance"
NOTE: For the creation of a sqlite database only the path to the file is relevant. The "self.host" variable
will be used as the sqlite string
CHANGELOG
Added 09.06.2019
:return:
"""
self.instance = SqliteDatabase(self.host)
def init_mysql(self):
"""
Creates a new mysql database object and puts it into the "self.instance".
CHANGELOG
Added 09.06.2019
:return:
"""
self.instance = MySQLDatabase(self.database,
host=self.host,
user=self.user,
password=self.password,
port=self.port)
def init_postgres(self):
"""
Creates a new postgrsql database object and puts it into the "self.instance"
CHANGELOG
Added 09.06.2019
:return:
"""
self.instance = PostgresqlDatabase(self.database,
host=self.host,
user=self.user,
password=self.password,
port=self.port)
# DATABASE OPERATIONS
# -------------------
def connect(self):
"""
When a database object has already been created, this method actually connects the object in "self.instance"
to the actual persistent database.
CHANGELOG
Added 09.06.2019
:return:
"""
self.instance.connect()
def create_tables(self):
"""
If the database object is already connected and created, this method will create the tables for all the models
of the rewardify project into this database.
CHANGELOG
Added 09.06.2019
:return:
"""
self.instance.create_tables(self.MODELS)
def drop_tables(self):
"""
If the database object is already connected and created, this method will drop the tables for all the models
of the rewardify project into this database.
CHANGELOG
Added 09.06.2019
:return:
"""
self.instance.drop_tables(self.MODELS)
def get(self):
"""
returns the database object
CHANGELOG
Added 09.06.2019
:return:
"""
return self.instance
# UTILITY METHODS
# ---------------
def update(self, database_dict: Dict) -> NoReturn:
"""
Given a database dict, all the internal values will be updated according to this dict
CHANGELOG
Added 09.06.2019
:param database_dict:
:return:
"""
for key, value in database_dict.items():
setattr(self, key, value)
# CLASS METHODS
# -------------
@classmethod
def from_dict(cls, database_dict):
"""
Given a dict, which contains the database config, this method will create and return a new DatabaseConfig
object from it.
The dict can contain any subset of the following keys, every key not used it substituted with the default item:
- engine
- host
- database
- port
- user
- password
CHANGELOG
Added 09.06.2019
:param database_dict:
:return:
"""
# We will use the default dict as a base line (in case not all keys are provided by the given dict as for the
# case of sqlite. Then we replace all possible values with the actual ones
argument_dict = cls.DEFAULT_DICT
argument_dict.update(database_dict)
# Finally we can pass the arguments to the constructor by unpacking the dict
return cls(**argument_dict)
from config import Config
from peewee import PostgresqlDatabase, Model
### SETUP peewee
# Initialize db_helper with Peewee; static variable
db_helper = PostgresqlDatabase(
Config.DATABASE,
user=Config.USERNAME,
password=Config.PASSWORD,
host=Config.HOST,
port=Config.PORT,
)
db_helper.connect()
# BaseModel needed for Peewee ORM models and connect to db_helper for all Pra.select() and other db calls
class BaseModel(Model):
class Meta:
database = db_helper
class UnknownField(object):
def __init__(self, *_, **__):
pass
"""
Perlu di ubah setingan postgre configuration nya
di /etc/postgresql/9.4/main/pg_hba.conf
ubah ['local', 'all', 'all', 'peer']
menjadi ['local', 'all', 'all', 'trust']
"""
from datetime import datetime
from peewee import PostgresqlDatabase, Model, CharField, BooleanField, DateField
#asumsinya bahwa kita sudah mempunyai user iniuser dgn password inipass di postgre kita
db_connection = PostgresqlDatabase("peewee_db",
user="******",
password="******")
db_connection.connect()
class BaseModel(Model):
class Meta:
database = db_connection
class Persons(BaseModel):
name = CharField()
birthday = DateField()
is_relative = BooleanField()
Persons.create_table(True)
# CAREFUL when creating new models - avoid Postgres keywords if that's your backend
# while peewee can handle them other ORMs or manual work might not
# see https://www.postgresql.org/docs/current/sql-keywords-appendix.html
def generateToken(size):
return base64.urlsafe_b64encode(os.urandom(size)).decode().replace('=', '')
def reset():
# order matters here - have to delete in the right direction given foreign key constraints
tables = [Auth, Account]
for table in tables:
table.drop_table()
# order matters here too for establishing the foriegn keys, but it's reversed from above
tables.reverse()
for table in tables:
table.create_table()
if __name__ == '__main__':
ok = input(
'WARNING! This will drop all tables in the database. Continue? y/n ')
if ok.lower() in ['y', 'yes']:
peewee_db.connect()
reset()
LOCAL_DB_DEFAULT = "postgres://localhost:5432/localDB"
import os # TODO: avoid os, use a env.py module to centralize all env-vars
DATABASE_URL = os.environ.get('DATABASE_URL', LOCAL_DB_DEFAULT)
import pw_database_url
db_dict = pw_database_url.parse(DATABASE_URL)
print("Database dict: %s" % db_dict)
TheDB = PostgresqlDatabase(db_dict['name'],
user=db_dict['user'],
password=db_dict['password'],
host=db_dict['host'],
port=db_dict['port'])
TheDB.connect()
class BaseModel(Model):
class Meta:
database = TheDB
@classmethod
def exists(cls, expr):
return cls.select().where(expr).exists()
class Chiefdom(BaseModel):
name = CharField(unique=True)
latitude = FloatField()
longitude = FloatField()
# Multi-valued: patients, health_workers
user_id=user_id,
ebs_volume_id=ebs_volume_id)
@classmethod
def get_server(cls, user_id):
return cls.get(user_id=user_id)
@classmethod
def get_server_count(cls):
return cls.select().count()
@classmethod
def remove_server(cls, server_id):
cls.delete().where(cls.server_id == server_id).execute()
class Role(BaseModel):
user_id = CharField(unique=True)
role_name = CharField(unique=True)
role_arn = CharField(unique=True)
s3_bucket = CharField(unique=True)
@classmethod
def get_role(cls, user_id):
return cls.get(user_id=user_id)
DB.connect()
Server.create_table(True)
Role.create_table(True)
