def __init__(self, host, dbport, dbname, user, password):
# Escape password as it may contain special characters.
# Strip whitespace from other parameters.
# Strip trailing '/' from host.
connection_uri = 'postgresql://{user}:{pw}@{host}:{dbport}/{dbname}'.format(
user=user.strip(),
pw=urllib.parse.quote_plus(password),
host=host.strip().rstrip('/'),
dbport=dbport,
dbname=dbname.strip(),
)
# Check that connection can be established
try:
with queries.Session(connection_uri) as session:
session.query('SELECT 1')
except Exception as exception:
print('ERROR: Unable to connect to database:')
raise exception
# Check that test archive contains data
try:
with queries.Session(connection_uri) as session:
session.query('SELECT count(*) FROM test_run')
except Exception:
print(
'ERROR: Given database is empty. Consider archiving some results first.'
)
sys.exit(1)
self.session = queries.TornadoSession(connection_uri)
def __init__(self, host, dbname, user, password):
connection_uri = 'postgresql://{user}:{pw}@{host}/{dbname}'.format(
user=user.strip(),
pw=urllib.parse.quote_plus(password),
host=host.strip().rstrip('/'),
dbname=dbname.strip(),
)
self.session = queries.Session(connection_uri)
def json_respone(self):
with queries.Session(uri) as s:
result_set = [row for row in s.query(self.query_set())]
for items in result_set:
for f in self._meta.list_fields:
if items[f]:
items[f] = items[f].split(',')
for f in self._meta.geo_fields:
if items[f]:
items[f] = list(map(float, items[f].split(',')))
return Response(encode_json({"data": result_set}, True))
def initial_retrieve(self):
with queries.Session(self.db_url) as session:
if self.dev:
session.cursor.execute("SELECT * FROM users AS u WHERE u.id=1")
users = session.cursor.fetchall()
session.cursor.execute(
"SELECT * FROM settings AS s WHERE s.user_id=1")
settings = session.cursor.fetchall()
session.cursor.execute(
"SELECT * FROM commands AS c WHERE c.user_id=1")
commands = session.cursor.fetchall()
session.cursor.execute(
"SELECT * FROM custom_commands AS c WHERE c.user_id=1")
c_commands = session.cursor.fetchall()
else:
session.cursor.execute(
"SELECT * FROM users AS u WHERE u.id IN (SELECT s.user_id FROM settings AS s WHERE s.active=true)"
)
users = session.cursor.fetchall()
session.cursor.execute(
"SELECT * FROM settings AS s WHERE s.active=true")
settings = session.cursor.fetchall()
session.cursor.execute(
"SELECT * FROM commands AS c WHERE c.user_id IN (SELECT s.user_id FROM settings AS s WHERE s.active=true)"
)
commands = session.cursor.fetchall()
session.cursor.execute(
"SELECT * FROM custom_commands AS c WHERE c.user_id IN (SELECT s.user_id FROM settings AS s WHERE s.active=true)"
)
c_commands = session.cursor.fetchall()
users_dict = {}
for i in users:
user_id = i["id"]
i["user_id"] = user_id
users_dict[user_id] = i
users_dict[user_id]["commands"] = []
users_dict[user_id]["custom_commands"] = []
for i in settings:
users_dict[i["user_id"]]["settings"] = i
for i in commands:
users_dict[i["user_id"]]["commands"].append(i)
for i in c_commands:
users_dict[i["user_id"]]["custom_commands"].append(i)
channels_dict = {}
for v in list(users_dict.values()):
channels_dict[v["twitch_name"]] = v
return channels_dict
def session(connect=None, pool_size=10, is_tornado=False):
if connect is None:
connect = DB_CONNECT
if connect is None:
raise Exception('Please provide a connection string or set the NHLDB_CONNECT env. variable')
if is_tornado:
s = queries.TornadoSession(connect, pool_max_size=pool_size)
else:
s = queries.Session(connect, pool_max_size=pool_size)
try:
yield s
finally:
s.close()
def prep_postgres(file):
try:
LOGGER.info('Processing %s', file)
db = os.path.splitext(os.path.basename(file))[0]
uri = os.environ.get('PGSQL_{0}'.format(db.upper()))
if uri is not None:
chop = len(db) + 1
base = uri[:-chop]
else:
base = os.environ.get('PGSQL',
'postgresql://postgres@localhost:5432')
uri = os.path.join(base, db)
with queries.Session(os.path.join(base, 'postgres')) as session:
LOGGER.debug('Creating database')
session.query('DROP DATABASE IF EXISTS {0};'.format(db))
session.query('CREATE DATABASE {0};'.format(db))
with queries.Session(uri) as session:
with open(file) as fh:
session.query(fh.read())
except Exception:
LOGGER.exception('Failed to execute pgsql queries.')
sys.exit(-1)
def connection(host='localhost',
port=5432,
dbname='postgres',
user='******',
password=None):
""" Create a new Database connection
Args:
host (str): ip address or hostname of running postgres process
port (int): port the running postgres is listening on
dbname (str): name of database to connect to
user (str): credentials username authorized to connect to db
password (str): credentials password to authenticate user
Returns:
queries.Session: new database connection
"""
connection_string = queries.uri(host, port, dbname, user, password)
return queries.Session(connection_string)
def fetch_one(self, user_id):
with queries.Session(self.db_url) as session:
session.cursor.execute("SELECT * FROM users AS u WHERE u.id=%s",
(user_id, ))
users = session.cursor.fetchone()
session.cursor.execute(
"SELECT * FROM settings AS s WHERE s.user_id=%s", (user_id, ))
settings = session.cursor.fetchone()
session.cursor.execute(
"SELECT * FROM commands AS c WHERE c.user_id=%s", (user_id, ))
commands = session.cursor.fetchall()
session.cursor.execute(
"SELECT * FROM custom_commands AS cc WHERE cc.user_id=%s",
(user_id, ))
custom_commands = session.cursor.fetchall()
users["settings"] = settings
users["commands"] = commands
users["custom_commands"] = custom_commands
return users
def check_weather(city):
session = queries.Session(
'postgresql://postgres@localhost:5432/weather_db')
appid = '9ecc560e5c99c8be650566914f4192e6'
url = 'http://api.openweathermap.org/data/2.5/weather'
payload = {'q': city, 'appid': appid, 'units': 'imperial'}
response = requests.get(url, params=payload)
current_time = int(time.time())
ts = time.localtime()
fmt = '%Y-%m-%d %H:%M:%S'
query_time = time.strftime(fmt, ts)
session.query(
'''INSERT INTO weather_history VALUES
(DEFAULT, %(city)s, %(contents)s, %(time)s, %(query_time)s)''', {
'city': city,
'contents': json.dumps(response.json()),
'time': current_time,
'query_time': query_time
})
print(query_time)
def loadDatabase():
dbURI = queries.uri(os.environ.get("DBHOST"),
port=os.environ.get("DBPORT"),
dbname="gpadmin",
user="******",
password="******")
with queries.Session(dbURI) as session:
result = session.query("drop table if exists customers CASCADE ;")
result = session.query(
"create table customers(existingLines int,birthDate date,creditAmount int,guarantors int,creditDuration int,cardNumber text,existingLinesBank int,city text,typeResidence int,zip text,employmentType int,mostValAsset int,streetAddress text,state text,creditPercent int,phoneNumber text,latitude float,employmentLength int,accountBalanceStatus int,job text ,paymentStatusPrevCredit int,emailAddress text,purpose int,foreignWorker int,sexMaritalStatus int,creditability int,firstName text,accountBalance float,lastName text,age int,longitude float,savingsValue int,socialsecurityNumber text,dependents int,customerNumber bigint,durationAddess int,telephoneAvail int) with (appendonly=true) DISTRIBUTED RANDOMLY;"
)
with open('./data/customers.csv') as csvfile:
reader = csv.reader(csvfile)
next(reader, None)
for row in reader:
rowString = "'" + "','".join(row) + "'"
result = session.query("insert into customers VALUES (" +
rowString + ");")
# This is to post new customers. Not implementing yet.
result = session.query("drop table if exists transactions CASCADE ;")
result = session.query(
"drop table if exists transactions_hive CASCADE ;")
result = session.query(
"create table transactions(city text,zip integer,amount float,flagged int,state text,longitude float,id text,streetaddress text,latitude float,transactiontimestamp timestamp,customerNumber bigint) with (appendonly=true) DISTRIBUTED RANDOMLY;"
)
result = session.query(
"create table transactions_hive(like transactions);")
result = session.query(
"drop external table if exists transactions_pxf CASCADE ;")
result = session.query(
"create external table transactions_pxf(like transactions) LOCATION('pxf://"
+ os.environ.get("DBHOST") +
":51200/scdf/*.txt?PROFILE=HDFSTextSimple') FORMAT 'CSV' (QUOTE '''') LOG ERRORS INTO err_transactions SEGMENT REJECT LIMIT 500;"
)
def loadTrainingSets():
print "LOADING TRAINING DATA SETS"
dbURI = queries.uri(os.environ.get("DBHOST"),
port=os.environ.get("DBPORT"),
dbname="gpadmin",
user="******",
password="******")
with queries.Session(dbURI) as session:
result = session.query("drop table if exists customers_train CASCADE;")
result = session.query(
"create table customers_train(existingLines int,birthDate date,creditAmount int,guarantors int,creditDuration int,cardNumber text,existingLinesBank int,city text,typeResidence int,zip text,employmentType int,mostValAsset int,streetAddress text,state text,creditPercent int,phoneNumber text,latitude float,employmentLength int,accountBalanceStatus int,job text ,paymentStatusPrevCredit int,emailAddress text,purpose int,foreignWorker int,sexMaritalStatus int,creditability int,firstName text,accountBalance float,lastName text,age int,longitude float,savingsValue int,socialsecurityNumber text,dependents int,customerNumber bigint,durationAddess int,telephoneAvail int) with (appendonly=true) DISTRIBUTED RANDOMLY;"
)
with open('./data/customers-training.csv') as csvfile:
reader = csv.reader(csvfile)
next(reader, None)
for row in reader:
rowString = "'" + "','".join(row) + "'"
result = session.query("insert into customers_train VALUES (" +
rowString + ");")
result = session.query(
"drop table if exists transactions_train CASCADE;")
result = session.query(
"create table transactions_train(city text,zip integer,amount float,flagged int,state text,longitude float,id text,streetaddress text,latitude float,transactiontimestamp timestamp,customerNumber bigint) with (appendonly=true) DISTRIBUTED RANDOMLY;"
)
with open('./data/transactions-training.csv') as csvfile:
reader = csv.reader(csvfile)
next(reader, None)
for row in reader:
rowString = "'" + "','".join(row) + "'"
result = session.query(
"insert into transactions_train VALUES (" + rowString +
");")
print "TRAINING DATA LOADED"
def initialize(self):
self.session = queries.Session(
'postgresql://postgres@localhost:5432/blog')
]
})
def title_extraction(item: dict):
title = item.get('title')
match = TITLE_REGEX.match(title)
if match:
item['title:code'] = match.group(1)
return item
if args.action == "upload":
session = queries.Session(URI)
for item in args.item:
print(f'> Initialization of the upload for `{item}`', end="\n\n")
for locale in args.locale:
print(f'[{item}] > Getting index for `{locale}`...', end="\n\n")
name = f'{locale}_{item}'
index = client.get_index(name)
try:
index.info()
except:
print(f'[{name}] > `{name}` does not seem to exist. Please wait...')
#!/usr/bin/env python3
import arrow
import json
import decimal
import os
import queries
import requests
session = queries.Session("postgresql://postgres@localhost:5432/home_mon")
results = session.query("SET timezone='America/Chicago'")
# Request last update to feeds
api_get_key = os.environ.get('ThingSpeak_API_KEY')
payload = {'api_key': api_get_key, 'timezone': 'America/Chicago'}
r = requests.get('https://api.thingspeak.com/channels/484266/feeds/last.json', params=payload)
data = r.json()
created = data['created_at']
bedroom_temp = data['field1']
bedroom_humidity = data['field2']
livingroom_temp = data['field3']
livingroom_humidity = data['field4']
kitchen_temp = data['field5']
kitchen_humidity = data['field6']
office_temp = data['field7']
office_humidity = data['field8']
results = session.query(
'INSERT INTO home_mon VALUES (DEFAULT, %(created)s, %(bedroom_temp)s, %(bedroom_humidity)s, %(livingroom_temp)s, %(livingroom_humidity)s, %(kitchen_temp)s, %(kitchen_humidity)s, %(office_temp)s, %(office_humidity)s)',
{
'created': created,
def initialize(self):
self.session = queries.Session(
os.environ.get('DATABASE_URL',
'postgresql://postgres@localhost:5432/pedipaws'))
def setUp(self):
try:
self.session = queries.Session(self.pg_uri, pool_max_size=10)
except queries.OperationalError as error:
raise unittest.SkipTest(str(error).split('\n')[0])
def initialize(self):
self.session = queries.Session(os.environ.get('DATABASE_URL'))
def setUp(self):
uri = queries.uri('localhost', 5432, 'postgres', 'postgres')
try:
self.session = queries.Session(uri, pool_max_size=10)
except queries.OperationalError as error:
raise unittest.SkipTest(str(error).split('\n')[0])
def initialize(self):
self.session = queries.Session(DATABASE_URL)
def initialize(self):
self.session = queries.Session(
'postgresql://postgres@localhost:5432/weather_app')
def get_session():
return queries.Session(URI)
