How to format a datetime MySQL database field to local using strftime()
DL Neil
PythonList at DancesWithMice.info
Sun Feb 24 20:20:19 EST 2019
Vergos,
On 25/02/19 11:53 AM, vergos.nikolas at gmail.com wrote:
> Τη Δευτέρα, 25 Φεβρουαρίου 2019 - 12:38:43 π.μ. UTC+2, ο χρήστης vergos.... at 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.nikolas at 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
More information about the Python-list
mailing list