First, let us take some time to familiarize with the steps necessary to create a new database from Python.
Enter the following code and run it (It is also available as taskdb1.py).
Chapter4/taskdb1.py
import sqlite3 database=':memory:' connection = sqlite3.connect(database) cursor=connection.executescript(''' create table if not exists task ( task_id integer primary key autoincrement, description, duedate, completed, user_id ); ''') connection.commit() sql = '''insert into task (description,duedate,completed,user_id) values(?,?,?,?)''' cursor.execute(sql,('work' ,'2010-01-01',None,'alice')) cursor.execute(sql,('more work' ,'2010-02-01',None,'alice')) cursor.execute(sql,('work' ,'2010-03-01',None,'john')) cursor.execute(sql,('even more work','2010-04-01',None,'john')) connection.commit() connection.close()
It will create a temporary database in memory and defines a task table. It also populates this table with a number of tasks using INSERT statements.
After establishing a connection to the database, the first task is to create the task table (highlighted). Here we use the executescript() method of the connection object, because this method allows us to pass more than one SQL statement in one go. Here our database schema consists of a single create statement so the execute() method would do just as well, but normally when creating a database, we create a number of tables and then passing all necessary SQL statements together is very convenient.
When you look at the create statement, you may notice it features a if not exists clause. This is completely redundant in this example because a freshly opened in-memory database is always empty, but should our database reside on disk, it might contain all the tables we want already. Once we have created the table, we commit our changes to the database with the commit() method.
The second highlighted line shows how we may create an insert statement that will insert new records in the task table. The values we will insert are placeholders, each represented by a question mark. In the next four lines, we execute this insert statement and supply a tuple of values that will be inserted in place of those placeholders.