Inserting a predetermined datetime value into SQL table
Question: how do I insert a datetime value into MS SQL server, given the code below? Context: I have a 2-D list (i.e., a list of lists) in Python that I’d like to upload to a table in Microsoft SQL Server 2008. For this project I am using Python’s pymssql package. Each value in each list is a string except for the very first element, which is a datetime value. Here is how my code reads:
import pymssql db_connect = pymssql.connect( # these are just generic names server = server_name, user = db_usr, password = db_pwd, database = db_name ) my_cursor = db_connect.cursor() for individual_list in list_of_lists: # the first value in the paranthesis should be datetime my_cursor.execute("INSERT INTO [DB_Table_Name] VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", tuple(individual_list)) db_connect.commit() The python interpreter is having a tough time inserting my datetime values. I understand that currently I have %s and that it is a string formatter, but I’m unsure what I should use for datetime , which is what the database’s first column is formatted as. The «list of lists» looks like this (after each list is converted into a tuple):
[(datetime.datetime(2012, 4, 1), '1', '4.1', 'hip', 'A1', 'J. Smith', 'B123', 'XYZ'). ] +-----------+------+------+--------+-------+-----------+---------+---------+ | date | step | data | type | ID | contact | notif. | program | +-----------+------+------+--------+-------+-----------+---------+---------+ |2012-04-01 | 1 | 4.1 | hip | A1 | J. Smith | B123 | XYZ | |2012-09-05 | 2 | 5.1 | hip | A9 | B. Armst | B123 | ABC | |2012-01-16 | 5 | 9.0 | horray | C6 | F. Bayes | P995 | XYZ | +-----------+------+------+--------+-------+-----------+---------+---------+ 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() Inserting a Python datetime.datetime object into MySQL
I have a date column in a MySQL table. I want to insert a datetime.datetime() object into this column. What should I be using in the execute statement? I have tried:
now = datetime.datetime(2009,5,5) cursor.execute("INSERT INTO table (name, id, datecolumn) VALUES (%s, %s , %s)",("name", 4,now)) I am getting an error as: «TypeError: not all arguments converted during string formatting» What should I use instead of %s ?
you missed quotes around %s cursor.execute(«INSERT INTO table (name, id, datecolumn) VALUES (%s, %s , ‘%s’)»,(«name», 4,now))
9 Answers 9
import time time.strftime('%Y-%m-%d %H:%M:%S') I think strftime also applies to datetime .
Also, make sure your column name isn’t a reserved word. Took me 30 minutes to realize the name «current_date» was causing problems. ugh!
You are most likely getting the TypeError because you need quotes around the datecolumn value.
now = datetime.datetime(2009, 5, 5) cursor.execute("INSERT INTO table (name, id, datecolumn) VALUES (%s, %s, '%s')", ("name", 4, now)) With regards to the format, I had success with the above command (which includes the milliseconds) and with:
Try using now.date() to get a Date object rather than a DateTime .
If that doesn’t work, then converting that to a string should work:
now = datetime.datetime(2009,5,5) str_now = now.date().isoformat() cursor.execute('INSERT INTO table (name, id, datecolumn) VALUES (%s,%s,%s)', ('name',4,str_now)) Use Python method datetime.strftime(format) , where format = ‘%Y-%m-%d %H:%M:%S’ .
import datetime now = datetime.datetime.utcnow() cursor.execute("INSERT INTO table (name, id, datecolumn) VALUES (%s, %s, %s)", ("name", 4, now.strftime('%Y-%m-%d %H:%M:%S'))) Timezones
If timezones are a concern, the MySQL timezone can be set for UTC as follows:
cursor.execute("SET time_zone = '+00:00'") And the timezone can be set in Python:
now = datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc) MySQL Documentation
MySQL recognizes DATETIME and TIMESTAMP values in these formats:
As a string in either ‘YYYY-MM-DD HH:MM:SS’ or ‘YY-MM-DD HH:MM:SS’ format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, ‘2012-12-31 11:30:45’, ‘2012^12^31 11+30+45’, ‘2012/12/31 11*30*45’, and ‘2012@12@31 11^30^45’ are equivalent.
The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.
The date and time parts can be separated by T rather than a space. For example, ‘2012-12-31 11:30:45’ ‘2012-12-31T11:30:45’ are equivalent.
As a string with no delimiters in either ‘YYYYMMDDHHMMSS’ or ‘YYMMDDHHMMSS’ format, provided that the string makes sense as a date. For example, ‘20070523091528’ and ‘070523091528’ are interpreted as ‘2007-05-23 09:15:28’, but ‘071122129015’ is illegal (it has a nonsensical minute part) and becomes ‘0000-00-00 00:00:00’.
As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as ‘1983-09-05 13:28:00’.