I have a database which is supposed to be moved to a new one. My idea is a python script that reads all old entries, converts them in the new format and sends them to the new database. The problem is that the date(time) is stored differently. In the old db there are 5 columns (year, month, day, hour, minute) but the new one just works with a datetime field (YYYY-MM-DD hh:mm:ss).
If I try something like this in python:
import time
t = time.mktime((2015,11,29,14,30,0,0*,246*,-1*)) # * not sure about these values
print time.strftime("%Y-%m-%d %H:%M:%S", time.gmtime(t))
I even get another time than expected:
2015-11-29 13:30:00
How can I get for 180k entries the right datetime format or is this maybe already the wrong way to enter a value into a datetime field?
EDIT:
import MySQLdb
mysql_opts = {
'host': "123.45.67.89",
'user': "user",
'pass': "password",
'db': "db"}
t = time.mktime((2015, 11, 29, 14, 30, 0, 0, 0, 0))
date = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t))
data = [date,...]
mysql = MySQLdb.connect(mysql_opts['host'],mysql_opts['user'],mysql_opts['pass'],mysql_opts['db'])
cursor = mysql.cursor()
cursor.execute("INSERT INTO table (date, ...) VALUES(%s,...)",(data[0],...))
mysql.commit()
mysql.close()
If I try this I am getting this error:
TypeError: not all arguments converted during string formatting
What else do I have to do before I can add an entry? :S
It seems like you are doing it right, but you are not printing the time in GMT:
# get from old DB
Y = 2015
m = 11
d = 29
H = 14
M = 30
S = 0
t = time.mktime((Y, m, d, H, M, S, 0, 0, 0))
time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t)) # use this to get the same time
Out[1]: '2015-11-29 14:30:00'