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
sqlite3 mydatabase.db
2. CREATE A DATABASE TABLE
create table person (
id integer primary key autoincrement,
firstname varchar(64) not null,
lastname varchar(64) not null,
email varchar(128),
phone varchar(32)
);
- 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.
- 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
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
select id, firstname, lastname, email phone from person;
#!/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()
data.py
, you can run it using the command:python data.py