Create table with sqlite

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

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

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.