def list_librarians(request):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.id,
l.location_id,
l.user_id,
u.first_name,
u.last_name,
u.email
from libraryapp_librarian l
join auth_user u on l.user_id = u.id
""")
all_librarians = db_cursor.fetchall()
template_name = 'librarians/list.html'
# conext is data to be used in the template - similar to props and state
context = {
'all_librarians': all_librarians
}
return render(request, template_name, context)
return redirect(reverse('libraryapp:librarians'))
def get_book(book_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
b.id,
b.title,
b.isbn,
b.author,
b.year_published,
b.librarian_id,
b.location_id,
l.id library_id,
l.title library_name,
l.address library_address,
u.id user_id,
u.first_name,
u.last_name
FROM libraryapp_book b
join libraryapp_library l on l.id = b.location_id
join auth_user u on u.id = b.librarian_id
WHERE b.id = ?
""", (book_id, ))
return db_cursor.fetchone()
def book_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
select
b.id,
b.title,
b.isbn,
b.author,
b.year_published,
b.librarian_id,
b.location_id
from libraryapp_book b
""")
all_books = db_cursor.fetchall()
template = 'books/list.html'
context = {'all_books': all_books}
return render(request, template, context)
def librarian_list(request):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.id,
l.location_id,
l.user_id,
u.first_name,
u.last_name,
u.email
from libraryapp_librarian l
join auth_user u on l.user_id = u.id
""")
all_librarians = db_cursor.fetchall()
template_name = 'librarians/list.html'
context = {'all_librarians': all_librarians}
return render(request, template_name, context)
def library_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Library)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.title,
l.address
from libraryapp_library l
""")
all_libraries = db_cursor.fetchall()
template = 'libraries/list.html'
context = {'all_libraries': all_libraries}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO libraryapp_library
( title, address )
VALUES (?, ?)
""", (form_data['title'], form_data['address']))
return redirect(reverse('libraryapp:libraries'))
def get_librarian(librarian_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute(
"""
select l.id,
l.user_id,
l.location_id,
u.id librarian_id,
u.first_name librarian_first,
u.last_name librarian_last,
ll.id location_id,
ll.title location_name,
ll.address location_address
from libraryapp_librarian l
join auth_user u on u.id = l.user_id
join libraryapp_library ll on ll.id = l.location_id
where l.id = ?
""", (librarian_id, ))
return db_cursor.fetchone()
def book_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
select
b.id,
b.title,
b.isbn,
b.author,
b.year_published,
b.librarian_id,
b.location_id
from libraryapp_book b
""")
# all_books = []
# dataset = db_cursor.fetchall()
# for row in dataset:
# book = Book()
# book.id = row['id']
# book.title = row['title']
# book.isbn = row['isbn']
# book.author = row['author']
# book.year_published = row['year_published']
# book.librarian_id = row['librarian_id']
# book.location_id = row['location_id']
# all_books.append(book)
all_books = db_cursor.fetchall()
template = 'books/list.html'
context = {'all_books': all_books}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO libraryapp_book
(
title, author, isbn,
year_published, location_id, librarian_id, publisher
)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", (form_data['title'], form_data['author'], form_data['isbn'],
form_data['year_published'], request.user.librarian.id,
form_data["location"], form_data['publisher']))
return redirect(reverse('libraryapp:books'))
def book_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
b.id,
b.title,
b.isbn,
b.author,
b.year_published,
b.librarian_id,
b.location_id
FROM libraryapp_book b
""")
all_books = db_cursor.fetchall()
template = 'books/list.html'
context = {'all_books': all_books}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO libraryapp_book
(
title,
author,
isbn,
year_published,
location_id,
librarian_id
)
VALUES (?, ?, ?, ?, ?, ?)
""",
(
form_data['title'],
form_data['author'],
form_data['isbn'],
form_data['year_published'],
form_data['location'],
request.user.librarian.
id # request has the user/librarian info available here
))
return redirect(reverse('libraryapp:books'))
def list_librarians(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
# conn.row_factory = sqlite3.Row
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
l.id,
l.location_id,
l.user_id,
u.first_name,
u.last_name,
u.email
FROM libraryapp_librarian l
JOIN auth_user u on l.user_id = u.id
""")
all_librarians = db_cursor.fetchall()
# all_librarians = []
# dataset = db_cursor.fetchall()
# for row in dataset:
# lib = Librarian()
# lib.id = row["id"]
# lib.location_id = row["location_id"]
# lib.user_id = row["user_id"]
# lib.first_name = row["first_name"]
# lib.last_name = row["last_name"]
# lib.email = row["email"]
# all_librarians.append(lib)
template_name = 'librarians/list.html'
context = {
'all_librarians': all_librarians
}
return render(request, template_name, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
INSERT INTO libraryapp_librarian
(
location_id, user_id
)
VALUES (?, ?)
""",
(form_data['location'], request.user.librarian.id))
return redirect(reverse('libraryapp:librarians'))
def list_library(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
conn.row_factory = model_factory(Library)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.id,
l.title,
l.address
from libraryapp_library l
""")
all_libraries = db_cursor.fetchall()
# all_libraries = []
# dataset = db_cursor.fetchall()
# for row in dataset:
# lib = Library()
# lib.id = row["id"]
# lib.title = row["title"]
# lib.address = row["address"]
# all_libraries.append(lib)
template_name = 'libraries/list.html'
context = {
'all_libraries': all_libraries
}
return render(request, template_name, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
#below, first argument is the SQL data, second argument is a tuple
db_cursor.execute("""
INSERT INTO libraryapp_library
(
title, address
)
VALUES (?, ?)
""",
(form_data['title'], form_data['address']))
return redirect(reverse('libraryapp:library'))
def book_list(request):
if request.method == "GET":
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
select
b.id,
b.title,
b.isbn,
b.author,
b.year_published,
b.librarian_id,
b.location_id
from libraryapp_book b
""")
all_books = db_cursor.fetchall()
template = "books/list.html"
context = {"all_books": all_books}
return render(request, template, context)
elif request.method == "POST":
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO libraryapp_book
(
title, author, isbn,
year_published, librarian_id, location_id
)
VALUES (?, ?, ?, ?, ?, ?)
""",
(
form_data["title"],
form_data["author"],
form_data["isbn"],
form_data["year_published"],
request.user.librarian.id,
form_data["location"],
),
)
return redirect(reverse("libraryapp:books"))
def get_libraries():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Library)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.id,
l.title,
l.address
from libraryapp_library l
""")
#Return db_cursor.fetchall because you will be calling get_libraries in another function
return db_cursor.fetchall()
def get_libraries():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Library)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
l.id,
l.name,
l.address
FROM libraryapp_library l
""")
return db_cursor.fetchall()
def get_librarians():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.id,
l.location_id,
l.user_id
from libraryapp_librarian l
""")
return db_cursor.fetchall()
def get_library(library_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Library)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
l.id,
l.title,
l.address
FROM libraryapp_library l
WHERE l.id = ?
""", (library_id, ))
return db_cursor.fetchone()
def get_libraries(): # gets libraries for form dropdown
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Library)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.id,
l.title,
l.address
from libraryapp_library l
""")
return db_cursor.fetchall()
def library_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Library)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.id,
l.title,
l.address
from libraryapp_library l
""")
all_libraries = db_cursor.fetchall()
# dataset = db_cursor.fetchall()
# for row in dataset:
# library = Library()
# library.id = row["id"]
# library.title = row["title"]
# library.address = row["address"]
# all_libraries.append(library)
template_name = 'libraries/list.html'
context = {'all_libraries': all_libraries}
return render(request, template_name, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO libraryapp_library
(
title, address
)
VALUES (?, ?)
""",
#The values thing above is quality control, protection from bad data injection.
(form_data['title'], form_data['address']))
return redirect(reverse('libraryapp:libraries'))
def get_library(library_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Library)
db_cursor = conn.cursor()
db_cursor.execute("""
select
l.id,
l.name,
l.address
from libraryapp_library l
where l.id = ?
""", (library_id,))
return db_cursor.fetchone()
def get_library(library_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
b.id,
b.title,
b.location_id
FROM libraryapp_library b
WHERE b.id = ?
""", (location_id,))
return db_cursor.fetchone()
def book_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
# row_factory has a default function, but we defined a custom model_factory function
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
# querying all books from db using sqlite3
db_cursor.execute("""
select
b.id,
b.title,
b.isbn,
b.author,
b.year_published,
b.librarian_id,
b.location_id
from libraryapp_book b
""")
# assigns list of instances of books to all_books
all_books = db_cursor.fetchall()
# template we want to load to display the info we queried
template = 'books/list.html'
# dict we are passing to our template to iterate (has to be dict)
context = {'all_books': all_books}
# returns httprequest, the template, and the info we queried(context)
return render(request, template, context)
# gets called when user wants to add a book
elif request.method == 'POST':
# form_data gets assigned the dictionary of all the inputs from the user
form_data = request.POST
# inserts and adds book to the db
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO libraryapp_book
(
title, author, isbn,
year_published, location_id, librarian_id
)
VALUES (?, ?, ?, ?, ?, ?)
""", (form_data['title'], form_data['author'], form_data['isbn'],
form_data['year_published'], request.user.librarian.id,
form_data["location"]))
# redirects after post to /books where you can see your new book
return redirect(reverse('libraryapp:books'))
def get_librarian(librarian_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute(
"""
select
l.id,
l.location_id,
l.user_id
from libraryapp_librarian l
where l.id = ?
""", (librarian_id, ))
return db_cursor.fetchone()
def get_librarians():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
l.id,
u.first_name,
u.last_name,
u.email
FROM libraryapp_librarian l
JOIN auth_user u ON l.user_id = u.id
""")
return db_cursor.fetchall()
def book_list(request):
if request.method == 'GET':
# connection is passed in with the db absolute path
with sqlite3.connect(Connection.db_path) as conn:
# Replaces conn.row_factory = sqlite3.Row, can only specify one row factory
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
select
b.id,
b.title,
b.isbn,
b.author,
b.year_published,
b.publisher,
b.librarian_id,
b.location_id
from libraryapp_book b
""")
# fetchall gets list of tuples, row factory executes to turn into book objects
all_books = db_cursor.fetchall()
# template is holding the template created in templates folder
template = 'books/list.html'
context = {'all_books': all_books}
# takes in the HTTP request, specified template, and dictionary of data to be used
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO libraryapp_book
(
title, author, isbn,
year_published, publisher, location_id, librarian_id
)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", (form_data['title'], form_data['author'], form_data['isbn'],
form_data['year_published'], form_data['publisher'],
request.user.librarian.id, form_data["location"]))
return redirect(reverse('libraryapp:books'))
def get_books():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
select b.id,
b.title,
b.isbn,
b.author,
b.year,
b.librarian_id,
b.location_id
FROM libraryapp_book b
""")
return db_cursor.fetchall()
def get_librarian(librarian_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
u.username,
u.first_name,
u.last_name,
u.email
FROM auth_user u
WHERE u.id = ?
""", (librarian_id, ))
return db_cursor.fetchone()
def book_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
select
b.id,
b.title,
b.ISBN_number,
b.author,
b.year_published,
b.librarian_id,
b.location_id
from libraryapp_book b
""")
all_books = db_cursor.fetchall()
template = 'books/list.html'
context = {
'all_books': all_books
}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
INSERT INTO libraryapp_book
(
title, author, ISBN_number,
year_published, location_id, librarian_id
)
VALUES (?, ?, ?, ?, ?, ?)
""",
(form_data['title'], form_data['author'],
str(form_data['ISBN_number']), form_data['year_published'],
request.user.librarian.id, form_data["location"]))
return redirect(reverse('libraryapp:books'))
def get_librarian(librarian_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
l.id,
l.location_id,
l.email,
l.first_name,
l.last_name
FROM libraryapp_librarian l
WHERE l.id = ?
""", (librarian_id,))
return db_cursor.fetchone()
def get_librarian(librarian_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
l.id,
l.location_id,
l.user_id,
u.first_name,
u.last_name,
u.email
from libraryapp_librarian l
join auth_user u on l.user_id = u.id
WHERE l.id = ?
""", (librarian_id, ))
return db_cursor.fetchone()
def get_librarian(librarian_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Librarian)
db_cursor = conn.cursor()
db_cursor.execute(
"""
select
a.first_name,
a.last_name,
a.email,
lib.name lib_name
from libraryapp_librarian l
join auth_user a on l.user_id = a.id
join libraryapp_library lib on l.library_id = lib.id
where l.id = ?
""", (librarian_id, ))
return db_cursor.fetchone()
def get_book(book_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Book)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
b.id,
b.title,
b.isbn_number,
b.author,
b.year_published,
b.librarian_id,
b.location_id
FROM libraryapp_book b
WHERE b.id = ?
""", (book_id,))
return db_cursor.fetchone()
