CODEWITHSUNDEEP
pythonmysqltypes
manxing
manxing

Reputation: 3335

Data type convert in Mysql or python

I have a .txt file which contains a column of unixtime, like (1322485992.420381000), the number of digits before and after the dot are the same for all data. First I want to import this column into a Mysql table, how should I define the data type? 

CREATE TABLE videoinfo (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, unixtime char(50))

After that, in Python, I need to convert this column into datetime like: 2011-11-28 14:25:23 I use resultsto keep the data fetched from database,then try to convert it into datetime. But it cannot work, it says datetime.fromtimestamp requires a float. But if I use unixtime float(10,10) to create the column, data from txt file cannot be written into database. 

results = cur.fetchall()
for result in results:
    times = datetime.fromtimestamp(result[0])
    cur.execute("ALTER TABLE youtube ADD date DATETIME NOT NULL DEFAULT after unixtime")
    for time in times:
        cur.execute(u'''insert into `date` values (%s)''', time)

Can anyone help? Many thanks!!!

-edit- for row in cur.fetchall(): print (row) times = datetime.fromtimestamp(float(row)) The print result is ('1322485970.084063000',)then, the error message is TypeError: float() argument must be a string or a number. So how can I fetch the pure string value to get rid of ('',)

-edit- use row[0] instead...problem solved...

Upvotes: 0

Views: 2276

Answers (1)

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29985

Looks like a DOUBLE(11,9) although such a large number may cause problems on 32-bit systems. Consider using a VARCHAR(21) if that's an issue.

Also, MySQL simply understands FROM_UNIXTIME(1322485992.420381000).

-edit-

When it says "requires a float", why don't you just use float(result) ?

Upvotes: 1

Related Questions