时间戳

首页 > 解决方案 > 根据python中的多个条件以及日期字段从访问表中删除行

python - 根据python中的多个条件以及日期字段从访问表中删除行

问题描述

我试图从一个访问数据库表中删除行，基于两列，一列是released_by 和released_date。

Sudo code

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

Released_Date 如果 day ==1 然后删除上个月的数据，否则删除当前月份的数据。

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:\Users\Desktop\Insights.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()

这没用。我得到：

AttributeError：“pyodbc.Row”对象没有属性“autoreleases”

如果我删除Row.autorelease，我得到

sql有0个属性，2个被传递。

我该如何避免这种情况。任何建议都会非常有帮助。

标签： pythonsqlms-accesspyodbc

解决方案

考虑针对几个问题重构您的代码：

  1. try/except通常用于处理运行时异常和错误，而不是应用程序逻辑。在if/else这些情况下使用。

  2. 删除不需要的for循环，因为您没有逐行删除，因为没有唯一标识符被传递到DELETE语句中。实际上，如果逻辑将未过滤的当前行传递给该行，则您正在跨整个表运行删除过程。

  3. 将纯 SQL 与一条DELETE语句一起使用，避免 Python 处理时间元素，如您所见，这会维护数据库的转换问题。MS Access SQL 确实具有日期函数，例如Date()（当前日期）和DatePart()月/日提取。此外，MS Access可以在DELETE.

SQL

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

使用 Access 引擎中处理的日期传递参数。

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()

推荐阅读