How to use multiple where varibles in my sql select statement in python?

Question

Question I have a simple application to query some of my oracle tables I want to be able to grab the value of my text boxes which work but, I keep getting this error any help would be greatly appreciated!

test.py

def grabEnts():
   global date 
   connection = cx_Oracle.connect('xxxxxxxx/[email protected]:xxxx/xxx')
   cursor = connection.cursor()
   startdate = e1.get()
   enddate = e2.get()
   #fs = datetime.date(startdate)
   #fe = datetime.date(endate)
   //this works but, I would like to use both startdate and endate
   #cursor.execute("SELECT EMP_ID FROM TO_ENTRIES WHERE LEAVE_START >= '%s'" % startdate)
    //Like this but I can't get it to work I keep getting this error 
    File "test.py", line 62, in grabEnts
   cursor.execute('SELECT EMP_FIRST_NAME FROM TO_ENTRIES WHERE LEAVE_START >=%s AND LEAVE_END <=%s', startdate, enddate)
   TypeError: function takes at most 2 arguments (3 given)

   cursor.execute('SELECT EMP_FIRST_NAME FROM TO_ENTRIES WHERE LEAVE_START >=%s AND LEAVE_END <=%s', startdate, enddate)
   for row in cursor:
       outputent.writerow(row)
   cursor.close()
   connection.close()

How many parameters are you passing to cursor.execute()? How many does it expect? What is therefore wrong? ;-) By the way, and I can't stress this enough, in general inserting user input directly into SQL opens you up to a so-called SQL injection attack and should therefore be avoided like the plague. You should instead use parameterized queries. See here: bobby-tables.com — W.Prins
– W.Prins, Commented Nov 3, 2015 at 15:22

Tom Karzes · Accepted Answer · 2015-11-03 15:05:11Z

1

When providing multiple values for a format string, you need to wrap them in parentheses:

"My name is %s.  Your name is %s." % ("Sam", "Joe")

answered Nov 3, 2015 at 15:05

Tom Karzes

24.3k3 gold badges28 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Snowman288 Over a year ago

Okay so how would I do this then ('SELECT * FROM TO_ENTRIES WHERE LEAVE_START >= %s AND LEAVE_END <= %s' % (startdate, enddate)) because this will not work either ... I get this error NameError: global name 'stardate' is not defined @TomKarzes

Snowman288 Over a year ago

I tried it this way as well and got this error cursor.execute('SELECT EMP_FIRST_NAME FROM TO_ENTRIES WHERE LEAVE_START >=%s AND LEAVE_END <=%s' % ("stardate", "enddate")) DatabaseError: ORA-00904: "ENDDATE": invalid identifier @TomKarzes

Tom Karzes Over a year ago

Did you type "startdate" or "stardate"? Make sure you spell it correctly.

Tom Karzes Over a year ago

It looks like you're doing the right thing, you just need to type your variable names correctly.

Tom Karzes Over a year ago

And no, don't put the variable names in quotes.

|

Ward · Accepted Answer · 2015-11-03 15:27:26Z

0

Using string formatting to generate SQL is general a bad idea. If a user inputs something like ; DROP TABLE blah this will be executed in your code. This is a typical example of SQL injection...

To avoid, use parameters in your query, like so:

cursor.execute('SELECT EMP_FIRST_NAME FROM TO_ENTRIES WHERE LEAVE_START between :start AND :end', {'start': 1, 'end': 10})

answered Nov 3, 2015 at 15:27

Ward

2,8721 gold badge26 silver badges38 bronze badges

Collectives™ on Stack Overflow

How to use multiple where varibles in my sql select statement in python?

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related