Connecting Python to a SQLite database.
WHAT WE'LL DO
1. Create the SQLite database.
2. Create a database table.
3. Put data in the table.
4. Write a Python script to display the data from the SQLite database.
How you use the data in a program is a different topic.
1. CREATE THE SQLITE DATABASE
A SQLite database is a file. Run the following command:
sqlite3 mydatabase.db
You'll land at the SQLite prompt. At this point, nothing has happened. No database file has been created until you create an object, such as a table.
2. CREATE A DATABASE TABLE
At the sqlite> prompt, run the following command to create a table called person.
create table person (
id integer primary key autoincrement,
firstname varchar(64) not null,
lastname varchar(64) not null,
email varchar(128),
phone varchar(32)
);
 |
Notes:
On the data fields
- id = a unique identifier for each person.
- firstname = the first name of the person.
- lastname = the lastname of the person.
- email = the email address of the person.
- phone = the phone number of the person.
On the data types
- integer = a whole number (1, 2, 3 ... ) with no decimals
- varchar = a "string" of characters. The number in parenthesis is the length of the string
3. PUT DATA IN THE DATABASE
We're going to add the following four people.
| ID | First Name | Last Name | Phone | |
|---|---|---|---|---|
| 1 | John | Jones | jj@email.com | +3.456.1234 |
| 2 | Peter | Panda | pete@email.com | 123.342312 |
| 3 | Sally | Smooth | s.smooth@earth.world | (0)567.123 |
| 4 | Jane | Jet | jj@spacetechnology.in | +(2)-666-889977 |
insert into person (firstname, lastname, email, phone) values
("John", "Jones", "jj@email.com", "+3.456.1234"),
("Peter", "Panda", "pete@email.com", "123.342312"),
("Sally", "Smooth", "s.smooth@earth.world", "(0)567.123"),
("Jane", "Jet", "jj@spacetechnology.in", "+(2)-666-889977");4. WRITE A PYTHON SCRIPT TO DISPLAY THE DATA FROM THE SQLITE DATABASE
The SQL command we need to use to fetch the data is:
select id, firstname, lastname, email phone from person;
And now for the Python code...
#!/bin/env python
import sqlite3
db_connection = sqlite3.connect("mydatabase.db")
db_connection.row_factory = sqlite3.Row
db_cursor = db_connection.cursor()
sql_statement = """
select id, firstname, lastname, email, phone from person
"""
db_cursor.execute(sql_statement)
data = db_cursor.fetchall()
for row in data:
print(row["id"], row["firstname"], row["lastname"], row["email"], row["phone"])
db_connection.close()
If you write that in a file called
data.py, you can run it using the command:python data.py
And you'll get the following results:
