Problem in DB requests using if()s

[nikos]

Hello, iam reposting my previous post more clear written this time:

@app.route( '/seek', methods=['GET', 'POST'] )
def seek():

	pdata = ''

	name = request.args.get('name')
	month = request.args.get('month')
	year = request.args.get('year')

	try:

		if name and month.isdigit() and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE clientID = (SELECT id FROM clients WHERE name = %s) and MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (name, month, year) )

		if (not name) and month.isdigit() and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (month, year) )

		if name and (not month) and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE clientID = (SELECT id FROM clients WHERE name = %s) and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (name, year) )

		if (not name) and (not month) and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE YEAR(lastvisit) = %s ORDER BY lastvisit ASC''', year )

Although the first two if()s work properly the third and forth doesnt and i cannot seem to understand why.
This is the data that form user selection upon drop down menus:

	name = month = year = ''

	# populate names, months, years
	names.append( '' )
	names.sort()

	months = ( '', 'Ιανουάριος', 'Φεβρουάριος', 'Μάρτιος', 'Απρίλιος', 'Μάϊος', 'Ιούνιος', 'Ιούλιος', 'Αύγουστος', 'Σεπτέμβριος', 'Οκτώβριος', 'Νοέμβριος', 'Δεκέμβριος' )
	years  = ( '', 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019 )


	pdata = pdata + '''
	<br><h3><br><font color=red size=6> Επιλεκτική Αναζήτηση </font><br>
	<form methods="POST" enctype="multipart/form-data" action="%s">
		<select name="name"><option> %s </option></select>
		<select name="month"> 		 %s 		 </select>
		<select name="year"><option> %s </option></select>
		<br>
		<input type="image" src="/static/img/play.gif" name="seek" value="<Αναζήτηση>">
	</form>
	''' % (url_for( 'seek' ),

			'</option><option>'.join( names ),
			''.join( map( lambda args: '<option value="%s">%s</option>' % (args[0], args[1]), enumerate( months ) ) ),
			'</option><option>'.join( list( map( str, years ) ) ) )[python]

Can you help me please? This is troubling me days now....

[woooee]

what exact do you want the third line to select?

if name and (not month) and year.isdigit():

"not month" will only be True if month equals zero or an empty string. Also, what happens if "month" is not in the args. This line doesn't make sense to me as you have too many clientIDs

'''SELECT * FROM jobs WHERE clientID = (SELECT id FROM clients WHERE name = %s)

Try some variation of this instead

sql_stmt="select * from jobs where "
if name:
    sql_stmt += "name=%s" % (name)
if month:
    if name:
        sql_stmt += "and "
    sql_stmt+="month=%s" % (month)
## same for year 

[nikos]

The line with the clientID is needed to fetch data from another table.

not month checks if month is an empty string. I suspect it's not empty, but whitespace, perhaps a space character or a 0.

After trying this:

if name and (not month.isdigit()) and year.isdigit():

but it returns an empty set as before.

can you please complete your code so i can try it?

---

Actually it worked like this:
But i still dont understand what was the issue.

---

I made it even shorter

		if name and month.isdigit() and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE clientID = (SELECT id FROM clients WHERE name = %s) and MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (name, month, year) )

		elif month.isdigit() and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (month, year) )

		if name and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE clientID = (SELECT id FROM clients WHERE name = %s) and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (name, year) )

		elif year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE YEAR(lastvisit) = %s ORDER BY lastvisit ASC''', year )

Is there another better way to write the above code? shorter?

[snippsat]

Is there another better way to write the above code? shorter?

Yes,for Flask you should use Flask-SQLAlchemy it's like a standard for Flask DB.

No do not do it like this,you should use jinja2(build into Flask),
so HTML should be on client side not sent from server side.
Send only values from server side into jinja HTML template on client side.

pdata = pdata + '''
<br><h3><br><font color=red size=6> Επιλεκτική Αναζήτηση </font><br>
<form methods="POST" enctype="multipart/form-data" action="%s">
    <select name="name"><option> %s </option></select>
    <select name="month">          %s          </select>
    <select name="year"><option> %s </option></select>
    <br>
    <input type="image" src="/static/img/play.gif" name="seek" value="<Αναζήτηση>">
</form>
''' % (url_for( 'seek' ),

[nikos]

This does not work well some parts of if work and some others don't:

		if name and month.isdigit() and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE clientID = (SELECT id FROM clients WHERE name = %s) and MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (name, month, year) )

		elif name and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE clientID = (SELECT id FROM clients WHERE name = %s) and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (name, year) )

		elif month.isdigit() and year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (month, year) )

		elif year.isdigit():
			cur.execute( '''SELECT * FROM jobs WHERE YEAR(lastvisit) = %s ORDER BY lastvisit ASC''', year )

Please helpe me write it correctly and simply.