def getUserByCredTask(self, username, password):
try:
connection = my_sql.mysql_get_connection()
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
sql = "select user_id, role_type from karamdb.user where user_name = %s and password_hash = %s"
val = (username, password)
print("********userName")
print(username, password)
cursor.execute(sql, val)
resp = cursor.fetchall()
print("********resp")
print(resp)
if len(resp) != 0:
return resp[0][0], resp[0][1]
return -1, -1
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if connection.is_connected():
cursor.close()
#connection.close()
print("MySQL connection is closed")
def createContractorTask(self,contractor):
connection = mysql_get_connection()
try:
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
statement = "Insert into karamdb.contractor"
colNames = "(contractor_id, first_name, phone_number, address)"
colValues = "VALUES (%s,%s,%s,%s)"
sql = statement + colNames + colValues
# TODO how to make it multiline
val = (
contractor.getContractorId(), contractor.getFirstname(),
contractor.getPhoneNumber(), contractor.getAddress())
cursor.execute(sql, val)
connection.commit()
print("Inserted successfully in contractor table")
return "SUCCESS"
except Error as e:
print("Error while inserting into contractor table", e)
return str(e)
finally:
if (connection.is_connected()):
cursor.close()
def get_autoincrement_id(self):
connection = my_sql.mysql_get_connection()
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
sql = "select LAST_INSERT_ID()"
cursor.execute(sql)
return cursor.fetchall()[0][0]
def updateLaborerTask(self, laborer):
connection = my_sql.mysql_get_connection()
try:
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
sql = "update karamdb.laborer set "
values = []
if (laborer.getFirstname()):
sql += "first_name = %s,"
values.append(laborer.getFirstname())
if (laborer.getLastname()):
sql += "last_name = %s,"
values.append(laborer.getLastname())
if (laborer.getPhoneNumber()):
sql += "phone_number = %s,"
values.append(laborer.getPhoneNumber())
if (laborer.getAddress()):
sql += "address = %s,"
values.append(laborer.getAddress())
if (laborer.getAadharStatus()):
sql += "adhar_card_status = %s,"
values.append(laborer.getAadharStatus())
if (laborer.getAadharNo()):
sql += "adhar_card_number = %s,"
values.append(laborer.getAadharNo())
if (laborer.getPanCard()):
sql += "pan_card = %s,"
values.append(laborer.getPanCard())
if (laborer.getSkill()):
sql += "skill = %s,"
values.append(laborer.getSkill())
if (laborer.getActiveInd()):
sql += "active_ind = %s,"
values.append(laborer.getActiveInd())
if (laborer.getPrefLoc()):
sql += "preferred_job_location = %s,"
values.append(laborer.getPrefLoc())
sql = sql.rstrip(',')
print(sql)
print(values)
cursor.execute(sql, values)
connection.commit()
#print("Updated successfully laborer table for " + laborer.getLaborerId())
return "SUCCESS"
except Error as e:
print("Error while connecting to MySQL", e)
return str(e)
finally:
if (connection.is_connected()):
cursor.close()
def getNewUserId(self):
connection = mysql_get_connection()
try:
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
sql = "SELECT max(user_id) FROM karamdb.user"
cursor.execute(sql)
result = cursor.fetchone()
return int(result[0])
except Error as e:
print("Error while connecting to MySQL", e)
return str(e)
finally:
if (connection.is_connected()):
cursor.close()
def deleteByIdTask(id):
try:
connection = my_sql.mysql_get_connection()
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
sql = "delete from karamdb.person where person_id = %s"
val = (id,)
cursor.execute(sql, val)
connection.commit()
record = cursor.fetchone()
print("You're connected to database: ", record)
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if (connection.is_connected()):
cursor.close()
#connection.close()
print("MySQL connection is closed")
def getAllContractorTask(self):
connection = my_sql.mysql_get_connection()
try:
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
sql = "SELECT * FROM karamdb.contractor"
cursor.execute(sql)
row_headers = [x[0] for x in cursor.description]
rec = cursor.fetchall()
json_data = []
for res in rec:
json_data.append(dict(zip(row_headers, res)))
return json_data
except Error as e:
print("Error while connecting to MySQL", e)
return str(e)
finally:
if (connection.is_connected()):
cursor.close()
def getPersonByIdTask(self, id):
try:
connection = my_sql.mysql_get_connection()
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
sql = "select * from karamdb.person where person_id = %s"
val = (id,)
cursor.execute(sql, val)
rec = cursor.fetchall()
result = list()
for x in rec:
result.append(x)
return result
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if connection.is_connected():
cursor.close()
#connection.close()
print("MySQL connection is closed")
def createJobTask(self, job):
connection = my_sql.mysql_get_connection()
try:
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
statement = "Insert into karamdb.job"
colNames = "(labour_id,contractor_id,active_ind)"
colValues = "VALUES (%s,%s,%s)"
sql = statement+colNames+colValues
val = (job.getLaborerId(),job.getContractorId(),job.getActiveInd())
cursor.execute(sql,val)
connection.commit()
print("Inserted successfully in job table")
return "SUCCESS"
except Error as e:
print("Error while inserting into user table", e)
return str(e)
finally:
if (connection.is_connected()):
cursor.close()
def createUserTask(self, user):
connection = my_sql.mysql_get_connection()
try:
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
statement = "Insert into karamdb.user"
colNames = "(role_type,user_name,password_hash)"
colValues = "VALUES (%s,%s,%s)"
sql = statement+colNames+colValues
val = (user.getRoleType(),user.getUserName(),user.getPasswordHash())
cursor.execute(sql,val)
connection.commit()
print("Inserted successfully in user table")
return "SUCCESS"
except Error as e:
print("Error while inserting into user table", e)
return str(e)
finally:
if (connection.is_connected()):
cursor.close()
def createLaborerTask(self, laborer):
connection = my_sql.mysql_get_connection()
try:
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
statement = "Insert into karamdb.laborer"
colNames = "(laborer_id,parent_id, first_name, last_name, gender, phone_number, address,aadhar_card_number,aadhar_card_status,pan_card,skills,active_ind,preferred_job_location)"
colValues = "VALUES (LAST_INSERT_ID(),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
sql = statement + colNames + colValues
val = (laborer.getParentId(), laborer.getFirstname(),
laborer.getLastname(), laborer.getGender(), laborer.getPhoneNumber(),
laborer.getAddress(), laborer.getAadharNo(), laborer.getAadharStatus(),
laborer.getPanCard(), laborer.getSkill(), laborer.getActiveInd(),
laborer.getPrefLoc())
print (val)
cursor.execute(sql, val)
connection.commit()
skills = laborer.getSkill().split(",")
# Insert into skill table
for skill in skills:
#TODO refactor this code to use select only once
sql = "select count(name) from skills where name=(%s)"
val = (skill,)
cursor.execute(sql,val)
res = cursor.fetchone()
if int(res[0]) == 0:
sql = "Insert into karamdb.skills (name,description) VALUES (%s,%s)"
val = (skill,"")
cursor.execute(sql,val)
connection.commit()
sql = "select name from skills where name=(%s)"
val = (skill,)
cursor.execute(sql,val)
res = cursor.fetchone()
skillName = res[0]
sql = "Insert into karamdb.laborerSkillRelation (laborer_id, skill_name) VALUES (%s,%s)"
val = (laborer.getLaborerId(), skillName)
cursor.execute(sql,val)
connection.commit()
locations = laborer.getPrefLoc().split(",")
# Insert into skill table
for loc in locations:
#TODO refactor this code to use select only once
sql = "select count(*) from preferredJobLocation where STATE= %s and city = %s and district = %s"
val = (loc, loc, loc)
cursor.execute(sql,val)
res = cursor.fetchone()
if int(res[0]) == 0:
sql = "Insert into karamdb.preferredJobLocation (STATE,CITY,district) VALUES (%s,%s,%s)"
val = (loc,loc,loc)
cursor.execute(sql,val)
connection.commit()
sql = "select id from preferredJobLocation where STATE= %s and city = %s and district = %s"
val = (loc, loc, loc)
cursor.execute(sql,val)
res = cursor.fetchone()
locId = int(res[0])
sql = "Insert into karamdb.laborerPreferredLocationRelation (laborer_id, location_id) VALUES (%s,%s)"
val = (laborer.getLaborerId(), locId)
cursor.execute(sql,val)
connection.commit()
print("Inserted successfully in laborer table")
return "SUCCESS"
except Error as e:
print("Error while inserting into laborer, skill, laborerSkillRelation table", e)
return str(e)
finally:
if (connection.is_connected()):
cursor.close()
#connection.close()
print("MySQL connection is closed")
def getAllLaborerTask(self, skills, locations):
connection = my_sql.mysql_get_connection()
try:
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
cursor = connection.cursor()
sql = "select * from karamdb.laborer"
if skills and not locations:
#TODO this is not good for scalability we need to have SkillEq,SkillLike,SkillIn operation in JSON Request
# that will map to differetn sql querries here. Even if we are using OOP sql builder
sql = "select * from karamdb.laborer where laborer_id in ("
sql = sql + "select laborer_id from laborerSkillRelation where"
skillNames = skills.split(',')
values = []
for skillName in skillNames:
sql = sql + " skill_name like %s " + "OR"
values.append("%"+skillName+"%")
sql = sql[:-2]
sql = sql + ")"
cursor.execute(sql, values)
elif locations and not skills:
sql = "select * from karamdb.laborer where laborer_id in ("
sql = sql + "select laborer_id from laborerPreferredLocationRelation where location_id in ("
sql = sql + "select id from preferredJobLocation where"
locationNames = locations.split(',')
values = []
for locationName in locationNames:
sql = sql + " state like %s " + "OR"
values.append("%"+locationName+"%")
sql = sql[:-2]
sql = sql + "))"
cursor.execute(sql, values)
elif locations and skills:
# TODO make above code modular so that it can be resused for this case
sql = "select * from karamdb.laborer where laborer_id in ("
sql = sql + "select labSkill.laborer_id from laborerSkillRelation labSkill INNER JOIN\
(select laborer_id from laborerPreferredLocationRelation where location_id in\
(select id from preferredJobLocation where"
locationNames = locations.split(',')
values = []
for locationName in locationNames:
sql = sql + " state like %s " + "OR"
values.append("%"+locationName+"%")
sql = sql[:-2]
sql = sql + "))"
sql = sql + " labLoc ON labSkill.laborer_id = labLoc.laborer_id where "
skillNames = skills.split(',')
for skillName in skillNames:
sql = sql + " labSkill.skill_name like %s " + "OR"
values.append("%"+skillName+"%")
sql = sql[:-2]
sql = sql + ")"
cursor.execute(sql, values)
else:
cursor.execute(sql)
row_headers = [x[0] for x in cursor.description]
rec = cursor.fetchall()
json_data = []
for res in rec:
json_data.append(dict(zip(row_headers, res)))
print (json_data)
return json_data
except Error as e:
print("Error while connecting to MySQL", e)
return str(e)
finally:
if (connection.is_connected()):
cursor.close()