def get_players():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Player)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
p.id,
p.firstName,
p.lastName
FROM baseballcardapp_player p
""")
return db_cursor.fetchall()
def get_sets():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Set)
db_cursor = conn.cursor()
db_cursor.execute("""
select
s.id,
s.name,
s.year,
s.setnotes
from baseballcardapp_set s
""")
return db_cursor.fetchall()
def player_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Player)
db_cursor = conn.cursor()
letter = '%'
if request.GET.get('letter', None) is not None:
letter = f'{request.GET.get("letter", None)}%'
else:
letter = '%'
db_cursor.execute(
"""
SELECT
p.firstName,
p.lastName,
p.id
FROM baseballcardapp_player p
WHERE lastName LIKE ?
""", (letter, ))
all_players = db_cursor.fetchall()
template = 'players/list.html'
context = {'all_players': all_players, 'alphabet': ascii_uppercase}
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 baseballcardapp_player
(
firstName,lastName
)
VALUES (?, ?)
""", (form_data['firstName'], form_data['lastName']))
return redirect(reverse('baseballcardapp:players'))
def card_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Card)
db_cursor = conn.cursor()
db_cursor.execute("""
select
c.id,
c.playerId,
c.year,
c.setId,
c.cardNumber,
c.imagePathFront,
c.imagePathBack,
c.attribute
from baseballcardapp_card c
""")
all_cards = db_cursor.fetchall()
template = 'card/list.html'
context = {'all_cards': all_cards}
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 baseballcardapp_card
(
playerId_id, year, setId_id, cardNumber, imagePathFront, ImagePathBack, attribute
)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", (form_data["player"], form_data['year'], form_data["set"],
form_data['cardNumber'], form_data['imagePathFront'],
form_data['imagePathBack'], form_data['attribute']))
return redirect(reverse('baseballcardapp:main'))
def get_player_cards(playerId):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Card)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
c.id,
c.playerId_id,
c.year,
c.setId_id,
c.cardNumber,
c.imagePathFront,
c.imagePathBack,
c.attribute,
se.name name
FROM baseballcardapp_card c
JOIN baseballcardapp_set se ON c.setId_id = se.id
WHERE c.playerId_id = ?
""", (playerId,))
return db_cursor.fetchall()
def get_card(card_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Card)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
c.id,
c.playerId_id,
c.year,
c.setId_id,
c.cardNumber,
c.imagePathFront,
c.imagePathBack,
c.attribute,
pl.firstName firstName,
pl.lastName lastName
FROM baseballcardapp_card c
JOIN baseballcardapp_player pl ON c.playerId_id = pl.id
WHERE c.id = ?
""", (card_id, ))
return db_cursor.fetchone()