python2.4 DateTime - Date Bug Hoary

The short error description: a python script read a date datatype from a postgresql database and store the date in an other table and got an error. For details read the following description of the test scenario.

Status

• Solved, see this patch and the Ubuntu BugNR 12127
• Ubuntu: fixed in python2.4-egenix-mxdatetime 2.0.6ubuntu1-1ubuntu3 - see Changelog 2.0.6ubuntu1-1ubuntu3
• Debian open, see BugNr 325903
• Testing on Fedora Core 4, same situation

1. Creating the tables

   Creating two tables, source s and destination d with only one column with datatype date:

           stbtest=> create table s (a date);
           CREATE TABLE
           stbtest=> create table d (a date);
           CREATE TABLE
           stbtest=>         
   

   And insert the startdate:

           insert into s values ('2005-06-24');
           INSERT 17147 1
   

2. Test Python script - copy date data

   This Python scripts read from the source table s the data (date type) and store this data in the destination table d. Use the locale:

           #!/usr/bin/python
   
           import locale
           from pyPgSQL import PgSQL
   
           if __name__ == '__main__':
               locale.setlocale (locale.LC_ALL, '')
   
               db = PgSQL.connect ("localhost::stbtest")
               st = db.cursor ()
               st1 = db.cursor ()
               st.execute ("select * from s")
               res = st.fetchone()
               print res
               st1.execute ("insert into d values(%s)", res[0])
   

3. Test the script with locale

   Now test the script with 1. locale en_US.utf8 and 2. with de_AT.utf8. The first test is working and the second test fails.

   • With locale en_US.utf8:

             ./test_sql_date.py
             [<DateTime object for '2005-06-24 00:00:00.00' at b7c46e90>]
     

   • With locale de_AT.utf8:

             export LANG=de_AT.utf8
             a@a:~/progs$ ./test_sql_date.py
             [<DateTime object for '2005-06-24 00:00:00,00' at b7c46e90>]
             Traceback (most recent call last):
               File "./test_sql_date.py", line 15, in ?
                 st1.execute ("insert into d values(%s)", res[0])
               File "/usr/lib/python2.4/site-packages/pyPgSQL/PgSQL.py", line 3072, in execute
                 raise OperationalError, msg
             libpq.OperationalError: ERROR:  invalid input syntax for type date: "2005-06-24 00:00:00,00"
     

   Note the error message, the timestamp containing now a comma(,) instead of a point (.).

4. Description
   1. The PEP 249 DB-API V2 descripes only the DATETIME format. Python-pgsql working with the mxDateTime Module.
   2. The pgresult.c from python-pgsql converts the PostgreSQL datatype date into the Python Class DateTime.
   3. The problem is in 'egenix-mx-base-2.0.6/mx/DateTime/mxDateTime/mxDateTime.c':

                 sprintf(buffer,"%04li-%02i-%02i %02i:%02i:%05.2f",
      

   4. With locale en_US.utf8 the float will converted to 123.123 and with de_AT.utf8 it looks like: 123,123
   5. The Postgresql does'nt know a Date with a comma (,) representation. See interfaces/ecpg/pgtypeslib/dt_common.c Version 7.4, 8.0:

                      /* Leading decimal point? Then fractional seconds... */
                      else if (*(*endstr) == '.')
                      {
                              *lp++ = *(*endstr)++;
                              while (isdigit((unsigned char) *(*endstr)))
                                      *lp++ = *(*endstr)++;
      
                              ftype[nf] = DTK_NUMBER;
                      }
      

5. Further Tests

   What do the datetime class? This little testscript:

           #!/usr/bin/python
   
           import locale
           from mx import DateTime
           from datetime import datetime
   
           if __name__ == '__main__':
               locale.setlocale (locale.LC_ALL, '')
   
               t1 = DateTime.now ()
               t2 = datetime.now()
   
               print "t1: %s" % t1
               print "t2: %s" % t2
               print "Testfloat: %5.3f" % 123.12345
               print "Testfloat with locale: %s" % locale.format ("%5.3f", 123.12345)
   

   The output with en_US.utf8:

           ./test_date.py
           t1: 2005-06-24 09:54:18.73
           t2: 2005-06-24 09:54:18.737181
           Testfloat: 123.123
           Testfloat with locale: 123.123
   

   The class DateTime and datetime shows the same string representation. Now the same test with de_AT.utf8:

           export LANG=de_AT.utf8
           a@a:~/progs$ ./test_date.py
           t1: 2005-06-24 09:55:27,29
           t2: 2005-06-24 09:55:27.298536
           Testfloat: 123.123
           Testfloat with locale: 123,123
   

   Now we can see, that the DateTime class using the locale.format function, because it looks like a float conversion with locale.

Solution

Using this patch

Testresult:

       test_sql_date.py  test_strtod
       s@a.a:~/progs$ ./test_sql_date.py
       [<DateTime object for '2005-06-24 00:00:00.000' at b7c46e58>]
       s@a.a:~/progs$ ./test_date.py
       t1: 2005-07-01 09:15:57.000
       t2: 2005-07-01 09:15:57.602884
       Testfloat: 123.123
       Testfloat with locale: 123,123

Fedore Core 4

On Fedora Core 4, the pg Interface is in the package postgresql-python. This pg Interface differs in many way from the interface used by Ubuntu. Here are the Testcode:

    #!/usr/bin/python

    import locale
    import pgdb

    if __name__ == '__main__':
        locale.setlocale (locale.LC_ALL, '')

        db = pgdb.connect ("localhost:stbtest:stb:test")
        st = db.cursor ()
        #st1 = db.cursor ()
        st.execute ("select * from s")
        res = st.fetchone ()
        print type(res[0])
        print res[0]
        t = res[0]
        st.execute ("insert into d values (%s)", t)

The output:

    ./test_sql_date.py
    <type 'str'>
    2005-06-24
    Traceback (most recent call last):
      File "./test_sql_date.py", line 18, in ?
        st.execute ("insert into d values (%s)", t)
      File "/usr/lib/python2.4/site-packages/pgdb.py", line 163, in execute
        self.executemany(operation, (params,))
      File "/usr/lib/python2.4/site-packages/pgdb.py", line 190, in executemany
        raise OperationalError, "internal error in '%s': %s" % (sql,err)
    pg.OperationalError: internal error in 'INIT': not all arguments converted during string formatting

The test_date.py script shows the same error with de_AT.utf8:

    ./test_date.py
    t1: 2005-07-02 16:46:01,89
    t2: 2005-07-02 16:46:01.893099
    Testfloat: 123.123
    Testfloat with locale: 123,123

And now, download the PyPgSQL Interface in src.rpm format and rebuild the package. To avoid a build error apply the pyPgSQL.sepc.FC4.pach to the .spec file. After installing this package we see the same situation:

    ./test_sql_date.py
    <type 'DateTime'>
    2005-06-24 00:00:00.00
    [a@a ~]$ export LANG=de_AT.utf8
    [a@a ~]$ ./test_sql_date.py
    <type 'DateTime'>
    2005-06-24 00:00:00,00
    Traceback (most recent call last):
      File "./test_sql_date.py", line 17, in ?
        st.execute ("insert into d values (%s)", t)
      File "/usr/lib/python2.4/site-packages/pyPgSQL/PgSQL.py", line 3072, in execute
        raise OperationalError, msg
    libpq.OperationalError: ERROR:  invalid input syntax for type date: "2005-06-24 00:00:00,00"