Issue 128: Update query to cast datetime as Julian date
Status:  NoFix
Owner: ----
Closed:  Nov 2010
Reported by Tony.Fis...@gmail.com, Oct 15, 2010
import pyodbc, datetime

db = r"E:\TEMP\WalrusPathProcessing.mdb"
DRIVER = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}'
cnxn = pyodbc.connect(DRIVER, DBQ=  db, autocommit=True)
cursor = cnxn.cursor()

#  VB format of SQL statment:
#  SQL = 'UPDATE FilteredLocations_tbl SET JDay = Format([dt],"y") WHERE (((Deployment)=371));'
#  I am struggling to compose this with a Python funciton.
SQL = 'UPDATE FilteredLocations_tbl SET JDay = ([dt]).strftime(%j))  WHERE (((Deployment)=371));'
cursor.execute(SQL)

cnxn.commit()
cursor.close()
cnxn.close()

#####What is the expected output?
## I expect this to update the field [Jday] with the Julian date
## of the input field [dt].
#####What do you see instead?
## I get an error submitting the cursor.execute(SQL).
## Is there a way to call a function on the [datetime field] that may
## be passed to the cursor.execute? 


####What version of the product are you using? Python 2.4 on Windows XP.



Oct 21, 2010
#2 Tony.Fis...@gmail.com
I have tried this with an updated version of Python (2.2.6) and pyodbc (2.1.8).
I still am unable to commit the update to the table fields.
Please see code below.

## Run with
## pyodbc 2.1.8 (c:\python26\lib\site-packages\pyodbc.pyd)
## Python 2.6.6
## windows verion (5, 1, 2600, 2, 'Service Pack 3')

import pyodbc

db = r"D:\TEMP\test.mdb"
DRIVER = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}'
cnxn = pyodbc.connect(DRIVER, DBQ=  db, autocommit=True)
cursor = cnxn.cursor()

SQL = "SELECT Test_tbl.Deployment, Test_tbl.relocation, Test_tbl.dt, Test_tbl.JDay FROM Test_tbl;"
 
cursor.execute(SQL)
for row in cursor:
    d = row.dt
    jd = int(d.strftime('%j'))
    row.JDay = jd
    # cnxn.commit  ## I have tried placing a cnxn.commit command here, with no results
    print row.dt, row.JDay  # This shows the updated value.
                            # However, when I open up the database,
                            # I find that the values have not been updated.

#cnxn.commit   ## I have tried placing a cnxn.commit command here, with no results
cursor.close()
cnxn.close()
test.mdb
216 KB   Download
Nov 21, 2010
Project Member #4 mkleehammer
I believe the error in the first report is due to the "%j".  The % character is an escape character in Python, so you'll need two of them:

  UPDATE FilteredLocations_tbl SET JDay = ([dt]).strftime(%%j))  WHERE (((Deployment)=371))

In the 2nd version, you've selected a value and simply replaced your variables value.  That isn't going to update the database.  The row objects merely hold the values selected, they are no longer connected to the database, so to speak.
Status: NoFix