SQL within Python

Python can be used in conjunction with SQL. For convenience we will use SQLite3 as the SQLite3 module is included in the Python standard library.

Setting up

First we use the sqlite3.connect() function to connect to the database, specifying the database name firsttest.db can be used to either create or open the database. Our SQL statements will be passed to the cursor object to be executed. So we need to specify the database that the cursor refers to using the cursor() function. Now we are set up we can write our SQL functions. 

import sqlite3

conn = sqlite3.connect('firsttest.db')
c = conn.cursor()

 

Creating databases and tables

First we use the sqlite3.connect() function to connect to the database, specifying the database name firsttest.db can be used to either create or open the database. Our SQL statements will be passed to the cursor object to be executed. So we need to specify the database that the cursor refers to using the cursor() function. Now we are set up we can write our SQL functions. To create our table we will use the maketable() function. As shown we will create a cars table which includes the car name, age colour and price.

def maketable():
    c.execute("CREATE TABLE cars(name TEXT, age INT, colour TEXT, price INT)")


Adding data to the database

We can insert data into our table using '?' placeholders to refer to Python variables. We then save our changes using the commit() function.

name1 = 'Audi'
age1 = 4
colour1 = 'Blue'
price1 = 20000

def adddata():
    c.execute("INSERT INTO cars(name, age, colour, price) VALUES(?,?,?,?)",
              (name1, age1, colour1, price1))
    conn.commit()


Adding multiple rows

To insert several rows of data we can use tuples and the executemany() function.

my_cars = [
    ('Ford', 7, 'Green', 3000),
    ('Toyota', 2, 'Red', 12000),
    ('BMW', 7, 'Black', 3000)
]

def addmany():
    c.executemany("INSERT INTO cars(name, age, colour, price) VALUES(?,?,?,?)", my_cars)
    conn.commit()


Retrieving data

Here are two examples to get different data from our table.
In both functions we use the fetchone() function to get each row in the cars database one by one. We access this data from the while loop. Once the last row has been read the loop is stopped.
We then use the print function to display our data.


def select1():
    """c.execute("SELECT name, price FROM cars WHERE price > 10000")
    rows = c.fetchall()
    for row in rows:
        print row
    """
    c.execute("SELECT * FROM cars")
    while True:
        row = c.fetchone()

        if row == None:
            break
        print row[0], row[1], row[2], row[3]
        
def select2():
    c.execute("SELECT * FROM cars WHERE name = 'Ford'")
    while True:
        row = c.fetchone()

        if row == None:
            break
        print row[0], row[1], row[2], row[3]

 

Updating and deleting data

Updating and deleting is done the same way as adding data. We simply include the WHERE clause in our SQL statement to specify the data we want to work on.


def update():
    c.execute("UPDATE cars SET price = 15000 WHERE name = 'Audi'")    
    conn.commit()    

def delete():
    c.execute("DELETE FROM cars WHERE colour = 'Black'")
    conn.commit()