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 04:34:09 EST 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