Introduction to SQLite in Python
前端之家收集整理的这篇文章主要介绍了
Introduction to SQLite in Python，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
Published: Thursday 11thApril 2013
Last Updated:Thursday 12
thDecember 2013
sqlite3 is a very easy to use database engine. It is self-contained,serverless,zero-configuration and transactional. It is very fast and lightweight,and the entire database is stored in a single disk file. It is used in a lot of applications as internal data storage. The Python Standard Library includes a module called "sqlite3" intended for working with this database. This module is a sql interface compliant with the DB-API 2.0 specification.
Using Python's sqlite Module
To use the sqlite3 module we need to add an import statement to our python script:
Connecting sqlite to the Database
We use the functionsqlite3.connectto connect to the database. We can use the argument ":memory:" to create a temporary DB in the RAM or pass the name of a file to open or create it.
# Create a database in RAM
db
=
sqlite3
.
connect
(
':memory:'
)
# Creates or opens a file called mydb with a sqlite3 DB
'data/mydb'
)
|
When we are done working with the DB we need to close the connection:
Creating (CREATE) and Deleting (DROP) Tables
In order to make any operation with the database we need to get a cursor object and pass the sql statements to the cursor object to execute them. Finally it is necessary to commit the changes. We are going to create a users table with name,phone,email and password columns.
4
5
6
7
# Get a cursor object
cursor
db
cursor
)
cursor
execute
'''
@H_ 502_206@
CREATE TABLE users(id INTEGER PRIMARY KEY,name TEXT,
phone TEXT,email TEXT unique,password TEXT)
'''
)
db
commit
)
|
To drop a table:
# Get a cursor object
)
cursor
'''DROP TABLE users'''
)
db
)
Please note that the commit function is invoked on the db object,not the cursor object. If we typecursor.commitwe will getAttributeError: 'sqlite3.Cursor' object has no attribute 'commit'
Inserting (INSERT) Data into the Database
To insert data we use the cursor to execute the query. If you need values from Python variables it is recommended to use the "?" placeholder. Never use string operations or concatenation to make your queries because is very insecure. In this example we are going to insert two users in the database,their information is stored in python variables.
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
cursor
)
name1
'Andres'
phone1
'3366858'
email1
'user@example.com'
# A very secure password
password1
'12345'
name2
'John'
phone2
'5557241'
email2
'johndoe@example.com'
password2
'abcdef'
# Insert user 1
cursor
'''INSERT INTO users(name,email,password)
VALUES(?,?,?)'''
,
(
name1
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
phone1
email1
password1
)
)
print
'First user inserted'
)
# Insert user 2
cursor
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,password)
(
name2
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
phone2
email2
password2
)
'Second user inserted'
)
db
)
|
The values of the Python variables are passed inside a tuple. Another way to do this is passing a dictionary using the ":keyname" placeholder:
2
3
cursor
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,password)
@H_940_ 502@VALUES(:name,:phone,:email,:password)'''
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
{
'name'
:
name1
'phone'
:
phone1
'email'
:
email1
'password'
:
password1
}
)
|
If you need to insert several users useexecutemanyand a list with the tuples:
4
5
users
[
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
Crayon-h" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(0,
(
name3
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
phone3
email3
password3
]
executemany
''' INSERT INTO users(name,password) VALUES(?,224)!important">
users
)
db
)
|
If you need to get the id of the row you just inserted uselastrowid:
2
id
cursor
lastrowid
'Last row id: %d'
%
id
)
|
Retrieving Data (SELECT) with sqlite
To retrieve data,execute the query against the cursor object and then usefetchone()to retrieve a single row orfetchall()to retrieve all the rows.
cursor
'''SELECT name,phone FROM users'''
)
user1
fetchone
)
#retrieve the first row
(
user1
[
0
]
#Print the first column retrieved(user's name)
all_rows
fetchall
)
for
row
in
all_rows
:
# row[0] returns the first column in the query (name),row[1] returns email column.
'{0} : {1},{2}'
.
format
(
row
row
[
1
[
2
)
The cursor object works as an iterator,invokingfetchall()automatically:
cursor
)
cursor
:
Crayon-h" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(0,row[1] returns email column.
)
To retrive data with conditions,use again the "?" placeholder:
user_id
3
Crayon-line
Crayon-striped-line" id="
Crayon-5337a561371a2414410155-2" style="font-family:inherit; border:0px; padding:0px 5px; margin:0px; height:inherit; background-color:rgb(249,phone FROM users WHERE id=?'''
(
user_id
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
)
user
)
Updating (UPDATE) and Deleting (DELETE) Data
The procedure to update or delete data is the same as inserting data:
10
11
# Update user with id 1
newphone
'3113093164'
userid
1
'''UPDATE users SET phone = ? WHERE id = ? '''
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
(
newphone
userid
)
# Delete user with id 2
delete_userid
2
cursor
'''DELETE FROM users WHERE id = ? '''
(
delete_userid
)
db
)
|
Using sqlite Transactions
Transactions are an useful property of database systems. It ensures the atomicity of the Database. Usecommitto save the changes:
cursor
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
)
db
#Commit the change
Orrollbackto roll back any change to the database since the last call tocommit:
cursor
Crayon-sy" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(13,
)
# The user's phone is not updated
rollback
)
Please remember to always callcommitto save the changes. If you close the connection usingcloseor the connection to the file is lost (maybe the program finishes unexpectedly),not committed changes will be lost.
sqlite Database Exceptions
For best practices always surround the database operations with a try clause or a context manager:
8
9
18
19
sqlite3
#Import the sqlite3 module
try
:
# Creates or opens a file called mydb with a sqlite3 DB
db
)
# Get a cursor object
cursor
)
# Check if table users does not exist and create it
cursor
'''CREATE TABLE IF NOT EXISTS
users(id INTEGER PRIMARY KEY,phone TEXT,password TEXT)'''
)
# Commit the change
db
)
# Catch the exception
except
Exception
as
e
:
# Roll back any change if something goes wrong
)
raise
e
finally
:
# Close the db connection
)
|
In this example we used a try/except/finally clause to catch any exception in the code. Thefinallykeyword is very important because it always closes the database connection correctly. Please refer to thisarticleto find more about exceptions. Please take a look to:
# Catch the exception
:
e
This is called a catch-all clause,This is used here only as an example,in a real application you should catch a specific exception such asIntegrityErrororDatabaseError,for more information please refer toDB-API 2.0 Exceptions.
We can use the Connection object as context manager to automatically commit or rollback transactions:
14
name1
'Andres'
phone1
'3366858'
email1
'user@example.com'
# A very secure password
password1
'12345'
:
with
db
:
Crayon-h" style="font-family:inherit; height:inherit; font-size:inherit!important; line-height:inherit!important; font-weight:inherit!important; color:rgb(0,password)
)
.
IntegrityError
:
'Record already exists'
)
:
)
|
In the example above if the insert statement raises an exception,the transaction will be rolled back and the message gets printed; otherwise the transaction will be committed. Please note that we callexecuteon thedbobject,not thecursorobject.
sqlite Row Factory and Data Types
The following table shows the relation between sqlite datatypes and Python datatypes:
-
Nonetype is converted toNULL
-
inttype is converted toINTEGER
-
floattype is converted toREAL
-
strtype is converted toTEXT
-
bytestype is converted toBLOB
The row factory classsqlite3.Rowis used to access the columns of a query by name instead of by index:
8
db
)
.
row_factory
Row
cursor
)
)
:
# row['name'] returns the name column in the query,row['email'] returns email column.
[
'name'
'email'
'phone'
)
)
|
via:http://www.pythoncentral.io/introduction-to-sqlite-in-python/
原文链接：https://www.f2er.com/sqlite/200657.html
