811 lines
27 KiB
Python
811 lines
27 KiB
Python
|
|
# import mysql.connector
|
|
import re
|
|
from werkzeug.security import generate_password_hash
|
|
import json
|
|
|
|
import pyodbc
|
|
|
|
# Set up the connection string
|
|
connection_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=dbilayer;UID=sa;PWD=Ganesh@m0477;'
|
|
# Connect to the database
|
|
db_connection = pyodbc.connect(connection_string)
|
|
|
|
# db_connection = mysql.connector.connect(
|
|
# host="localhost",
|
|
# user="root",
|
|
# passwd="Machint@123",
|
|
# database="dbiLayer"
|
|
# )
|
|
|
|
# db_connection = mysql.connector.connect(
|
|
# host="10.11.0.24",
|
|
# user="root",
|
|
# passwd="Machint@2022",
|
|
# database="reactpropmod",
|
|
# port=3303
|
|
# )
|
|
|
|
# Registration for user
|
|
|
|
def registerUserNow(tablename, fields, userData):
|
|
my_database=db_connection.cursor()
|
|
username=userData['username']
|
|
useremail=userData['useremail']
|
|
userpassword=userData['userpassword']
|
|
userdesig=userData['userdesig']
|
|
status=userData['status']
|
|
try:
|
|
query_for_user_existence="SELECT * FROM {} WHERE username='{}'".format(tablename, username)
|
|
if not re.fullmatch(r'[^@]+@[^@]+\.[^@]+', useremail):
|
|
msg='Invalid Email Address !'
|
|
elif not re.fullmatch(r'[A-Za-z0-9]+', username):
|
|
msg='Username must contain only characters and numbers !'
|
|
elif (not username) or (not userpassword) or (not useremail):
|
|
msg='Please provide the complete details !'
|
|
elif username:
|
|
try:
|
|
my_database.execute(query_for_user_existence)
|
|
user_exist=my_database.fetchone()
|
|
if user_exist:
|
|
msg='User already exists!'
|
|
else:
|
|
hashed_password=generate_password_hash(userpassword)
|
|
sql_query="INSERT INTO {}({}) VALUES('{}', '{}','{}','{}','{}')".format(tablename, fields,username,useremail,hashed_password,userdesig,status)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
db_connection.commit()
|
|
msg='User Register/Created Successfully !'
|
|
except:
|
|
msg='User Creation Failed'
|
|
except:
|
|
msg='Failed'
|
|
except:
|
|
msg='Failed'
|
|
# db_connection.close()
|
|
return msg
|
|
|
|
# Login User
|
|
|
|
def getUserActivity(tablename, fields, userData):
|
|
my_database=db_connection.cursor(dictionary=True)
|
|
sql_query="SELECT {} FROM {} WHERE username='{}'".format(fields, tablename, userData['username'])
|
|
try:
|
|
my_database.execute(sql_query)
|
|
output=my_database.fetchone()
|
|
except:
|
|
output={"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
print(output)
|
|
return output
|
|
|
|
|
|
|
|
|
|
|
|
def readallWithoutCondition(tablename, fields):
|
|
my_database = db_connection.cursor()
|
|
sql_query = "SELECT {} FROM {}".format(fields,tablename)
|
|
print(sql_query)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
rows = my_database.fetchall()
|
|
columns = [column[0] for column in my_database.description]
|
|
results = [dict(zip(columns, row)) for row in rows]
|
|
retval = "Success"
|
|
return results
|
|
except :
|
|
retval = "Failure"
|
|
return retval
|
|
|
|
def readallWithCondition(tablename, fields, condition):
|
|
my_database = db_connection.cursor()
|
|
sql_query = "SELECT {} FROM {} WHERE {}".format(fields,tablename,condition)
|
|
print(sql_query)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
rows = my_database.fetchall()
|
|
columns = [column[0] for column in my_database.description]
|
|
results = [dict(zip(columns, row)) for row in rows]
|
|
retval = "Success"
|
|
return results
|
|
except :
|
|
retval = "Failure"
|
|
return retval
|
|
|
|
def readOneWithcondition(tablename, fields, cond):
|
|
my_database = db_connection.cursor()
|
|
sql_query = "SELECT {} FROM {} WHERE {}".format(fields,tablename,cond)
|
|
print(sql_query)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
rows = my_database.fetchall()
|
|
columns = [column[0] for column in my_database.description]
|
|
results = [dict(zip(columns, row)) for row in rows]
|
|
print(rows)
|
|
return results
|
|
except :
|
|
retval = "Failure"
|
|
return retval
|
|
|
|
def insertIntoDBWithoutCondition(tablename,fields,data):
|
|
my_database=db_connection.cursor()
|
|
sql_query="INSERT INTO {} ({}) VALUES({})".format(tablename, fields, data)
|
|
print(sql_query)
|
|
# try:
|
|
my_database.execute(sql_query)
|
|
db_connection.commit()
|
|
retval = "Success"
|
|
return retval
|
|
# except:
|
|
# retval = "Failure"
|
|
# return retval
|
|
|
|
def updateValuesIndb(tablename,updatestatement,condition):
|
|
my_database = db_connection.cursor()
|
|
sql_statement = "UPDATE " + tablename + " SET " + updatestatement + " WHERE " + condition + ""
|
|
print(sql_statement)
|
|
# try:
|
|
my_database.execute(sql_statement)
|
|
db_connection.commit()
|
|
retval = "Success"
|
|
return retval
|
|
# except:
|
|
# retval = "Failure"
|
|
# return retval
|
|
|
|
def updateDataWithCondition(tablename,updateStatement,condition):
|
|
pass
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
'''
|
|
def registerUserNow(tablename, fields, userData):
|
|
my_database=db_connection.cursor()
|
|
username=userData['username']
|
|
useremail=userData['useremail']
|
|
userpassword=userData['userpassword']
|
|
userdesig=userData['userdesig']
|
|
status=userData['status']
|
|
try:
|
|
query_for_user_existence="SELECT * FROM {} WHERE username='{}'".format(tablename, username)
|
|
if not re.fullmatch(r'[^@]+@[^@]+\.[^@]+', useremail):
|
|
msg='Invalid Email Address !'
|
|
elif not re.fullmatch(r'[A-Za-z0-9]+', username):
|
|
msg='Username must contain only characters and numbers !'
|
|
elif (not username) or (not userpassword) or (not useremail):
|
|
msg='Please provide the complete details !'
|
|
elif username:
|
|
try:
|
|
my_database.execute(query_for_user_existence)
|
|
user_exist=my_database.fetchone()
|
|
if user_exist:
|
|
msg='User already exists!'
|
|
else:
|
|
hashed_password=generate_password_hash(userpassword)
|
|
sql_query="INSERT INTO {}({}) VALUES('{}', '{}','{}','{}','{}')".format(tablename, fields,username,useremail,hashed_password,userdesig,status)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
db_connection.commit()
|
|
msg='User Register/Created Successfully !'
|
|
except:
|
|
msg='User Creation Failed'
|
|
except:
|
|
msg='Failed'
|
|
except:
|
|
msg='Failed'
|
|
# db_connection.close()
|
|
return msg
|
|
|
|
# Login User
|
|
|
|
def getUserActivity(tablename, fields, userData):
|
|
my_database=db_connection.cursor(dictionary=True)
|
|
sql_query="SELECT {} FROM {} WHERE username='{}'".format(fields, tablename, userData['username'])
|
|
try:
|
|
my_database.execute(sql_query)
|
|
output=my_database.fetchone()
|
|
except:
|
|
output={"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
print(output)
|
|
return output
|
|
|
|
# Return List of Available stored single column values for any table
|
|
|
|
def returnspecificlist(field, tablename):
|
|
my_database=db_connection.cursor(dictionary=True)
|
|
sql_query="SELECT {} FROM {}".format(field, tablename)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
stored_column_data=my_database.fetchall()
|
|
final_stored_column_data_list=[]
|
|
for name_dict in stored_column_data:
|
|
final_stored_column_data_list.append(name_dict[field])
|
|
except:
|
|
final_stored_column_data_list=[]
|
|
return final_stored_column_data_list
|
|
|
|
#Inseret Values into any table except propset
|
|
|
|
def insertintotablewocond(tablename, fields, data, namefield):
|
|
final_stored_names_list=returnspecificlist(namefield, tablename)
|
|
print(final_stored_names_list)
|
|
my_database=db_connection.cursor()
|
|
sql_query="INSERT INTO {}({}) VALUES({})".format(tablename, fields, data)
|
|
print(sql_query)
|
|
print(final_stored_names_list)
|
|
sql_statement = "SELECT " + fields + " from " + tablename + " GROUP BY" + fields
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
db_connection.commit()
|
|
|
|
my_database.execute("SELECT LAST_INSERT_ID()")
|
|
last_inserted_id=my_database.fetchone()
|
|
last_inserted_id=last_inserted_id[0]
|
|
|
|
retVal={"message": "Success", "last_insert_id": last_inserted_id, "stored_names": final_stored_names_list}
|
|
except:
|
|
retVal={"message": "Failed", "last_insert_id": None}
|
|
return retVal
|
|
|
|
|
|
#Read one with condition from any table
|
|
|
|
def readoneonewithcond(tablename, fields, condition):
|
|
my_database=db_connection.cursor(dictionary=True)
|
|
condition_data=''
|
|
for key, value in condition.items():
|
|
if isinstance(value, str):
|
|
condition_data += " {}='{}' AND".format(key, value)
|
|
else:
|
|
condition_data += " {}={} AND".format(key, value)
|
|
len_of_condition=len(condition_data)
|
|
condition_data=condition_data[:(len_of_condition-3)]
|
|
sql_query="SELECT {} FROM {} WHERE {}".format(fields, tablename, condition_data)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
output=my_database.fetchall()
|
|
""" if (len(output) == 1):
|
|
output=output[0]
|
|
else:
|
|
output=output """
|
|
except:
|
|
output = {"Error":"No Data Recieved or Error Occured"}
|
|
return output
|
|
|
|
#Read many with condition from any table
|
|
|
|
def readmanywithcond(tablename, fields, condition):
|
|
my_database=db_connection.cursor(dictionary=True)
|
|
condition_data=''
|
|
request_condition=''
|
|
for key,value in condition.items():
|
|
if isinstance(value, str):
|
|
condition_data += "{}={}".format(key, value)
|
|
request_condition=value
|
|
if request_condition == 'all':
|
|
sql_query="SELECT {} FROM {} WHERE status='1'".format(fields, tablename)
|
|
else:
|
|
sql_query="SELECT {} FROM {} WHERE status='1' AND {}".format(fields, tablename, condition_data)
|
|
print(sql_query)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
output=my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No Data Recieved or Error Occured"}
|
|
return output
|
|
|
|
#Read Many without condition(status) from any table
|
|
|
|
def readmanywocond(tablename, fields, condition):
|
|
my_database=db_connection.cursor(dictionary=True)
|
|
condition_data=''
|
|
request_condition=''
|
|
for key,value in condition.items():
|
|
if isinstance(value, str):
|
|
condition_data += "{}={}".format(key, value)
|
|
request_condition=value
|
|
if request_condition == 'all':
|
|
sql_query="SELECT {} FROM {}".format(fields, tablename)
|
|
else:
|
|
sql_query="SELECT {} FROM {} WHERE {}".format(fields, tablename, condition_data)
|
|
print(sql_query)
|
|
try:
|
|
my_database.execute(sql_query)
|
|
output=my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No Data Recieved or Error Occured"}
|
|
return output
|
|
|
|
#Delete (Means update status) with condition anything(0,1) to 9 for any table
|
|
|
|
def updatevaluesindb(condition, updatestatement, tablename):
|
|
my_database = db_connection.cursor()
|
|
sql_statement = "UPDATE " + tablename + " SET " + updatestatement + " WHERE " + condition + ""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
db_connection.commit()
|
|
retval = "Success"
|
|
except:
|
|
retval = "Failed"
|
|
return retval
|
|
|
|
def insertintodb(condition, fields, values, tablename):
|
|
my_database = db_connection.cursor()
|
|
sql_statement = "INSERT INTO " + tablename + "(" + fields + ") VALUES(" + values + ") WHERE " + condition + ""
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
db_connection.commit()
|
|
retval = "Success"
|
|
except:
|
|
retval = "Failed"
|
|
db_connection.close()
|
|
return retval
|
|
|
|
def insertintodbwocondition(fields, values, tablename):
|
|
my_database = db_connection.cursor()
|
|
sql_statement = "INSERT INTO " + tablename + "(" + fields + ") VALUES(" + values + ")"
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
db_connection.commit()
|
|
retval = "Success"
|
|
except:
|
|
retval = "Failed"
|
|
#db_connection.close()
|
|
return retval
|
|
|
|
def getfromdbwithcondition(condition, fields, tablename):
|
|
# my_database = db_connection.cursor()
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename + " WHERE " + condition + ""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def getfromdbwocondition(fields, tablename):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename + ""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def getfromdbwoconditiongroupBy(fields, tablename):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename + " GROUP BY" + fields + ""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def getfromdbwithconditiongroupBy(condition, fields, tablename):
|
|
# my_database = db_connection.cursor()
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from" + tablename + " WHERE" + condition + " GROUP BY" + fields + ""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def unionwocondition(fields, tablename1, tablename2):
|
|
# my_database = db_connection.cursor()
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT" + fields + " from" + tablename1 + " UNION" + " SELECT" + fields + " from" + tablename2 + ""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def unionwithcondition(fields, condition, tablename1, tablename2):
|
|
# my_database = db_connection.cursor()
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT" + fields + " from" + tablename1 + " WHERE" + condition + "UNION" + " SELECT" + fields + " from" + tablename2 + " WHERE" + condition + ""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def groupbyhavingorderby(fields, condition, tablename):
|
|
# my_database = db_connection.cursor()
|
|
sql_statement = "SELECT" + fields + "FROM" + tablename + "GROUP BY" + fields + "HAVING" + condition + "ORDER BY" + fields + "DESC" + ""
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def insertselectwhereorderby(fields, condition, tablename1, tablename2):
|
|
# my_database = db_connection.cursor()
|
|
sql_statement = "INSERT INTO" + tablename1 + " SELECT" + fields + " from" + tablename2 + " WHERE" + condition + "ORDER BY" + fields + "DESC" + ""
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def insertselectwhere(fields, condition, tablename1, tablename2):
|
|
# my_database = db_connection.cursor()
|
|
sql_statement = "INSERT INTO" + tablename1 + "SELECT" + fields + "FROM" + tablename2 + "WHERE" + condition + ""
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
def getfromdbwithconditionrange(fields,tablename,columnname,value_1,value_2):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} WHERE {} BETWEEN {} AND {}".format(fields,tablename,columnname,value_1,value_2)
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
def getfromdbwoorderby(fields,tablename,order_by):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} ORDER BY {}".format(fields,tablename,order_by)
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
def getfromdbwoalias(old_columnname,new_columnname,tablename,):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} AS {}".format(old_columnname,new_columnname,tablename)
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
def getfromdbwithconditionhaving(fields,tablename,columnname,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} WHERE {} GROUP BY {} HAVING {} ORDER BY {}".format(fields,tablename,columnname,condition)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
def getfromdbinnerjoinwithorderby(fields,tableName,columnName,condition,order_by):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} INNER JOIN {} ON {} WHERE {} ORDER BY {}".format(fields,tableName,columnName,condition,order_by)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
|
|
def getfromdbwithExists(columnname,tablename,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} WHERE EXISTS {}".format(columnname,tablename,condition)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
def getfromdbwithand(fields,columnname,tablename,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} WHERE {} AND {}".format(fields,columnname,tablename,condition)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
|
|
def getfromdbwithor(fields,columnname,tablename,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} WHERE {} OR {}".format(fields,columnname,tablename,condition)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
def getfromdbwithnot(fields,columnname,tablename,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} WHERE {} NOT {}".format(fields,columnname,tablename,condition)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
|
|
def getfromdbwithconditionin(fields,tablename,columnname,values):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement="SELECT {} FROM {} WHERE {} IN {}".format(fields,columnname,tablename,values)
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
# db_connection.close()
|
|
return output
|
|
|
|
|
|
def innerJoinwithCondition(fields, tablename1,tabelname2,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +" INNER JOIN " +tabelname2 +" ON " +condition
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def leftJoinwithCondition(fields, tablename1,tabelname2,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"LEFT JOIN" +""+tabelname2 +" ON " +condition+""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def rightJoinwithCondition(fields, tablename1,tabelname2,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"RIGHT JOIN"+""+tabelname2 +"ON" +condition+""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def fullOuterJoinwithCondition(fields, tablename1,tabelname2,condition):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"FULL OUTER JOIN" +tabelname2 +"ON" +condition
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def innerLeftJoinwithCondition(fields, tablename1,tabelname2,condition,condition2):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"INNER JOIN" +tabelname2 +"ON" +condition + "" +"LEFT JOIN" +""+tabelname2 +"ON" +condition2+""
|
|
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def innerRightJoinwithCondition(fields, tablename1,tabelname2,condition,condition2):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"INNER JOIN" +tabelname2 +"ON" +condition + "" +"RIGHT JOIN" +""+tabelname2 +"ON" +condition2+""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def leftRightJoinwithCondition(fields, tablename1,tabelname2,condition,condition2):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"LEFT JOIN" +tabelname2 +"ON" +condition + "" +"RIGHT JOIN" +""+tabelname2 +"ON" +condition2+""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def fullOuterRightJoinwithCondition(fields, tablename1,tabelname2,condition,condition2):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"FULL OUTER JOIN" +tabelname2 +"ON" +condition + "" +"RIGHT JOIN" +""+tabelname2 +"ON" +condition2+""
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def leftJoinwithOutCondition(fields, tablename1,tabelname2,):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"LEFT JOIN" +""+tabelname2
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def rightJoinwithOutCondition(fields, tablename1,tabelname2):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"RIGHT JOIN"+""+tabelname2
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
def fullOuterJoinwithoutcondition(fields, tablename1,tabelname2):
|
|
my_database = db_connection.cursor(dictionary = True)
|
|
sql_statement = "SELECT " + fields + " from " + tablename1 + "" +"FULL OUTER JOIN" +tabelname2
|
|
print(sql_statement)
|
|
try:
|
|
my_database.execute(sql_statement)
|
|
output = my_database.fetchall()
|
|
except:
|
|
output = {"Error":"No data recieved or error occured"}
|
|
db_connection.close()
|
|
return output
|
|
|
|
''' |