Insert Update Delete sqlite queries

You will need to use the sqlite connection code from the previous page. You should add this method:

def db_execute(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

If you have completed the 'Create' tutorial you can just rename the method you added instead. The method to execute is the same for create, insert, delete, and update queries. The code above will create a cursor first, this is essentially an sql command. You can then execute the cursor with the sql passed into the method.

You can use this code by:

    database = "test.db"

    sqlprojects = """ CREATE TABLE IF NOT EXISTS projects (
                id integer PRIMARY KEY,
                name text NOT NULL,
                begin_date text,
                end_date text
                ); """ 

    sqltasks = """CREATE TABLE IF NOT EXISTS tasks (
               id integer PRIMARY KEY,
               name text NOT NULL,
               priority integer,
               status_id integer NOT NULL,
               project_id integer NOT NULL,
               begin_date text NOT NULL,
               end_date text NOT NULL,
               FOREIGN KEY (project_id) REFERENCES projects (id)
                ); """ 
                                 
    # create a database connection
    conn = create_connection(database)
    if conn is not None:
        create_table(conn, sqlprojects)
        create_table(conn, sqltasks)

This uses a variable for the database file, and 2 examples of sql to create 2 different tables. conn is the connection, and it uses the connection method from the previous page. If the conn is successful it passes the sql and the connection to the create table method. The second sql statement also shows how to create a table with a foreign key.