Re: How to format a datetime MySQL database field to local using strftime()

[DL Neil]

Vergos,

On 25/02/19 11:53 AM, vergos.niko...@gmail.com wrote:
Τη Δευτέρα, 25 Φεβρουαρίου 2019 - 12:38:43 π.μ. UTC+2, ο χρήστης 
vergos....@gmail.com έγραψε:
Τη Κυριακή, 24 Φεβρουαρίου 2019 - 8:52:03 μ.μ. UTC+2, ο χρήστης DL Neil έγραψε:
Vergos,
Please provide more information and show how you've debugged the code so
far...


On 25/02/19 7:03 AM, vergos.niko...@gmail.com wrote:
pymydb.execute( '''SELECT host, ref, location, useros, browser, visits, hits, 
downloads, authuser FROM guests
                                        WHERE pagesID = (SELECT ID FROM pages 
WHERE url = %s) ORDER BY visits DESC''', page )
        data = pymydb.fetchall()
          for visit in visits:
                visit = visit.strftime('%A %e %b,  %I:%M %p')

Is this the actual code? How do we get from the collection of
tuples/dicts called "data", to an iterable called "visits"?

Also: %e should likely be %w or %d.

Which 'connector' are you using between MySQL and Python?
(may not be the same as the one I favor)


'visit' is being returned from database containing a MySQL datatime field that 
i want to change to another format which is ('%A %e %b, %I:%M %p') thats why 
i'm using that function. If not convert or comment out then results are not 
appearing normally.

Where are these actual results? (might they help us to help you?)
What do you see, as the same row/col, when using the MySQL cmdLN shell
or MySQL-Workbench (etc)?

Have you shown us what comes back as the first row's value for "visit"?
Please check its type() before the code processes it further - is it
coming back as a Python date or time format, or is it a string?
Remember logging or even debug print()-s are your friend!

--
Regards =dn

The  'connector' that i'am using between MySQL and Python is 'bottle-pymysql'

In the following code:

        def coalesce( data ):
                newdata = []
                seen = {}
                for host, ref, location, useros, browser, visits, hits, 
downloads, authuser in data:
                        # Here i have to decide how to group the rows together
                        # I want an html row for every unique combination of 
(host) and that hits should be summed together
                        key = host
                        if key not in seen:
                                newdata.append( [ [host], [ref], location, 
useros, browser, [visits], hits, [downloads], authuser ] )
                                seen[key] = len( newdata ) - 1      # Save 
index (for 'newdata') of this row
                        else:       # This row is a duplicate row with a different 
referrer & visit datetime & torrent download
                                rowindex = seen[key]
                                newdata[rowindex][0].append( host )
                                newdata[rowindex][1].append( ref )
                                newdata[rowindex][5].append( visits )
                                newdata[rowindex][6] += hits
                                newdata[rowindex][7].append( downloads )
                return newdata


        pymydb.execute( '''SELECT host, ref, location, useros, browser, visits, 
hits, downloads, authuser FROM guests
                                        WHERE pagesID = (SELECT ID FROM pages 
WHERE url = %s) ORDER BY visits DESC''', page )
        data = pymydb.fetchall()


        newdata = coalesce( data )
        for row in newdata:
                (hosts, refs, location, useros, browser, visits, hits, 
downloads, authuser) = row

                # start of table
                pdata = pdata + '<tr>'

                pdata = pdata + '<td><center><font color=silver face=verdana size=3> 
%s </td>' % hosts[0]

                pdata = pdata + '<td><center><select>'
                for ref in refs:
                        pdata = pdata + '<option> %s </option>' % ref
                pdata = pdata + '</select></td>'

                for item in (location, useros, browser):
                        pdata = pdata + '<td><center><font color=plum> %s 
</td>' % item

                print( visits )
                pdata = pdata + '<td><center><select>'
                for visit in visits:
                        print( visit)
                        pdata = pdata + '<option> %s </option>' % visit
                pdata = pdata + '</select></td>'


if i try to print 'visits' filed before and during the loop the results is 
multiple

[Mon Feb 25 00:23:55.165094 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165098 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165102 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165107 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165111 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165115 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165119 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165123 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165127 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165132 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits
[Mon Feb 25 00:23:55.165136 2019] [wsgi:error] [pid 15158] [remote 
46.103.69.193:5068] visits

and actually all the variables that are being selected from the query are NOT 
being interpolated to their corresponding values.


if for example i try to print:

print( hosts, refs, location, useros, browser, visits, hits, downloads, 
authuser )

and i check the error_log i get long results like:


[Mon Feb 25 00:33:52.768914 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] [['host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 'host', 
'host', 'host', 'host', 'host'], ['ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 'ref', 
'ref', 'ref', 'ref', 'ref', 'ref'
[Mon Feb 25 00:33:52.769014 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769020 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769025 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769029 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769033 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769037 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769042 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769046 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769050 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769054 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769058 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769063 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769067 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits
[Mon Feb 25 00:33:52.769071 2019] [wsgi:error] [pid 15545] [remote 
46.103.69.193:5109] visits

The exact same code works WITHOUT error if i use the 'pymysql' connector 
instead of 'bottle-pymysql'

Is this a bug in the connector or somethng else?

after printing data and newdata i noticed that 'data' hold all values correctly 
while 'newdata' has the output i posted in my previous post.

Something gone wrong when calling coalesce funtion, and row dont have the 
unpcked values.

        newdata = coalesce( data )
        for row in newdata:
                (hosts, refs, location, useros, browser, visits, hits, 
downloads, authuser) = row


Repeating the suggestion that the first row of data returned from the 
DB-query be closely analysed to ensure that its format and type(s) are 
exactly what you expect!

The first row retrieved from the DB must populate newdata, by 
definition. So, temporarily add a break at the end of the foreach-loop 
in coalesce() - or at the end of the 'then' clause. Thus that function 
will return newdata as a list containing precisely one element.

That element is itself a list (newdata being a list of lists). Some of 
the elements of that single inner-list are themselves lists. Because 
only one query-result row has been entered, each list-element will also 
be only a single element in length.

Now you know the exact size and shape of the data!
(and if it was difficult to read the preceding paragraph, keeping 
separate in your mind so many lists within other lists; perhaps that 
indicates why you're having such a hard time?)

Now, instead of immediately passing newdata into the 'mainline' 
foreach-loop, pretty-print it.

Does this output match your expectations?

If that doesn't reveal clues about how to proceed, extend the 
pretty-print[ing] code to examine the inner-most element of each list, 
and confirm that its type() is str[ing] (or something else).
(then we will find ourselves back on-track to solving the format of 
those 'date' fields)

PS I had to think about it - might it be more 'readable' to keep a 
running counter to populate seen[]? Thus 'the number of unique hosts 
found (so far)' becomes a simple increment cf len( newdata ) - 1.

Please let us know how you get on...
--
Regards =dn
--
https://mail.python.org/mailman/listinfo/python-list