SQLite With Python Tutorial
SQLite With Pyhton Tutorial
1. What’s SQLite
SQLite is a small database with high effeciency. When your application is under the development stage, SQLite is a handy tool for you. And SQLite is the common selection of WebSQL. Pyhton embraces it tightly.
2. Python with SQLite
Python has supporting APIs for SQLite via
In this article, we’ll learn how to use this module to manipulate SQLite databases.
3. Creating Database
3.1 Creating a Database on Disk
We created a table on disk. Putting together with
3.2 Creating a Memory Database
We can use
You don’t have to drop the table before creating the table while using an in-memory database. When an in-memory database is closed, its data is gone away, nothing left. Yet it’s fast and convenient to learn practice our database skills on fly.
The shortage of using an in-memory database is that you’ll have to do your all stuffs, such as creating tables, inserting data, and fetching data, in one session just before the
4 Insterting Data
4.1 A Single Line Straight Inserting
The following is just the way of single line straight inserting.
4.2 Using ? Placeholder
This style is somewhat like the previous example code. However, there are differences between them.
The synopsis of
Parameter
The example in this section use the second style:
Using this style requires that we put the
4.3 Using Variable
We can use a variable of type
So why is tuple? JavaScript use arrays to provide such arguments in WebSQL. Compared with an array, the presentation of tuple
4.4 Multiple Insertions At a Time
We can insert multiple records like this:
Using the placeholder, we can insert multyple records on fly by invoking
We invoke
Variable
Since we inserted 3 records through one invokation of
5. Fetching Data
5.1 Preparing The Data
We are now concerning about only fetching data, in avoid of creating the in-memory database every time, we create an on-disk database and insert some fake data into it this time for later use.
5.2 Starting Fetching Data
To fetch records, connect to the dabase again and excute
Since we are not updating the database, we don’t have to invoke
After executing the query statement, we invoke
As you can see, the records returned is a list, with 3 matching elements each in style of tuple.
What columns are selected and returned?
5.2 Excuting a More Specific Query
To limit the records, we can narrow the range using the
Wow, stop doing this! Don’t put the key words directly in your query string. It’s under the danger of SQL injection attack. Instead, use the
Note on the presentation of
5.3 Selecting Range of Records Returned
In additon with
Although the recordset for
And
The argument
5.3 What Is Returned?
You may wonder what is returned by the query. Let’s go through it.
With a tuple, we can do something such as:
What about the record’s name? We know the column names of the database are “id”, “name”, and “age”. So can each column be accessed to via it’s name like this:
The answer is No, because
However, we can turn this tuple into a
First, the code
turns the tuple returned into a
Then,
5.4 Dealing With Multiple Rows
That’s easy enough.
6. Executescript
In the development cycle, it may be very convenient to execute all statements in a bulk.
With this style, all fake data would be generated in one place using the native sql statements.
7. Shortcut Methods
To those who are not much familiar with database concepts,
We can even do it in a much shorter way.
No
8. Conclusion
Prgramming in Python’s database is somewhat different from the pure SQL manipulation. However, the
9. References DB-API 2.0 interface for SQLite databases
1. What’s SQLite
SQLite is a small database with high effeciency. When your application is under the development stage, SQLite is a handy tool for you. And SQLite is the common selection of WebSQL. Pyhton embraces it tightly.
2. Python with SQLite
Python has supporting APIs for SQLite via
sqlite3
module. It’s compliant with DB-API 2.0. In this article, we’ll learn how to use this module to manipulate SQLite databases.
3. Creating Database
3.1 Creating a Database on Disk
import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS students")
cur.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
conn.commit()
conn.close()
We created a table on disk. Putting together with
DROP TABLE
and the following statement CREATE TABLE
means we can re-create the specific table anytime. 3.2 Creating a Memory Database
We can use
:memory:
instead of database name to create database in RAM. import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
conn.commit()
conn.close()
You don’t have to drop the table before creating the table while using an in-memory database. When an in-memory database is closed, its data is gone away, nothing left. Yet it’s fast and convenient to learn practice our database skills on fly.
The shortage of using an in-memory database is that you’ll have to do your all stuffs, such as creating tables, inserting data, and fetching data, in one session just before the
connection
is closed. 4 Insterting Data
4.1 A Single Line Straight Inserting
The following is just the way of single line straight inserting.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
cur.execute("INSERT INTO students (id, name, age) VALUES (1, 'Tom', 25)")
conn.commit()
conn.close()
4.2 Using ? Placeholder
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
cur.execute("INSERT INTO students (id, name, age) VALUES (?, ?, ?)", (1, 'Tom', 25))
conn.commit()
conn.close()
This style is somewhat like the previous example code. However, there are differences between them.
The synopsis of
execute()
method of the Cursor
is as followed: execute(sql[, parameters])
Parameter
parameters
is optional. In the example code in 4.1, sql
is "INSERT INTO students (id, name, age) VALUES (1, 'Tom', 25)"
, and parameters
is none. The example in this section use the second style:
execute(sql, parameters)
Using this style requires that we put the
?
s in the sql
string, and provide the actual data in a tuple: (1, 'Tom', 25)
. 4.3 Using Variable
We can use a variable of type
tuple
instead of hard-coding of actual values for the optional parameters
. import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
t = (1, 'Tom', 25)
cur.execute("INSERT INTO students (id, name, age) VALUES (?, ?, ?)", t)
conn.commit()
conn.close()
So why is tuple? JavaScript use arrays to provide such arguments in WebSQL. Compared with an array, the presentation of tuple
(1, 2, 3)
is much closer to the native presentation of sql statements. JavaScript has no concepts of tuple
after all. 4.4 Multiple Insertions At a Time
We can insert multiple records like this:
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
cur.execute("INSERT INTO students (id, name, age) VALUES (1, 'Tom', 25)")
cur.execute("INSERT INTO students (id, name, age) VALUES (2, 'Mike', 23)")
cur.execute("INSERT INTO students (id, name, age) VALUES (3, 'John', 27)")
print(cur.rowcount)
conn.commit()
conn.close()
1
Using the placeholder, we can insert multyple records on fly by invoking
executemany()
method. We invoke
cur.execute()
three times individually. cur.rowcount
shows the rows affected for the cur
object. The first 2 ones are overrided with the last one. So actually there are 3 rows affected. import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
students = [
(1, 'Tom', 25),
(2, 'Mike', 23),
(3, 'John', 27)
]
cur.executemany("INSERT INTO students (id, name, age) VALUES (?, ?, ?)", students)
print(cur.rowcount)
conn.commit()
conn.close()
3
Variable
students
is a list, with each element as a tuple for a record. Since we inserted 3 records through one invokation of
cur.execute()
method, cur.rowcount
reveals that there are 3 rows affected in one statement. 5. Fetching Data
5.1 Preparing The Data
We are now concerning about only fetching data, in avoid of creating the in-memory database every time, we create an on-disk database and insert some fake data into it this time for later use.
import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS students")
cur.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
students = [
(1, 'Tom', 25),
(2, 'Mike', 23),
(3, 'John', 27)
]
cur.executemany("INSERT INTO students (id, name, age) VALUES (?, ?, ?)", students)
conn.commit()
conn.close()
5.2 Starting Fetching Data
To fetch records, connect to the dabase again and excute
SELECT
sql statements. import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute("SELECT * FROM students")
print(cur.fetchall())
conn.close()
[(1, 'Tom', 25), (2, 'Mike', 23), (3, 'John', 27)]
Since we are not updating the database, we don’t have to invoke
conn.commit()
method here. After executing the query statement, we invoke
cur.fetchall()
method to print all records returned. As you can see, the records returned is a list, with 3 matching elements each in style of tuple.
What columns are selected and returned?
print(cur.description)
(('id', None, None, None, None, None, None), ('name', None, None, None, None, None, None), ('age', None, None, None, None, None, None))
cur.description
returns the column names of the last query. For each column, there is a tuple with 7 elements and only the first is used to store the column name. 5.2 Excuting a More Specific Query
To limit the records, we can narrow the range using the
WHERE
clause in sql
arguments. import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute("SELECT * FROM students WHERE name = 'Tom'")
print(cur.fetchall())
conn.close()
[(1, 'Tom', 25)]
Wow, stop doing this! Don’t put the key words directly in your query string. It’s under the danger of SQL injection attack. Instead, use the
?
placeholder and provide the key words in the second argument. import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute("SELECT * FROM students WHERE name = ?", ('Tom',))
print(cur.fetchall())
cur.execute("SELECT * FROM students WHERE name = ? AND age = ?", ('Mike',23))
print(cur.fetchall())
conn.close()
[(1, 'Tom', 25)]
[(2, 'Mike', 23)]
Note on the presentation of
('Tom',)
in the first query. Since it’s a tuple, and a tuple with one element requires a ,
be followed. Tuples with more than 2 elements need no such additional ‘,’, and that’s the way of ('Mike,23')
. 5.3 Selecting Range of Records Returned
In additon with
fetchall()
method, we can fetch only one record. import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute("SELECT * FROM students")
print(cur.fetchone())
conn.close()
(1, 'Tom', 25)
Although the recordset for
SELECT * FROM students
is a list with 3 records, the fetchone()
method insures the only top record is returned. And
fetchmany()
: import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute("SELECT * FROM students")
print(cur.fetchmany(2))
conn.close()
[(1, 'Tom', 25), (2, 'Mike', 23)]
The argument
2
is optional, which specifies the size to fetch. If it is not provided, the arraysize
property of the cursor
would be used to specify the size. By defualt, it’s of value 1. 5.3 What Is Returned?
You may wonder what is returned by the query. Let’s go through it.
import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute("SELECT * FROM students ORDER BY id")
result = cur.fetchone()
print(type(result))
print(result)
conn.close()
(1, 'Tom', 25)
fetchone()
method fetches one row from the resultset and we assigned this instance to a variable result
. The we printed the type of it and it’s value. In no suprise, it’s a tuple, with each element representing each selected column value of the record. With a tuple, we can do something such as:
print(len(result)) # length
print(result[0], result[1], result[2], sep=", ") # extract each element
for element in result:
print(element) # iteration
3
1, Tom, 25
1
Tom
25
What about the record’s name? We know the column names of the database are “id”, “name”, and “age”. So can each column be accessed to via it’s name like this:
print(result[id])
The answer is No, because
result
is a tuple, not a hashmap. However, we can turn this tuple into a
sqlite3.Row
class and achieve this goal. import sqlite3
conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("SELECT * FROM students ORDER BY id")
row = cur.fetchone()
# tuple's features are remained
print(type(row))
print(len(row))
print(row[0], row[1], row[2], sep=", ")
for column in row:
print(column)
# new features for Row
print()
print(row.keys(), sep=", ")
for columnName in row.keys():
print(columnName , "=", row[columnName])
conn.close()
3
1, Tom, 25
1
Tom
25
['id', 'name', 'age']
id = 1
name = Tom
age = 25
First, the code
conn.row_factory = sqlite3.Row
turns the tuple returned into a
Row
object. Even so, the features of a tuples are remained so we can treat it as an tuple as before. Then,
row.keys()
returns a list of column names and we can get access to each column using the property names: row["id"]
, row["name"]
and row["age"]
etc. 5.4 Dealing With Multiple Rows
import sqlite3
conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("SELECT * FROM students ORDER BY id")
rows = cur.fetchall()
for record in rows:
print(record["id"], record["name"], record["age"])
conn.close()
1 Tom 25
2 Mike 23
3 John 27
That’s easy enough.
6. Executescript
In the development cycle, it may be very convenient to execute all statements in a bulk.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.executescript("""
CREATE TABLE students (
id,
name,
age
);
-- many other tables can also be created here
-- ...
INSERT INTO students VALUES (1, "Tom", 25);
INSERT INTO students VALUES (2, "Mike", 23);
INSERT INTO students VALUES (3, "John", 27);
""")
conn.commit()
cur.execute("SELECT * FROM students")
print(cur.fetchall())
conn.close()
[(1, 'Tom', 25), (2, 'Mike', 23), (3, 'John', 27)]
With this style, all fake data would be generated in one place using the native sql statements.
7. Shortcut Methods
To those who are not much familiar with database concepts,
Cursor
would bring more confusions rather than clarities. Never mind, you can skip over it. import sqlite3
conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row
result = conn.execute("SELECT * FROM students ORDER BY id")
print(result)
rows = result.fetchall()
for record in rows:
print(record["id"], record["name"], record["age"])
conn.close()
1 Tom 25
2 Mike 23
3 John 27
conn.execute()
method returns a Cursor
. So we can invoke fetchall()
method on it. Whatever, since we use the variable result
to hide the details, it makes us happy. We can even do it in a much shorter way.
import sqlite3
conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row
for record in conn.execute("SELECT * FROM students ORDER BY id"):
print(record["id"], record["name"], record["age"])
conn.close()
1 Tom 25
2 Mike 23
3 John 27
No
result
, no fetchall()
any more. 8. Conclusion
Prgramming in Python’s database is somewhat different from the pure SQL manipulation. However, the
sqlite3
module makes it much easier for us to use. The core of this module provides us a convient way to manipulate database, while giving us much more freedom of combination with SQL recordsets and the programming context. With this in hand, we can program in a object-oriented way rather than the raw data manipulation. 9. References