[Tutor] Dates
Alan,
Thanks for the help. I have converted the dates to strings but I get
the same error. Please see the updated code below, is this what you
meant by converting the dates to strings?
import mx.ODBC
import mx.ODBC.Windows
import mx.DateTime
import datetime
a = datetime.date(2006,01,31)
b = datetime.date(2006,12,31)
c = str(a)
d = str(b)
print a,b,c,d
db = mx.ODBC.Windows.DriverConnect('DSN=tnt')
c = db.cursor()
c.execute('SELECT * FROM times where rt_weekst = ? and rt_weekst = ?
and rt_type == ?', (c,d,R,))
for row in c.fetchall():
print row
row = str(row)
c.close()
The output is below:
2006-01-31 2006-12-31 2006-01-31 2006-12-31
Traceback (most recent call last):
File C:\test\timemanager.py, line 18, in ?
c.execute('SELECT * FROM times where rt_weekst = ? and rt_weekst =
? and rt_type == ?', (c,d,R,))
DataError: ('22005', 301, '[Microsoft][ODBC Visual FoxPro
Driver]Operator/operand type mismatch.', 4579)
Is there another way I can approach this problem? Say if I use
something like:
c.execute('SELECT * FROM times where rt_weekst = date(?) and rt_weekst
= date(?) and rt_type == ?', (c,d,R,))
I get the following error:
2006-01-31 2006-12-31 2006-01-31 2006-12-31
Traceback (most recent call last):
File C:\test\timemanager.py, line 18, in ?
c.execute('SELECT * FROM times where rt_weekst = date(?) and
rt_weekst = date(?) and rt_type == ?', (c,d,R,))
ProgrammingError: ('37000', 229, '[Microsoft][ODBC Visual FoxPro
Driver]Too few arguments.', 4579)
Thanks for any suggestions.
John.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Dates
a = datetime.date(2006,01,31)
b = datetime.date(2006,12,31)
c = str(a)
d = str(b)
I'm not sure what format your data base expects for dates
but given you used the SQL date function before I'd go for
that again. Just pass the string equivalent of your dates
into the SQL date function. You probavly don;t need the
datetime stuff above at all.
Is there another way I can approach this problem? Say if I use
something like:
c.execute('SELECT * FROM times where rt_weekst = date(?) and
rt_weekst
= date(?) and rt_type == ?', (c,d,R,))
Yes this is what I mean but to male it work c and d need to be in
the same format you had before, something like
c = 2006,01,31
That will then be inserted into the query string.
I'm not sure if it will work like that, you may need
to split it into 3 separate parameters.
Driver]Too few arguments.', 4579)
I think thats because it sees the x-y-z format as an arithmetic
sum or as a single non standard string.
ButIi'm guessing a bit here as I've never used the ODBC driver nor
Foxbase.
HTH,
Alan G.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Dates
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
[Tutor] Dates
Bob and Alan,
Thanks for the help. I have gone with the following code and it 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 = %s ' % (a,b,R,)
db = mx.ODBC.Windows.DriverConnect('DSN=tnt')
c = db.cursor()
c.execute(sql)
As you rightly pointed out, I needed to get my sql string formatted and
working before putting it anywhere near the c.execute command.
Many thanks,
John.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
[Tutor] Dates
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?
Thanks,
John.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
[Tutor] Dates and databases, and blobs and Python.
Hi,
Just a quick query. I want to store dates and work with them in my
SQLite database.
There's no specific need for any calculations to done on one side or
another (i.e. it's a single user database).
I googled how to work with dates in SQL, and I got one like this -
SELECT * FROM totp
WHERE wk BETWEEN '1980/05/20'
AND '1980/05/26'
as an example of finding a range for a date. I'm not too sure about
how SQL works with dates (and times for that matter), so I'm tempted
to stick to what I know and just store dates/times as strings in the
db, and then use them to create datetime objects when needed.
i.e.
cx.execute('select date, time from foo where c_id = 10')
dat = cx.next()
tim = cx.next()
print dat, time
2005-12-31, 7:00
splitD = dat.split('-')
splitT = time.split(':')
intD = [int(i) for item in splitD]
intT = [int(i) for item in splitT]
theDateTime = datetime.datetime(intD[0], intD[1], intD[2], intT[0], intT[1])
Although working with dates like that doesn't seem that flash either.
Alternatively, I was thinking of storing the actual datetime object in
the database (this is a blob I believe?), and that's a whole new
kettle of fish.
So far I've tried this -
import datetime
theDT = datetime.datetime(2004, 12, 31, 7, 30)
print theDT
2004-12-31 07:30:00
import sqlite
c = sqlite.connect('foo.db')
cx = c.cursor()
import pickle
j = pickle.dumps(theDT)
cx.execute('insert into bob values(%s)', j)
cx.execute('select A from bob')
q = cx.next()
print q
(cdatetime\ndatetime\np0\n(S'\\x07\\xd4\\x0c\\x1f\\x07\\x1e\\x00\\x00\\x00\\x00'\np1\ntp2\nRp3\n.,)
w = pickle.loads(q[0])
print w
2004-12-31 07:30:00
So, it works, but I'm not too sure. I tend to have a random approach
to using the standard library, as I don't fully understand what all of
the modules do.
This provokes the following questions -
1. Should I be using StringIO for this instead?
2. Would my retrieved unpickled datetime object still work if datetime
hadn't been imported?
3. Is there a better way to work with blobs and Python?
And then there's the overall question -
What would be the least fiddly least error prone way of working with
dates and times? Python or SQL?
Thank you for your time.
Regards,
Liam Clarke
--
'There is only one basic human right, and that is to do as you damn well please.
And with it comes the only basic human duty, to take the consequences.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Dates and databases, and blobs and Python.
Liam Clarke wrote: And then there's the overall question - What would be the least fiddly least error prone way of working with dates and times? Python or SQL? Liam, SQL is another language. You need to learn it like you are learning Python. It has pretty decent support for dates and times as part of the ANSI SQL spec. There are defined types, SQL functions, etc. week(date) = week(mydate) for instance. I tend to prefer to write programming language agnostic databases because that way I can interact with them from any random language I need to. Sometimes things go bad and a quick little script which does: rows = exec(select * from table) exec(begin transaction) for row in rows: row[3] = row[2] + row[4] - 5 # we decided on a new value exec(update fields in table) can be handy. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Dates and databases, and blobs and Python.
Hi Kent,
Most databases have a DATE type and you can directly store and retrieve date
objects. Looking at the
pysqlite code I see it uses mxDateTime for date support - it doesn't use the
Python datetime module.
So the first step is to install mxDateTime from
http://www.egenix.com/files/python/mxDateTime.html
OK, now let's create a table with a DATE column:
import sqlite
con = sqlite.connect('mydatabase.db')
cur = con.cursor()
cur.execute('CREATE TABLE foo (aDate DATE)')
The sqlite Date class is called sqlite.main.Date. (According to the DB-API
this should be exposed as
sqlite.Date but whatever...). Let's create a Date:
d=sqlite.main.Date(2005, 3, 26)
d
DateTime object for '2005-03-26 00:00:00.00' at a31f20
insert it into the table:
cur.execute('insert into foo (aDate) values (%s)', d)
and read it back:
cur.execute('select * from foo')
cur.fetchall()
[(DateTime object for '2005-03-26 00:00:00.00' at 9bf660,)]
Note how the data returned from fetchall() contains a Date object.
BTW there doesn't seem to be much available in the way of pysqlite docs. You
should probably become
familiar with the DB-API spec (http://www.python.org/peps/pep-0249.html) and
the pysqlite source...
Thanks for that, I've installed mxDateTime, and just playing around at
the with it, I can see that it's very useful, and being able to
pass it as a parameter is just what I needed.
And yeah, I'm familiar with the (lack of) documentation for pysqlite.
Apparently a whole new version is in the works, based around the
latest SQLite release, and it's a one man show as far as I can tell,
so perhaps I should get my skills up to speed and write the docs. : )
Regards,
Liam Clarke
--
'There is only one basic human right, and that is to do as you damn well please.
And with it comes the only basic human duty, to take the consequences.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
