Advanced SQLite Usage in Python
前端之家收集整理的这篇文章主要介绍了
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 somedateanddatetimetypes in our sqlite3 database. When you execute the insert query with a date or datetime object,thesqlite3module 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_DECLTYPESandPARSE_COLNAMESto theconnectmethod:
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'>
)
|
Changing the connect method,the database now is returning a date object. Thesqlite3module uses the column's type to return the correct type of object. So,if we need to work with adatetimeobject,we must declare the column in the table as atimestamptype:
10
11
Crayon-o" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(255,created_at timestamp)'''
)
# Insert a datetime object
now
datetime
now
)
(
now
)
)
@H_425_ 403@>>>
# Retrieve the inserted object
)
)
)
# The date is 2013-04-14 16:29:11.666274 and the datatype is <class 'datetime.datetime'>
|
In case you have declared a column type asDATE,but you need to work with a
8
c
)
# We are going to insert a datetime object into a DATE column
now
)
c
)
db
)
# Retrieve the inserted object
'''SELECT created_at as "created_at [timestamp]" FROM example'''
)
|
Usingas "created_at [timestamp]"in the sql query will make the adapter to parse the object correctly.
Insert Multiple Rows with sqlite's executemany
Sometimes we need to insert a sequence of objects in the database,51); white-space:nowrap">sqlite3module provides theexecutemanymethod to execute a sql query against a sequence.
18
19
20
21
# Import the sqlite3 module
db
)
c
)
c
'''CREATE TABLE users(id INTEGER PRIMARY KEY,name TEXT,phone TEXT)'''
)
users
[
'John'
'5557241'
'Adam'
'5547874'
'Jack'
'5484522'
'Monthy'
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
' 6656565'
)
]
c
executemany
'''INSERT INTO users(name,phone) VALUES(?,?)'''
users
)
db
)
# Print the users
c
'''SELECT * FROM users'''
)
for
row
in
c
:
(
row
)
db
)
|
Please note that each element of the sequence must be a tuple.
Execute sql File with sqlite's executescript
Theexecutemethod only allows you to execute a single sql sentence. If you need to execute several different sql sentences you should useexecutescriptmethod:
# Import the sqlite3 module
db
)
)
script
Crayon-h" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(0,phone TEXT);
CREATE TABLE accounts(id INTEGER PRIMARY KEY,description TEXT);
INSERT INTO users(name,phone) VALUES ('John','5557241'),
('Adam','5547874'),('Jack','5484522');'''
executescript
(
script
)
# Print the results
c
)
:
)
db
)
If you need to read the script from a file:
4
fd
open
'myscript.sql'
'r'
)
script
fd
read
)
c
)
fd
)
|
Please remember that it is a good idea to surround your code with atry/except/elseclause in order to catch the exceptions. To learn more about thetry/except/elsekeywords,checkout theCatching Python Exceptions – The try/except/else keywordsarticle.
Defining sqlite sql Functions
Sometimes we need to use our own functions in a statement,specially when we are inserting data in order to accomplish some specific task. A good example of this is when we are storing passwords in the database and we need to encrypt those passwords:
14
15
sqlite3
#Import the sqlite3 module
hashlib
def
encrypt_password
(
password
)
:
# Do not use this algorithm in a real environment
encrypted_pass
hashlib
sha1
(
password
encode
'utf-8'
hexdigest
)
return
encrypted_pass
db
)
# Register the function
db
create_function
'encrypt'
1
encrypt_password
)
)
c
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,email TEXT,password TEXT)'''
)
user
'johndoe@example.com'
'12345678'
)
c
'''INSERT INTO users(email,password) VALUES (?,encrypt(?))'''
user
)
|
Thecreate_functiontakes 3 parameters:name(the name used to call the function inside the statement),the number of parameters the function expects (1 parameter in this case) and a callable object (the function itself). To use our registered function,we called it usingencrypt()in the statement.
Finally,PLEASE use a true encryption algorithm when you are storing passwords!
via:http://www.pythoncentral.io/advanced-sqlite-usage-in-python/
原文链接：https://www.f2er.com/sqlite/200656.html
