Struggling to convert a mysql datetime object to a python string of a different format

rahul mishra rahulmsr at yahoo.co.in
Thu Feb 24 10:34:09 CET 2011


try this 

test = time.time(2011, 2, 1, 2, 4, 10)
# this is your datetime object from mysql

print time.mktime(test.timetuple())

hope this would help you


> On Wednesday, August 04, 2010 7:40 PM ????? wrote:

> Okey, i have many hours now struggling to convert a mysql datetime
> field that i retreive to a string of this format '%d %b, %H:%M'
> 
> I google a lot but couldnt found out how to format it being a string
> 
> Here si the code so far:
> 
> try:
> cursor.execute( ''' SELECT host, hits, date FROM visitors WHERE page
> =3D '%s' ORDER BY date DESC ''' % (page) )
> except MySQLdb.Error:
> print( "Error %d: %s" % (e.args[0], e.args[1]) )
> else:
> print ( '''<center><br><h3> ( =C5=F0=E9=F3=EA=DD=F0=F4=E7=F2 ) ----- ( =
> =C5=F0=E9=F3=EA=DD=F8=E5=E9=F2 )
> ----- ( =C7=EC=E5=F1=EF=EC=E7=ED=DF=E1 ) </h3><br>''' )
> print ( '''<table border =3D "5" cellpadding =3D "5">''' )
> 
> results =3D cursor.fetchall()
> 
> for row in results:
> print ( ''' <tr> ''' )
> 
> for entry in row:
> entry =3D datetime.datetime.strftime( entry, '%d %b, %H:%M' ) #!!!
> this is wrong!
> print ( ''' <td> %s </td> ''' % entry )
> 
> sys.exit(0)
> 
> Apart from that i do not know how iam supposed to print it, because the
> date string is the 3rd string in every row of the dataset.
> 
> Please help me out!


>> On Thursday, August 05, 2010 4:55 AM Dennis Lee Bieber wrote:

>> gmane.comp.python.general:
>> 
>> As you state, it is the third item in each returned row... So why
>> are you trying to treat EVERY item in the row as a date?
>> 
>> Since MySQLdb appears to return datetime objects (my quick test is
>> showing datetime.date for dates in a test database) you should be
>> probably be using
>> 
>> formatted_entry = entry.strftime("%d... %M")
>> 
>> to do the formatting as string
>> 
>> 
>> 
>> --
>> Wulfraed                 Dennis Lee Bieber         AF6VN
>> wlfraed at ix.netcom.com    HTTP://wlfraed.home.netcom.com/


>>> On Thursday, August 05, 2010 12:31 PM ????? wrote:

>>> rote:
>>> r entry in row:
>>> =C2=A0 =C2=A0 =C2=A0 =C2=A0entry =3D datetime.datetime.strftime( entry, '%d=
>>> %b, %H:%M' ) #!!!
>>> =C2=A0 =C2=A0 =C2=A0 =C2=A0print ( ''' <td> %s </td> ''' % entry )
>>> turned row... So why
>>> 
>>> Because when i try to prin the 3 items liek that
>>> 
>>> print row[0], row[1], row[2]
>>> 
>>> it gives me an error, so i dont knwo how to tell it how to print the
>>> 3rd item differently.
>>> 
>>> 
>>> 
>>> cts (my quick test is
>>> )
>>> 
>>> I tried that myself yesterday but look it fails to the following
>>> message
>>> 
>>> /home/webville/public_html/cgi-bin/index.py
>>> 63
>>> 64                         for entry in row:
>>> 65                                 formatted_entry =3D
>>> entry.strftime('%d %b, %H:%M')
>>> 66                                 print ( ''' <td> %s </td> ''' %
>>> entry )
>>> 67
>>> formatted_entry undefined, entry =3D '178-124-186.dynamic.cyta.gr',
>>> entry.strftime undefined
>>> AttributeError: 'str' object has no attribute 'strftime'
>>> args =3D ("'str' object has no attribute 'strftime'",)


>>>> On Thursday, August 05, 2010 2:52 PM ????? wrote:

>>>> Hey i made it! :-)
>>>> 
>>>> dataset = cursor.fetchall()
>>>> 
>>>> for row in dataset:
>>>> print ( ''' <tr> ''' )
>>>> 
>>>> date = row[2].strftime( '%d %b, %H:%M' )
>>>> 
>>>> print ( ''' <td> %s </td>	<td> %s </td>	<td> %s </td> ''' %
>>>> ( row[0], row[1], date ) )
>>>> 
>>>> Unfortunately had to ditch the 'for entry in row' line because
>>>> could not iterate over the items of the row.
>>>> 
>>>> Could you please shoe me how could i do the same thing with
>>>> iteration?!
>>>> Thanks!


>>>>> On Thursday, August 05, 2010 3:09 PM Tim Chase wrote:

>>>>> On 08/05/10 13:52, ?????????? wrote:
>>>>> 
>>>>> Well, depending on whether "row" is a tuple or a list, you can do
>>>>> either
>>>>> 
>>>>> row[2] = row[2].strftime(...)  # if it is a list
>>>>> 
>>>>> or you can just iterate over a predefined list/tuple:
>>>>> 
>>>>> for row in dataset:
>>>>> print ("<tr>")
>>>>> for item in (row[0], row[1], row[2].strftime(...)):
>>>>> print ("<td>%s</td" % item)
>>>>> print ("</tr>")
>>>>> 
>>>>> Though I think I'd make it a bit clearer by naming the fields:
>>>>> 
>>>>> for host, hits, dt in dataset:
>>>>> print ("<tr>")
>>>>> for item in (host, hits, dt.strftime(...)):
>>>>> print ("<td>%s</td>" % item)
>>>>> print ("</tr>")
>>>>> 
>>>>> Or perhaps even just
>>>>> 
>>>>> print ("".join("<td>%s</td>" % item
>>>>> for item in (host, hits, dt.strftime(...))
>>>>> )
>>>>> 
>>>>> Whichever you prefer.  I think I am partial to the 2nd-from-last
>>>>> version, especially as the list of fields may grow.
>>>>> 
>>>>> -tkc


>>>>>> On Thursday, August 05, 2010 5:01 PM ????? wrote:

>>>>>> As i have it the returned 'dataset' is stored line per line to 'row'.
>>>>>> 
>>>>>> So,
>>>>>> 'dataset' in here is a 'list of tuples' right?
>>>>>> and
>>>>>> 'row' in here is a tuple form the above list of tuples right?
>>>>>> 
>>>>>> Am i understanding this correctly?!
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> It was a tuple. But it migth as well be a list too?!?!
>>>>>> 
>>>>>> Could 'dataset' be a 'list of lists' as well?
>>>>>> 
>>>>>> How one would know in which way the returned mysql data is saved in?
>>>>>> 
>>>>>> 
>>>>>> Cool! I myself like this solution best over the all working other!
>>>>>> very nice approach thank you very much! Is what i anted and could not
>>>>>> write myself!
>>>>>> 
>>>>>> But please tell me if in my example 'row' was a tuple, what kind of
>>>>>> objects is 'host', 'hits', 'dt' here and how do they sore the data?
>>>>>> 
>>>>>> Thanks again for the cool examples!


>>>>>>> On Thursday, August 05, 2010 7:46 PM Tim Chase wrote:

>>>>>>> On 08/05/10 16:01, ????? wrote:
>>>>>>> 
>>>>>>> Pretty much...it is either a list-of-tuples or a list-of-lists
>>>>>>> (I am not sure if is part of the DB-API spec to mandate one or the
>>>>>>> other).  For the most part, you can treat them as the same thing.
>>>>>>> However, tuples are immutable, so you cannot say
>>>>>>> 
>>>>>>> my_tuple[3] = some_value
>>>>>>> 
>>>>>>> but with a list you can:
>>>>>>> 
>>>>>>> my_list[3] = some_value
>>>>>>> 
>>>>>>> 
>>>>>>> Well, you can ask:
>>>>>>> 
>>>>>>> print type(row)
>>>>>>> 
>>>>>>> (I *suspect* it is a tuple) or you can just tell it what to be:
>>>>>>> 
>>>>>>> for row in dataset:
>>>>>>> row = list(row)
>>>>>>> row[3] = row[3].strftime(...)
>>>>>>> for item in row:
>>>>>>> ...
>>>>>>> 
>>>>>>> I do not usually have cause to write a value back into the data
>>>>>>> I am reading from the DB, so it is never really mattered to me
>>>>>>> whether the DB gives me tuples or lists.
>>>>>>> 
>>>>>>> 
>>>>>>> Python supports "tuple assignment" which means that the following
>>>>>>> are about[*] the same:
>>>>>>> 
>>>>>>> for row in dataset:
>>>>>>> host = row[0]
>>>>>>> hits = row[1]
>>>>>>> dt = row[2]
>>>>>>> 
>>>>>>> for row in dataset:
>>>>>>> host, hits, dt = row
>>>>>>> 
>>>>>>> for host, hits, dt in row:
>>>>>>> 
>>>>>>> The data-type of the individual values would be whatever comes
>>>>>>> back from the database as translated into Python (string,
>>>>>>> float/Decimal, boolean, datetime, etc).  In your example, it is
>>>>>>> likely a string+integer+datetime as the 3 values.  You can see
>>>>>>> why I prefer the elegance of just performing the assignment in
>>>>>>> the for-loop (variant C).
>>>>>>> 
>>>>>>> Hope this helps,
>>>>>>> 
>>>>>>> -tkc
>>>>>>> 
>>>>>>> 
>>>>>>> [*] "about" the same because in #1 and #2, you also have access
>>>>>>> to the whole row; whereas in #3, you do not have something called
>>>>>>> "row", but you could reassemble it if you needed:
>>>>>>> 
>>>>>>> row = (host, hits, dt)


>>>>>>>> On Friday, August 06, 2010 1:13 AM John Nagle wrote:

>>>>>>>> On 8/4/2010 4:40 PM, ????? wrote:
>>>>>>>> 
>>>>>>>> 
>>>>>>>> Don't do string substitution ("%") on SQL statements.  Let MySQLdb do it
>>>>>>>> for you, with proper escaping:
>>>>>>>> 
>>>>>>>> cursor.execute('''SELECT host, hits, date FROM visitors WHERE page=%s
>>>>>>>> ORDER BY date DESC''', (page,))
>>>>>>>> 
>>>>>>>> The difference is that if some external source can control "page", and
>>>>>>>> they put in a value like
>>>>>>>> 
>>>>>>>> 100 ; DELETE FROM visitors; SELECT * FROM visitors
>>>>>>>> 
>>>>>>>> you just lost your data.
>>>>>>>> 
>>>>>>>> John Nagle


>>>>>>>>> On Saturday, August 07, 2010 2:45 AM ?????a??_????a? wrote:

>>>>>>>>> ???? 6/8/2010 2:46 ??, ?/? Tim Chase ??????:
>>>>>>>>> So, row is a tuple comprising of 3 fields,
>>>>>>>>> and host, hist, dt are variables assigned each one of row's tuple values
>>>>>>>>> by breaking it to it is elements.
>>>>>>>>> 
>>>>>>>>> But what kind of objects is host, hits, dt that containes the row's
>>>>>>>>> tuple data themselves? tuples or lists and why?
>>>>>>>>> 
>>>>>>>>> host, hits, data each and every one of them hold a piece of the row's
>>>>>>>>> tuple values.
>>>>>>>>> 
>>>>>>>>> But what happens in here?
>>>>>>>>> 
>>>>>>>>> 'for host, hits, dt in dataset:'
>>>>>>>>> 
>>>>>>>>> Here we do not have the row tuple. So what tthose variabels store, and in
>>>>>>>>> what datatype they strore info in and what is the difference between this
>>>>>>>>> and
>>>>>>>>> 
>>>>>>>>> 'for host, hits, dt in row:'
>>>>>>>>> 
>>>>>>>>> What datatypes are these vars here and what data each one hold?
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>>> If the fieds datatypes returned form the database are for exmaple page
>>>>>>>>> varchar(50) , hits inteeger[11], date datetime then
>>>>>>>>> the when python grabs those results fields it would translate them to
>>>>>>>>> 'page as string' , (hits as int) , 'date as string' respectively?
>>>>>>>>> Whcih emans it translated those fileds returned to the most
>>>>>>>>> appropriate-most close to prototype stored in database' datatypes
>>>>>>>>> automatically?
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>>> Would that be a row or a tuple when joined?
>>>>>>>>> 
>>>>>>>>> Again, thanks for ALL your preciosu help you provide me!


>>>>>>>>>> On Saturday, August 07, 2010 3:09 AM ????? wrote:

>>>>>>>>>> t, hits, date FROM visitors WHERE page
>>>>>>>>>> o it
>>>>>>>>>> RE page=3D%s
>>>>>>>>>> 
>>>>>>>>>> Thanks i did not know.
>>>>>>>>>> But you also do use '%s' here as well. Just without the quotes? Why?
>>>>>>>>>> How is this valid?
>>>>>>>>>> And also what is the comma after the (page,) ? What does the comam do?
>>>>>>>>>> 
>>>>>>>>>> itors
>>>>>>>>>> 
>>>>>>>>>> Thanks iam trying to test this because its very interesting and when i
>>>>>>>>>> try:
>>>>>>>>>> 
>>>>>>>>>> http://webville.gr/index.html?show=3Dlog&page=3D"index.html ; DELETE FROM
>>>>>>>>>> visitors; SELECT * FROM visitors"
>>>>>>>>>> 
>>>>>>>>>> cgitb gives me an error
>>>>>>>>>> 
>>>>>>>>>> 53         except MySQLdb.Error:
>>>>>>>>>> 54                 print ( "Error %d: %s" % (e.args[0],
>>>>>>>>>> e.args[1]) )
>>>>>>>>>> 55         else:
>>>>>>>>>> 56                 #display dataset
>>>>>>>>>> e undefined
>>>>>>>>>> NameError: name 'e' is not defined
>>>>>>>>>> args =3D ("name 'e' is not defined",)
>>>>>>>>>> 
>>>>>>>>>> How can i try it successfully?
>>>>>>>>>> Have to ssay here that i use mod_rewrite in my remote web server and
>>>>>>>>>> every incoming html request gets treated by counter.py with this rule
>>>>>>>>>> 
>>>>>>>>>> RewriteCond %{REQUEST_FILENAME} -f
>>>>>>>>>> RewriteRule ^/?(.+\.html) /cgi-bin/counter.py?page=3D$1 [L,PT,QSA]
>>>>>>>>>> 
>>>>>>>>>> Also for the program exectuion to reach the:
>>>>>>>>>> 
>>>>>>>>>> cursor.execute( ''' SELECT host, hits, date FROM visitors WHERE page =3D
>>>>>>>>>> '%s' ORDER BY date DESC ''' % (page) )
>>>>>>>>>> 
>>>>>>>>>> statement i expect the user to:
>>>>>>>>>> 
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>>>>>>>>>> page log and exit
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>>>>>>>>>> if form.getvalue('show') =3D=3D 'log':
>>>>>>>>>> 
>>>>>>>>>> Please help me reproduce what you suggest. I beleive its called MySQL
>>>>>>>>>> injection and i very like want to see this in action! :-)


>>>>>>>>>>> On Saturday, August 07, 2010 1:12 PM Tim Chase wrote:

>>>>>>>>>>> On 08/07/10 01:45, ???????? ?????? wrote:
>>>>>>>>>>> 
>>>>>>>>>>> They contain the data of each respective element.  E.g.:
>>>>>>>>>>> 
>>>>>>>>>>> ... (1, 'a', True),
>>>>>>>>>>> ... (2, 'b', False),
>>>>>>>>>>> ... ]
>>>>>>>>>>> ...     print 'one%s = %r' % (type(one), one)
>>>>>>>>>>> ...     print 'two%s = %r' % (type(two), two)
>>>>>>>>>>> ...     print 'three%s = %r' % (type(three), three)
>>>>>>>>>>> ...     print '-' * 10
>>>>>>>>>>> ...
>>>>>>>>>>> one<type 'int'> = 1
>>>>>>>>>>> two<type 'str'> = 'a'
>>>>>>>>>>> three<type 'bool'> = True
>>>>>>>>>>> ----------
>>>>>>>>>>> one<type 'int'> = 2
>>>>>>>>>>> two<type 'str'> = 'b'
>>>>>>>>>>> three<type 'bool'> = False
>>>>>>>>>>> ----------
>>>>>>>>>>> 
>>>>>>>>>>> So likely in your case, "host" is a string, "hits" is an int, and
>>>>>>>>>>> "dt" is a datetime.datetime object.  The three of them together
>>>>>>>>>>> are the row as represented as a tuple:
>>>>>>>>>>> 
>>>>>>>>>>> <type 'tuple'>
>>>>>>>>>>> 
>>>>>>>>>>> which you can see in your own code by changing it temporarily to:
>>>>>>>>>>> 
>>>>>>>>>>> for row in dataset:
>>>>>>>>>>> print type(row), len(row)
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> The same as Variant B, only it does not use the intermediate tuple
>>>>>>>>>>> "row".
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> The second one will fail because it would be the same as
>>>>>>>>>>> 
>>>>>>>>>>> for tpl in row:
>>>>>>>>>>> host, hits, dt = tpl
>>>>>>>>>>> 
>>>>>>>>>>> The 1st time through the loop, tpl=host; the 2nd time through the
>>>>>>>>>>> loop, tpl=hits; and the 3rd time through the loop, tpl=dt
>>>>>>>>>>> 
>>>>>>>>>>> Attempting to do a tuple assignment (that 2nd line) will attempt
>>>>>>>>>>> to do something like
>>>>>>>>>>> 
>>>>>>>>>>> host, hits, dt = "example.com"  # 1st pass through the loop
>>>>>>>>>>> host, hits, dt = 42  # 2nd pass through the loop
>>>>>>>>>>> host, hits, dt = datetime(2010,7,5)# 3rd pass through the loop
>>>>>>>>>>> 
>>>>>>>>>>> In most cases, it will fail on the first pass through the loop
>>>>>>>>>>> (except in the freak case your string value happens to have 3
>>>>>>>>>>> characters:
>>>>>>>>>>> 
>>>>>>>>>>> 'a'
>>>>>>>>>>> )
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> Yes, except the internals (of the DB module...in this case mysql)
>>>>>>>>>>> are smart enough to translate the date into a datetime.datetime
>>>>>>>>>>> object, instead of a string.
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> A "row" is a conceptual thing -- one row of data from your query.
>>>>>>>>>>> It can be represented as either a tuple or a list (or any
>>>>>>>>>>> iteratable that represents "things in this row").  In this case
>>>>>>>>>>> (and I believe each row returned by a cursor.fetch*() call), it
>>>>>>>>>>> was tuple.
>>>>>>>>>>> 
>>>>>>>>>>> I hope that helps...it would behoove you to experiment with
>>>>>>>>>>> tuple-assignments such as the example code above so that you
>>>>>>>>>>> understand what it is doing in each case.
>>>>>>>>>>> 
>>>>>>>>>>> -tkc


>>>>>>>>>>>> On Saturday, August 07, 2010 8:17 PM Dennis Lee Bieber wrote:

>>>>>>>>>>>> gmane.comp.python.general:
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> 1) please read the DB-API PEP (PEP 249)
>>>>>>>>>>>> http://www.python.org/dev/peps/pep-0249/
>>>>>>>>>>>> 
>>>>>>>>>>>> It describes the basic requirements of all Python standard database
>>>>>>>>>>>> adapters. Then read the documentation specific to MySQLdb.
>>>>>>>>>>>> http://mysql-python.sourceforge.net/MySQLdb.html
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> Short answer: it is valid because that is what MySQLdb uses for a
>>>>>>>>>>>> placeholder. SQLite uses a ?.
>>>>>>>>>>>> 
>>>>>>>>>>>> Long answer requires one study both the MySQL C-language interface
>>>>>>>>>>>> and the Python source code for the cursor operations (for MySQL versions
>>>>>>>>>>>> 3 and 4 -- MySQL 5 supports those but adds prepared statements which
>>>>>>>>>>>> MySQLdb does not support).
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> Read the Python documentation... Try the Python Library Reference,
>>>>>>>>>>>> section 3.6 in particular (at least in my Python 2.5; do not know if
>>>>>>>>>>>> major sections changed in 3.x).
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> Define the variable "e".
>>>>>>>>>>>> 
>>>>>>>>>>>> Are you using Python 2.x or 3.x? Language reference manual section
>>>>>>>>>>>> 7.4 covers the try/except clause.
>>>>>>>>>>>> 
>>>>>>>>>>>> That will let you see what the MySQLdb error reported.
>>>>>>>>>>>> 
>>>>>>>>>>>> --
>>>>>>>>>>>> Wulfraed                 Dennis Lee Bieber         AF6VN
>>>>>>>>>>>> wlfraed at ix.netcom.com    HTTP://wlfraed.home.netcom.com/


>>>>>>>>>>>> Submitted via EggHeadCafe 
>>>>>>>>>>>> SharePoint Get Attachment with Attachment Icon Using Javascript
>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/84606954-21c2-4038-a9ed-bcbd525f978e/sharepoint-get-attachment-with-attachment-icon-using-javascript.aspx



More information about the Python-list mailing list