def retrieve_genres():
conn = connect()
c = conn.cursor()
select_lib = "SELECT g.name AS genre, count(*) AS num FROM copy " \
"JOIN book b ON copy.bookid = b.bookid " \
"JOIN genre g ON b.genreid = g.genreid " \
"WHERE sent IS NULL " \
"GROUP BY g.name " \
"ORDER BY g.name"
select_sent = "SELECT g.name AS genre, count(*) AS num FROM copy " \
"JOIN book b ON copy.bookid = b.bookid " \
"JOIN genre g ON b.genreid = g.genreid " \
"WHERE sent IS NOT NULL " \
"GROUP BY g.name " \
"ORDER BY g.name"
c.execute(select_lib)
data = c.fetchall()
library = {}
for row in data:
library[row[0]] = row[1]
c.execute(select_sent)
data = c.fetchall()
sent = {}
for row in data:
sent[row[0]] = row[1]
return library, sent
def bk_login(copy):
conn = connect()
with conn.cursor() as cursor:
s = "UPDATE copy SET sent = NULL WHERE copyid = %s"
cursor.execute(s, [copy])
conn.commit()
disconnect(conn)
def bk_logout(copy):
conn = connect()
with conn.cursor() as cursor:
s = "UPDATE copy SET sent = %s WHERE copyid = %s"
cursor.execute(s, [date.today(), copy])
conn.commit()
disconnect(conn)
def get_books(sql):
conn = connect()
c = conn.cursor()
select = sql
c.execute(select)
data = c.fetchall()
books = {}
for tuple in data:
if tuple[0] in books.keys():
if tuple[2] not in books[tuple[0]]['authors']:
books[tuple[0]]['authors'].append(tuple[2])
# if tuple[3] != '':
# books[tuple[0]]['editors'].append(tuple[3])
else:
d = {
'title': proper_case(tuple[1]).replace("^", "'"),
'authors': [tuple[2]],
#'editors': [tuple[3]],
'genre': tuple[3],
'logged': tuple[4],
'id': tuple[0]
}
d['logged'] = d['logged'].strftime("%m/%d/%Y")
books[tuple[0]] = d
disconnect(conn)
books = books.values()
for b in books:
bauth = b['authors']
authors = ''
for a in bauth:
authors += proper_case(a)
if a != bauth[len(bauth) - 1]:
authors += ", "
b['authors'] = authors
# bedit = b['editors']
# editors = ''
# if None not in b['editors']:
# for e in b['editors']:
# editors += proper_case(e)
# if e != bedit[len(bedit)-1]:
# editors += ", "
# else:
# editors = 'N/A'
#
# b['editors'] = editors
return books
def get_ithaca():
conn = connect()
c = conn.cursor()
select = "SELECT latitude, longitude FROM ithaca"
c.execute(select)
data = c.fetchall()[0]
coords = {'name': "Ithaca", 'latitude': data[0], 'longitude': data[1]}
return coords
def submit_book(title, authors, editors, genre, quant):
conn = connect()
with conn.cursor() as cursor:
s = "SELECT bookid FROM book WHERE title = %s"
cursor.execute(s, [title])
data = cursor.fetchall()
if len(data) == 0:
s = "SELECT genreid FROM genre WHERE name = %s"
cursor.execute(s, [genre])
gid = cursor.fetchall()[0][0]
i = "INSERT INTO book(title, genreid, location) VALUES(%s, %s, 'SAC') RETURNING bookid"
cursor.execute(i, [title, gid])
id = cursor.fetchall()[0][0]
for auth in authors:
s = "SELECT authorid FROM author WHERE name = %s"
cursor.execute(s, [auth])
adata = cursor.fetchall()
if len(adata) == 0:
i = "INSERT INTO author(name) VALUES(%s) RETURNING authorid"
cursor.execute(i, [auth])
aid = cursor.fetchall()[0][0]
else:
aid = adata[0][0]
i = "INSERT INTO written_by(bookid, authorid) VALUES(%s, %s)"
cursor.execute(i, [id, aid])
for edit in editors:
s = "SELECT editorid FROM editor WHERE name = %s"
cursor.execute(s, [edit])
edata = cursor.fetchall()
if len(edata) == 0:
i = "INSERT INTO editor(name) VALUES(%s) RETURNING editorid"
cursor.execute(i, [edit])
eid = cursor.fetchall()[0][0]
else:
eid = edata[0][0]
i = "INSERT INTO edited_by(bookid, editorid) VALUES(%s, %s)"
cursor.execute(i, [id, eid])
else:
id = data[0][0]
for i in range(int(quant)):
i = "INSERT INTO copy(bookid, logged) VALUES(%s, %s)"
cursor.execute(i, [id, date.today()])
conn.commit()
disconnect(conn)
def get_all_titles():
result = []
try:
with connect().cursor() as cursor:
sql = "select title from book order by title asc"
cursor.execute(sql)
data = cursor.fetchall()
finally:
for t in data:
result.append(proper_case(t[0].replace("^", "'")))
return json.dumps(result)
def get_genres():
result = {}
try:
with connect().cursor() as cursor:
sql = "select name, color, label from genre order by name asc"
cursor.execute(sql)
data = cursor.fetchall()
finally:
for t in data:
result[t[0]] = [proper_case(t[1]), proper_case(t[2])]
return json.dumps(result)
def get_all_editors():
result = []
try:
with connect().cursor() as cursor:
sql = "select name from editor where name not like 'VARIOUS' order by name asc"
cursor.execute(sql)
data = cursor.fetchall()
finally:
for t in data:
result.append(proper_case(t[0].replace("^", "'")))
return json.dumps(result)
def retrieve_mailings():
conn = connect()
c = conn.cursor()
select = "SELECT sent, count(*) FROM copy WHERE sent IS NOT NULL GROUP BY sent;"
c.execute(select)
data = c.fetchall()
mailings = {}
for row in data:
mailings[str(row[0])] = row[1]
return mailings
def get_titles(query):
result = []
try:
with connect().cursor() as cursor:
q = query.replace("'", "^")
sql = "select title from book where title like UPPER('%" + q + "%')"
cursor.execute(sql)
data = cursor.fetchall()
finally:
for t in data:
result.append(proper_case(t[0].replace("^", "'")))
return json.dumps({"suggestions": result})
def main():
connection, channel = connect()
def callback(ch, method, properties, body):
logger.info(' [x] Received %s', body)
channel.basic_consume(
queue='hello',
on_message_callback=callback,
auto_ack=True,
)
logger.info(' [x] Waiting for messages. To exit press CTRL+C')
channel.start_consuming()
def main():
connection, channel = connect()
for n in range(100):
time.sleep(5)
body = f'Hello {n}'
logger.info(' [x] Sending %s', body)
channel.basic_publish(
exchange='',
routing_key='hello',
body=body,
)
connection.close()
def check_password(user, password):
un = user.get_user()
pw = user.get_pwhash()
conn = connect()
c = conn.cursor()
select = "SELECT password FROM admin WHERE username = %s"
c.execute(select, [un])
data = c.fetchall()
if data == []:
return False
else:
return bcrypt.check_password_hash(pw, password)
def get_user_by_name(username):
conn = connect()
c = conn.cursor()
select = "SELECT id, username, password FROM admin WHERE username = %s"
c.execute(select, [username])
data = c.fetchall()
if data == []:
return None
else:
user = User()
user.set_id(data[0][0])
user.set_user(data[0][1])
user.set_password(data[0][2])
return user
def import_library():
conn = connect()
try:
#values = read_library_csv('catalog.csv')
#insert_books(conn, values)
#insert_copies(conn, values)
#insert_authors(conn, values)
values = read_prison_csv('packages_confirmed.csv')
import_facilities(conn, values)
conn.commit()
except DatabaseError as e:
print(f'Error {e}')
sys.exit(1)
disconnect(conn)
def retrieve_facilities():
conn = connect()
c = conn.cursor()
select = "SELECT name, latitude, longitude FROM facility"
c.execute(select)
data = c.fetchall()
facilities = []
for tuple in data:
dictionary = {
'name': tuple[0],
'latitude': tuple[1],
'longitude': tuple[2]
}
facilities.append(dictionary)
for f in facilities:
f['name'] = f['name'].replace("^", "'")
return facilities
import sys
from app.connect import connect
if __name__ == '__main__':
print("AI script is alive.")
if len(sys.argv) != 3:
print("Illegal number of arguments")
else:
connect(sys.argv[1], int(sys.argv[2]))
print('AI script complete')