def insert(tittle,author): conn, cursor = connect() songs_list = []; sql = """INSERT INTO [dbo].[SONGS] ( [TITTLE] ,[AUTHOR] ,[CREATED] ,[UPDATED]) VALUES ('{0}','{1}','{2}','{3}')""".format( tittle, author, datetime.now().strftime('%Y-%m-%d %H:%M:%S'), datetime.now().strftime('%Y-%m-%d %H:%M:%S') ) print(sql) try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database conn.commit() except: # Rollback in case there is any error conn.rollback() # disconnect from server conn.close()
def create(): print('Enabeling connection') conn, cursor = connect() print('Creation Started') print('Checking in table exist') #Drop table if it already exist using execute() method. cursor.execute("DROP TABLE IF EXISTS SONGS") #Create table as per requirement sql = """CREATE TABLE SONGS ( ID INT IDENTITY(1,1) PRIMARY KEY, TITTLE CHAR(120) NOT NULL, AUTHOR CHAR(120) NOT NULL, CREATED DATETIME NOT NULL, UPDATED DATETIME NOT NULL )""" print('Creating new table') cursor.execute(sql) #commit changes print('Commiting changes') conn.commit() print('Creation ended') # disconnect from server print('Closing connection') conn.close()
def allocate(self): conn = db_connect.connect() query = conn.execute( "SELECT * FROM collaborators WHERE id in (%d, %d)" % (int(self.manager_id), int(self.collaborator_id))) collaborators = query.fetchall() print(collaborators) if (len(collaborators) != 2 or collaborators[0][3] != collaborators[1][3]): return {'message': 'forbidden operation.'}, 409 query = conn.execute( "SELECT * FROM managers WHERE manager_id = %d AND collaborator_id = %d" % (int(self.collaborator_id), int(self.manager_id))) relation = query.first() if (relation != None): return {'message': 'loop detected.'}, 409 query = conn.execute( "SELECT * FROM managers WHERE collaborator_id = %d" % int(self.collaborator_id)) relation = query.first() if (relation != None): return {'message': 'collaborator already has manager.'}, 409 conn.execute( "INSERT INTO managers (manager_id, collaborator_id) VALUES (%d, %d)" % (int(self.manager_id), int(self.collaborator_id))) return {'message': 'relation has been set.'}
def all(): conn = db_connect.connect() query = conn.execute("SELECT * FROM collaborators") return { 'collaborators': [dict(zip(tuple(query.keys()), i)) for i in query.cursor] }
def manager_of(collaborator_id): conn = db_connect.connect() query = conn.execute( "SELECT * FROM managers WHERE collaborator_id = %d" % int(collaborator_id)) relation = query.first() if (relation != None): return relation[1] return 0
def collaborators_from_company(company_id): conn = db_connect.connect() query = conn.execute( "SELECT cl.id, cl.name, cl.email FROM collaborators AS cl WHERE company_id = %d" % int(company_id)) return { 'collaborators': [dict(zip(tuple(query.keys()), i)) for i in query.cursor] }
def collaborators_from_manager(manager_id): conn = db_connect.connect() query = conn.execute( """SELECT cl.id, cl.name, cl.email AS company_name FROM managers AS mn INNER JOIN collaborators AS cl ON cl.id = mn.collaborator_id WHERE mn.manager_id = %d""" % int(manager_id)) return { 'collaborators': [dict(zip(tuple(query.keys()), i)) for i in query.cursor] }
def get_collaborator(id): conn = db_connect.connect() query = conn.execute( """SELECT cl.id, cl.name, cl.email, cl.company_id, cp.name AS company_name FROM collaborators AS cl INNER JOIN companies AS cp ON cl.company_id = cp.id WHERE cl.id = %d""" % int(id)) return { 'collaborator': [dict(zip(tuple(query.keys()), i)) for i in query.cursor] }
def select(): conn, cursor = connect() songs_list = [] sql = "select * from SONGS" try: # Execute the SQL command cursor.execute(sql) # Fetch all the rows in a list of lists. results = cursor.fetchall() for row in results: songs_list.append(Song(row[0], row[1], row[2])) except: print("Error: unable to fetch data") # disconnect from server conn.close() return songs_list
def register_collaborator_in_company(self): conn = db_connect.connect() conn.execute("UPDATE collaborators SET company_id = %d WHERE id = %d" % (int(self.company_id), int(self.collaborator_id))) return {'message': 'collaborator has been registered.'}
def remove_collaborator_from_company(collaborator_id): conn = db_connect.connect() conn.execute( "UPDATE collaborators SET company_id = null WHERE id = %d" % int(collaborator_id)) return {'message': 'collaborator has been removed.'}
def insert(self): conn = db_connect.connect() conn.execute( "INSERT INTO collaborators (name, email, company_id) VALUES ('%s', '%s', %d)" % (self.name, self.email, int(self.company_id))) return {'message': 'collaborator has been created successfully.'}, 201
def delete_collaborator(id): conn = db_connect.connect() conn.execute("DELETE FROM collaborators WHERE id = %d" % int(id)) return {'message': 'collaborator has been deleted.'}