create table sqlite python

1CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, ...);
2

1
2            
3                
4            
5         import sqlite3
6from sqlite3 import Error
7
8
9def create_connection(db_file):
10    """ create a database connection to the SQLite database
11        specified by db_file
12    :param db_file: database file
13    :return: Connection object or None
14    """
15    conn = None
16    try:
17        conn = sqlite3.connect(db_file)
18        return conn
19    except Error as e:
20        print(e)
21
22    return conn
23
24
25def create_table(conn, create_table_sql):
26    """ create a table from the create_table_sql statement
27    :param conn: Connection object
28    :param create_table_sql: a CREATE TABLE statement
29    :return:
30    """
31    try:
32        c = conn.cursor()
33        c.execute(create_table_sql)
34    except Error as e:
35        print(e)
36
37
38def main():
39    database = r"C:\sqlite\db\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
72
73if __name__ == '__main__':
74    main()Code language: Python (python)

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