sqlite中的时间戳列在python中返回字符串 [英] timestamp column in sqlite return string in python

问题描述

我用SQLite Date Browse App 创建一个表...

i create a table with SQLite Date Browse App ...

当我想从 timestamp 列中检索 datetime 值时，SQLite 返回 unicod 类型 ...

when i want retrieve datetime value from timestamp column , SQLite return unicod type ...

这是我的插入代码:

def Insert(self,mode,path,vname,stime,ftime):
        con = sqlite3.connect(PATH_DataBase)  # @UndefinedVariable
        con.execute('INSERT INTO SendList VALUES(?,?,?,?,?)',(mode,path,vname,stime,ftime))
        con.commit()
        con.close()

dt1 = datetime.datetime(2013,01,01,01,01,01,0)
dt2 = datetime.datetime(2015,01,01,01,01,01,0)
c = 0
    for f in os.listdir('/home/abbas/test/'):
        c += 1
        slist.Insert(common.MODE_Bluetooth_JAVA, '/home/abbas/test/'+f,'flower'+str(c) , dt1, dt2)

现在这是我的桌子:

但是当我想比较 starttime 和 datetime.now() python 给我错误:

but when i want compare starttime with datetime.now() python give me error :

TypeError: 无法将 datetime.datetime 与 unicode 进行比较

推荐答案

SQLite 没有专门用于存储日期和/或时间的存储类."参考:https://www.sqlite.org/datatype3.html

"SQLite does not have a storage class set aside for storing dates and/or times." Reference: https://www.sqlite.org/datatype3.html

Python 的 sqlite3 模块提供日期时间模块中日期和日期时间类型的默认适配器".参考:https://docs.python.org/2/library/sqlite3.html#default-adapters-and-converters

Python's sqlite3 module offers "default adapters for the date and datetime types in the datetime module." Reference: https://docs.python.org/2/library/sqlite3.html#default-adapters-and-converters

唯一的问题是您必须确保适当地定义列.示例 DDL:

The only catch is that you must be sure to define the columns appropriately. Example DDL:

import sqlite3

con = sqlite3.connect(PATH_DataBase, detect_types=sqlite3.PARSE_DECLTYPES)
con.execute('''create table if not exists SendList (
                 cid primary key, 
                 mode text, 
                 path text,
                 vname text,
                 starttime timestamp, 
                 endtime timestamp);''')
con.commit()
con.close()

插入或选择数据的任何后续连接都必须将 sqlite3.PARSE_DECLTYPES 作为关键字参数(又名 kwarg)detect_types 的值传递.示例:

Any subsequent connections to insert or select data must pass sqlite3.PARSE_DECLTYPES as the value for the keyword argument (aka kwarg) detect_types. Example:

import datetime as dt

con = sqlite3.connect(PATH_DataBase, detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute('''select 
                 *
               from 
                 SendList
               where 
                 starttime between ? and ?
               limit 10;''',
            (dt.datetime(2013,1,1,0,0,0), dt.datetime(2014,12,31,23,59,59)))
results = cur.fetchall()

这篇关于sqlite中的时间戳列在python中返回字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！