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()