# To change this license header, choose License Headers in Project Properties.
# To change this template file, choose Tools | Templates
# and open the template in the editor.
__author__ = "Arius_EX"
__date__ = "$10 31, 16 3:15:37 PM$"
from connectdb import ConnectDB
from time import gmtime, strftime
from datetime import datetime
c = ConnectDB()
connection = c.connection()
cur = connection.cursor()
class RequisitionAndIssuanceSlip:
def addRIS(self, reqnum, purpose, details, idnum):
sql = "insert into req_iss_slip values('{}', null, '{}','{}', '{}', NULL, NULL, NULL)".format(
reqnum, purpose, strftime("%Y-%m-%d", gmtime()), idnum)
print(sql)
cur.execute(sql)
connection.commit()
print("Done")
def updateRISApproval(self, reqnum, decision, reason):
if decision == 'TRUE':
sql = "update req_iss_slip set status = TRUE where slipnum = '" + reqnum + "'"
"""Module Main: process all task"""
from connectdb import ConnectDB
from getairport import GetAirport
from importcsv import ImportCSV
if __name__ == "__main__":
db = ConnectDB('root', '', 'search')
db.open()
db.drop_table('ticket')
db.drop_table('airport')
db.close()
csv = ImportCSV('root', '', 'search')
csv.create_csv_table('ticket')
csv.import_csv('/csv/', 'ticket')
url = "http://partners.api.skyscanner.net/apiservices/browsequotes/v1.0/JP/JPY/en-JP/VN/anywhere/anytime/anytime?apiKey=ho762281241371727571102068156668"
air = GetAirport(url)
air.request_api()
air.filter_data()
air.create_json_table('airport')
air.import_json('airport')
class GetAirport(object):
"""Get airport information through skyscanner API"""
_url = _data = _response = _places = _db = None
_airport_info = []
def __init__(self, url):
self._url = url
self._db = ConnectDB('root', '', 'search')
self._db.open()
def request_api(self):
"""Get data from API in JSON format"""
self._response = requests.get(self._url)
self._response.encoding = 'utf-8'
self._data = self._response.json()
self._places = self._data['Places']
if self._response.status_code == 200:
print "Request successfully."
return self._data
def filter_data(self):
"""Filter necessary data from API"""
for info in self._places:
if info.has_key('IataCode'):
del info['SkyscannerCode']
del info['PlaceId']
del info['CityId']
del info['Type']
self._airport_info.append(info)
return self._airport_info
def create_json_table(self, table_name):
"""Create table to import JSON data"""
query = """CREATE TABLE {} (
iata_code VARCHAR(3) NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
city_name VARCHAR(50) NOT NULL,
country_name VARCHAR(50) NOT NULL,
PRIMARY KEY (iata_code)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;"""
self._db.create_table(query, table_name)
def import_json(self, table_name):
"""Import JSON to database"""
query = "INSERT INTO {} ".format(table_name)
for info in self._airport_info:
value = """VALUES ("{}", "{}", "{}", "{}")
ON DUPLICATE KEY UPDATE iata_code = iata_code;""".format(
info['IataCode'], info['Name'], info['CityName'],
info['CountryName'])
self._db.insert(query + value, table_name)
self._db.close()
def write_file(self, data_str, file_name):
"""Open and write data to file with extension json"""
file_json = open('./json/' + file_name, 'w+')
file_json.write(data_str)
file_json.close()
print "Write data to '{}' successfully.".format(file_name)
def json_str(self, data_json):
"""Convert JSON to STRING"""
return json.dumps(data_json, indent=4 * ' ')
def test(self):
"""Test filter data from API"""
data = self.request_api()
self.write_file(self.json_str(data), 'data.json')
filter_data = self.filter_data()
self.write_file(self.json_str(filter_data), 'filter.json')
def __init__(self, url):
self._url = url
self._db = ConnectDB('root', '', 'search')
self._db.open()
from connectdb import ConnectDB
connection = ConnectDB()
cur = connection.cursor()
class dbTable:
def __init__(self):
pass
from connectdb import ConnectDB
cdb = ConnectDB()
connection = cdb.connection()
cur = connection.cursor()
class dbTable:
dbname = None
def __init__(self, dbname):
self.dbname = dbname
sql = "create table if not exists " + dbname + "()"
dbTable.executeUpdate(self, sql)
def column(self, columnname, datatype, pkey):
try:
sqlwhilesy = "alter table " + self.dbname + " add column " + columnname + " " + datatype + ";"
if pkey == True:
sqlwhilesy = sqlwhilesy + "alter table " + self.dbname + " alter column " + columnname + " set not null; alter table " + self.dbname + " ADD PRIMARY KEY (" + columnname + ");"
sql = "DO $$ BEGIN BEGIN " + sqlwhilesy + " EXCEPTION WHEN duplicate_column THEN RAISE NOTICE ''; END; END;$$"
dbTable.executeUpdate(self, sql)
