create table

#!/usr/bin/python

import sqlite3

connection = sqlite3.connect('database.db')

# initialize the table
with open('schema.sql') as f:
    connection.executescript(f.read())

# load the table
cur = connection.cursor()

companies = [
('ACME INC', 'c2c',38,20210601,00000000),
('KEEBLER', 'c2c',40,20210501,00000000)
]

projects = [
('LDAP Integration', 'bleh\nbleh\nbleh',1,20211101,20211213,6.5,2,0,'Planning','bleh bleh notes')
]


contacts = [
('Dana','Smith','2','user1@foo.com','123-123-1234','N/A','Manager','Carmen\'s Boss','N/A'),
('Carmen','Ramirez','2','user2@foo.com','123-123-1235','N/A','Manager','Jame\'s Manager,'N/A')
]

cur.executemany("INSERT INTO COMPANIES VALUES (?, ?,?,?,?);",companies);
cur.executemany("INSERT INTO CONTACTS VALUES (?,?,?,?,?,?,?,?,?,?);",contacts);""
cur.executemany("INSERT INTO PROJECTS VALUES (?,?,?,?,?,?,?,?,?,?);",projects);""



connection.commit()
connection.close()

schema.sql

DROP TABLE IF EXISTS COMPANIES;
CREATE TABLE COMPANIES (
    ID INT PRIMARY KEY  NOT NULL,
    NAME        TEXT    NOT NULL,    -- ACME INC
    C2CORW2     TEXT    NOT NULL,    -- C2C or W2
    HOURLY      INT,                 -- 72
    STARTDATE   CHAR(8),             -- YYYYMMDD
    STOPDATE    CHAR(8)              -- YYYYMMDD
);

DROP TABLE IF EXISTS CONTACTS;
CREATE TABLE CONTACTS (
    ID INT PRIMARY KEY       NOT NULL,
    FNAME        TEXT        NOT NULL,  -- Dana
    LNAME        TEXT        NOT NULL,  -- Smith
    COMPANY      CHAR(64),              -- ACHEME INC
    EMAIL        CHAR(64),              -- user@foo.com
    PHONE1       TEXT,                  -- 123-123-1234
    PHONE2       TEXT,                  -- 123-123-1234
    ROLE         CHAR(64),              -- Manager, Worker, Recruiter, Finance, HR, Peer
    TITLE        CHAR(64),              -- Carmen's Manager
    MANAGER      INT,                   -- links to some other person
    NOTES        TEXT);

DROP TABLE IF EXISTS PROJECTS;
CREATE TABLE PROJECTS (
    ID INT PRIMARY KEY       NOT NULL,
    TITLE        CHAR(64)    NOT NULL,   -- LDAP Integration
    CRITERIA     TEXT        NOT NULL,   -- checklist, several lines
    OWNERIS      INT,                    -- assign it to someone to MANAGE it
    ASSIGNED     CHAR(8),                -- YYYYMMDD
    DUEDATE      CHAR(8),                -- YYYYMMDD
    ESTHOURS     FLOAT,                  -- 6.5
    PRIORITY     INT,                    -- 0-9
    PREWORK      INT,                    -- Some other project ID or 0
    STATUS       CHAR(12),               -- Planning, Assigned, Working, Waiting, Complete
    NOTES        TEXT);

DROP TABLE IF EXISTS SUBPROJECTS;
CREATE TABLE SUBPROJECTS (
    ID INT PRIMARY KEY   NOT NULL,
    TITLE        TEXT    NOT NULL,    -- Dana
    DESCRIPTION  TEXT    NOT NULL,    -- bleh bleh we did xyz
    HURDLES      TEXT,                -- I need help with bleh bleh
    DATENOW      CHAR(64),            -- YYYYMMDD
    OWNER        CHAR(64),            -- user@foo.com
    NOTES        TEXT);

DROP TABLE IF EXISTS ACTVITIES;
CREATE TABLE ACTVITIES (
    ID INT PRIMARY KEY   NOT NULL,
    TITLE        TEXT    NOT NULL,   -- Dana
    PROJECT      INT     NOT NULL,   -- Some Parent Project
    CRITERIA     TEXT    NOT NULL,   -- checklist, several lines
    OWNER        INT,                -- assign it to someone to DO it
    ASSIGNED     CHAR(8),            -- YYYYMMDD
    DUE          CHAR(8),            -- YYYYMMDD
    FOLLOWUP     CHAR(8),            -- YYYYMMDD
    ESTHOURS     FLOAT,              -- 6.5
    PRIORITY     INT,                -- 0-9
    PREWORK      INT,                -- Some other task ID or 0
    STATUS       CHAR(12),           -- Planning, Assigned, Working, Waiting, Complete
    NOTES        TEXT);