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.'}
