195x8ccZ

1import sqlite3
2from sqlite3 import Error
3
4def create_connection(db_file):
5    """ create a database connection to the SQLite database
6        specified by db_file
7    :param db_file: database file
8    :return: Connection object or None
9    """
10    conn = None
11    try:
12        conn = sqlite3.connect(db_file)
13        return conn
14    except Error as e:
15        print(e)
16
17    return conn
18
19def create_table(conn, create_table_sql):
20    """ create a table from the create_table_sql statement
21    :param conn: Connection object
22    :param create_table_sql: a CREATE TABLE statement
23    :return:
24    """
25    try:
26        c = conn.cursor()
27        c.execute(create_table_sql)
28    except Error as e:
29        print(e)
30
31def get_tables_in_database(file_path):
32    conn = sqlite3.connect(file_path)
33    cursor = conn.cursor()
34    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
35    print(cursor.fetchall())
36
37
38def main():
39    database = r"./generated_files/pythonsqlite.db"
40
41    sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
42                                        id integer PRIMARY KEY,
43                                        name text NOT NULL,
44                                        begin_date text,
45                                        end_date text
46                                    ); """
47
48    sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
49                                    id integer PRIMARY KEY,
50                                    name text NOT NULL,
51                                    priority integer,
52                                    status_id integer NOT NULL,
53                                    project_id integer NOT NULL,
54                                    begin_date text NOT NULL,
55                                    end_date text NOT NULL,
56                                    FOREIGN KEY (project_id) REFERENCES projects (id)
57                                );"""
58
59    # create a database connection
60    conn = create_connection(database)
61
62    # create tables
63    if conn is not None:
64        # create projects table
65        create_table(conn, sql_create_projects_table)
66
67        # create tasks table
68        create_table(conn, sql_create_tasks_table)
69    else:
70        print("Error! cannot create the database connection.")
71    get_tables_in_database(database)
72
73
74if __name__ == '__main__':
75    main()
76
77
78
79