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()