[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
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
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
[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
John, > c.execute('SELECT * FROM times where rt_weekst >= date(2006,01,31) > and > rt_weekst <= date(2006,12,31) and rt_type == ?', ("R",)) > a = datetime.date(2006,01,31) > b = datetime.date(2006,12,31) > c.execute('SELECT * FROM times where rt_weekst >= ? and rt_weekst <= > ? > and rt_type == ?', (a,b,"R",)) In the first case the dates are strings passed to the SQL date function. In the second they are Python datetime objects. I suspect you need to convert the datetime objects to strings before passing them as arguments to execute() Alan G ___ 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
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 > > > 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() > [(,)] > > 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
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
[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