Python converto datetime.isoformat(datetime.utcnow()) to mysql datetime format?
That generates something like the following: 2017-05-24T04:08:09.530033 How do I convert that to «MYSQL insertable» datetime format in a clean way? Thanks!
3 Answers 3
Try to use MySQL’s STR_TO_DATE() function to parse the string that you’re attempting to insert.
You can specify any type of format like this depending on the one you `ve set in mysql
data['timestamp'] =pd.to_datetime(data['timestamp'] , format='%d%b%Y:%H:%M:%S.%f') but I already have a lot of registers in that format in a text file that I need to convert to the other format, how I do this?
could you add in your question,how it is the file,how it is loaded in pandas,and which final format you it to converted to.
First off, it looks like you ran from datetime import * rather than import datetime . That’s tempting because it lets you type less when you want to refer to parts of the module, but it can get you into name collision issues later. An alternative with less typing is something like import datetime as dt , that way later you can just use dt.datetime. This will make your code cleaner.
MySQL accepts several date formats, which can be read about in detail here. In particular:
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format.
ISO8601 numbers look just like that! 2017-05-24T04:19:32
So if the only difference is the «T» in the middle instead of a space, just run something like this, assuming you don’t change your import statements.
timestamp = str(datetime.isoformat(datetime.utcnow())) timestamp = timestamp.replace("T", " ") data['timestamp'] = timestamp convert python string date to mysql datetime
Lun= day of week (lunes/monday) Ene = month (enero/january) I need to enter them in a mysql table, in a datetime field.
I imagine it is a very common issue and was wondering if someone already have a script to do it or could point out where I could look for. Thanks in advance!
What date format is the first string in? The second one seems to have two months, and the first seems to include to non-english words.
@fr1tz The second does not have two months, it is also in spanish. Lun is «Lunes» = «Monday», and Mar is «Martes» = «Tuesday». The months seem to be too «Enero» = «January» and «Febrero» = «February».
@Cyber This is particularly confusing considering that Mar is a common english contraction of March which is the month after Feb!
2 Answers 2
month_of_the_year = ['Ene', 'Feb', 'Mar', 'Abr', 'May', 'Jun', 'Jul', 'Ago', 'Sep', 'Oct', 'Nov', 'Dec'] def convert_to_mysql_format(string): explode = string.split() day_of_the_month = explode[2][:-1] if int(explode[2][:-1]) < 10: day_of_the_month = "%02d" % (int(explode[2][:-1]),) if explode[5] == 'am': time_split = explode[4].split(':') if time_split[0] == '12': time_split[0] = '00' elif int(time_split[0]) < 10: time_split[0] = "%02d" % int(time_split[0]) else: time_split = explode[4].split(':') if int(time_split[0]) in range(1, 12): time_split[0] = str(int(time_split[0]) + 12) if month_of_the_year.index(explode[1]) < 12: explode[1] = "%02d" % (month_of_the_year.index(explode[1])+1) return explode[3]+'-'+explode[1]+'-'+day_of_the_month+' '+time_split[0]+':'+time_split[1]+':00' print convert_to_mysql_format("Lun Ene 27, 2014 9:52 am") print convert_to_mysql_format("Lun Ene 27, 2014 9:52 pm") 2014-01-27 09:52:00
2014-01-27 21:52:00
Thanks mahmoh! I adjusted the las if to be < 12 and is working perfectly! Just for my info, what is the "%02d" % statement??
@user2950162 Firstly thank you for correcting the last if statement, did not debug enough :-). Secondly "%02d" (d)=integer, (02)=if the number representation is 1 digit, add a leading zero. Take in consideration that a negative single digit number like -2 it will not be shown as -02, to have the -02 result you must say "%03".
By default, Python runs using C locale:
>>> from datetime import datetime >>> datetime.strptime("Tue Feb 11, 2014 5:38 PM", "%a %b %d, %Y %I:%M %p") datetime.datetime(2014, 2, 11, 17, 38) >>> import locale >>> locale.nl_langinfo(locale.T_FMT_AMPM) '%I:%M:%S %p' Changing locale partially helps on my system:
>>> locale.setlocale(locale.LC_TIME, 'es_ES.UTF-8') 'es_ES.UTF-8' >>> datetime.strptime("Lun Ene 27, 2014 9:52 am"[:-2], "%a %b %d, %Y %I:%M %p") datetime.datetime(2014, 1, 27, 9, 52) >>> locale.nl_langinfo(locale.T_FMT_AMPM) '' T_FMT_AMPM is not set for some reason on my system for es_ES.UTF-8 . To fix it, you could manually add 12 hours if the time string ends with 'pm' .
The strftime() and time behaviour is the same.
Note: the locale name may be different on other systems.
Python Datetime into MySQL
My Python is able to parse out the date in the last field of line 2 and then iteratively combine it with the time on each row. Using the combo of date and time as a timestamp (type incompatibility is probably the problem?) I try to insert it into a mySQL database into a column of type timestamp (and power into a column of type DECIMAL(4,3)) I've played around with various type combinations but clearly not in the right way. I'd be really grateful for a Python expert to clarify how to successfully get the synthesised date into a form compatible with MySQL timestamp. The Python is:
#!/usr/bin/python from os import listdir from datetime import datetime import MySQLdb #from sys import argv def is_dated_csv(filename): """ Return True if filename matches format YY-MM-DD.csv, otherwise False. """ date_format = '%y-%m-%d.csv' try: date = datetime.strptime(filename, date_format) return True except ValueError: # filename did not match pattern print filename + ' did NOT match' pass #'return' terminates a function return False def parse_for_date(filename): """ Read file for the date - from line 2 field 10 """ currentFile = open(filename,'r') l1 = currentFile.readline() #ignore first line read date_line = currentFile.readline() #read second line dateLineArray = date_line.split("|") day_in_question = dateLineArray[-1]#save the last element (date) currentFile.close() return day_in_question def normalise_date_to_UTF(day_in_question): """ Rather wierdly, some days use YYYY.MM.DD format & others use DD/MM/YYYY This function normalises either to UTC with a blank time (midnight) """ if '.' in day_in_question: #it's YYYY.MM.DD dateArray = day_in_question.split(".") dt = (dateArray[0] +dateArray[1] + dateArray[2].rstrip() + '000000') elif '/' in day_in_question: #it's DD/MM/YYYY dateArray = day_in_question.split("/") dt = (dateArray[2].rstrip() + dateArray[1] + dateArray[0] + '000000') theDate = datetime.strptime(dt,'%Y%m%d%H%M%S') return theDate #A datetime object def parse_power_values(filename, theDate): currentFile = open(filename,'r') for i, line in enumerate(currentFile): if i 7) and (i 0): #print str(i) + '/ ' + str(timestamp) + ' power = ' + power + 'kWh' append_to_database(timestamp,power) #else: # print str(i) + '/ ' elif i > 151: print str(timestamp) + ' DONE!' print '----------------------' break currentFile.close() def append_to_database(timestampval,powerval): a = datetime.strptime(timestampval,"%b %d %Y %H:%M") host="localhost", # your host, usually localhost user="root", # your username passwd="******" database_name = 'SunnyData' table_name = 'DTP' timestamp_column = 'DT' power_column = 'PWR' sql = ("""INSERT INTO %s(%s,%s) VALUES(%s,'%s')""", ('table_name', 'timestamp_column', 'power_column', a.strftime('%Y-%m-%d %H:%M:%S'), powerval) ) print sql #db = MySQLdb.connect(host,user,passwd,database_name) cur = SD.cursor() try: cur.execute(sql) print 'SQL: ' + sql SD.commit() except: print 'DB append failed!' SD.rollback() # Main start of program path = '.' for filename in listdir(path): if is_dated_csv(filename): SD = MySQLdb.connect(host="localhost", user="root",passwd="**********", db = 'SunnyData') print filename + ' matched' # Do something with the desired .csv file day_in_question = parse_for_date(filename) print 'the date is ' + day_in_question theDate = normalise_date_to_UTF(day_in_question) parse_power_values(filename, theDate) SD.close() pass CREATE TABLE `DTP` ( `idDTP` int(11) NOT NULL, `DT` timestamp NULL DEFAULT NULL, `PWR` decimal(4,3) DEFAULT NULL, PRIMARY KEY (`idDTP`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; what is the proper way to convert between mysql datetime and python timestamp?
according to http://dev.mysql.com/doc/refman/5.0/en/datetime.html. i got to find a way to convert the string value 'YYYY-MM-DD HH:MM:SS' to a timestamp int. i looked up in python's doc. i tried:
print(time.strptime('2013-01-12 15:27:43', '%Y-%m-%d %H:%M:%S')) python give me a result like this. time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=15, tm_min=27, tm_sec=43, tm_wday=5, tm_yday=12, tm_isdst=-1) i tried this to convert timestamp to YYYY-MM-DD HH:MM:SS format
print(time.strftime('%Y-%m-%d %H:%M:%S',time.time())) python give me a type error. i only use timestamp to calculate time and date, i hope there's already a way in python, simple and efficient , and don't have to create temp data. according to the answer i write two methods. hope it would be helpful
import time def convertTimestampToSQLDateTime(value): return time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(value)) def convertSQLDateTimeToTimestamp(value): return time.mktime(time.strptime(value, '%Y-%m-%d %H:%M:%S')) 2 Answers 2
Happy to update this if I'm not properly understanding, but here are a few examples which may help. Note that this uses the datetime module instead of time .
Here we set up an example timestamp ts and a format f :
>>> ts = '2013-01-12 15:27:43' >>> f = '%Y-%m-%d %H:%M:%S' Similar to what you did above, we use the strptime function (from datetime.datetime ) to convert our string into a datetime object based on the formatting parameter:
>>> datetime.datetime.strptime(ts, f) datetime.datetime(2013, 1, 12, 15, 27, 43) Now in reverse - here we use datetime.datetime.now() to get the current time as a datetime object:
>>> now = datetime.datetime.now() >>> now datetime.datetime(2013, 1, 12, 0, 46, 54, 490219) In the datetime case, the strftime method is actually called on the datetime object itself, with the formatting parameter as an argument:
>>> now.strftime(f) '2013-01-12 00:46:54' In your situation, the reason you were getting an error is because time.time() returns a float:
>>> time.time() 1357980846.290231 But time.strftime needs a time tuple, similar to what you had above. Without getting into the maddening spiral that is time, a function such as time.localtime() will return the aforementioned time tuple and will return as you expect:
>>> now = time.localtime() >>> now time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=0, tm_min=55, tm_sec=55, tm_wday=5, tm_yday=12, tm_isdst=0) >>> f = '%Y-%m-%d %H:%M:%S' >>> time.strftime(f, now) '2013-01-12 00:55:55' good indeed.im not looking for datetime solution but you also give the time solution .use time.localtime() to convert the timestamp data ,and its inverse function is time.mktime().
I'm only adding this class to potentially save the next guy a little time. If anyone finds this useful, upvote RocketDonkey's answer.
## dev on v3.7.6 from datetime import datetime from time import mktime, time class Time: '''\ *Convenience class for easy format conversion*\n Accepts time() float, datetime object, or SQL datetime str.\n If no time arg is provided, object is initialized with time().\n id kwarg can be used to keep track of objects.\n Access formats as instance.t, instance.dt, or instance.sql.\ ''' f = '%Y-%m-%d %H:%M:%S' def __init__(self, *arg, -> None: self.id = id if len(arg) == 0: self.t = time() self.dt = self._dt self.sql = self._sql else: arg = arg[0] if isinstance(arg, float) or arg == None: if isinstance(arg, float): self.t = arg else: self.t = time() self.dt = self._dt self.sql = self._sql elif isinstance(arg, datetime): self.t = arg.timestamp() self.dt = arg self.sql = self._sql elif isinstance(arg, str): self.sql = arg if '.' not in arg: self.dt = datetime.strptime(self.sql, Time.f) else: normal, fract = arg.split('.') py_t = datetime.strptime(normal, Time.f) self.dt = py_t.replace( microsecond=int(fract.ljust(6, '0')[:6])) self.t = self.dt.timestamp() @property def _dt(self) -> datetime: return datetime.fromtimestamp(self.t) @property def _sql(self) -> str: t = self.dt std = t.strftime(Time.f) fract = f'.' return std + fract def __str__(self) -> str: if self.id == None: return self.sql else: return f'Time obj "": ' def test(): def test_one(*arg): t = Time(*arg, print(t) print(t.t) print(t.dt) sql = '2020-01-22 15:30:33.433' time_float = 1579927395.3708763 dt_obj = datetime.now() for datum in [sql, time_float, dt_obj, None]: test_one(datum) if __name__ == '__main__': test()