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:38:31 EST 2019
Τη Κυριακή, 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?
More information about the Python-list
mailing list