Uploaded image for project: 'CUBRID APIs'
  1. CUBRID APIs
  2. APIS-522

[Python] Django_cubrid DATE_FORMAT problem

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Python Driver
    • Labels:
      None

      Description

      From: "reetaeg"<reetaeg@nhn.com>
      To: <dl_cubrid.support@nhn.com>;
      Cc:
      Sent: 2013-03-25 (월) 12:36:45
      Subject: Python Cubrid Driver9.1 DATE_FORMAT 오류

      From : http://devcafe.nhncorp.com/CUBRID/1420861
      CUbrid Python Driver 관련 문의 드립니다.

      DB : 8.4.3 , 9.1
      Python Driver: 9.1

      우선 Datetime Field Data 조회시
      src/django_cubrid/base.py 내용중 date_trunc_sql 정의 중에서

      def date_trunc_sql(self, lookup_type, field_name):
      fields = ['year', 'month', 'day', 'hour', 'minute', 'second', 'milisecond']
      format = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s', '.%%ms') # Use double percents to escape.
      format_def = ('0000-', '01', '-01', ' 00:', '00', ':00', '.00')
      try:
      i = fields.index(lookup_type) + 1
      except ValueError:
      sql = field_name
      else:
      format_str = ''.join([f for f in format[:i]] + [f for f in format_def[i:]])
      sql = "CAST(DATE_FORMAT(%s, '%s') AS DATETIME)" % (field_name, format_str)
      return sql

      format이 %%Y 로 이중 퍼센트로 치환을 하는지 이로 인해서 실제 Django에서 수행 쿼리가 아래와 같이 수행 됩니다.

      SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pubdate`, '%%Y-%%m-%%d 00:00:00.00') AS DATETIME) FROM `polls_poll` WHERE (`polls_poll`.`pubdate` BETWEEN ? and ? AND EXTRACT(MONTH FROM `polls_poll`.`pubdate`) = ?) ORDER BY 1 ASC LIMIT 21

      확인 부탁 드립니다.

      1. APIS-522_FIX01.creview
        3 kB
        李金虎

        Activity

        Hide
        jinhu 李金虎 added a comment -

        ----Original Message----
        From: "오보명(Laura Oh)"<obm@nhn.com>
        To: "李成龙[이성룡]"<lichenglong@nhn.com>;
        Cc: "강철규"<cgkang@nhn.com>;
        Sent: 2013-03-28 (목) 11:46:38
        Subject: FW: Cubrid Python Driver 관련 소스 및 정보

        이걸로 확인 부탁!!

        ----Original Message----
        From: "김리택"<reetaeg@nhn.com>
        To: "오보명[Laura Oh]"<obm@nhn.com>;
        Cc:
        Sent: 2013-03-28 (목) 11:40:03
        Subject: FW: Cubrid Python Driver 관련 소스 및 정보

        ----Original Message----
        From: "김리택"<reetaeg@nhn.com>
        To: "오보명[Laura Oh]"<obm@nhn.com>;
        Cc: "클라우드스토리지사업팀"<nt00244@nhn.com>;
        Sent: 2013-03-28 (목) 11:39:05
        Subject: Cubrid Python Driver 관련 소스 및 정보

        안녕하세요 클라우드스토리지사업팀 김리택 입니다.

        요청 하신 Sample Test 소스 전달 드립니다.
        Python 2.7 + Django 1.5 환경이며
        추가적으로 site-packages 는

        • cubrid_python-0.1.0.0001-py2.7
        • django_admin_tools-0.5.1-py2.7
        • south-0.7.6.py2.7

        Cubrid DB는 Cubrid 2008 R4.1 (8.4.1.7007) 입니다.

        Cubrid DB 설정은 아래와 같이 수정 하시면 됩니다.
        /mysite/settings.py
        DATABASES = {
        'default':

        { 'ENGINE': 'django_cubrid', 'NAME': 'admindb', 'USER': 'dba', 'PASSWORD': 'XXXX', 'HOST': 'XXX.XXX.XXX.XXX', 'PORT': 'XXXXX', }

        }

        혹 필요한 정보 있으시면 답변 주시기 바랍니다 감사합니다.

        Show
        jinhu 李金虎 added a comment - ---- Original Message ---- From: "오보명(Laura Oh)"<obm@nhn.com> To: "李成龙 [이성룡] "<lichenglong@nhn.com>; Cc: "강철규"<cgkang@nhn.com>; Sent: 2013-03-28 (목) 11:46:38 Subject: FW: Cubrid Python Driver 관련 소스 및 정보 이걸로 확인 부탁!! ---- Original Message ---- From: "김리택"<reetaeg@nhn.com> To: "오보명 [Laura Oh] "<obm@nhn.com>; Cc: Sent: 2013-03-28 (목) 11:40:03 Subject: FW: Cubrid Python Driver 관련 소스 및 정보 ---- Original Message ---- From: "김리택"<reetaeg@nhn.com> To: "오보명 [Laura Oh] "<obm@nhn.com>; Cc: "클라우드스토리지사업팀"<nt00244@nhn.com>; Sent: 2013-03-28 (목) 11:39:05 Subject: Cubrid Python Driver 관련 소스 및 정보 안녕하세요 클라우드스토리지사업팀 김리택 입니다. 요청 하신 Sample Test 소스 전달 드립니다. Python 2.7 + Django 1.5 환경이며 추가적으로 site-packages 는 cubrid_python-0.1.0.0001-py2.7 django_admin_tools-0.5.1-py2.7 south-0.7.6.py2.7 Cubrid DB는 Cubrid 2008 R4.1 (8.4.1.7007) 입니다. Cubrid DB 설정은 아래와 같이 수정 하시면 됩니다. /mysite/settings.py DATABASES = { 'default': { 'ENGINE': 'django_cubrid', 'NAME': 'admindb', 'USER': 'dba', 'PASSWORD': 'XXXX', 'HOST': 'XXX.XXX.XXX.XXX', 'PORT': 'XXXXX', } } 혹 필요한 정보 있으시면 답변 주시기 바랍니다 감사합니다.
        Hide
        jinhu 李金虎 added a comment -

        django_date.zip is the web application which reproduces this issue.

        Show
        jinhu 李金虎 added a comment - django_date.zip is the web application which reproduces this issue.
        Hide
        jinhu 李金虎 added a comment -

        问题分析:
        当date_trunc_sql()中DATE_FORMAT为双%时
        最后的SQL将是类似如下:
        SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pub_date`, '%%Y-%%m-%%d 00:00:00.00') AS DATETIME) FROM `polls_poll` ORDER BY 1 ASC LIMIT 21;
        执行将出错:
        Cannot coerce value of domain "character varying" to domain "datetime".

        当date_trunc_sql()中DATE_FORMAT改为单%格式时,
        最后的SQL将类似如下:
        SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pub_date`, '%Y-%m-%d 00:00:00.00') AS DATETIME) FROM `polls_poll` ORDER BY 1 ASC LIMIT 21;
        在执行时,是成功的。
        然而,接下来,Django 的loger将记录这条SQL,在记录过程中却出错了,堆栈如下:
        File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/query.py", line 123, in _result_iter
        self._fill_cache()
        File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/query.py", line 939, in _fill_cache
        self._result_cache.append(next(self._iter))
        File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/query.py", line 344, in _safe_iterator
        for item in iterator:
        File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 1108, in results_iter
        for rows in self.execute_sql(MULTI):
        File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 840, in execute_sql
        cursor.execute(sql, params)
        File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/backends/util.py", line 45, in execute
        sql = self.db.ops.last_executed_query(self.cursor, sql, params)
        File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/backends/_init_.py", line 632, in last_executed_query
        return force_text(sql) % u_params
        ValueError: unsupported format character 'Y' (0x59) at index 59
        > /home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django_cubrid/base.py(98)date_trunc_sql()
        -> fields = ['year', 'month', 'day', 'hour', 'minute', 'second', 'milisecond']
        (Pdb)

        原因是这一句: Django中的last_executed_query()有 return force_text(sql) % u_params
        force_text(sql) 返回的SQL:
        u"SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pubdate`, '%Y-%m-%d 00:00:00.00') AS DATETIME) FROM `polls_poll` WHERE (`polls_poll`.`pubdate` BETWEEN %s and %s AND EXTRACT(MONTH FROM `polls_poll`.`pubdate`) = %s) ORDER BY 1 ASC"
        u_params为 (u'2013-01-01 00:00:00.00', u'2013-12-31 23:59:59.99', 3)
        相当于执行这样一句:
        u"SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pubdate`, '%Y-%m-%d 00:00:00.00') AS DATETIME) FROM `polls_poll` WHERE (`polls_poll`.`pubdate` BETWEEN %s and %s AND EXTRACT(MONTH FROM `polls_poll`.`pubdate`) = %s) ORDER BY 1 ASC" % (u'2013-01-01 00:00:00.00', u'2013-12-31 23:59:59.99', 3)
        这里将%作为占位符,%Y,%m, %d不能识别,故出错。

        修改方案:
        (1)django_cubrid中的date_trunc_sql()仍采用:%%方式
        在执行SQL之前,将SQL中的%%换为%

        (2)django_cubrid中的date_trunc_sql()采用:%方式
        在执行SQL之后,在 django_cubrid中对last_executed_query重写,来生成正确的sql text信息供logger使用
        但是有问题:
        DATE_FORMAT中的格式很多: http://www.cubrid.org/manual/831/en/DATE_FORMAT%20Function 其中包含:%s
        无法与占位符%s 进行区分。

        因此采用第一种方案。

        Show
        jinhu 李金虎 added a comment - 问题分析: 当date_trunc_sql()中DATE_FORMAT为双%时 最后的SQL将是类似如下: SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pub_date`, '%%Y-%%m-%%d 00:00:00.00') AS DATETIME) FROM `polls_poll` ORDER BY 1 ASC LIMIT 21; 执行将出错: Cannot coerce value of domain "character varying" to domain "datetime". 当date_trunc_sql()中DATE_FORMAT改为单%格式时, 最后的SQL将类似如下: SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pub_date`, '%Y-%m-%d 00:00:00.00') AS DATETIME) FROM `polls_poll` ORDER BY 1 ASC LIMIT 21; 在执行时,是成功的。 然而,接下来,Django 的loger将记录这条SQL,在记录过程中却出错了,堆栈如下: File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/query.py", line 123, in _result_iter self._fill_cache() File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/query.py", line 939, in _fill_cache self._result_cache.append(next(self._iter)) File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/query.py", line 344, in _safe_iterator for item in iterator: File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 1108, in results_iter for rows in self.execute_sql(MULTI): File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 840, in execute_sql cursor.execute(sql, params) File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/backends/util.py", line 45, in execute sql = self.db.ops.last_executed_query(self.cursor, sql, params) File "/home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django/db/backends/_ init _.py", line 632, in last_executed_query return force_text(sql) % u_params ValueError: unsupported format character 'Y' (0x59) at index 59 > /home/jinhu/apps/Python-2.7.3/lib/python2.7/site-packages/django_cubrid/base.py(98)date_trunc_sql() -> fields = ['year', 'month', 'day', 'hour', 'minute', 'second', 'milisecond'] (Pdb) 原因是这一句: Django中的last_executed_query()有 return force_text(sql) % u_params force_text(sql) 返回的SQL: u"SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pubdate`, '%Y-%m-%d 00:00:00.00') AS DATETIME) FROM `polls_poll` WHERE (`polls_poll`.`pubdate` BETWEEN %s and %s AND EXTRACT(MONTH FROM `polls_poll`.`pubdate`) = %s) ORDER BY 1 ASC" u_params为 (u'2013-01-01 00:00:00.00', u'2013-12-31 23:59:59.99', 3) 相当于执行这样一句: u"SELECT DISTINCT CAST(DATE_FORMAT(`polls_poll`.`pubdate`, '%Y-%m-%d 00:00:00.00') AS DATETIME) FROM `polls_poll` WHERE (`polls_poll`.`pubdate` BETWEEN %s and %s AND EXTRACT(MONTH FROM `polls_poll`.`pubdate`) = %s) ORDER BY 1 ASC" % (u'2013-01-01 00:00:00.00', u'2013-12-31 23:59:59.99', 3) 这里将%作为占位符,%Y,%m, %d不能识别,故出错。 修改方案: (1)django_cubrid中的date_trunc_sql()仍采用:%%方式 在执行SQL之前,将SQL中的%%换为% (2)django_cubrid中的date_trunc_sql()采用:%方式 在执行SQL之后,在 django_cubrid中对last_executed_query重写,来生成正确的sql text信息供logger使用 但是有问题: DATE_FORMAT中的格式很多: http://www.cubrid.org/manual/831/en/DATE_FORMAT%20Function 其中包含:%s 无法与占位符%s 进行区分。 因此采用第一种方案。
        Hide
        jinhu 李金虎 added a comment -

        Fix in RB-9.1.0, rev: 1755
        Fix in RB-9.1.0_static_cci, rev: 1757
        Fix in RB-8.4.1, rev: 1758
        Fix in RB-8.4.3, rev: 1759
        Fix in RB-8.4.4, rev: 1760
        Fix in trunk, rev: 1761

        Resolved.

        Show
        jinhu 李金虎 added a comment - Fix in RB-9.1.0, rev: 1755 Fix in RB-9.1.0_static_cci, rev: 1757 Fix in RB-8.4.1, rev: 1758 Fix in RB-8.4.3, rev: 1759 Fix in RB-8.4.4, rev: 1760 Fix in trunk, rev: 1761 Resolved.

          People

          • Assignee:
            jinhu 李金虎
            Reporter:
            jinhu 李金虎
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: