How to format a datetime MySQL database field to local using strftime()
vergos.nikolas at gmail.com
vergos.nikolas at gmail.com
Sun Feb 24 17:53:41 EST 2019
Τη Δευτέρα, 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
More information about the Python-list
mailing list