Python and SQLite

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

1. id = a unique identifier for each person.
2. firstname = the first name of the person.
3. lastname = the lastname of the person.
4. email = the email address of the person.
5. phone = the phone number of the person.

On the data types

1. integer = a whole number (1, 2, 3 ... ) with no decimals
2. 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	Email	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 you get the following results:

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: