Re: [Tutor] date conversion
Python wrote:
> On Wed, 2006-04-05 at 10:50 -0400, Kent Johnson wrote:
>> Ray Allen wrote:
>>> I would like Python to convert a date returned by MySQL (2006-04-05)
>
> Kent's advice below is of course correct, but I'd bet your variable is
> already a datetime.date (or mx.DateTime.Date with older Python
> releases). In that case you'd already be at step 7.
>
> When executing your update statement use the args field.
>
> cursor.execute("UPDATE table SET mydate=%s WHERE id=%s", (date_variable,
> recordID))
>
>
> You should not have to worry about getting your date into the proper
> string format for the database SQL syntax.
Good point. You may well be getting some kind of date object back from
MySQL, too, rather than a string.
Kent
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] date conversion
On Wed, 2006-04-05 at 10:50 -0400, Kent Johnson wrote:
> Ray Allen wrote:
> > I would like Python to convert a date returned by MySQL (2006-04-05)
Kent's advice below is of course correct, but I'd bet your variable is
already a datetime.date (or mx.DateTime.Date with older Python
releases). In that case you'd already be at step 7.
When executing your update statement use the args field.
cursor.execute("UPDATE table SET mydate=%s WHERE id=%s", (date_variable,
recordID))
You should not have to worry about getting your date into the proper
string format for the database SQL syntax.
Here's the help from MySQLdb:
help(curs.execute)
execute(self, query, args=None) method of MySQLdb.cursors.Cursor instance
Execute a query.
query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.
Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.
Returns long integer rows affected, if any
> to a
> > user readable format such as 05-Apr-2006 for display and then to convert it
> > back to ISO format for update.
>
> Here's one way:
>
> In [1]: from datetime import date
>
> In [2]: data = '2006-04-05'
>
> Use split() and int() to convert to a list of year, month, day
> In [4]: ymd = map(int, data.split('-'))
>
> In [5]: ymd
> Out[5]: [2006, 4, 5]
>
> Turn it into a date. The * makes the list act like individual parameters.
> In [6]: d=date(*ymd)
>
> In [7]: d
> Out[7]: datetime.date(2006, 4, 5)
>
> See the docs for the time module for info about strftime() format codes
> In [8]: d.strftime('%d-%b-%Y')
> Out[8]: '05-Apr-2006'
>
> ISO format is built-in.
> In [9]: d.isoformat()
> Out[9]: '2006-04-05'
>
>
> For other input formats you might have to use time.strptime() to convert
> to a time tuple, then pass the first three elements do date():
>
> In [10]: import time
>
> In [15]: t=time.strptime(data, '%Y-%m-%d')
>
> In [16]: t
> Out[16]: (2006, 4, 5, 0, 0, 0, 2, 95, -1)
>
> In [17]: date(*t[:3])
> Out[17]: datetime.date(2006, 4, 5)
>
>
> Kent
>
> ___
> Tutor maillist - Tutor@python.org
> http://mail.python.org/mailman/listinfo/tutor
--
Lloyd Kvam
Venix Corp.
1 Court Street, Suite 378
Lebanon, NH 03766-1358
voice: 603-653-8139
fax:320-210-3409
--
Lloyd Kvam
Venix Corp
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] date conversion
Oh yeah - you can also simply use Mysql's date_format() function when
you query the database ??
select date_format(column_name,'%d-%b-%Y') as formatted_date from table
where blah;
would output the date as 05-Apr-2006
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
Brian Gustin wrote:
> I wrote something similar on my blog some time back..
> http'://phplogix.com/codefool/?postid=13
> This is for converting Apache logfiles to a ISO formatted date for
> mysql.. It could easily be reversed, with a little tweaking.. :)
>
>
> to wit:
> def mreplace(s, chararray, newchararray):
> for a, b in zip(chararray, newchararray):
> s = s.replace(a, b)
> return s
>
> olddatestring =
> ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
>
> newdatestring =
> ('01','02','03','04','05','06','07','08','09','10','11','12')
>
> def clftosql(date):
> ttime = date.split(":",1)
> time = ttime[1]
> datelist = ttime[0].split('/')
> #should be , E.G DD,MM, we need -MM-DD for sql
> day = datelist[0]
> month = datelist[1]
> year = datelist[2]
> newdate = year+'-'+month+'-'+day+' '+time
> return newdate
>
> then you can take a date stamp from an Apache CLF log like this :
> 01/Oct/2000:00:00:00 (after you regex it out of the log with:
>
> datechk =
> re.compile('([0-9]+/.../[0-9][0-9][0-9][0-9]:[0-9][0-9]:[0-9][0-9]:[0-9][0-9])')
>
>
> of course)
>
> and run the functions thus:
>
> mydate = mreplace(dated,olddatestring,newdatestring)
> mydate1 = clftosql(mydate)
>
>
> Ray Allen wrote:
>
>>I would like Python to convert a date returned by MySQL (2006-04-05) to a
>>user readable format such as 05-Apr-2006 for display and then to convert it
>>back to ISO format for update. What is the most convenient way of doing
>>this? I'm struggling to understand the datetime module's functionality.
>>Ray Allen
>>
>>___
>>Tutor maillist - Tutor@python.org
>>http://mail.python.org/mailman/listinfo/tutor
>>
>>
>>
>>
>
>
> ___
> Tutor maillist - Tutor@python.org
> http://mail.python.org/mailman/listinfo/tutor
>
> !DSPAM:4433db1a224961964068235!
>
>
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] date conversion
I wrote something similar on my blog some time back..
http://phplogix.com/codefool/?postid=13
This is for converting Apache logfiles to a ISO formatted date for
mysql.. It could easily be reversed, with a little tweaking.. :)
to wit:
def mreplace(s, chararray, newchararray):
for a, b in zip(chararray, newchararray):
s = s.replace(a, b)
return s
olddatestring =
('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
newdatestring =
('01','02','03','04','05','06','07','08','09','10','11','12')
def clftosql(date):
ttime = date.split(":",1)
time = ttime[1]
datelist = ttime[0].split('/')
#should be , E.G DD,MM, we need -MM-DD for sql
day = datelist[0]
month = datelist[1]
year = datelist[2]
newdate = year+'-'+month+'-'+day+' '+time
return newdate
then you can take a date stamp from an Apache CLF log like this :
01/Oct/2000:00:00:00 (after you regex it out of the log with:
datechk =
re.compile('([0-9]+/.../[0-9][0-9][0-9][0-9]:[0-9][0-9]:[0-9][0-9]:[0-9][0-9])')
of course)
and run the functions thus:
mydate = mreplace(dated,olddatestring,newdatestring)
mydate1 = clftosql(mydate)
Ray Allen wrote:
> I would like Python to convert a date returned by MySQL (2006-04-05) to a
> user readable format such as 05-Apr-2006 for display and then to convert it
> back to ISO format for update. What is the most convenient way of doing
> this? I'm struggling to understand the datetime module's functionality.
> Ray Allen
>
> ___
> Tutor maillist - Tutor@python.org
> http://mail.python.org/mailman/listinfo/tutor
>
> !DSPAM:4433d45e204351006614580!
>
>
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] date conversion
Ray Allen wrote:
> I would like Python to convert a date returned by MySQL (2006-04-05) to a
> user readable format such as 05-Apr-2006 for display and then to convert it
> back to ISO format for update.
Here's one way:
In [1]: from datetime import date
In [2]: data = '2006-04-05'
Use split() and int() to convert to a list of year, month, day
In [4]: ymd = map(int, data.split('-'))
In [5]: ymd
Out[5]: [2006, 4, 5]
Turn it into a date. The * makes the list act like individual parameters.
In [6]: d=date(*ymd)
In [7]: d
Out[7]: datetime.date(2006, 4, 5)
See the docs for the time module for info about strftime() format codes
In [8]: d.strftime('%d-%b-%Y')
Out[8]: '05-Apr-2006'
ISO format is built-in.
In [9]: d.isoformat()
Out[9]: '2006-04-05'
For other input formats you might have to use time.strptime() to convert
to a time tuple, then pass the first three elements do date():
In [10]: import time
In [15]: t=time.strptime(data, '%Y-%m-%d')
In [16]: t
Out[16]: (2006, 4, 5, 0, 0, 0, 2, 95, -1)
In [17]: date(*t[:3])
Out[17]: datetime.date(2006, 4, 5)
Kent
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
