Using SQLite from Python

Here is a simple example of using sqlite from Python:

1. Import the database API
2. Connect to an existing database. If no database exists, the connect method will create one.
3. Instantiate a cursor which acts somewhat like an iterator.
4. Use the execute method of the cursor to issue SQL commands:
   1. create a table
   2. insert values into a table
   3. select data from a table
5. Use the fetchall method to return rows currently selected.
6. Iterate through the returned sequence of rows. Each row prints as a tuple of field values.
7. The other thing to look at is the description attribute of the cursor. This is useful for determining the names of the fields in the database, in cases where the table description has not been provided. It’s a bit kludgy. The Python Library Reference sayeth This read-only attribute provides the column names of the last query. To remain compatible with the Python DB API, it returns a 7-tuple for each column where the last six items of each tuple are None.

    import sqlite3
    db = sqlite3.connect("yada.sqlite")
    iterator = db.cursor()
    iterator.execute("create table donuts (customer, glazed, cake, due)")
    iterator.execute("insert into donuts values ('jeff', 10, 10, 30)")
    iterator.execute("select * from donuts")
    for customer in iterator.fetchall():
        print(customer)
    iterator.execute("insert into donuts values ('kevin', 13, 13, 60)")
    iterator.execute("select * from donuts")
    for customer in iterator.fetchall():
        print(customer)