def create_data(self):
# Get the sql connection
connection = dbConn.getConnection()
name = input('Enter Name = ')
code = input('Enter Code = ')
price = input('Enter Price = ')
try:
query = "Insert Into [Python.Crud.Operations].[dbo].Product(Name, Code, Price) Values(?,?,?)"
cursor = connection.cursor()
# Execute the sql query
cursor.execute(query, name, code, price)
# Commit the data
connection.commit()
print('Data Saved Successfully')
except Exception as e:
print('Something wrong, please check. Error:' + str(e))
finally:
# Close the connection
connection.close()
def func_DeleteData(self):
# Get the sql connection
connection = dbConn.getConnection()
id = input('Enter Employee Id = ')
try:
# Get record which needs to be deleted
sql = "Select * From Employee Where Id = ?"
cursor = connection.cursor()
cursor.execute(sql, [id])
item = cursor.fetchone()
print('Data Fetched for Id = ', id)
print('ID\t\t Name\t\t\t Age')
print('-------------------------------------------')
print(' {}\t\t {} \t\t\t{} '.format(item[0], item[1], item[2]))
print('-------------------------------------------')
confirm = input('Are you sure to delete this record (Y/N)?')
# Delete after confirmation
if confirm == 'Y':
deleteQuery = "Delete From Employee Where Id = ?"
cursor.execute(deleteQuery, [id])
connection.commit()
print('Data deleted successfully!')
else:
print('Wrong Entry')
except:
print('Somethng worng, please check')
finally:
connection.close()
def func_CreateData(self):
# Get the sql connection
connection = dbConn.getConnection()
name = input('Enter Name = ')
age = input('Enter Age = ')
try:
query = "Insert Into Employee(Name, Age) Values(?,?)"
cursor = connection.cursor()
# Execute the sql query
cursor.execute(query, [name, age])
# Commit the data
connection.commit()
print('Data Saved Successfully')
except:
print('Somethng worng, please check')
finally:
# Close the connection
connection.close()
def func_UpdateData(self):
connection = dbConn.getConnection()
id = input('Enter Employee ID = ')
try:
sql = "Select * From Employee Where ID = ?"
cursor = connection.cursor()
cursor.execute(sql, [id])
item = cursor.fetchone()
print('Data Fetched for ID = ', id)
print('ID\t\t Name\t\t\t Age')
print('-------------------------------------------')
print(' {}\t\t {} \t\t\t{} '.format(item[0], item[1], item[2]))
print('-------------------------------------------')
print('Enter New Data To Update Employee Record ')
name = input('Enter New Name = ')
age = input('Enter New Age = ')
query = "Update Employee Set Name = ?, Age =? Where ID =?"
cursor.execute(query, [name, age, id])
connection.commit()
print('Data Updated Successfully')
except:
print('Something wrong, please check')
finally:
connection.close()
def delete_data(self):
# Get the SQL connection
connection = dbConn.getConnection()
# Ask the user for the Id of the product
id = input('Enter Product Id = ')
try:
# Get record which needs to be deleted
sql = "Select * From [Python.Crud.Operations].[dbo].Product Where Id = ?"
cursor = connection.cursor()
cursor.execute(sql, [id])
item = cursor.fetchone()
print('Data Fetched for Id = ', id)
print('ID\t\t Name\t\t\t Code \t\t\t Price')
print('-------------------------------------------')
print(' {}\t\t {} \t\t\t{} '.format(item[0], item[1], item[2],
item[3]))
print('-------------------------------------------')
# Get user's delete confirmation
confirm = input(
'Are you sure you want to delete this record (Y/N)?')
# Delete after confirmation
if confirm == 'Y':
deleteQuery = "Delete From [Python.Crud.Operations].[dbo].Product Where Id = ?"
cursor.execute(deleteQuery, [id])
connection.commit()
print('Data deleted successfully!')
else:
print('Wrong Entry')
except Exception as e:
print('Something wrong, please check. Error:' + str(e))
finally:
connection.close()
def insert_data():
connection = getConnection()
cursor = connection.cursor()
clean_data()
# drop all the rows for next update
sql_truncate = "truncate forecast;"
cursor.execute(sql_truncate)
for i in range(len(forecast_list)):
date = forecast_list[i]['Date']
hour = forecast_list[i]['Hour']
temp = forecast_list[i]['temp']
feels_like = forecast_list[i]['feels_like']
temp_min = forecast_list[i]['temp_min']
temp_max = forecast_list[i]['temp_max']
pressure = forecast_list[i]['pressure']
humidity = forecast_list[i]['humidity']
wind_speed = forecast_list[i]['windSpeed']
main = forecast_list[i]['Desc']
sql = "insert into db1.forecast values ('%s','%s', '%s','%s','%s','%s', '%s', '%s', '%s', '%s');" % (
date, hour, temp, feels_like, temp_min, temp_max, pressure,
humidity, wind_speed, main)
cursor.execute(sql)
connection.commit()
connection.close()
def func_UpdateData(self):
# Ge the sql connection
connection = dbConn.getConnection()
MACS = input('Ma co so = ')
try:
# Fethc the data which needs to be updated
sql = "Select * From CoSo Where MACS = ?"
cursor = connection.cursor()
cursor.execute(sql, [MACS])
item = cursor.fetchone()
print('Data Fetched for Id = ', MACS)
print('MACS\t\t TENCS\t\t\t DIACHI')
print('-------------------------------------------')
print(' {}\t\t {} \t\t\t{} '.format(item[0], item[1], item[2]))
print('-------------------------------------------')
print('Enter New Data To Update Co so Record ')
TENCS = input('Nhap ten co so = ')
DIACHI = input('Nhap dia chi = ')
query = "Update CoSo Set TENCS = ?, DIACHI =? Where MACS =?"
# Execute the update query
cursor.execute(query, [TENCS, DIACHI])
connection.commit()
print('Data Updated Successfully')
except:
print('Somethng worng, please check')
finally:
# Close the connection
connection.close()
def func_DeleteData(self):
# Get the SQL connection
connection = dbConn.getConnection()
id = input('Enter Employee name = ')
try:
# Get record which needs to be deleted
sql = "Select * From Employee Where name = %s"
cursor = connection.cursor()
cursor.execute(sql, [id])
item = cursor.fetchone()
print('Data Fetched for Id = ', item)
confirm = input('Are you sure to delete this record (Y/N)?')
# Delete after confirmation
if confirm == 'Y':
deleteQuery = "Delete From Employee Where name = %s"
cursor.execute(deleteQuery, [id])
connection.commit()
print('Data deleted successfully!')
else:
print('Wrong Entry')
except:
print('Something wrong, please check')
finally:
connection.close()
def func_CreateData(self):
# Get the sql connection
connection = dbConn.getConnection()
name = input('Enter Name = ')
gender = input('Enter Gender = ')
email = input('Enter Email= ')
try:
query = "Insert Into tblEmployee(Name, Gender, Email) Values(?,?,?)"
cursor = connection.cursor()
# Execute the sql query
cursor.execute(query, [name, gender, email])
# Commit the data
connection.commit()
print('Data Saved Successfully')
except:
print('Something wrong, please check')
finally:
# Close the connection
connection.close()
def createAuditTrail():
# Get the sql connection
connection = dbConnection.getConnection()
cursor = connection.cursor()
try:
_json = request.json
_auditTrailTypeId = _json['auditTrailTypeId']
_actionToId = str(
requests.get('https://www.wikipedia.org').headers['X-Client-IP'])
_remark = _json['remark']
_notes = _json['notes']
_createdBy = _json['createdBy']
_createdDate = datetime.now()
# validate the received values
if _auditTrailTypeId and _actionToId and _remark and _notes and _createdBy and request.method == 'POST':
# save edits
sql = "INSERT INTO AuditTrail(AuditTrailTypeId, ActionToId, Remark, Notes, CreatedDate, CreatedBy) VALUES(?, ?, ?, ?, ?, ?)"
data = (_auditTrailTypeId, _actionToId, _remark, notes,
_createdDate, _createdBy)
cursor.execute(sql, data)
connection.commit()
response = jsonify('Record created successfully!')
response.status_code = 200
return response
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def func_ReadData(self):
# Get the sql connection
connection = dbConn.getConnection()
cursor = connection.cursor()
# Execute the sql query
cursor.execute('Select * from CoSo')
# Print the data
for row in cursor:
print('row = %r' % (row, ))
def read_data(self):
# Get the sql connection
connection = dbConn.getConnection()
cursor = connection.cursor()
# Execute the sql query
cursor.execute('Select * from [Python.Crud.Operations].[dbo].Product')
# Print the data
for row in cursor:
print('row = %r' % (row, ))
def func_ReadData(self):
global dbConn
# Get the sql connection
connection = dbConn.getConnection()
cursor = connection.cursor()
# Execute the sql query
cursor.execute('Select * from tblEmployee')
# Print the data
for row in cursor:
print('row data = %r' % (row,))
def user_detail(id):
con = dbcon.getConnection() # create connection instance
try:
query = "select * from tblEmployee where id = (?)"
cursor = con.cursor()
cursor.execute(query,[id])
execMsg = cursor.fetchall()
# for row in cursor:
# execMsg+=row
except Exception as exp:
execMsg = exp
return str(execMsg)
def get_employee():
con = dbcon.getConnection() #create connection instance
try:
query = "select * from tblEmployee"
cursor = con.cursor()
cursor.execute(query)
execMsg = cursor.fetchall()
# for row in cursor:
# execMsg+=row
except Exception as exp:
execMsg=exp
return str(execMsg)
def getAllAuditTrail():
# Get the sql connection
connection = dbConnection.getConnection()
cursor = connection.cursor()
try:
sql = "SELECT * FROM AuditTrail"
all_records = cursor.execute(sql).fetchall()
return jsonify(all_records)
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
def getHistoricalData(start_station_number, destination_station_number, hour):
'''get historical data of the last seven days '''
db_connect = getConnection()
cur = db_connect.cursor()
forma = '%a %b %e %H:%i:%s %Y'
sql1 = 'SELECT * from Bike.bike_station where date_sub(curdate(),INTERVAL 7 DAY) <= DATE(STR_TO_DATE(Update_time, "%s")) and Station_number = "%s" and Hour(STR_TO_DATE(Update_time, "%s")) = "%s"and minute(STR_TO_DATE(Update_time, "%s"))=0;' % (
forma, start_station_number, forma, hour, forma)
sql2 = 'SELECT * from Bike.bike_station where date_sub(curdate(),INTERVAL 7 DAY) <= DATE(STR_TO_DATE(Update_time, "%s")) and Station_number = "%s" and Hour(STR_TO_DATE(Update_time, "%s")) = "%s"and minute(STR_TO_DATE(Update_time, "%s"))=0;' % (
forma, destination_station_number, forma, hour, forma)
cur.execute(sql1)
s = cur.fetchall()
cur.execute(sql2)
d = cur.fetchall()
db_connect.commit()
db_connect.close()
return s, d
def user_delete(id):
con = dbcon.getConnection() # create connection instance
try:
query = "select * from tblEmployee where id = (?)"
cursor = con.cursor()
cursor.execute(query,[id])
count=len(cursor.fetchall())
if(count>0):
query = "delete from tblEmployee where id = (?)"
cursor = con.cursor()
cursor.execute(query,[id])
execMsg = 'Employee record got deleted!!!'
con.commit()
else:
execMsg = "No employees found for provided employee id"
except Exception as exp:
execMsg = exp
return str(execMsg)
def func_CreateData(self):
connection = dbConn.getConnection()
id = input('Enter Id = ')
name = input('Enter Name = ')
age = input('Enter Age = ')
try:
query = "Insert Into Employee(ID, Name, Age) Values(?,?,?)"
cursor = connection.cursor()
cursor.execute(query, [id, name, age])
connection.commit()
print('Data Saved Successfully')
except:
print('Something wrong, please check')
finally:
connection.close()
def func_UpdateData(self):
# Get the SQL connection
connection = dbConn.getConnection()
choice = int(input("What do you want to update 1: for name, 2: for Age"))
cursor = connection.cursor()
try:
if choice == 1:
name = input("Input the old name of the employee")
sql = "Select * From Employee Where name = %s"
cursor.execute(sql, [name])
item = cursor.fetchone()
print("Old record is ", item)
name1 = str(input("Enter the new name of the employee"))
sql1 = "Update Employee set name = %s where name = %s"
cursor.execute(sql1, [name1,name])
connection.commit()
print("Data Updated Successfully")
elif choice == 2:
name = input("Enter the name of the employee to update the age")
sql = "Select * From Employee Where name = %s"
cursor.execute(sql, [name])
item = cursor.fetchone()
print("Old record is ", item)
age = int(input("Enter the new age of the Employee"))
sql1 = "Update Employee set age = %s where name = %s"
cursor.execute(sql1, [age, name])
connection.commit()
print("Data Updated Successfully")
else:
print("You have enterd the wrong choice please check")
except:
print('Something wrong, please check')
finally:
# Close the connection
connection.close()
def employee_update(id):
con = dbcon.getConnection() # create connection instance
name = request.json["name"]
gender = request.json["gender"]
emailid = request.json["emailid"]
try:
query = "select * from tblEmployee where id = (?)"
cursor = con.cursor()
cursor.execute(query, [id])
count = len(cursor.fetchall())
if (count>0):
query = "update tblemployee set name =?,gender=?,emailid=? where id= ?"
cursor = con.cursor()
cursor.execute(query,[name, gender, emailid, id])
con.commit()
execMsg="Data Updated Successfully!!!"
else:
execMsg= "No employees found for provided employee id"
except Exception as exp:
execMsg=exp
finally:
con.close()
return str(execMsg)
def add_user():
# print(request.json)
username = request.form.get("username")
gender = request.form.get("gender")
emailid = request.form.get("email")
con = dbcon.getConnection() #create connection instance
new_employee = Models.Employee(username,gender, emailid)
try:
query = 'insert into tblEmployee(name, gender, email) values (?,?,?)'
cursor = con.cursor()
cursor.execute(query, [new_employee.name, new_employee.gender, new_employee.emailid])
con.commit()
# print("Data saved successfully")
execMsg="Data saved successfully"
except Exception as exp:
execMsg=exp
finally:
con.close()
return str(execMsg)
def func_UpdateData(self):
# Get the SQL connection
connection = dbConn.getConnection()
id = input('Enter Employee Id = ')
try:
# Fetch the data which needs to be updated
sql = "Select * From tblEmployee Where Id = ?"
cursor = connection.cursor()
cursor.execute(sql, [id])
item = cursor.fetchone()
print('Data Fetched for Id = ', id)
print(item)
print('ID\t\t Name\t\t\t Gender\t\t EmailID')
print('-------------------------------------------')
print(' {}\t\t {} \t\t\t{}\t\t{} '.format(item[0], item[1],
item[2], item[3]))
print('-------------------------------------------')
print('Enter New Data To Update Employee Record ')
name = input('Enter New Name = ')
gender = input('Enter New Gender = ')
email = input('Enter New EmailID = ')
query = "Update tblEmployee Set Name = ?, Gender = ?, Email = ? Where Id =?"
# Execute the update query
cursor.execute(query, [name, gender, email, id])
connection.commit()
print('Data Updated Successfully')
except:
print('Something wrong, please check')
finally:
# Close the connection
connection.close()
def func_CreateData(self):
# Get the sql connection
connection = dbConn.getConnection()
MACS = input('Nhap ma co so = ')
TENCS = input('Nhap ten co so = ')
DIACHI = input('Nhap dia chi = ')
try:
query = "INSERT INTO CoSo(MACS, TENCS, DIACHI) VALUES(?, ?, ?)"
cursor = connection.cursor()
cursor.execute(query, [MACS, TENCS, DIACHI])
# Commit the data
connection.commit()
print('Data Saved Successfully')
except:
print('Something wrong, please check')
finally:
# Close the connection
connection.close()
def update_data(self):
# Get the SQL connection
connection = dbConn.getConnection()
id = input('Enter Product Id = ')
try:
# Fetch the data which needs to be updated
sql = "Select * From [Python.Crud.Operations].[dbo].Product Where Id = ?"
cursor = connection.cursor()
cursor.execute(sql, [id])
item = cursor.fetchone()
print('Data Fetched for Id = ', id)
print('ID\t\t Name\t\t\t Code \t\t\t Price')
print('-------------------------------------------')
print(' {}\t\t {} \t\t\t{} '.format(item[0], item[1], item[2],
item[3]))
print('-------------------------------------------')
print('Enter New Data To Update Product Record ')
name = input('Enter New Name = ')
code = input('Enter New Code = ')
price = input('Enter New Price = ')
query = "Update [Python.Crud.Operations].[dbo].Product Set Name = ?, Code = ?, Price = ? Where Id =?"
# Execute the update query
cursor.execute(query, [name, code, price, id])
connection.commit()
print('Data Updated Successfully')
except Exception as e:
print('Something wrong, please check. Error:' + str(e))
finally:
# Close the connection
connection.close()
class Update:
def func_UpdateData(self):
connection = dbConn.getConnection()
import db_connection as dbcon
import Models
name = 'mickey'
gender = 'male'
emailid = '*****@*****.**'
con = dbcon.getConnection() #create connection instance
# new_employee = Models.Employee(username,gender, emailid)
# try:
# query = 'insert into tblEmployee(name, gender, email) values (?,?,?)'
# cursor = con.cursor()
# cursor.execute(query, [new_employee.name, new_employee.gender, new_employee.emailid])
# con.commit()
# print("Data saved successfully")
#
# except Exception as exp:
# print(exp)
# finally:
# con.close()
# name = request.json["name"]
# gender = request.json["gender"]
# emailid = request.json["emailid"]
id=24
try:
query = "update tblemployee set name =?,gender=?,email=? where id= ?"
cursor = con.cursor()
cursor.execute(query,[name, gender, emailid, id])
import db_connection as dbConn
# Get the SQL connection
connection = dbConn.getConnection()
choice = int(input("What do you want to update 1: for name, 2: for Age"))
cursor = connection.cursor()
if choice == 1:
name = input("Input the old name of the employee")
sql = "Select * From Employee Where name = %s"
cursor.execute(sql, [name])
item = cursor.fetchone()
print("Old record is ", item)
name1 = str(input("Enter the new name of the employee"))
sql1 = "Update Employee set name = %s where name = %s"
cursor.execute(sql1, [name1, name])
connection.commit()
print("Data Updated Successfully")
elif choice == 2:
name = input("Enter the name of the employee to update the age")
sql = "Select * From Employee Where name = %s"
cursor.execute(sql, [name])
item = cursor.fetchone()
print("Old record is ", item)
age = int(input("Enter the new age of the Employee"))
sql1 = "Update Employee set age = %s where name = %s"
cursor.execute(sql1, [age, name])
connection.commit()
print("Data Updated Successfully")
else:
print("You have enterd the wrong choice please check")
class Delete:
def func_DeleteData(self):
connection = dbConn.getConnection()
class Read():
def func_ReadData(self):
connection = dbConn.getConnection()
