Mysql 或 python 中的数据类型转换

发布于 2024-12-18 15:02:18 字数 1061 浏览 0 评论 0原文

我有一个 .txt 文件，其中包含一列unixtime，例如（1322485992.420381000），所有数据的点之前和之后的位数都相同。首先我想将这一列导入到Mysql表中，我应该如何定义数据类型？

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

之后，在Python中，我需要将此列转换为日期时间，例如：2011-11-28 14:25:23 我使用结果来保存从数据库获取的数据，然后尝试将其转换为日期时间。但它无法工作，它说 datetime.fromtimestamp 需要一个浮点数。但是如果我使用unixtime float(10,10) 创建列，txt 文件中的数据无法写入数据库。

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)

有人可以帮忙吗？非常感谢！

-编辑- 对于 cur.fetchall() 中的行：打印（行） times = datetime.fromtimestamp(float(row)) 打印结果为('1322485970.084063000',)，则错误信息为TypeError: float() argument must be a string or a number。 那么我如何获取纯字符串值以摆脱 ('',)

-edit- 使用 row[0] 代替...问题解决了...

原文

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

分享到QQ

分享到微博