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 db():
test_db = PostgresqlDatabase('slurpertest')
for model in MODELS:
model.bind(test_db, bind_refs=False, bind_backrefs=False)
test_db.create_tables(MODELS)
with test_db.transaction() as txn:
yield txn
txn.rollback()
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
def __init__(self, *args, **kwargs):
self.models = {}
self.introspector = {}
self._metadata = {}
autorollback = kwargs.pop('autorollback', True)
PostgresqlDatabase.__init__(self, None, autorollback=autorollback)
DatabaseConnection.__init__(self, *args, **kwargs)
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 initialize_postgres(cls):
if os.environ['ENV'] == 'TEST':
PostgresDB.instance = PostgresqlDatabase('movie_catalogue_test',
user='******',
password='******',
host='0.0.0.0',
port=5432)
else:
PostgresDB.instance = PostgresqlDatabase('movie_catalogue',
user='******',
password='******',
host='0.0.0.0',
port=5432)
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 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)
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 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)
def gendb(type, path, host, port, db, user, passwd, tablename=config.DB_TABLE):
if type == 'sqlite':
db = SqliteDatabase(config.DB_PATH)
elif type == 'mysql':
if host is None or port is None or user is None or passwd is None:
return config.failed
db = MySQLDatabase(db, host=host, port=port, user=user, passwd=passwd)
elif type == 'postgresql':
if host is None or port is None or user is None or passwd is None:
return config.failed
db = PostgresqlDatabase(db,
host=host,
port=port,
user=user,
password=passwd)
else:
logging.error('Tipo de base de datos no soportado o inválido: %s',
type)
return config.failed
class table(BaseModel):
class Meta:
database = db
table._meta.db_table = tablename
return (db, table)
def init_database(db_type, config_filename=None):
if db_type == 'postgres':
# If the user wants to use Postgres, they should define their credentials
# in an external config file, which are used here to access the database.
config_filename = config_filename if config_filename else POSTGRES_CONFIG_NAME
with open(config_filename) as pg_config_file:
pg_config = json.load(pg_config_file)
config = {}
config['user'] = pg_config['dbusername']
if 'dbpassword' in pg_config:
config['password'] = pg_config['dbpassword']
if 'host' in pg_config:
config['host'] = pg_config['host']
if 'port' in pg_config:
config['port'] = pg_config['port']
db = PostgresqlDatabase(DATABASE_NAME, **config)
# Sqlite is the default type of database.
elif db_type == 'sqlite' or not db_type:
db = SqliteDatabase(DATABASE_NAME + '.sqlite')
db_proxy.initialize(db)
def db_init():
if 'HEROKU' in os.environ:
urlparse.uses_netloc.append('postgres')
url = urlparse.urlparse(os.environ['DATABASE_URL'])
DATABASE = {
'engine': 'peewee.PostgresqlDatabase',
'name': url.path[1:],
'user': url.username,
'password': url.password,
'host': url.hostname,
'port': url.port,
}
database = PostgresqlDatabase(
DATABASE.get('name'),
user=DATABASE.get('user'),
password=DATABASE.get('password'),
host=DATABASE.get('host'),
port=DATABASE.get('port')
)
print('Postgres database created')
else:
DATABASE = 'quest.db'
database = SqliteDatabase(DATABASE)
print('SQLite database created')
return database
def __init__(self, database_name, user, password, host, port):
db = PostgresqlDatabase(database_name, user=user, password=password, host=host, port=port)
# define our peewee models
class Group(Model):
name = CharField()
reuse = BooleanField()
broadcast_id = CharField()
class Meta:
database = db
db_table = 'groups'
class Contact(Model):
name = CharField()
email = CharField()
phone = CharField()
sid = CharField()
vanid = CharField()
group_id = ForeignKeyField(Group, db_column='group_id', related_name='contacts', to_field='id')
class Meta:
database = db
db_table = 'contacts'
self.models["Group"] = Group
self.models["Contact"] = Contact
self.db = db
class Meta:
# database = db
db = PostgresqlDatabase(
'scheduleDB',
user='******',
password='******',
host='schedule-db.clhrtwy2vi9q.us-east-2.rds.amazonaws.com', port=5432)
def main():
"""The main function."""
# Get runtime arguments
cli_args = parse_runtime_args()
# Parse config file
config_dict = parse_config_file(config_path=cli_args.config)
# Connect to database
db = PostgresqlDatabase(
database=config_dict['postgres-database-name'],
user=config_dict['postgres-database-user'],
password=config_dict['postgres-database-user-password'],
host=config_dict['postgres-database-host'],
port=config_dict[
'postgres-database-port'], # TODO: this *might* need to be an int
)
database_proxy.initialize(db)
# Do specific things based on the sub-command passed in
if cli_args.subcommand is None:
# Default action here
print("This is a default")
elif cli_args.subcommand == 'migrate':
# Migrate the database and exit
# TODO: add some kind of warning here, I think this might drop
# existing tables and recreate them
migrate_database(db)
def __init__(self, db_name, db_user, db_password, db_path, uploads_path, project_name):
"""
"""
db = PostgresqlDatabase(db_name, user=db_user, host=db_path)
database_proxy.initialize(db)
self.uploads_path = uploads_path
self.project_name = project_name
def init_database(conf: configparser.ConfigParser,
config_section: str,
schema: Optional[str] = None) -> PostgresqlDatabase:
"""
Specify a default schema that the database should use for creating a querying tables with
the 'schema' parameter. This can allow you to upload data to development tables, rather
than the public schema which is queried by the live application.
"""
db_name = conf[config_section]["db_name"]
user = conf[config_section]["user"]
password = conf[config_section]["password"]
host = conf[config_section]["host"]
port = conf[config_section]["port"]
# Set the default schema for creating and querying tables by setting as the sole schema in the
# database connection's search path.
options = f'-c search_path="{schema}"' if schema is not None else ""
print(options)
return PostgresqlDatabase(
db_name,
user=user,
password=password,
host=host,
port=port,
options=options,
)
def get_system_database():
"""Returns connection to database received from Config"""
database_type = Config.DATABASE_TYPE.lower()
logger.debug("Connection to %s database (%s)", database_type,
Config.DATABASE_NAME)
if database_type == "mysql":
port = Config.DATABASE_PORT or 3306
return MySQLDatabase(Config.DATABASE_NAME,
host=Config.DATABASE_HOST,
user=Config.DATABASE_USER,
password=Config.DATABASE_PASSWORD,
port=port,
charset="utf8mb4")
elif database_type == "postgres":
port = Config.DATABASE_PORT or 5432
return PostgresqlDatabase(Config.DATABASE_NAME,
host=Config.DATABASE_HOST,
user=Config.DATABASE_USER,
password=Config.DATABASE_PASSWORD,
port=port)
elif database_type == "sqlite":
return SqliteDatabase(Config.DATABASE_NAME)
else:
raise DatabaseException(
"Supports sqlite, postgres or mysql(mariadb) databases, not '{}'".
format(database_type))
def db_factory():
db = PostgresqlDatabase(url.path[1:],
user=url.username,
password=url.password,
host=url.hostname,
port=url.port)
return db
def configure(binder: Binder) -> Binder:
## User Interface ::
### REST ::
binder.bind(FlaskPredictByStationIdResponse,
FlaskPredictByStationIdResponse())
## Infrastructure ::
### Bicing API ::
binder.bind(PostgresqlDatabase,
to=PostgresqlDatabase(BICING_API_DB_DATABASE,
user=BICING_API_DB_USER,
password=BICING_API_DB_PASSWORD,
host=BICING_API_DB_HOST,
port=BICING_API_DB_PORT))
binder.bind(PeeweeClient,
to=PeeweeClient(binder.injector.get(PostgresqlDatabase)))
binder.bind(PeeweeStationQuery,
to=PeeweeStationQuery(binder.injector.get(PeeweeClient)))
binder.bind(PeeweeStationStateQuery,
to=PeeweeStationStateQuery(binder.injector.get(PeeweeClient)))
### Persistence ::
binder.bind(StationAvailabilityAlgorithmRepositoryInterface,
to=FileSystemStationAvailabilityAlgorithmRepository(
pickle, PERSISTENCE_MODEL_FILE_SYSTEM_PATH,
StorageManager()))
### Data Mining ::
binder.bind(XGBRegresser, to=XGBRegresser())
binder.bind(XGBoostDataTrainer,
to=XGBoostDataTrainer(binder.injector.get(XGBRegresser)))
### Request ::
binder.bind(ByDateTimeInPeriodFilterConverter,
to=ByDateTimeInPeriodFilterConverter())
## Application ::
### Use Case ::
#### Handler ::
binder.bind(CreateStationAvailabilityAlgorithmHandler,
to=CreateStationAvailabilityAlgorithmHandler(
binder.injector.get(PeeweeStationStateQuery),
binder.injector.get(XGBoostDataTrainer),
binder.injector.get(
StationAvailabilityAlgorithmRepositoryInterface)))
### Process ::
#### Manager ::
binder.bind(
CreateStationAvailabilityAlgorithmsManager,
to=CreateStationAvailabilityAlgorithmsManager(
binder.injector.get(PeeweeStationQuery),
binder.injector.get(CreateStationAvailabilityAlgorithmHandler)))
#### Data Provider ::
binder.bind(
StationAvailabilitiesPredictionByDateTimeInPeriodFilterDataProvider,
to=StationAvailabilitiesPredictionByDateTimeInPeriodFilterDataProvider(
binder.injector.get(
StationAvailabilityAlgorithmRepositoryInterface)))
return binder
class Settings:
DB = PostgresqlDatabase(database=env("DB_NAME"),
user=env("DB_USER"),
password=env("DB_PASSWORD"),
host=env("DB_HOST"),
autorollback=True)
SESSION_COOKIE_SECRET_KEY = env("SESSION_COOKIE_SECRET_KEY")
SECRET_KEY = env("SECRET_KEY")
def init_db():
db.initialize(
PostgresqlDatabase(os.getenv('PGDATABASE'),
user=os.getenv('PGUSER'),
password=os.getenv('PGPASSWORD'),
host=os.getenv('PGHOST'),
port=5432))
db.create_tables([TargetNode, UserContext], safe=True)
def get_db_connection(db='', user='', passwd='', host='', port='', autocommit=True):
db = PostgresqlDatabase(
db, # databse name
user=user,
password=passwd,
host=host,
port=port
)
return db
def init_db(cfg):
path_py = str(os.path.dirname(os.path.abspath(__file__)))
login = cfg.User()
password = cfg.Password()
host = cfg.Host()
port = cfg.Port()
name_db = cfg.Name()
type_db = int(cfg.Type())
blacklist_db = False
if type_db == 0:
blacklist_db = SqliteDatabase(path_py + '/' + name_db + '.db',
pragmas=(('foreign_keys', 1), ))
database_proxy.initialize(blacklist_db)
database_proxy.create_tables(
[Dump, Item, IP, DNSResolver, Domain, URL, History], safe=True)
init_dump_tbl()
logger.info('Check database: SQLite Ok')
elif type_db == 1:
import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
db = psycopg2.connect(dbname='postgres',
host=host,
port=port,
user=login,
password=password)
db.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = db.cursor()
check_db = "SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower('" + name_db + "')"
cursor.execute(check_db)
db_exist_flag = cursor.fetchone()
if not db_exist_flag:
create_db = "CREATE DATABASE " + name_db + " WITH ENCODING = 'UTF8' " \
"LC_COLLATE = 'ru_RU.UTF-8' " \
"LC_CTYPE = 'ru_RU.UTF-8'"
cursor.execute(create_db)
privileges_set = "GRANT ALL PRIVILEGES ON DATABASE " + name_db + " TO " + login
cursor.execute(privileges_set)
cursor.close()
blacklist_db = PostgresqlDatabase(name_db,
host=host,
port=port,
user=login,
password=password)
database_proxy.initialize(blacklist_db)
database_proxy.create_tables(
[Dump, Item, IP, DNSResolver, Domain, URL, History], safe=True)
init_dump_tbl()
logger.info('Check database: PostgreSQL Ok')
else:
logger.info('Wrong type DB. Check configuration.')
exit()
return blacklist_db
def post(self):
self.connection = PostgresqlDatabase(DB_NAME,
user=DB_USER,
port=DB_PORT,
password=DB_PASSWORD,
host=DB_HOST)
res = self.method(self.request)
self.write(res)
self.finish()
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.')
def get_db():
if 'db' not in g:
g.db = PostgresqlDatabase(
os.environ['POSTGRES_DB'],
user=os.environ['POSTGRES_USER'],
host=os.environ['POSTGRES_HOST'],
password=os.environ['POSTGRES_PASS'],
)
g.db.connect()
return g.db
def load_database(self):
self.logger.info(f"DB use - {self.config['ENGINE']}")
if self.config["ENGINE"] == "PostgreSQL":
return PostgresqlDatabase(
self.config["SCHEMA"],
user=self.config["USER"],
password=self.config["PASSWORD"],
host=self.config["HOST"],
port=self.config["PORT"],
)
def _init_database(self, database, database_type_str, database_options):
# Will raise ValueError if database type is not supported
database_type = Databases(database_type_str)
if database_type == Databases.SQLITE:
return SqliteDatabase(database, **(database_options or {}))
elif database_type == Databases.MYSQL:
return MySQLDatabase(database, **(database_options or {}))
elif database_type == Databases.POSTGRES:
return PostgresqlDatabase(database, **(database_options or {}))
def _database():
parsed = urlparse(config('DATABASE_URL'))
if parsed.scheme == 'sqlite':
return SqliteDatabase(parsed.path[1:]) # skip first slash
return PostgresqlDatabase(
parsed.path[1:], # skip first slash
user=parsed.username,
password=parsed.password,
host=parsed.hostname,
port=parsed.port)
import os
from peewee import PostgresqlDatabase
database = PostgresqlDatabase(os.environ['munin_db_name'],
user=os.environ['munin_db_user'],
password=os.environ['munin_db_password'],
host=os.environ['munin_db_host'])
from munin.models.model import Model
from munin.models.user import User
from munin.models.token import Token
from munin.models.location import Location
database.create_tables([User, Token, Location], safe=True)
from model_column import AdminColumnModel
from model_table import AdminTableModel
from model_user import AdminUserModel
from peewee import PostgresqlDatabase
if __name__ == "__main__":
database = PostgresqlDatabase('cs419', user='******')
tables = [
AdminColumnModel,
AdminTableModel,
AdminUserModel
]
database.drop_tables(tables, safe=True, cascade=True)
# -*- coding: utf8 -*-
import peewee
from peewee import Model, CharField, DateTimeField, ForeignKeyField, Field, PostgresqlDatabase
from config import DATABASE_URL
from playhouse.db_url import connect
from passlib.hash import pbkdf2_sha256
from datetime import datetime
db = connect(DATABASE_URL)
PostgresqlDatabase.register_fields({'password_hash': 'password_hash'})
class _Model(Model):
class Meta:
database = db
def __repr__(self):
data = ", ".join(["%s: %s" % (key, unicode(value).encode('utf8') if value else None) for key, value in self._data.items()])
return "{class_name}: {{ {data} }}".format(class_name = self.__class__.__name__, data = data)
@classmethod
def get_by_id(cls, id):
return cls.get(cls.id == id)
@classmethod
def get_by_name(cls, name):
return cls.get(cls.name == name)
class Merchant(_Model):
w1_checkout_id = CharField()
name = CharField()
contacts = CharField()
def load_db(self, config):
self.db = PostgresqlDatabase(self.app.config['DATABASE'], **config)
# -*- coding: utf-8 -*-
# Define here the models for your scraped items
#
# See documentation in:
# http://doc.scrapy.org/en/latest/topics/items.html
from peewee import Model, CharField, DateField, TimeField, ForeignKeyField, \
DecimalField, IntegerField, PostgresqlDatabase
COORDINATES_LENGTH = 9
COORDINATES_DECIMAL = 6
db = PostgresqlDatabase('accidents', # Required by Peewee.
user='******',
# Will be passed directly to psycopg2.
password='******',
host='localhost', )
class Airfield(Model):
asn_id = CharField(20)
name = CharField(100)
ICAO = CharField(6)
IATA = CharField(6)
lat = DecimalField(max_digits=COORDINATES_LENGTH,
decimal_places=COORDINATES_DECIMAL)
long = DecimalField(max_digits=COORDINATES_LENGTH,
decimal_places=COORDINATES_DECIMAL)
class Meta:
from peewee import Model, TextField, FixedCharField, BooleanField, PostgresqlDatabase
from settings import DATABASE, DATABASE_URL
from playhouse.db_url import connect
if DATABASE_URL:
db = connect(DATABASE_URL)
else:
db = PostgresqlDatabase(DATABASE['name'],
user=DATABASE['user'],
password=DATABASE['password'],
host=DATABASE['host'],
port=DATABASE['port'])
class BaseModel(Model):
class Meta:
database = db
class Comic(BaseModel):
title = TextField()
url = TextField()
image_url = TextField()
type = FixedCharField(max_length=1)
posted = BooleanField(default=False)
db.create_table(Comic, True)
"""Custom fields."""
import ujson
from cached_property import cached_property
from peewee import Field, PostgresqlDatabase, Proxy
try:
from playhouse.postgres_ext import Json, JsonLookup
PostgresqlDatabase.register_fields({'json': 'json'})
except:
Json = JsonLookup = None
class JSONField(Field):
"""Implement JSON field."""
def __init__(self, dumps=None, loads=None, *args, **kwargs):
"""Initialize the serializer."""
self.dumps = dumps or ujson.dumps
self.loads = loads or ujson.loads
super(JSONField, self).__init__(*args, **kwargs)
@cached_property
def db_field(self):
"""Return database field type."""
database = self.get_database()
if isinstance(database, Proxy):
database = database.obj
if Json and isinstance(database, PostgresqlDatabase):
return 'json'
else:
return int(value)
def python_value(self, value):
return None if value is None else timedelta(seconds=value)
class MACAddress:
def __init__(self, mac):
self.mac = str(mac)
def __str__(self):
return self.mac
PostgresqlDatabase.register_fields({
'datetime_tz': 'timestamp with time zone',
'macaddr': 'macaddr',
'inet': 'inet',
'cidr': 'cidr',
})
SqliteDatabase.register_fields({
'datetime_tz': 'DATETIME',
"inet": "VARCHAR(32)",
"cidr": "VARCHAR(32)",
"macaddr": "VARCHAR(32)",
})
change the DB for whatever reason, just change the line defining "db", providing
the neccesary information to connect to that database.
"""
if "HEROKU" in environ:
uses_netloc.append("postgres")
url = urlparse(environ["DATABASE_URL"])
DATABASE = {
"name": url.path[1:],
"user": url.username,
"password": url.password,
"host": url.hostname,
"port": url.port,
}
db = PostgresqlDatabase(DATABASE["name"], user=DATABASE["user"],
password=DATABASE["password"],
host=DATABASE["host"],
port=DATABASE["port"])
db.get_conn().set_client_encoding('UTF8')
else:
db = MySQLDatabase("fbCalDB", user="******")
class BaseModel(Model):
"""This is the base model that all tables in the database will follow. It
simply outlines that all tables will be a part of the database "db".
"""
class Meta:
database = db
class Users(BaseModel):
# HEROKU_TEST_DB = "postgres://vyxede....."
# ELEPHANTSQL_TEST_DB = "postgres://bffueem....."
## THE PRODUCTION DB:
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()
