def etl_job():
data = json.load(open('/home/ubuntu/ti_etl/secrets.json'))
logger = helpers.setup_logging()
s3_client = boto3.client(
's3',
aws_access_key_id=data['aws_access_key_id'],
aws_secret_access_key=data['aws_secret_access_key'])
s3_resource = boto3.resource(
's3',
aws_access_key_id=data['aws_access_key_id'],
aws_secret_access_key=data['aws_secret_access_key'])
keys = []
resp = s3_client.list_objects_v2(Bucket='dealer-churn-analysis')
for obj in resp['Contents']:
keys.append(obj['Key'])
for key in keys:
names = key.split("/")
obj = s3_resource.Bucket('dealer-churn-analysis').Object(
helpers.zip_file_name())
file_name = 'praxis/etl/logs/log_{file}.txt'.format(file=helpers.date())
obj_log = s3_resource.Bucket('dealer-churn-analysis').Object(file_name)
buffer = io.BytesIO(obj.get()["Body"].read())
zip_file = zipfile.ZipFile(buffer, 'r')
logger.info("Name of csv in zip file :%s", zip_file.namelist())
logs = ""
dataframe = pd.DataFrame()
for name_of_zipfile in zip_file.namelist():
zip_open = pd.read_csv(zip_file.open(name_of_zipfile))
dataframe['created_at'] = pd.Series([datetime.datetime.now()] *
len(zip_open))
dataframe['last_updated_at'] = pd.Series([datetime.datetime.now()] *
len(zip_open))
zip_open = pd.concat([dataframe, zip_open], axis=1)
zip_open = zip_open.dropna()
table_name = "{name}_table".format(
name=name_of_zipfile.replace('.csv', ''))
#print (zip_open)
try:
zip_open.to_sql(name=name_of_zipfile.replace('.csv', ''),
con=database.db_connection(),
if_exists='append',
index=False)
except SQLAlchemyError as sqlalchemy_error:
print(sqlalchemy_error)
logs = '\n{table_name}\n{error}\n{logs}'.format(
logs=logs, error=sqlalchemy_error, table_name=table_name)
logger.error(" %s", sqlalchemy_error)
database.db_connection().execute('SET FOREIGN_KEY_CHECKS=1;')
end_time = datetime.datetime.now()
logger.info("End time of program : %s", end_time)
logs = '{logs} \nstart_time : {start_time} \nend_time : {end_time}'.format(
start_time=helpers.start_time(), logs=logs, end_time=end_time)
print(logs)
obj_log.put(Body=logs)
def get_msf():
try:
query = """UPDATE martha.nifty_excel_data_all
SET MSF = ( @msf := CASE WHEN msf IS NULL
THEN (closed - @msf) * 0.33 + @msf
ELSE msf
END )
ORDER BY Sr;"""
db_connection("localhost", "root", "root", "martha", query)
except Exception as e:
print("Error while getting msf !")
print("Source location : modules >> arithmatic_modules >> get_msf()")
print("ERROR : {}".format(e))
def get_dimentions():
con = dbc.db_connection()
cursor = dbc.execute_query(con, 'desc restaurant_features')
rows = cursor.fetchall()
dimentions = []
for row in rows:
if row != 'restaurant_business_id':
dimentions.append(row[0])
dbc.lose_connection(con)
return dimentions
def get_yelp_data():
# print 'got request'
con = dbc.db_connection()
cursor = dbc.execute_query(con, restaurant_features_query)
rows = cursor.fetchall()
test_x_data = []
test_y_data = []
# print rows
for row in rows:
# print row
test_touple = []
index = 0
for field in row:
if index < 27:
if index == 1:
if field is None or field not in alcohol_mapping:
test_touple.append(0)
else:
test_touple.append(alcohol_mapping[field])
# print type(alcohol_mapping[field])
elif index == 11:
if field is None or field not in attire_mapping:
test_touple.append(0)
else:
test_touple.append(attire_mapping[field])
elif index == 16:
if field is None or field not in noise_mapping:
test_touple.append(0)
else:
test_touple.append(noise_mapping[field])
elif index == 23:
test_touple.append(int(field))
else:
test_touple.append(field)
else:
test_y_data.append(float(field))
index = index + 1
test_x_data.append(test_touple)
dbc.close_connection(con)
# print test_data
test_data = {'data': test_x_data, 'target': test_y_data}
# print test_data
# print len(test_data['data'])
# print len(test_data['target'])
# print test_data['data'][0]
return test_data
import mysql.connector
from database_connection import db_connection
query = """UPDATE martha.nifty_excel_data_all
SET MSF = ( @msf := CASE WHEN msf IS NULL
THEN (closed - @msf) * 0.33 + @msf
ELSE msf
END )
ORDER BY Sr;"""
db_connection("localhost", "root", "root", "martha", query)
def create_tables():
logger = helpers.setup_logging()
logger.info("Start time of program :%s", helpers.start_time())
db = database.db_connection()
meta = MetaData(bind=db)
logger.info("INFO: Creating tables schema")
try:
table_realm_type = Table(
'realm_type', meta,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('name', TEXT(50), nullable=True),
Column('category', TEXT(50), nullable=True),
Column('is_active', BOOLEAN, nullable=True),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
table_category = Table(
'category', meta,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('name', TEXT(50), nullable=True),
Column('is_active', BOOLEAN, nullable=True),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
table_dealer_master = Table(
'dealer_master', meta,
Column('dealer_id', Integer, primary_key=True,
autoincrement=False),
Column('dealer_name', TEXT(255), nullable=True),
Column('realm_name', TEXT(50), nullable=True),
Column('customer_id', Integer, nullable=True),
Column('customer_name', TEXT(255), nullable=True),
Column('category_name', TEXT(50), nullable=True),
Column('city', TEXT(255), nullable=True),
Column('state_code', TEXT(10), nullable=False),
Column('zip_code', Integer, nullable=True),
Column('country_code', TEXT(10), nullable=False),
Column('area_code', Integer, nullable=True),
Column('start_date', TIMESTAMP, nullable=True),
Column('expire_date', TIMESTAMP, nullable=True),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
table_ads_data = Table(
'ads_data', meta,
Column('ad_id', Integer, primary_key=True, autoincrement=False),
Column('dealer_id', Integer,
ForeignKey('dealer_master.dealer_id')),
Column('make_id', Integer, nullable=True),
Column('make_name', TEXT, nullable=True),
Column('ad_status', TEXT, nullable=True),
Column('created_at', TIMESTAMP, nullable=True),
Column('last_updated_at', TIMESTAMP, nullable=True),
Column('expire_date', TIMESTAMP, nullable=True),
Column('create_date', TIMESTAMP, nullable=True),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
table_rep_master = Table(
'rep_master', meta,
Column('rep_id', Integer, primary_key=True, autoincrement=False),
Column('rep_name', TEXT(255), nullable=True),
Column('is_active', BOOLEAN, nullable=True),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
table_ManagerMaster = Table(
'manager_master', meta,
Column('manager_id',
Integer,
primary_key=True,
autoincrement=False),
Column('manager_name', TEXT(255), nullable=True),
Column('is_active', BOOLEAN, nullable=True),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
table_DealerRepManagerMapping = Table(
'dealer_rep_manager_mapping', meta,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('dealer_id', Integer,
ForeignKey('dealer_master.dealer_id')),
Column('rep_id', Integer, ForeignKey('rep_master.rep_id')),
Column('manager_id', Integer,
ForeignKey('manager_master.manager_id')),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
table_Billing = Table(
'billing_data', meta,
Column('dealer_id', Integer,
ForeignKey('dealer_master.dealer_id')),
Column('billing_id',
Integer,
primary_key=True,
autoincrement=False),
Column('bill_amount', Integer, nullable=True),
Column('billing_month', Integer, nullable=True),
Column('billing_year', Integer, nullable=True),
Column('payment_method', TEXT(255), nullable=True),
Column('package_name', TEXT(255), nullable=True),
Column('dealer_change_status', TEXT(2), default='A'),
Column('billing_change_previous_month', Integer, nullable=True),
Column('billing_date', TIMESTAMP, nullable=True),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
table_CoopData = Table(
'co_op_data', meta,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('dealer_id', Integer,
ForeignKey('dealer_master.dealer_id')),
Column('co_op_month', Integer, nullable=True),
Column('co_op_year', Integer, nullable=True),
Column('co_op_flag', BOOLEAN, nullable=True),
Column('created_at', TIMESTAMP, onupdate=datetime.datetime.now),
Column('last_updated_at',
TIMESTAMP,
onupdate=datetime.datetime.now))
meta.create_all(db)
except SQLAlchemyError as sqlalchemy_error:
logger.error("ERROR: %s", sqlalchemy_error)
sys.exit()
logger.info("SUCCESS: Tables created")
insert_to_realm = table_realm_type.insert()
try:
insert_to_realm.execute(
{
'id': 1,
'name': 'CYCLE',
'category': 'COMMERCIAL',
'is_active': 1
}, {
'id': 2,
'name': 'RV',
'category': 'COMMERCIAL',
'is_active': 1
}, {
'id': 3,
'name': 'TRUCK',
'category': 'RECREATIONAL',
'is_active': 1
}, {
'id': 4,
'name': 'EQUIPMENT',
'category': 'RECREATIONAL',
'is_active': 1
})
except SQLAlchemyError as err:
print(err)
logger.error("ERROR: %s", err)
logger.info("SUCCESS: Inserted values")
return meta
from database_connection import db_connection
from customer_package import customer as c
session = db_connection()
def login_method(username, password):
print("hi")
user = session.query(c.Customer).filter_by(username=username,
password=password).first()
if user != None:
id = user.customer_id
print(id)
return True, id
else:
return False, username
def add_to_db():
insert_into_db(db_connection(), log_data)