| Issue 128: | Update query to cast datetime as Julian date | |
| 1 person starred this issue and may be notified of changes. | Back to list |
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.
Nov 21, 2010
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
|
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()216 KB Download