Data type convert in Mysql or python

Question

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...

Tom van der Woerdt · Accepted Answer · 2011-11-29 17:00:59Z

1

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) ?

answered Nov 29, 2011 at 17:00

Tom van der Woerdt

30.1k7 gold badges76 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

manxing Over a year ago

ok, but I have a column of data needed to be converted, not just a single one

manxing Over a year ago

I used float to define the column, but like I said, the problem is the data in txt file cannot be written into database, but when it's defined as char, it can be written

Tom van der Woerdt Over a year ago

Actually I meant using float() in Python: times = datetime.fromtimestamp(float(result[0]))

manxing Over a year ago

Ok, I tried, got error: ValueError: invalid literal for float(): None

Tom van der Woerdt Over a year ago

In that case, as the error says, result[0] is None which explains why you can't insert it :-)

|

Collectives™ on Stack Overflow

Data type convert in Mysql or python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related