Deleting rows from an access table based on multiple conditions in python along with a date field

I am trying to delete rows from an access database table, based on two columns one which is released_by and released_date.

Sudo code

WHERE released_by = '27' and released_date would change based on the day of the month.


Released_Date if day ==1 then delete previous month's data else current months data.

import pandas as pd
import numpy as np
import datetime
from datetime import date
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
import pyodbc
import calendar
import xlrd
import defusedxml
from defusedxml.common import EntitiesForbidden
from xlrd import open_workbook
defusedxml.defuse_stdlib()

# connecting to access database
conn = pyodbc.connect(r'Driver=Microsoft Access Driver (*.mdb, *.accdb);DBQ=C:UsersDesktopInsights.accdb;')
insights_dbcurs = conn.cursor()

select_auto_releases = "SELECT * FROM RELEASES WHERE RELEASED_BY = '27'"
autoreleases = insights_dbcurs.execute(select_auto_releases).fetchall()
#display(autoreleases)

for row in autoreleases:
previousmonth = datetime.now() - relativedelta(months=1)
previousmonth = previousmonth.strftime("%m - %Y")
currentmonth = datetime.now()
currentmonth = currentmonth.strftime("%m - %Y")
if ((row.autoreleases['RELEASED_DATE']).strftime) ==1:
try:
delete = 'DELETE * FROM RELEASES WHERE RELEASED_DATE = ? and RELEASED_BY = ?'
insights_dbcurs.execute(delete,'RELEASED_DATE':currentmonth,'RELEASED_BY':'27')
except:
delete = 'DELETE * FROM RELEASES WHERE RELEASED_DATE = ? and RELEASED_BY = ?'
insights_dbcurs.execute(delete,'RELEASED_DATE':previousmonth,'RELEASED_BY':'27')

insights_dbcurs.close()
conn.commit()
conn.close()


It does not work. I get:

AttributeError: 'pyodbc.Row' object has no attribute 'autoreleases'

if I remove Row.autorelease, I get

Row.autorelease

sql has 0 attributes, 2 being passed.

How do i avoid this. Any suggestions would be really helpful.

if ((row.RELEASED_DATE).strftime) ==1:

'AttributeError: 'str' object has no attribute 'strptime' or strftime '

((row.RELEASED_DATE).astype(date).strftime ==1

row.RELEASED_DATE

.strptime(...)

.strftime("%m - %Y")

.strftime("%m - %Y")

if datetime.now().strftime('%d') !=1:

delete = 'DELETE * FROM RELEASES WHERE RELEASED_DATE = ? and RELEASED_BY = ?'

insights_dbcurs.execute(delete,'RELEASED_DATE':currentmonth,'RELEASED_BY':'27')

SystemError: <class 'pyodbc.ProgrammingError'> returned a result with an error set

1 Answer
1

Consider re-factoring your code for several issues:

try/except is usually used for handling runtime exceptions and errors, not application logic. Use the if/else for these situations.

try/except

if/else

Remove the unneeded for loop as you are not deleting row by row since no unique identifier is being passed into DELETE statements. In actuality, you are running the deletion process across whole table if the logic passes that current row not filtered to that one row.

for

DELETE

Use pure SQL with one DELETE statement and avoid the Python handling of time elements which as you see maintains conversion issues from the database. MS Access SQL does have date functions such as Date() (current date) and DatePart() for month/day extractions. Additionally, MS Access can run complex subquery logic in DELETE.

DELETE

Date()

DatePart()

DELETE

SQL

Using IN subquery for both date conditions. NOTE: ID should be replaced with unique identifier of table. Check SELECT version of query prior to DELETE version.

IN

ID

SELECT

DELETE

DELETE FROM RELEASES r
WHERE r.ID IN
(SELECT sub.ID
FROM RELEASES sub
WHERE sub.RELEASED_BY = ?
AND (
(
DatePart('m', sub.RELEASED_DATE) = DatePart('m', Date()) - 1
AND DatePart('yyyy', sub.RELEASED_DATE) = DatePart('yyyy', Date())
AND DatePart('d', sub.RELEASED_DATE) = 1
)
OR
(
DatePart('m', sub.RELEASED_DATE) = DatePart('m', Date())
AND DatePart('yyyy', sub.RELEASED_DATE) = DatePart('yyyy', Date())
AND DatePart('d', sub.RELEASED_DATE) > 1
)
)
)


Python

Passing parameters with dates handled in Access engine.

conn = pyodbc.connect(r'...')
insights_dbcurs = conn.cursor()

sql = """DELETE FROM RELEASES r
WHERE r.ID IN
(SELECT sub.ID
FROM RELEASES sub
WHERE sub.RELEASED_BY = ?
AND (
(
DatePart('m', sub.RELEASED_DATE) = DatePart('m', Date()) - 1
AND DatePart('yyyy', sub.RELEASED_DATE) = DatePart('yyyy', Date())
AND DatePart('d', sub.RELEASED_DATE) = 1
)
OR
(
DatePart('m', sub.RELEASED_DATE) = DatePart('m', Date())
AND DatePart('yyyy', sub.RELEASED_DATE) = DatePart('yyyy', Date())
AND DatePart('d', sub.RELEASED_DATE) > 1
)
)
)"""

insights_dbcurs.execute(sql, ('27',))
conn.commit()
conn.close()


ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Invalid use of '.', '!', or '()'. in query expression 'SUB.RELEASED_BY = Pa_RaM000n AND n (n (DatePart('m',SUB.RELEASED_DATE) = DatePart('m'.Date())-1n AND DatePart('d',SUB.RELEASED'. (-3100) (SQLExecDirectW)")

DatePart("m", "2018-09-12")

d.month

d

date

d

str

Pa_RaM000

SQL has 0 parameters 2 parameters being passed.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.