osdir.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


?? ???????, 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?