def ETL_first():
"""
Runs the ETL pipeline monthly on the docker locally.
"""
params = config(section="api")
month = date.today().month
try:
resp = requests.get("https://calendarific.com/api/v2/holidays?\
api_key={token}&\
country=PL&\
year=2021&month={month}}".format(token=params["token"], month=month))
resp.raise_for_status()
except requests.exceptions.HTTPError as err:
print(err)
data = resp.json()
holiday_df = custom_json_to_df(data)
if check_if_valid_data(holiday_df):
print("Valid data")
else:
raise Exception("Empty dataframe")
# get prarams for postgresql
params = config(filename='database\database.ini', section='postgresql')
#The db ip address of container
with postgresEngine(**params) as db:
engine, connect = db
sql_query = """
CREATE TABLE IF NOT EXISTS holidays(
name VARCHAR(200),
description VARCHAR(255),
country VARCHAR(200),
date VARCHAR(200),
holiday_type VARCHAR(200),
PRIMARY KEY (country,date)
)
"""
connect.execute(sql_query)
# cur.execute(sql_query)
# conn.commit()
try:
holiday_df.to_sql("holidays",
engine,
index=False,
if_exists='append')
except Exception as e:
print(e)
print("Data already exists in the database")
print("Close database successfully")
def readImage(email= None, pathToSave= None):
""" read images from database """
conn = None
try:
# read database configuration
params = config()
# connect to the PostgresQL database
conn = psycopg2.connect(**params)
# create a new cursor object
cur = conn.cursor()
# find id of user
cur.execute("select id from users where email= %s", (email, ))
owner_id = cur.fetchone()[0]
# execute the SELECT statement
cur.execute(""" SELECT data_pic, upload_at, format_pic
FROM img
WHERE img.owner_id = %s """,
(owner_id,))
imgs = cur.fetchall()
for img in imgs:
open(os.path.join(pathToSave, str(img[1]) + '.' + img[2]), 'wb').write(img[0])
# close the communication with the PostgresQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def registerD(**info):
""" Register user in database """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
cur.execute("select email from users where email=%s", (info['email'], ))
# insert user to database if there is no user with this info
if not cur.fetchone():
queryCode = """insert into notverified(email, FirstName, LastName, password, phone, created_at, updated_at, user_type, regester_type, doctor_id)
values(%s, %s, %s, %s, %s, now(), now(), %s, %s, %s)"""
cur.execute(queryCode, (info['email'], info['FirstName'], info['LastName'], info['password'], info['phone'], 'Doctor', info['regester_type'], info['doctor_id']))
conn.commit()
else:
print("you have been registered before")
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def writeImage(imgPath= None, email= None):
""" Save image in img table of database """
conn = None
try:
# read data from a picture
pic = open(imgPath, 'rb').read()
format_pic = (imgPath.split("/")[-1].split("."))[-1]
# read database configuration
params = config()
# connect to the PostgresQL database
conn = psycopg2.connect(**params)
# create a new cursor object
cur = conn.cursor()
# find id of user
cur.execute("select id from users where email= %s", (email, ))
_id = cur.fetchone()[0]
queryCode = """insert into img(owner_id, data_pic, format_pic, upload_at)
values(%s, %s, %s, now())"""
cur.execute(queryCode, (_id, psycopg2.Binary(pic), format_pic, ))
conn.commit()
# close the communication with the PostgresQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def deleteAccount(email= None):
""" Delete user """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# change password query code
queryCode = """DELETE from users
where email= %s"""
cur.execute(queryCode, (email, ))
conn.commit()
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def changePass(new_Pass= None, email= None):
""" Change password of user """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# change password query code
queryCode = """UPDATE users set password= %s, updated_at= now()
where email= %s"""
cur.execute(queryCode, (new_Pass, email))
conn.commit()
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def userExist(email= None, password= None):
""" Check exist user in database """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
cur.execute("select email, password from users where email=%s AND password= %s", (email, password, ))
if cur.fetchone():
cur.close()
print("True")
return 1
else:
cur.close()
print("False")
return 0
except (Exception, psycopg2.DatabaseError) as error:
return -1
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def fetch(query, params=None):
""" Connect to the PostgreSQL database server """
conn = None
try:
# read connection parameters
configs = config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**configs)
# create a cursor
cur = conn.cursor()
# execute a statement
cur.execute(query, params)
# display the results of the query
db_version = cur.fetchall()
# return the results to the API
return db_version
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def read_all_devices():
commands = []
""" Read devices data """
commands.append("SELECT * FROM devices; ")
conn = None
deviceItems = []
try:
# read the connection parameters
params = config('database/database.ini')
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
cur = conn.cursor()
print("read_all_devices")
# create table one by one
for command in commands:
print(command)
cur.execute(command)
device_records = cur.fetchall()
for row in device_records:
# skip the device id row @ row[0]
device = Device(row[1], row[2], row[3], row[4], row[5])
deviceItems.append(device.toJSON())
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return deviceItems
def connect():
""" Connect to the PostgreSQL database server """
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# execute a statement
print('PostgreSQL database version:')
cur.execute('SELECT version()')
# display the PostgreSQL database server version
db_version = cur.fetchone()
print(db_version)
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
global conn
if __name__ == '__main__':
if conn is not None:
conn.close()
print('Database connection closed.')
else:
return conn
def connect(data):
""" Connect to the PostgreSQL database server """
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(command)
# for resource in get_resource_watches():
query = cur.mogrify("INSERT INTO baby_g ( {0} ) VALUES ( {1} )".format(
', '.join(data.keys()),
', '.join(['%s'] * len(data.values())),
), get_tuple(data))
cur.execute(query)
cur.execute("SELECT list_spec_icon FROM baby_g WHERE id_watches=1;")
print(cur.fetchall())
# for record in cur.fetchall():
# print(record)
cur.close()
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print("Done")
def connect():
""" Connect to the PostgreSQL database server """
conn = None
try:
params = config.config()
conn = psycopg2.connect(**params)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
return conn, conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
def insertUserData(val1,val2,val3,val4,val5,val6,val7,val8,val9):
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
sql_command="INSERT INTO crypto_user (user_id, first_name, last_name, email, pan_no, dob, mobile_no, exchange_account_id, status) VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}');".format(str(val1),str(val2),str(val3),str(val4),str(val5),str(val6),str(val7),str(val8),str(val9))
print(sql_command)
cur.execute(sql_command)
conn.commit()
print("Record inserted successfully")
cur.close()
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def insertTrade(val1,val2):
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
sql_command="INSERT INTO crypto_trade (user_id, trade_id) VALUES ('{}', '{}');".format(str(val1),str(val2))
print(sql_command)
cur.execute(sql_command)
conn.commit()
print("Record inserted successfully")
cur.close()
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def insertWalletHistory(val1,val2,val3,val4,val5,val6):
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
sql_command="INSERT INTO crypto_wallet_history( wallet_id, user_id, type, balance, updated_timestamp, status) VALUES ('{}', '{}', '{}', '{}', '{}', '{}');".format(str(val1),str(val2),str(val3),str(val4),str(val5),str(val6))
print(sql_command)
cur.execute(sql_command)
conn.commit()
print("Record inserted successfully")
cur.close()
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def updateWallet(val1,val2,val3,val4):
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
sql_command="UPDATE crypto_wallet SET balance='{}', updated_timestamp='{}' WHERE wallet_id='{}' and user_id='{}';".format(str(val3),str(val4),str(val1),str(val2))
print(sql_command)
cur.execute(sql_command)
conn.commit()
print("Record updated successfully")
cur.close()
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def delete(table):
sql = f"DELETE FROM {table} WHERE 1=1;"
conn = None
id = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(sql)
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return id
def getAllData(table):
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
sql_command = "SELECT * FROM {};".format(str(table))
print (sql_command)
cur.execute(sql_command)
result = cur.fetchall()
cur.close()
return result
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def insertPort(port, ip):
""" insert a new port into the port table """
sql = """INSERT INTO ports(port_number, ip_address)
VALUES(%s, %s);"""
conn = None
id = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(sql, (port, ip, ))
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return id
def getSpecificDataList(table,col,param):
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
sql_command = "SELECT * FROM {} where {}='{}';".format(str(table),str(col),str(param))
print ("sql_command:",sql_command)
cur.execute(sql_command)
result = cur.fetchall()
print("getSpecificDatList result:",result)
cur.close()
return result
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def deleteWallet(val1,val2,val3,val4):
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
update_Wallet_query="UPDATE crypto_wallet SET updated_timestamp='{}', status='{}' WHERE {}='{}';".format(str(val1),str(val2),str(val3),str(val4))
print(update_Wallet_query)
cur.execute(update_Wallet_query)
update_Wallet__history_query="UPDATE crypto_wallet_history SET updated_timestamp='{}', status='{}' WHERE {}='{}';".format(str(val1),str(val2),str(val3),str(val4))
print(update_Wallet__history_query)
cur.execute(update_Wallet__history_query)
conn.commit()
print("Record updated successfully")
cur.close()
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def insert(ip, os, name, vendor, osFamily, osGen, vulns, openPorts):
""" insert a new device into the device table """
sql = """INSERT INTO devices(ip_address, os, name, num_of_vulns, vendor, os_family, os_gen, open_ports)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s) RETURNING ip_address;"""
conn = None
id = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(sql, (ip, os, name, vulns, vendor, osFamily, osGen, openPorts, ))
id = cur.fetchone()[0]
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return id
def connect():
""" Connect to the PostgreSQL database server """
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute('SELECT version()')
db_version = cur.fetchone()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection is successfull.')
def __init__(self, config_path):
# Class Constructor
try:
params = config(config_path) # Get parameters for Connection
self.conn = psycopg2.connect(**params) # Postgres Database
self.curs = self.conn.cursor(
) # Cursor Object for Database Operations
# Check if schema like that exists, if not then create and add data
initQuery = f"""select count(schema_name) check_existence
from information_schema.schemata
where schema_name = '{schema_name}' """
self.curs.execute(initQuery) # Execute Command
check = self.curs.fetchone() # Get Data
if check[0] == 0:
self.initialize_database()
except psycopg2.Error as err:
print(f'Connection Error: {err}')
def doctorRegisterDone(email= None):
""" save doctors in users database """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# insert user to database if there is no user with this info
queryCodeInsert = """insert into users(email, FirstName, LastName, password, phone, created_at, updated_at, user_type, regester_type, doctor_id)
select email, FirstName, LastName, password, phone, created_at, updated_at, user_type, regester_type, doctor_id from notverified
where email=%s"""
cur.execute(queryCodeInsert, (email, ))
conn.commit()
# remove doctor from not verified
queryCodeRemove = """delete from notverified where email=%s"""
cur.execute(queryCodeRemove, (email, ))
conn.commit()
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def registerOP(**info):
""" Register user in database """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# insert user to database
try:
queryCode = """insert into users(email, FirstName, LastName, password, phone, created_at, updated_at, user_type, regester_type, doctor_id)
values(%s, %s, %s, %s, %s, now(), now(), %s, %s, %s)"""
cur.execute(queryCode, (info['email'], info['FirstName'], info['LastName'], info['password'], info['phone'], info['user_type'], info['regester_type'], info['doctor_id']))
conn.commit()
cur.close()
return 1
except:
cur.close()
print("user has been registered before")
return 0
# close the communication with the PostgreSQL
except (Exception, psycopg2.DatabaseError) as error:
print(error)
return -1
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def connect(self):
params = config()
print('Connecting to the PostgreSQL database...')
connection = psycopg2.connect(**params)
self.connection = connection
return self.connection
For the full list of settings and their values, see
https://docs.djangoproject.com/en/2.1/ref/settings/
"""
import os
import logging
from database.config import config
import scraping.emaillogin as el
try:
from c import *
DEBUG = False
except:
DEBUG = True
database_params = config()
#import c
#print(c.DEBUG)
#print(DEBUG)
# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/2.1/howto/deployment/checklist/
# SECURITY WARNING: keep the secret key used in production secret!
try:
SECRET_KEY = os.environ['SECRET_KEY']
except:
def __init__(self, ini_path, file_path, source, sink):
self.database = DbModel(config(ini_path))
self.data = None
self.file_path = file_path
self.source = source
self.sink = sink
import requests
from helpers.data_manipulation import custom_json_to_df
from database.config import config
from helpers.data_manipulation import custom_json_to_df
from helpers.data_validation import check_if_valid_data
from database.config import config
from database.engine import postgresEngine
# https://calendarific.com/api-documentation
if __name__ == "__main__":
params = config(section="api")
try:
resp = requests.get(
"https://calendarific.com/api/v2/holidays?\
api_key={token}&\
country=PL&\
year=2021&month=5".format(token=params["token"])
)
resp.raise_for_status()
except requests.exceptions.HTTPError as err:
print(err)
data = resp.json()
holiday_df = custom_json_to_df(data)
