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