前端之家收集整理的这篇文章主要介绍了
Advanced SQLite Usage in Python,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
Following the sqlite3 series,this post is about some advanced topics when we are working with the sqlite3 module. If you missed the first part,you can find ithere.
Using sqlite's date and datetime Types
Sometimes we need to insert and retrieve somedate
anddatetime
types in our sqlite3 database. When you execute the insert query with a date or datetime object,thesqlite3
module calls the default adapter and converts them to an ISO format. When you execute a query in order to retrieve those values,51); white-space:nowrap">sqlite3module is going to return a string object:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
from
datetime
date
,
datetime
>>>
db
=
sqlite3
.
connect
(
':memory:'
)
c
db
cursor
(
)
c
execute
'''CREATE TABLE example(id INTEGER PRIMARY KEY,created_at DATE)'''
)
>>>
# Insert a date object into the database
today
date
today
)
'''INSERT INTO example(created_at) VALUES(?)'''
(
today
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
)
)
commit
)
>>>
# Retrieve the inserted object
'''SELECT created_at FROM example'''
)
row
fetchone
)
print
'The date is {0} and the datatype is {1}'
.
format
(
row
[
0
]
type
)
# The date is 2013-04-14 and the datatype is <class 'str'>
close
)
|
The problem is that if you inserted a date object in the database,most of the time you are expecting a date object when you retrieve it,not a string object. This problem can be solved passingPARSE_DECLTYPES
andPARSE_COLNAMES
to theconnect
method:
16
17
datetime
>>>
detect_types
=
.
PARSE_DECLTYPES
|
.
PARSE_COLNAMES
)
)
)
# Insert a date object into the database
)
)
)
>>>
# Retrieve the inserted object
)
)
)
# The date is 2013-04-14 and the datatype is <class 'datetime.date'>
)