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)