commit 9df27a809036d55962be1079763b2d13519ff39b
parent 28fba63115f92f2726ba610310c8a90035f0e643
Author: pyratebeard <root@pyratebeard.net>
Date: Sun, 25 Sep 2022 17:10:14 +0100
lots of search options
Diffstat:
| M | longbox | | | 73 | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++----------------- |
1 file changed, 56 insertions(+), 17 deletions(-)
diff --git a/longbox b/longbox
@@ -18,6 +18,19 @@
# * search stash
# * organise
# * credentials from variables
+# * fix date search with no issue number
+
+# multiple db connections
+#mydb1 = MySQLdb.connect(host="localhost",
+# user="skipper",
+# passwd="mysecret",
+# db="fish")
+#mydb2 = MySQLdb.connect(host="localhost",
+# user="skipper",
+# passwd="mysecret",
+# db="fruit")
+#cursor1 = mydb1.cursor()
+#cursor2 = mydb2.cursor()
from getpass import getpass
from mysql.connector import connect, Error
@@ -69,7 +82,7 @@ series_id = argument.series_id
search_series = '%'
search_number = None
-search_date = '%'
+search_date = None
if query:
search_series = query[0]
@@ -84,23 +97,35 @@ if query:
if search_number:
search_terms = """
- SELECT gcd_issue.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),gcd_issue.publication_date,right(gcd_issue.barcode, 5)
+ SELECT gcd_issue.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, '-', coalesce(gcd_series.year_ended, 'unknown')),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),gcd_issue.publication_date,right(gcd_issue.barcode, 5)
FROM gcd_series JOIN gcd_issue
- WHERE gcd_series.name LIKE CONCAT('%', %s, '%')
- AND (gcd_issue.number = %s OR gcd_issue.number LIKE CONCAT(%s, ' (%)'))
+ WHERE gcd_series.name LIKE CONCAT('%', %(search_series)s, '%')
+ AND (gcd_issue.number = %(search_number)s OR gcd_issue.number LIKE CONCAT(%(search_number)s, ' (%)'))
AND gcd_issue.series_id = gcd_series.id
- AND (gcd_series.year_began < %s AND gcd_series.year_ended > %s)
+ AND (gcd_series.year_began <= %(search_began)s AND (gcd_series.year_ended >= %(search_ended)s or gcd_series.year_ended is null))
"""
else:
- search_terms = """
- SELECT gcd_series.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),first.number,last.number
- FROM gcd_series
- INNER JOIN gcd_issue first
- ON first.id = gcd_series.first_issue_id
- INNER JOIN gcd_issue last
- ON last.id = gcd_series.last_issue_id
- WHERE gcd_series.name LIKE CONCAT('%', %s, '%')
- """
+ if search_date:
+ search_terms = """
+ SELECT gcd_series.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),first.number,last.number
+ FROM gcd_series
+ INNER JOIN gcd_issue first
+ ON first.id = gcd_series.first_issue_id
+ INNER JOIN gcd_issue last
+ ON last.id = gcd_series.last_issue_id
+ WHERE gcd_series.name LIKE CONCAT('%', %(search_series)s, '%')
+ AND (gcd_series.year_began <= %(search_began)s AND (gcd_series.year_ended >= %(search_ended)s or gcd_series.year_ended is null))
+ """
+ else:
+ search_terms = """
+ SELECT gcd_series.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),first.number,last.number
+ FROM gcd_series
+ INNER JOIN gcd_issue first
+ ON first.id = gcd_series.first_issue_id
+ INNER JOIN gcd_issue last
+ ON last.id = gcd_series.last_issue_id
+ WHERE gcd_series.name LIKE CONCAT('%', %s, '%')
+ """
show_issue = """
SELECT gcd_issue.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),gcd_issue.publication_date,right(gcd_issue.barcode, 5)
@@ -134,6 +159,14 @@ try:
cursor.execute(show_issue, (issue_id,))
for issue in cursor.fetchall():
x.add_row(issue)
+ box_id = issue[0]
+ box_series = issue[1]
+ box_year = issue[2]
+ box_number = issue[3]
+ box_variant = issue[4]
+ box_date = issue[5]
+ box_barcode = issue[6]
+ print('id: ', box_id, '\nseries: ', box_series)
elif series_id:
x.field_names = ['id','series','year','issue','variant','issue date','barcode']
cursor.execute(show_series, (series_id,))
@@ -141,9 +174,15 @@ try:
x.add_row(issue)
elif search_number:
x.field_names = ['id','series','year','issue','variant','issue date','barcode']
- cursor.execute(search_terms, (search_series, search_number, search_number, search_date, search_date,))
- for issue in cursor.fetchall():
- x.add_row(issue)
+ if search_date is None:
+ cursor.execute(search_terms, {'search_series': search_series, 'search_number': search_number, 'search_number': search_number, 'search_began': 9999 , 'search_ended': 0 })
+ for issue in cursor.fetchall():
+ x.add_row(issue)
+ else:
+ print(search_date)
+ cursor.execute(search_terms, {'search_series': search_series, 'search_number': search_number, 'search_number': search_number, 'search_began': search_date, 'search_ended': search_date,})
+ for issue in cursor.fetchall():
+ x.add_row(issue)
else:
x.field_names = ['id','series','year','first issue', 'last issue']
cursor.execute(search_terms, (search_series,))
