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

Discussion in 'Python' started by Íßêïò, Aug 5, 2010.

  1. Íßêïò

    Íßêïò Guest

    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:

    cursor.execute( ''' SELECT host, hits, date FROM visitors WHERE page
    = '%s' ORDER BY date DESC ''' % (page) )
    except MySQLdb.Error:
    print( "Error %d: %s" % (e.args[0], e.args[1]) )
    #display results
    print ( '''<center><br><h3> ( ÅðéóêÝðôçò ) ----- ( ÅðéóêÝøåéò )
    ----- ( Çìåñïìçíßá ) </h3><br>''' )
    print ( '''<table border = "5" cellpadding = "5">''' )

    results = cursor.fetchall()

    for row in results:
    print ( ''' <tr> ''' )

    for entry in row:
    entry = datetime.datetime.strftime( entry, '%d %b, %H:%M' ) #!!!
    this is wrong!
    print ( ''' <td> %s </td> ''' % entry )


    Apart from that i don't 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!
    Íßêïò, Aug 5, 2010
    1. Advertisements

  2. 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 for dates in a test database) you should be
    probably be using

    formatted_entry = entry.strftime("%d... %M")

    to do the formatting as string
    Dennis Lee Bieber, Aug 5, 2010
    1. Advertisements

  3. 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.

    I tried that myself yesterday but look it fails to the following

    64 for entry in row:
    65 formatted_entry =
    entry.strftime('%d %b, %H:%M')
    66 print ( ''' <td> %s </td> ''' %
    entry )
    formatted_entry undefined, entry = '',
    entry.strftime undefined
    AttributeError: 'str' object has no attribute 'strftime'
    args = ("'str' object has no attribute 'strftime'",)
    Îίκος, Aug 5, 2010
  4. Íßêïò

    Íßêïò Guest

    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
    couldn't iterate over the items of the row.

    Could you please shoe me how could i do the same thing with
    Íßêïò, Aug 5, 2010
  5. Íßêïò

    Tim Chase Guest

    Well, depending on whether "row" is a tuple or a list, you can do

    row[2] = row[2].strftime(...) # if it's a list
    # the assignment will fail if it's a tuple

    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'm partial to the 2nd-from-last
    version, especially as the list of fields may grow.

    Tim Chase, Aug 5, 2010
  6. Íßêïò

    Íßêïò Guest

    As i have it the returned 'dataset' is stored line per line to 'row'.

    'dataset' in here is a 'list of tuples' right?
    '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 couldn't
    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!
    Íßêïò, Aug 5, 2010
  7. Íßêïò

    Tim Chase Guest

    Pretty's either a list-of-tuples or a list-of-lists
    (I'm 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 can't 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's 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 don't usually have cause to write a value back into the data
    I'm reading from the DB, so it's 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:

    # variant A
    for row in dataset:
    host = row[0]
    hits = row[1]
    dt = row[2]
    # rest of your code here

    # variant B
    for row in dataset:
    host, hits, dt = row
    # rest of your code here

    # variant C
    for host, hits, dt in row:
    # rest of your code here

    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's
    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,


    [*] "about" the same because in #1 and #2, you also have access
    to the whole row; whereas in #3, you don't have something called
    "row", but you could reassemble it if you needed:

    row = (host, hits, dt)
    Tim Chase, Aug 6, 2010
  8. Íßêïò

    John Nagle Guest

    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
    John Nagle, Aug 6, 2010
  9. Óôéò 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's 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 don't have the row tuple. So what tthose variabels store, and in
    what datatype they strore info in and what is the difference between this

    '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
    Would that be a row or a tuple when joined?

    Again, thanks for ALL your preciosu help you provide me!
    Íéêüëáïò Êïýñáò, Aug 7, 2010
  10. Íßêïò

    Íßêïò Guest

    Thanks i didn't know.
    But you also do use '%s' here as well. Just without the quotes? Why?
    How is this valid?
    And also what's the comma after the (page,) ? What does the comam do?
    Thanks iam trying to test this because its very interesting and when i
    try:"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 = ("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 with this rule

    RewriteCond %{REQUEST_FILENAME} -f
    RewriteRule ^/?(.+\.html) /cgi-bin/$1 [L,PT,QSA]

    Also for the program exectuion to reach the:

    cursor.execute( ''' SELECT host, hits, date FROM visitors WHERE page =
    '%s' ORDER BY date DESC ''' % (page) )

    statement i expect the user to:

    # if extra string is attached to the URL is 'log' then show excplicit
    page log and exit
    if form.getvalue('show') == 'log':

    Please help me reproduce what you suggest. I beleive its called MySQL
    injection and i very like want to see this in action! :)
    Íßêïò, Aug 7, 2010
  11. Íßêïò

    Tim Chase Guest

    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 doesn't use the intermediate tuple
    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 = "" # 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
    Yes, except the internals (of the DB 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 would behoove you to experiment with
    tuple-assignments such as the example code above so that you
    understand what it's doing in each case.

    Tim Chase, Aug 7, 2010
  12. 1) please read the DB-API PEP (PEP 249)

    It describes the basic requirements of all Python standard database
    adapters. Then read the documentation specific to MySQLdb.
    Short answer: It's 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; don't 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.
    Dennis Lee Bieber, Aug 8, 2010
  13. Íßêïò

    rahul mishra Guest

    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

    rahul mishra, Feb 24, 2011
  14. You do realize that email was sent over four months ago, correct?
    Corey Richardson, Feb 24, 2011
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.