Python Forum
Selection based of variables issue
Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Selection based of variables issue
#1
name = request.args.get('name')
        month = request.args.get('month')
        year = request.args.get('year')

        try:
                if '=' not in name + month + year:
                        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 '=' not in name + year:
                        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 '=' not in month + year:
                        cur.execute( '''SELECT * FROM jobs WHERE MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (month, year) )
                elif '=' not in year:
                        cur.execute( '''SELECT * FROM jobs WHERE YEAR(lastvisit) = %s ORDER BY lastvisit ASC''', year )
                else:
Hello, iam tryting to execute mysql queries based on python variables.
Problem is that only the first clause works as expected and not the rest.'=' means variables contains no value.

How can i make it work?

Request arguments come from a previous endpoint being executed that had a form within it to get the values. elif '=' not in month + year: and elif '=' not in month + year: and elif '=' not in name + year: does no return any results althoughtthey are present innto the database.
Reply
#2
You have the if structure backwards. If the character is not in the first if clause then its not going to be in any of the other ones either because the first one has all of the options listed in it from the elif's. So it will either execute the first if clause or the else clause, but never the elif's.

also there is no point in checking it twice. So if you started the if structure with:
if '=' not in year:
Then the second one
elif '=' not in month:
because it would not be in year and month if it is not in year. So thus you only have to check for month.
Recommended Tutorials:
Reply
#3
Can you please show me how can it be written correctly?

(Feb-02-2019, 02:53 PM)nikos Wrote: Can you please show me how can it be written correctly?

Like this you mean?

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

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

		elif '=' not in name + year:
			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 '=' not in name + month + year:
			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) )
			
		else:
Reply
#4
i believe it would be something along the lines of this
if '=' not in year:
    cur.execute( '''SELECT * FROM jobs WHERE YEAR(lastvisit) = %s ORDER BY lastvisit ASC''', year )
elif '=' not in month:
    cur.execute( '''SELECT * FROM jobs WHERE MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (month, year) )
elif '=' not in name:
    cur.execute( '''SELECT * FROM jobs WHERE clientID = (SELECT id FROM clients WHERE name = %s) and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (name, year) )
although the first selection is not used because its condition is repetetive...and you didnt post the else clause
Recommended Tutorials:
Reply
#5
It does not work neither in my 2nd attempt, nor in your attempt.
For examle i select a specific month and a year and it shows me instead ALL of the months of this year.

Same with name, i select a specific name and a a year and i got returned a bunch of names instead.

Even with this
elif ('=' not in name) and ('=' not in year) and ('=' in month): 
i cannot get it to work.

Could you please help" i cannot get the desired results.
Reply
#6
Hello, anyone?
Reply
#7
Can you re-ask your question with full, minimal code to reproduce the issue along with the explicit input and desired output?
Reply
#8
I have 3 fieldsa within a form , a name field, a month field and a year field.

As first value fields have '=======' whuch meant to mean not selected.

No the user can make combination asking how many money

name + month + year
month + year
name + year
year

that is why i tried with the following code
		if '=' not in name + month + year:
			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 '=' not in month + year:
			cur.execute( '''SELECT * FROM jobs WHERE MONTH(lastvisit) = %s and YEAR(lastvisit) = %s ORDER BY lastvisit DESC''', (month, year) )

		elif '=' not in name + year:
			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 '=' not in year:
			cur.execute( '''SELECT * FROM jobs WHERE YEAR(lastvisit) = %s ORDER BY lastvisit ASC''', year )
The if and the first elif are returnings results properly, while the other 2 elifs always return mixed results mostly of them irrelevant.
Reply
#9
@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 '=' not in year:
			cur.execute( '''SELECT * FROM jobs WHERE YEAR(lastvisit) = %s ORDER BY lastvisit ASC''', year )

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

		elif '=' not in name + year:
			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 '=' not in name + month + year:
			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) )

		else:
			pdata = pdata + "<h2><font color=red>Πώς να γίνει αναζήτηση αφού δεν επέλεξες ούτε πελάτη ούτε μήνα ή τουλάχιστον το έτος?"


		data = cur.fetchall()

		hits = money = 0

		for row in data:
			hits += 1
			money = money + row[2]


		pdata = pdata + '''
		<body background="/static/img/pelatologio.jpg">
		<center><h2><font color=yellow size=5> Απολαβές από </font> <font color=lime size=5> [ %s - %s - %s ] </font>
		<br><br>
		<font color=white>
		Επισκευές:  <font color=violet> %s </font>
		Σύνολο:     <font color=orangered> %s € </font>
		<br><br>
		<table border=5 cellpadding=5 bgcolor=black>
		<th><u><font color=lime size=5> Πελάτης </th>   <th><u><font color=tomato size=5> Περιγραφή </th>
		<th><u><font color=cyan size=5> Αμοιβή </th>    <th><u><font color=orange size=5> Ημερομηνία </th>
		''' % (name, month, year, hits, money)

		for row in data:
			(clientID, task, price, lastvisit) = row

			cur.execute( '''SELECT name FROM clients WHERE id = %s''', clientID )
			data = cur.fetchone()

			name = data[0]
			lastvisit = lastvisit.strftime('%A, %e %b %Y')

			pdata = pdata + '''
			<tr>
				<td><center><font color=lime   size=5> %s </td>
				<td><center><font color=tomato size=5> %s </td>
				<td><center><font color=cyan   size=5> %s </td>
				<td><center><font color=orange size=5> %s </td>
			</tr>
			''' % (name, task, price, lastvisit)
	except pymysql.ProgrammingError as e:
		print( repr(e) )

	pdata = pdata + "<meta http-equiv='REFRESH' content='200;%s'>" % url_for( 'index' )
	return pdata
Here is all relevenat code in case problem is elsewhere.
Reply
#10
Anyone?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  generate random variables based on a non-standard t-distribution nathalie 4 3,374 Dec-03-2019, 12:11 AM
Last Post: scidam

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020