def demo():
from connection import Connection, Database
db = Database()
print repr(db.list())
cnx = Connection(dbname="demo", login="******", password="******")
modules = Object(cnx, "ir.module.module")
ids = modules.search([('state', '=', 'installed')])
for p in modules.read(ids, ['name']):
print p['name']
def demo():
from connection import Connection, Database
db = Database()
print repr(db.list())
cnx = Connection(dbname="demo", login="******", password="******")
modules = Object(cnx, "ir.module.module")
ids = modules.search([('state', '=', 'installed')])
for p in modules.read(ids, ['name']):
print p['name']
def all():
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("SELECT * FROM jakk.FutureBorough;")
cursor.execute(query)
result = cursor.fetchall()
return jsonify(result)
def test_connection():
# Creating a Connection
new_instance = Database()
cnx = new_instance.database_connector()
cursor_variable = cnx.cursor()
# Using the Connection
try:
SQL_Q = 'SELECT COUNT(*) FROM company_list;'
cursor_variable.execute(SQL_Q)
row = cursor_variable.fetchall()
print row
except Exception as e:
print "Exception Occured while Connecting to the Database"
print "Error code:", e.errno # error number
print "SQLSTATE value:", e.sqlstate # SQLSTATE value
print "Error message:", e.msg # error message
print "Error:", e # errno, sqlstate, msg values
def all():
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("SELECT * FROM jakk.CityWater;")
cursor.execute(query)
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def division():
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("SELECT C1.City FROM City C1 WHERE NOT EXISTS ( "
"SELECT B1.City FROM Borough B1 "
"WHERE B1.Borough NOT IN ( "
"SELECT B2.Borough FROM Borough B2 WHERE B2.City = C1.City) )")
cursor.execute(query)
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def updateBoroughSize():
# Format is admin/updateborough/size?borough=XXXX&new_value=XXXX
borough = request.args['borough']
new_value = request.args['new_value']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("UPDATE jakk.Borough SET Size = %s WHERE Borough = %s")
cursor.execute(query, (new_value, borough))
db.connection.commit()
db.connection.close()
return jsonify(["Number of rows affected", cursor.rowcount])
def index():
# Format is /borough?name=XXXX
# All arguments are mandatory
name = request.args['name']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("SELECT * FROM jakk.Borough WHERE borough = %s")
cursor.execute(query, (name,))
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def updateBorough():
# Format is admin/updateborough?target=XXXX&result=XXXX
target = request.args['target']
result = request.args['result']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("UPDATE jakk.Borough SET Borough = %s WHERE Borough = %s")
cursor.execute(query, (result, target))
db.connection.commit()
db.connection.close()
return jsonify(["Number of rows affected", cursor.rowcount])
def kingkong():
# Format is admin/deletecity?city=XXXX
city = request.args['city']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("DELETE FROM jakk.City WHERE City = %s")
cursor.execute(query, (city, ))
print(cursor.statement)
db.connection.commit()
db.connection.close()
return jsonify(["Number of rows affected", cursor.rowcount])
def insertCity():
# Format is admin/insertcity?city=XXXX
city = request.args['city']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = (
"INSERT INTO jakk.City (City, Country, Population, Size) VALUES (%s, 'Canada', 630000, 500)"
)
cursor.execute(query, (city, ))
db.connection.commit()
db.connection.close()
return jsonify(["Number of rows affected", cursor.rowcount])
def aggregationGroupBy():
# Format is admin/groupby?largerthan=XXXX
# Returns boroughs and their sum(consumption) larger than XXXX
largerThan = int(request.args['largerthan'])
db = Database.fromconfig()
cursor = db.connection.cursor()
query = (
"SELECT borough, CAST(sum(consumption) AS float) FROM jakk.BoroughWater "
"Group by borough having sum(consumption) > %s")
# "Group by borough having sum(consumption) > 1000000")
cursor.execute(query, (largerThan, ))
# cursor.execute(query)
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def join():
# Format is /admin/join?borough1=XXXX&borough2=XXXX
# Returns borough1 and borough2 alongside their consumptions based on the same month respectively
borough1 = request.args['borough1']
borough2 = request.args['borough2']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = (
"SELECT b1.startDate, b1.borough, b1.consumption, b2.borough, b2.consumption "
"FROM BoroughWater b1 "
"INNER JOIN BoroughWater b2 ON b1.startDate = b2.startDate "
"WHERE b1.borough = %s AND b2.borough = %s")
cursor.execute(query, (borough1, borough2))
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def boroughList():
# Format is /futureborough?name=XXXX&intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD
# All arguments are mandatory
name = request.args['name']
intervalStart = request.args['intervalStart']
intervalEnd = request.args['intervalEnd']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("SELECT * FROM jakk.FutureBorough WHERE Borough = %s AND StartDate BETWEEN %s AND %s")
start = datetime.strptime(intervalStart, '%Y-%m-%d')
end = datetime.strptime(intervalEnd, '%Y-%m-%d')
cursor.execute(query, (name, start, end))
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def boroughList():
# Provide arguments intervalStart & intervalEnd, or 400/500 will be returned
# Format is /futurecity?intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD
intervalStart = request.args['intervalStart']
intervalEnd = request.args['intervalEnd']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("SELECT * FROM jakk.FutureCity WHERE StartDate BETWEEN %s AND %s")
start = datetime.strptime(intervalStart, '%Y-%m-%d')
end = datetime.strptime(intervalEnd, '%Y-%m-%d')
cursor.execute(query, (start, end))
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def futureBoroughStats():
# Format is /futureborough?name=XXXX&intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD
# All arguments are mandatory
# Returns [avg(consumption), max(consumption), min(consumption), avg(charges), max(charges), min(charges)]
name = request.args['name']
intervalStart = request.args['intervalStart']
intervalEnd = request.args['intervalEnd']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("SELECT CAST(AVG(Consumption) AS float), MAX(Consumption), MIN(Consumption)"
"FROM jakk.FutureBorough WHERE Borough = %s AND StartDate BETWEEN %s AND %s")
start = datetime.strptime(intervalStart, '%Y-%m-%d')
end = datetime.strptime(intervalEnd, '%Y-%m-%d')
cursor.execute(query, (name, start, end))
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def login():
# Format is /auth/login?username=XXXX&password=YYYY
# All arguments are mandatory
authorized = False
username = request.args['username']
password = request.args['password']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = ("SELECT * FROM jakk.Account WHERE Username = %(Username)s")
cursor.execute(query, {'Username': username})
account = cursor.fetchone()
if (account):
if password == account[2]:
current_app.config['DB_USERNAME'] = username
current_app.config['DB_PASSWORD'] = password
current_app.config['DB_USERTYPE'] = 'admin'
authorized = True
db.connection.close()
return jsonify(authorized)
def boroughListStats():
# Format is /userwater?name=XXXX&intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD
# All arguments are mandatory
name = request.args['name']
intervalStart = request.args['intervalStart']
intervalEnd = request.args['intervalEnd']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = (
"SELECT CAST(AVG(Consumption) AS float), MAX(Consumption), MIN(Consumption),"
"CAST(AVG(Charges) AS float), MAX(Charges), MIN(Charges)"
"FROM jakk.UserWater WHERE User = %s AND StartDate BETWEEN %s AND %s")
start = datetime.strptime(intervalStart, '%Y-%m-%d')
end = datetime.strptime(intervalEnd, '%Y-%m-%d')
cursor.execute(query, (name, start, end))
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
def cityStats():
# Provide arguments intervalStart & intervalEnd, or 400/500 will be returned
# Format is /citywater?intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD
# Returns [avg(consumption), max(consumption), min(consumption), avg(charges), max(charges), min(charges)]
intervalStart = request.args['intervalStart']
intervalEnd = request.args['intervalEnd']
db = Database.fromconfig()
cursor = db.connection.cursor()
query = (
"SELECT CAST(AVG(Consumption) AS float), MAX(Consumption), MIN(Consumption),"
"CAST(AVG(Charges) AS float), MAX(Charges), MIN(Charges)"
"FROM jakk.CityWater WHERE StartDate BETWEEN %s AND %s")
start = datetime.strptime(intervalStart, '%Y-%m-%d')
end = datetime.strptime(intervalEnd, '%Y-%m-%d')
cursor.execute(query, (start, end))
result = cursor.fetchall()
db.connection.close()
return jsonify(result)
from bs4 import BeautifulSoup
import urllib2, sys
import mysql.connector
from datetime import datetime
# Database Connection Related Imports
import sys, ssl
sys.path.insert(0, sys.path[0] + '\\database')
from connection import Database
# Creating a Connection
new_instance = Database()
cnx = new_instance.database_connector()
cursor_variable = cnx.cursor()
def job_details(url):
# print url
header = {'User-Agent': 'Mozilla/5.0'}
req = urllib2.Request(url, headers=header)
# page = urllib2.urlopen(req)
gcontext = ssl.SSLContext(ssl.PROTOCOL_TLSv1)
info = urllib2.urlopen(req, context=gcontext).read()
soup = BeautifulSoup(info, 'html.parser')
total_data = soup.find_all("div",
{"class": "det-text group-effect1 arrived"})[0]
data = total_data.find_all("a", href=True)
mail_id = ''
for d in data:
def get_db():
db = Database(app.config['DB_USERNAME'], app.config['DB_PASSWORD'],
app.config['DB_HOST'], app.config['DB_SCHEMA'],
app.config['DB_USERTYPE'])
return db
from connection import Database
from pandas import read_excel, read_sql, DataFrame
import os
from config_db import Config
import datetime
from send_mail import SendMail
from ftplib import FTP
from log_error import logError
import pymssql
db = Database(Config)
log = logError()
class ProcessingFileANS:
def downloadFile(self):
try:
ftp = FTP(Config.ftp_host)
ftp.login(user=Config.ftp_user, passwd=Config.ftp_password)
listing = []
ftp.retrlines("LIST", listing.append)
if len(listing) != 0: #File existing to download
words = listing[0].split(None, 8)
filename = words[-1].lstrip()
# download the file to folder repository
local_filename = os.path.join(Config.ftp_folder, filename)
lf = open(local_filename, "wb")
ftp.retrbinary("RETR " + filename, lf.write, 8 * 1024)
ftp.delete(filename) #delete file
lf.close()
