John CORRY wrote:
> Hi All,
>
> I am using the code below to select items from a visual foxpro database
> where the dates are between the 31/01/2006 and 31/12/2006. The good
> news is that the code below works.
>
> However, I want to make the from and to dates variable. I want to
> change the range depending on user input. I can't get this to work. I
> have tried the code below marked "Tried" but I get the error:
>
> Traceback (most recent call last):
> File "C:\test\timemanager.py", line 16, in ?
> c.execute('SELECT * FROM times where rt_weekst >= ? and rt_weekst <=
> ? and rt_type == ?', (a,b,"R",))
> DataError: ('22005', 301, '[Microsoft][ODBC Visual FoxPro
> Driver]Operator/operand type mismatch.', 4579)
>
> Code that works is below:
>
>
> import mx.ODBC
> import mx.ODBC.Windows
> import mx.DateTime
>
>
>
> db = mx.ODBC.Windows.DriverConnect('DSN=tnt')
> c = db.cursor()
> c.execute('SELECT * FROM times where rt_weekst >= date(2006,01,31) and
> rt_weekst <= date(2006,12,31) and rt_type == ?', ("R",))
> for row in c.fetchall():
> print row
> row = str(row)
>
> c.close()
>
>
> Tried but get errors:
>
> import mx.ODBC
> import mx.ODBC.Windows
> import mx.DateTime
>
> import datetime
> a = datetime.date(2006,01,31)
> b = datetime.date(2006,12,31)
> db = mx.ODBC.Windows.DriverConnect('DSN=tnt')
> c = db.cursor()
> c.execute('SELECT * FROM times where rt_weekst >= ? and rt_weekst <= ?
> and rt_type == ?', (a,b,"R",))
> for row in c.fetchall():
> print row
> row = str(row)
>
> c.close()
>
> Is there a way to format the date so that the Select statement works?
>
a = "date(%i,%i,%i)" % (2006,01,31)
b = "date(%i,%i,%i)" % (2006,12,31)
sql = 'SELECT * FROM times where rt_weekst >= %s and rt_weekst <= %s and
rt_type = ' % (a,b,"R")
c.execute(sql)
OR you could shorten it a bit:
sql = 'SELECT * FROM times where rt_weekst between %s and %s and rt_type =
"%s"' % (a,b,"R")
I like to assign sql first, then execute it, as I can inspect sql if there is a
problem.
FoxPro also recognizes date constants like {12/31/2006} (note braces not
parentheses).
--
Bob Gailer
510-978-4454
_______________________________________________
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
