SQL¶
Definition¶
SQL stands for Structured Query Language. Python comes with SQLite3 as part of its standard library.
SQL is a language for communicating with databases. It allows us to create tables and also to insert, delete and search for records.
This page looks at how you can set up a database using SQLite3 in Python and some simple SQL statements you can use.
Easy example¶
import sqlite3
db = sqlite3.connect('example.db')
cursor = db.cursor()
cursor.execute('''DROP TABLE students''')
cursor.execute('''CREATE TABLE IF NOT EXISTS students(id INTEGER, name TEXT, age INTEGER)''')
cursor.execute('''INSERT INTO students(id, name, age) VALUES (1, 'John', 15)''')
cursor.execute('''INSERT INTO students(id, name, age) VALUES (2, 'Amy', 16)''')
cursor.execute('''INSERT INTO students(id, name, age) VALUES (3, 'Janice', 14)''')
db.commit()
cursor.execute('''SELECT * FROM students''')
for row in cursor:
print(row)
db.close()
Note
There is a lot to consider in this example. While it may look difficult, it really isn’t. Generally, most of the effort in programming a database will be spent querying it. The line of code cursor.execute('''SQL QUERY HERE''') is all you need to use queries with a database.
(1, 'John')
(2, 'Amy')
(3, 'Janice')
Syntax¶
There is a lot of syntax for using a database. To make it easier to understand, the above example is explained here line by line.
import sqlite3 #imports the sqlite3 library so that you can use it
db = sqlite3.connect('example.db') #Connect to the database stored in the file example.sql
#The file will be created if it doesn't already exist
cursor = db.cursor() #The cursor will store the results of any query
cursor.execute('''DROP TABLE students''') #We can use this to delete a table completely
cursor.execute('''CREATE TABLE IF NOT EXISTS students(id INTEGER, name TEXT, age INTEGER)''') #Create a table if it doesn't already exist. The table will contain two fields 'id' and 'name'. The datatypes are INTEGER and TEXT
cursor.execute('''INSERT INTO students(id, name, age) VALUES (1, 'John', 15)''') #Create a new record and put the integer 1 into 'id', the text 'John' into 'name' and the integer 15 into age
cursor.execute('''INSERT INTO students(id, name, age) VALUES (2, 'Amy', 16)''') #Create another record for Amy
cursor.execute('''INSERT INTO students(id, name, age) VALUES (3, 'Janice', 14)''') #Create another record for Janice
db.commit() #Commit the changes to the database (this means store them permanently)
cursor.execute('''SELECT * FROM students''') #Search for all students in the database - the * means all fields
for record in cursor: #For each record print it on the screen. Each record will be stored as a tuple.
print(record)
db.close() #Close the connection to the database
Examples¶
The following examples consider that you already have the database built as in the example above. They only look at the queries.
You should change the line cursor.execute('''SELECT * FROM students''') above to whatever is in the example below to see it working.
Example 1 - Insert a record¶
cursor.execute('''INSERT INTO students(id, name, age) VALUES (4, 'Elizabeth', 15)''')
A new record is added to the database with the values id=4, name=’Elizabeth’ and age=15.
Example 2 - Update a record¶
cursor.execute('''UPDATE students SET age = 15 WHERE ID = 3''')
The record for Janice (with ID = 3) will be updated so that the age = 15.
Example 3 - Delete a record¶
cursor.execute('''DELETE FROM students WHERE ID = 3''')
The record for Janice (with ID = 3) will be deleted.
Example 4 - Select the names and ages of students who are 15 or over¶
cursor.execute('''SELECT name, age FROM students WHERE age >= 15''')
('John', 15)
('Amy', 16)
Example 5 - Select the names and ages of students who are 15 or over¶
cursor.execute('''SELECT name, age FROM students WHERE age >= 15''')
('John', 15)
('Amy', 16)
Example 6 - Select the names of students that start with the letter J¶
cursor.execute('''SELECT name FROM students WHERE name LIKE 'J%'''')
('John',)
('Janice',)
Note
The % symbol here is known as a wildcard. It means match the % to one or more characters.
Example 7 - Select all fields where the name is Amy or the name is John¶
cursor.execute('''SELECT * FROM students WHERE name='Amy' OR name='John' ''')
(1, 'John', 15)
(2, 'Amy', 16)
Note
The * symbol here is known as a wildcard. It means find all the fields for each record that matches.
Example 8 - Accessing data in the records to improve their output¶
This is a complete example.
import sqlite3
db = sqlite3.connect('example.db')
cursor = db.cursor()
cursor.execute('''DROP TABLE students''')
cursor.execute('''CREATE TABLE IF NOT EXISTS students(id INTEGER, name TEXT, age INTEGER)''')
cursor.execute('''INSERT INTO students(id, name, age) VALUES (1, 'John', 15)''')
cursor.execute('''INSERT INTO students(id, name, age) VALUES (2, 'Amy', 16)''')
cursor.execute('''INSERT INTO students(id, name, age) VALUES (3, 'Janice', 14)''')
db.commit()
cursor.execute('''SELECT * FROM students''')
for row in cursor:
studentID = row[0]
name = row[1]
age = row[2]
print('--------Student record---------')
print('Student number: ' + str(studentID))
print('name: ' + name)
print('age: ' + str(age))
print('-------------------------------\n')
db.close()
--------Student record---------
Student number: 1
name: John
age: 15
-------------------------------
--------Student record---------
Student number: 2
name: Amy
age: 16
-------------------------------
--------Student record---------
Student number: 3
name: Janice
age: 14
-------------------------------
Key points¶
Hint
The main use for SQL is to do queries. Generally, these are very simple in their syntax. For example: SELECT * FROM tableName WHERE criteria.
Note
To sort by a column use an SQL query like this: SELECT * FROM tableName ORDER BY lastName ASC. ASC means sort ascending. Use DESC for descending.
Note
The SQL database should be stored with one of the following file extensions: .db, .sqlite, .sqlite2, db3.
