Python SQLite

What is SQLite?

You might have heard of MySQL, it's a type of database technology. It is extremely popular with 100 million downloads (as of 2015). MySQL is quite resource intensive often requiring a whole server just for the database software alone.

SQLite requires no server to actually run the database software. Instead, it's all stored in files. But it is interfaced in a similar to MySQL with exactly the same commands.

SQLite is also used in smartphones to store data for apps.

Setup

sqlite3 is a build in library for Python so no downloading is required.

import sqlite3

SQLite is useful for storing larger data that you couldn't store in lists. For example you might have user accounts.

Python Commands

Make a new file with the following contents making sure to replace **DATABASEFILENAME** with a name of your choice.

import sqlite3                # Import SQLite.

conn = sqlite3.connect("**DATABASEFILENAME**.db") # Open/Create a new database file.
c = conn.cursor()             # Make a new cursor which is used to interface with the file.

##### WRITE MAIN CODE HERE #####

conn.commit()                 # Save the changes made to the database.
                              # Needs to be ran before the program quits.

conn.close()                  # Close the connection with the database.
                              # This needs to be done as good practice.

Make comments with these commands which you will need to use later.

c.execute("**SQL_COMMAND**")                              # Execute a command.

c.execute("SELECT * FROM people WHERE name=?", myvariable) # Execute command with input.

output = c.fetchone()                                     # Get the output of the
                                                          # executed command and store
                                                          # in 'output' variable

Tables

In SQL, tables are what data inside the database is stored in. This is what a SQL table might look like graphically:

To make a new table you would run this command:

CREATE TABLE **table_name** (**column_name** **data_type**, **column_name**, **data_type**)

We are going to make a simple program to store information about people. Call your table something appropriate and change the column name to what you want to store (e.g name, email, favourite colour). Change data type to any of:

text, number, real (decimal numbers).

Inserting Data

INSERT INTO **table_name** (**column_name_for_value_1**, **column_name_for_value_2**)
VALUES (**value_1**, **value_2**)

Deleting Data

# Python Example.
# For security reasons please make sure you do this when adding changing values:

name = "Fred"
email = "fred@hotmail.com"
c.execute("INSERT INTO people (name, email) VALUES (?, ?)", [name, email])

### NOT ###

name = "Fred"
email = "fred@hotmail.com"
c.execute("INSERT INTO people (name, email) VALUES (" + name + ", " + email + ")")

Deleting Data

/* for example if you wanted to delete a person you would have
   column_name as "name" and a_value as "Fred" to delete the
   entry for Fred in the database */

DELETE FROM **table_name** WHERE **column_name** = **a_value**

Retrieving Data

/* GET EVERY COLUMN AND ROW IN THE DATABASE */
SELECT * FROM **table_name**
SELECT * FROM people

/* GET EVERY ROWS VALUE FOR COLUMN_NAME */
SELECT **column_name** FROM **table_name**
SELECT name FROM people

/* SEARCH FOR A ROW AND FIND VALUES */
SELECT * FROM **table_name** WHERE **condition**
SELECT * FROM people WHERE name="Fred"     /* Only get the rest of the entry
                                              for Fred */
SELECT email FROM people WHERE name="Fred" /* Get Fred's email address */

Have a Try

Firstly, try some of the commands to understand the outputs.

Then make a Python program that can:

• Make a new database
• Make a new table
• Store user inputted data into the table
• Show all of the data in the table
• Allow you to search for somebody

Next Week

Next week, by request, we will have another shot at Linux and start with simple commands for manipulating files, etc.. then we will move onto more complicated scripts and the filesystem.